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

> >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
> >);
[snip slower function]
> I believe, you can save one query by replacing 'if exists then
> update else insert' part with just 'delete unconditionally then
> insert'

Which is why I use something similar to the function below.

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

That doesn't help you limit the number of rows in the table though
because what row is going to get "pushed out" of the table? The nifty
thing about using a wrapping sequence is that the id's are sequential
across transactions, which correctly maps to the progression of time,
which obviates the need for relying on any kind of a date column for
doing syslog message ordering.

> ... 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

There's no logic necessary with the above function. You get the next
val in the sequence and update the ID. No need to delete, no nothing
other than VACUUM to remove the dead tuples, which, you have to do
regardless of the method chosen. A DELETE + UPDATE is just a waste of
CPU and IO with the table being MVCC backed. Just UPDATE and be done
with it.

That said, it'd be cool if there was a GLOBAL TEMP table that could be
used for this kinda stuff... not having something WAL backed on this
kind of an application would make things really scream.

-sc

--
Sean Chittenden