agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Scott Rohde <srohde@illinois.edu>
To: pgsql-sql@postgresql.org
Subject: Re: Check/unique constraint question
Date: Tue, 9 Dec 2014 15:06:59 -0700 (MST)
Message-ID: <1418162819422-5829820.post@n5.nabble.com> (raw)
In-Reply-To: <7561.1418150595@sss.pgh.pa.us>
References: <Pine.LNX.4.64.0603050015050.29717@discord.dyndns.org>
<e431ff4c0603050102m36b68e08w9d84e52e1f8701cb@mail.gmail.com>
<e431ff4c0603050149i45b6469djdaa078343d79b5be@mail.gmail.com>
<1418148099196-5829778.post@n5.nabble.com>
<7561.1418150595@sss.pgh.pa.us>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
Tom Lane-2 wrote
> ...
> Indeed, this illustrates perfectly why subqueries in CHECK constraints
> are generally a Bad Idea: the constraint is no longer just about the
> contents of one row but about its relationship to other rows, and that
> makes the timing of checks relevant. Hiding the subquery in a function
> doesn't do anything to resolve that fundamental issue.
> ...
I don't think subqueries in CHECK constraints are a bad idea /per se/--to my
mind it would depend on how they actually work. I don't know enough about
the SQL standard or about products that support them to know if they work
the way I /think/ they should work, which is basically this: "Guarantee that
condition X (written as a constraint on table Y) is satisfied by the
database when (1) the constraint is first added, and (2) whenever a change
is made to one or more rows of table Y."
In our example, if the function in the CHECK constraint was run after
provisionally changing the foo table, and if the changes were rolled back if
and only if the check failed, then it seems there would be no problem. It
may well be that bona fide CHECK subqueries (as opposed to procedural
functions run by the CHECK constraint) /do/ work this way.
In PostgreSQL, the best approximation I could come up with was a combination
of the TRIGGER function mentioned in Nikolay's first post together with this
CHECK:
CREATE OR REPLACE FUNCTION id_is_valid(
val INTEGER
) RETURNS boolean AS $BODY$
DECLARE id_is_unique boolean;
BEGIN
SELECT COUNT(*) <= 1 FROM foo WHERE active = TRUE AND id = val INTO
id_is_unique;
RETURN id_is_unique;
END
$BODY$ LANGUAGE plpgsql;
ALTER TABLE foo ADD CONSTRAINT C_foo_iniq_if_true CHECK (id_is_valid(id));
To summarize, any constraint should be fine as long as (1) it is always run
when any change is made to the database that might affect its value; (2) it
is run on the (provisional) /result/ state of the database.
--
View this message in context: http://postgresql.nabble.com/Check-unique-constraint-question-tp2145289p5829820.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
view thread (11+ messages) latest in thread
Message-ID: <1418162819422-5829820.post@n5.nabble.com>
Permalink: ../1418162819422-5829820.post@n5.nabble.com/
Also on: postgresql.org/message-id/1418162819422-5829820.post@n5.nabble.com
reply
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: srohde@illinois.edu
Subject: Re: Check/unique constraint question
In-Reply-To: <1418162819422-5829820.post@n5.nabble.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox