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