Skip site navigation (1) Skip section navigation (2)

Re: limiting number of allowed rows in a table

From: Christopher Browne <cbbrowne(at)acm(dot)org>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: limiting number of allowed rows in a table
Date: 2005-12-28 17:11:30
Message-ID: m3bqz1no3h.fsf@mobile.int.cbbrowne.com (view raw or flat)
Thread:
Lists: pgsql-novice
> On 12/28/05 9:53 AM, "Vanja" <milosevski(at)gmail(dot)com> wrote:
>
>> Can someone please tell me how can I add a constraint which will limit
>> the number of rows that are allowed to be inserted in a table?
>
> I don't think there is such a constraint (but would love to hear
> otherwise).  However, you could place a trigger on a table that does
> a count(*) on the table (which might be time-consuming on a large
> table, but....) and then does whatever you want (ignore the insert,
> put it in another table, or delete an old row before inserting the
> new one).  Why do you want to limit the number of rows?

That strikes me as being a terribly expensive approach.

I'd think that something more along the lines of having a column
defaulting to a sequence value would make more sense, where you'd
constrain that column to the relevant range (e.g. - from 0 to n-1),
and have a uniqueness constraint.

Efficiently finding a "free" entry is then the challenge.  A
thought...

Create a *second* table, which just has the one column, limited to
range [0, n-1].  Fill that table up, at the start.

On the main table, an "on insert" trigger searches the second table
for an entry, grabs it, and deletes it.  An "on delete" trigger
returns entries to the second table when records are deleted.  An "on
update" trigger should prevent users from messing with the "special
column."

Each of those triggers should be of ~ O(1) cost regardless of how many
entries are free.

It would be real nice if selection of candidates from the secondary
table were done in some quasi-randomized fashion so that each
connection wouldn't normally be trying to grab the same tuple...
-- 
"cbbrowne","@","acm.org"
http://linuxdatabases.info/info/
Warning: Dates in calendar are closer than they appear. 

In response to

pgsql-novice by date

Next:From: s anwarDate: 2005-12-28 18:01:47
Subject: Re: Postgres 8.1 choosing the larger index for an index scan
Previous:From: VanjaDate: 2005-12-28 16:35:07
Subject: Re: limiting number of allowed rows in a table

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group