Subject: Re: table constraints
From: "Casey T. Deccio" <ctdecci@sandia.gov>
To: andrew@supernews.com
Cc: pgsql-sql@postgresql.org
In-Reply-To: <slrnd27fni.2shl.andrew+nonews@trinity.supernews.net>
References: <1109618910.32166.0.camel@boomerang.ran.sandia.gov>
	<20050228202016.GA32034@wolff.to>
	<1109622215.32226.11.camel@boomerang.ran.sandia.gov>
	<slrnd27fni.2shl.andrew+nonews@trinity.supernews.net>
Organization: Sandia National Laboratories
Date: Mon, 28 Feb 2005 17:25:56 -0800
Message-ID: <1109640356.32228.53.camel@boomerang.ran.sandia.gov>
MIME-Version: 1.0
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

On Mon, 2005-02-28 at 18:01 -0700, Andrew - Supernews wrote:
> The CHECK is obviously being evaluated prior to the actual insertion
> of
> the record, whereas the logic of your function clearly expects to be
> evaluated after the insertion.
> 

I finally came to that conclusion just a few minutes before I got this
email.  However, it was your email that made it quite clear why this
was.

> Allowing non-immutable functions in CHECK is probably an error, since
> it
> can lead to tables which can not be dumped+restored (consider in your
> example what happens when the constraint becomes false as a result of
> deleting a row - at that point, a dump and restore of the table will
> fail,
> since the constraint can not be defined after loading the data if it
> is
> violated by that data).

> Using triggers is a more reliable way to do this sort of thing - at
> least
> then it is clear that you are checking the data only at the time of
> modification, whereas CHECK constraints are declarative constraints
> which
> are expected to be true at all times.
> 

This is a very good point.  I've now implemented the check with a
trigger, and it works very well.  Thanks for the input and the logic.

Casey