From: | "Ben K(dot)" <bkim(at)coe(dot)tamu(dot)edu> |
---|---|
To: | Jeff Frost <jeff(at)frostconsultingllc(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: keeping last 30 entries of a log table |
Date: | 2006-06-21 17:49:20 |
Message-ID: | Pine.GSO.4.64.0606211227020.20083@coe.tamu.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Jeff Frost | 2006-06-21 17:57:24 | Re: keeping last 30 entries of a log table |
Previous Message | Frank Bax | 2006-06-21 15:21:36 | Re: Displaying first, last, count columns |