From: | Dennis Gearon <gearond(at)cvc(dot)net> |
---|---|
To: | |
Cc: | softlist(at)codeangels(dot)com, pg_general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Postgresql "FIFO" Tables, How-To ? |
Date: | 2003-07-16 16:26:09 |
Message-ID: | 3F157CA1.3020207@cvc.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Jonathan Bartlett | 2003-07-16 16:28:17 | Re: Postgresql "FIFO" Tables, How-To ? |
Previous Message | Ron | 2003-07-16 16:22:07 | COPY problem |