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;

END; -- *blush*

> ' 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;
END;
> ' LANGUAGE 'plpgsql';

-sc
--
Sean Chittenden