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

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';
>

I believe, you can save one query by replacing 'if exists then update else insert' part with just
'delete unconditionally then insert'

>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';
>
>

... or you could have another sequence (with no limit, and no cycle) to
count the number of inserts - you'd then increment both in the trigger,
and, if the insert count is greater then the limit you'd update, else
insert.

... or you could do it with the single sequence still, if you get rid of
the limit and cycle, and just do
if nextval >= limit then update ... where id = nextval % limit else insert

Dima