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

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

--
Sean Chittenden