From: Daniel CAUNE <d.caune@free.fr>
To: Jeff Frost <jeff@frostconsultingllc.com>
To: pgsql-sql@postgresql.org
Subject: Re: keeping last 30 entries of a log table
Date: Sat, 17 Jun 2006 11:16:13 -0400
Message-ID: <0J10001JS4N1G9G0@VL-MH-MR001.ip.videotron.ca> (raw)
In-Reply-To: <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.
>
Depending on whether id is a kind of auto-incremented column that never cycles, I would suggest something like:
DELETE FROM log
WHERE account_id = 1
AND id < (
SELECT MIN(id)
FROM log
WHERE account_id = 1
ORDER BY timestamp DESC
LIMIT 30);
I think there will be a performance difference with your method when the number of records to be deleted is huge.
--
Daniel
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: d.caune@free.fr, jeff@frostconsultingllc.com
Subject: Re: keeping last 30 entries of a log table
In-Reply-To: <0J10001JS4N1G9G0@VL-MH-MR001.ip.videotron.ca>
* 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