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

On 16 Jul 2003 at 17:59, 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 ?

It is practically impossible due to concurrency limitation unless you 
explicitly serialize everything which might be a bad idea.

I think it is doable. Create a normal table 't' and write a before insert 
trigger. Create another table 'control' which contains the limit value and oid 
of last row deleted. In the before insert trigger, do a select for update on 
table 'control' so that no other transaction can update it. Proceed to 
insertion/deletion in  table 't'.

It would be a bad idea to update the control table itself. You need to release 
the lock with transaction commit.( I hope it gets released with the commit) If 
you update control table, you would generate a dead row for every insertion in 
main table which could be a major performance penalty for sizes you are talking 
about.

Frankly I would like to know fist why do you want to do this. Unless there are 
good enough practical reasons, I would not recommend this approach at all. Can 
you tell us why do you want to do this?

Bye
 Shridhar

--
Lieberman's Law:	Everybody lies, but it doesn't matter since nobody listens.