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 with a 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
>