Re: [GENERAL] Postgresql "FIFO" Tables, How-To ?

[Dmitry Tkach]

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



---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
     subscribe-nomail command to [EMAIL PROTECTED] so that your
     message can get through to the mailing list cleanly