Jean-Luc Lachance wrote:
OUCH!!! Do a COUNT(*) on a 10M row table???? Forget it.
Here is a simple solution. Add a SERIAL field to the table. Set the maximum value for that sequence to the number of records you want to keep. Use a before insert trigger to replace the insert with an update if the key already exist.
No need for a cron.
Dennis Gearon wrote:
use a PL/PGSQL function. Just do count(*) to find out how many there are, calculate how many to be deleted, and put a timestamp field in the table. NOW, how to select the correct ones to delete is PROBABLY done by:
DELETE FROM table_in_question WHERE some_primary_key_id IN (SELECT some_primary_key_id FROM table_in_question ORDER BY the_time_stamp_field LIMIT the_qty_to_be_deleted);
More than likely, in a concurrent environment, you will oscillate between:
(the maximum number you want)
and
(the maximum number you want - the maximum current connections).
Unless you so some kind of table locking.
Kirill Ponazdyr wrote:
Hello,
We are currently working on a project where we need to limit number of records in a table to a certain number. As soon as the number has been reached, for each new row the oldest row should be deleted (Kinda FIFO), thus keeping a total number of rows at predefined number.
The actual limits would be anywhere from 250k to 10mil rows per table.
It would be great if this could be achieved by RDBMS engine itself, does Postgres supports this kind of tables ? And if not, what would be the most elegant soluion to achieve our goal in your oppinion ?
Regards
Kirill
---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
