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
>