Re: rotate records

On Mon, Feb 27, 2006 at 07:39:22PM -0800, Natasha Galkina wrote:
> I tried your solutions but still it looks like it doesn't work when I
> delete random records.
[...]
> As you can see the record with value '14' is gone without explicit
> delete, which is not what I expected. Do you have any ideas on how to
> avoid this?

Deletes are indeed a problem with the simple example I posted. It
simply re-uses the next value in the sequence, deleting any row
that currently has that value; it doesn't count the number of rows
and delete the excess ones.

I recall past discussions about mechanisms to limit the number of
rows in a table. Here's a recent message with a suggestion:

http://archives.postgresql.org/pgsql-novice/2005-12/msg00323.php

Before putting more thought into it I'd want to search for other
past discussion. A couple of problems are efficiency and concurrency:
how to quickly know which, if any, old rows to delete, and how to
ensure that inserts in concurrent transactions can't result in the
table having more rows than it should.

--
Michael Fuhr