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
Date: Wed, 21 Jun 2006 12:49:20 -0500 (CDT)
Message-ID: <Pine.GSO.4.64.0606211227020.20083@coe.tamu.edu> (raw)
In-Reply-To: <Pine.LNX.4.64.0606161924550.27250@glacier.frostconsultingllc.com>
References: <Pine.LNX.4.64.0606161924550.27250@glacier.frostconsultingllc.com>
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: bkim@coe.tamu.edu, jeff@frostconsultingllc.com
Subject: Re: keeping last 30 entries of a log table
In-Reply-To: <Pine.GSO.4.64.0606211227020.20083@coe.tamu.edu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox