Re: keeping last 30 entries of a log table

> 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