Re: Postgresql "FIFO" Tables, How-To ?

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
>