keeping last 30 entries of a log table

From: Jeff Frost <jeff(at)frostconsultingllc(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: keeping last 30 entries of a log table
Date: 2006-06-17 02:29:31
Message-ID: Pine.LNX.4.64.0606161924550.27250@glacier.frostconsultingllc.com
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.

--
Jeff Frost, Owner <jeff(at)frostconsultingllc(dot)com>
Frost Consulting, LLC http://www.frostconsultingllc.com/
Phone: 650-780-7908 FAX: 650-649-1954

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message sathish kumar shanmugavelu 2006-06-17 03:53:17 Re: concurrency problem
Previous Message PFC 2006-06-16 20:21:06 Re: sessions and prepared statements