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