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

Good idea!

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
timestampfield 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
>