Re: Postgresql "FIFO" Tables, How-To ?
От | Shridhar Daithankar |
---|---|
Тема | Re: Postgresql "FIFO" Tables, How-To ? |
Дата | |
Msg-id | 3F15C7A9.12666.1E6423D@localhost обсуждение исходный текст |
Ответ на | Postgresql "FIFO" Tables, How-To ? ("Kirill Ponazdyr" <softlist@codeangels.com>) |
Ответы |
Re: Postgresql "FIFO" Tables, How-To ?
Re: Postgresql "FIFO" Tables, How-To ? |
Список | pgsql-general |
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.
В списке pgsql-general по дате отправления: