From: Bruno Wolff III <bruno@wolff.to>
To: David Garamond <lists@zara.6.isreserved.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: constraint and ordered value
Date: Thu, 29 Dec 2005 01:16:25 -0600
Message-ID: <20051229071625.GA2815@wolff.to> (raw)
In-Reply-To: <43B17F52.7000305@zara.6.isreserved.com>
References: <43B17F52.7000305@zara.6.isreserved.com>
On Wed, Dec 28, 2005 at 00:52:18 +0700,
David Garamond <lists@zara.6.isreserved.com> wrote:
> Is it possible to use only CHECK constraint (and not triggers) to
> completely enforce ordered value of a column (colx) in a table? By that
> I mean:
>
> 1. Rows must be inserted in the order of colx=1, then colx=2, 3, and so on;
>
> 2. When deleting (or updating), "holes" must not be formed, e.g. if
> there are three rows then row with colx=3 must be the first one deleted,
> and then colx=2 the second, and so on.
>
> I can see #1 being accomplished using a NOT NULL + UNIQUE constraint and
> a CHECK constraint that calls some PL function where the function does a
> simple checking (new.colx=1 if COUNT(colx)==0, or new.colx=MAX(colx)+1).
>
> But is it possible to do #2 using only constraints?
No. A constraint only applies to one row at a time. If you try to work around
this by calling a function that does queries it isn't guarenteed to work.
And if you are thinking of calling a function that does a query, you aren't
looking at saving time over using triggers.
Also, if you are going to have concurrent updates, you are going to need to
do table locking to make this work.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: bruno@wolff.to, lists@zara.6.isreserved.com
Subject: Re: constraint and ordered value
In-Reply-To: <20051229071625.GA2815@wolff.to>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox