From: | "scott(dot)marlowe" <scott(dot)marlowe(at)ihs(dot)com> |
---|---|
To: | Shridhar Daithankar <shridhar_daithankar(at)persistent(dot)co(dot)in> |
Cc: | pg_general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Postgresql "FIFO" Tables, How-To ? |
Date: | 2003-07-16 16:13:48 |
Message-ID: | Pine.LNX.4.33.0307161012560.29844-100000@css120.ihs.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Wed, 16 Jul 2003, Shridhar Daithankar wrote:
> 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?
If he only needs an approximate number of rows (i.e. having max +/- 100
rows is ok...) then maybe just use a sequence and delete any rows that
are current_max_seq - max_records???
From | Date | Subject | |
---|---|---|---|
Next Message | Viorel Dragomir | 2003-07-16 16:14:34 | Re: Postgresql "FIFO" Tables, How-To ? |
Previous Message | scott.marlowe | 2003-07-16 16:11:03 | Re: how to get table information from php client ? |