Date: Wed, 21 Jun 2006 12:49:20 -0500 (CDT)
From: "Ben K." <bkim@coe.tamu.edu>
To: Jeff Frost <jeff@frostconsultingllc.com>
cc: pgsql-sql@postgresql.org
Subject: Re: keeping last 30 entries of a log table
In-Reply-To: 
 <Pine.LNX.4.64.0606161924550.27250@glacier.frostconsultingllc.com>
Message-ID: <Pine.GSO.4.64.0606211227020.20083@coe.tamu.edu>
References: <Pine.LNX.4.64.0606161924550.27250@glacier.frostconsultingllc.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed

> I need to write a function which inserts a log entry in a log table and only 
> keeps the last 30 records.  I was thinking of using a subquery similar to the 
> following:
>
> insert into log (account_id, message) values (1, 'this is a test);
> delete from log where account_id = 1 and id not in ( select id from log
>  where account_id = 1 order by timestamp desc limit 30);
>
> I'm wondering if there is a more performance oriented method of doing the 
> delete that I'm not thinking of.


Just for the sake of alternatives -

create sequence cy30 maxvalue 30 cycle;

insert into log values(select generate_series(1,30), 'dummy');
INSERT 0 30

update log set des='....' where account_id=(select nextval('cy30'));
UPDATE 1


There are details to consider I guess. For example what if an update fails 
and the sequence already advanced... Also, since we cycle the id, for 
sorting, we'll need to add timestamp or something similar.

My 2 pence...

P.S.

This A) failed me and I wonder if this is supposed to be so or if it's 
just a place where no one treaded on ??

B) works fine except it doesn't advance the sequence.

A) update  tc set des='b' where id=nextval('cy30')::int;
UPDATE 30

B) update tc set des='c' where id=currval('cy30');
UPDATE 1


Regards,

Ben K.
Developer
http://benix.tamu.edu