Or, you could make a view, or all of your queries have the phrase
'ORDER BY the_time_stamp_field
LIMIT the_qty_to_be_deleted'
in them. That'd be the standard way.
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
>