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