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

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


> It would be great if this could be achieved by RDBMS engine itself, does
> Postgres supports this kind of tables ? And if not, what would be the most
> elegant soluion to achieve our goal in your oppinion ?

First question: no. :)

Second question: two ideas.

I. First idea:

Assuming that it is okay for the tables to run over a little bit, the best
way I can think of is to run a periodic cronjob. Assuming you have an
indexed column "id" which increments on new inserts:

DELETE FROM bigtable WHERE id <
  (SELECT id FROM bigtable ORDER BY id DESC LIMIT 1 OFFSET 100000);

If you don't have a unique incrementing field but have a timestamp:

DELETE FROM bigtable WHERE oid <
  (SELECT oid FROM bigtable ORDER BY in_time DESC LIMIT 1 OFFSET 100000);

This assumes your oid is not about to roll over, and that you have an
index on the oid column, of course.

Running a VACUUM immediately after the DELETE is highly advised.

Even running this job every 10 minutes would probably not be too bad - if
the table is not "full", no harm is done. Deleting and vacuuming for the
rows that have built up in the previous 10 minutes will also not
be too expensive. If it is, decrease the time. Even a cronjob running once
a minute is probably better than a trigger that fires upon very insert,
if the table is very active and getting hundreds or thousands of inserts
per minute.


II. Second idea:

Prepopulate the table, use a wrapping sequence, and a timestamp.

CREATE SEQUENCE mmlog_seq MINVALUE 1 MAXVALUE 500 CYCLE;

CREATE TABLE mmlog (
  id INTEGER NOT NULL,
  message VARCHAR,
  ctime TIMESTAMP WITH TIME ZONE NOT NULL
);

CREATE INDEX mmlog_id ON mmlog(id);
CREATE INDEX mmlog_ctime ON mmlog(ctime);

BEGIN;

- -- Run this next command exactly 500 times:
INSERT INTO mmlog (id,message,ctime) VALUES (nextval('mmlog_seq'),'',now());

COMMIT;

REVOKE INSERT ON mmlog FROM PUBLIC;

To add a new row, run this:

UPDATE mmlog SET message=?, ctime=now() WHERE a = (SELECT nextval('mmlog_seq'));


Voila! A self-limiting table: INSERTING is not allowed, and the oldest record
is always overwritten. Remember to vacuum of course. And ORDER BY on the
ctime field to keep things in the proper order.


- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200307161435

-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html

iD8DBQE/FaEdvJuQZxSWSsgRAs2TAKCi+ss3cGmwYEWU1zl7c6MpT+5RuACfW/K5
SfkKRslsAqMBLL7wLA0Dt7w=
=kUQE
-----END PGP SIGNATURE-----