Re: Postgresql "FIFO" Tables, How-To ?

You could skip the extra select in any case, if you used a boolean column named 'valid_data', and always updated it
witha rue, but prepopulated it with a 'false'. Your selects OUT of the table would use the WHERE clause of 'valid_data'
=TRUE. 
 updating and selecting on a bool would not take much time.

Sean Chittenden wrote:

>>>store 10mil+ syslog messages this might not be the right tool. I'm
>>>just mentioning it because it perhaps the way the rrd keeps track
>>>of wrap-around might be a good way to implement this in postgres.
>>
>>Hmm.  Using the cycling feature of a sequence, couldn't you create a
>>trigger which either inserts (if, e.g., the value of the trigger is
>>not there) or updates (if the value of the trigger is there)?  I'm
>>not sure how to do it efficiently, but I haven't thought about it
>>very much.
>
>
> I use this very approach.
>
> CREATE SEQUENCE syslog_id_seq INCREMENT 1 MINVALUE 1 MAXVALUE 250000 CYCLE;
> CREATE TABLE syslog (
>   id INT NOT NULL,
>   msg TEXT NOT NULL
> );
> CREATE UNIQUE INDEX syslog_id_udx ON syslog(id);
> CREATE FUNCTION syslog_ins(TEXT)
>   RETURNS INT
>   EXTERNAL SECURITY DEFINER
>   AS '
> DECLARE
>     a_msg ALIAS FOR $1;
>     v_id syslog.id%TYPE;
> BEGIN
>     v_id := NEXTVAL(''syslog_id_seq''::TEXT);
>     PERFORM TRUE FROM syslog WHERE id = v_id;
>     IF FOUND THEN
>         UPDATE syslog SET msg = a_msg WHERE id = v_id;
>     ELSE
>         INSERT INTO syslog (id,msg) VALUES (id,msg);
>     END IF;
>
>     RETURN v_id;
> ' LANGUAGE 'plpgsql';
>
> Though this is the inefficient way of doing this.  If you wanted to be
> really slick about it and incur some upfront disk space, populate the
> table with your 250000 rows of bogus data, empty strings, then use the
> following instead to save yourself a SELECT (which is only of use for
> the first 250000 syslog msgs, then it becomes a given after the
> sequence wraps):
>
> CREATE FUNCTION syslog_ins(TEXT)
>   RETURNS INT
>   EXTERNAL SECURITY DEFINER
>   AS '
> DECLARE
>     a_msg ALIAS FOR $1;
>     v_id syslog.id%TYPE;
> BEGIN
>     v_id := NEXTVAL(''syslog_id_seq''::TEXT);
>     UPDATE syslog SET msg = a_msg WHERE id = v_id;
>     RETURN v_id;
> ' LANGUAGE 'plpgsql';
>
>
> You may want to add a time component to the table/function, but I'll
> leave that as an exercise to the reader.  Just make sure you're
> VACUUMing on a regular basis.  :) -sc
>