From: Casey T. Deccio <ctdecci@sandia.gov>
To: andrew@supernews.com
Cc: pgsql-sql@postgresql.org
Subject: Re: table constraints
Date: Mon, 28 Feb 2005 17:25:56 -0800
Message-ID: <1109640356.32228.53.camel@boomerang.ran.sandia.gov> (raw)
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>
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
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: ctdecci@sandia.gov, andrew@supernews.com
Subject: Re: table constraints
In-Reply-To: <1109640356.32228.53.camel@boomerang.ran.sandia.gov>
* 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