agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Tom Lane <tgl@sss.pgh.pa.us>
To: Scott Rohde <srohde@illinois.edu>
Cc: pgsql-sql@postgresql.org
Subject: Re: Check/unique constraint question
Date: Tue, 09 Dec 2014 13:43:15 -0500
Message-ID: <7561.1418150595@sss.pgh.pa.us> (raw)
In-Reply-To: <1418148099196-5829778.post@n5.nabble.com>
References: <Pine.LNX.4.64.0603050015050.29717@discord.dyndns.org>
<e431ff4c0603050102m36b68e08w9d84e52e1f8701cb@mail.gmail.com>
<e431ff4c0603050149i45b6469djdaa078343d79b5be@mail.gmail.com>
<1418148099196-5829778.post@n5.nabble.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
Scott Rohde <srohde@illinois.edu> writes:
> There is something a bit odd about this solution: If you start with an empty
> table, the constraint will allow you to do
> INSERT INTO foo (active, id) VALUES ('t', 5);
> But if you insert this row into the table first and /then/ try to add the
> constraint, it will complain that an existing row violates the constraint.
> This begs the question of when constraints are checked.
> I had always thought of constraints as being static conditions that (unlike
> some trigger condition that masquerades as a constraint) apply equally to
> existing rows and to rows you are about to add. This seems to show that not
> all constraints work this way.
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.
The original example seemed to work for retail inserts because the check
gets applied before the row is physically inserted. It would fail on
updates though, or when trying to add the constraint after the fact.
regards, tom lane
--
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: <7561.1418150595@sss.pgh.pa.us>
Permalink: ../7561.1418150595@sss.pgh.pa.us/
Also on: postgresql.org/message-id/7561.1418150595@sss.pgh.pa.us
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: tgl@sss.pgh.pa.us, srohde@illinois.edu
Subject: Re: Check/unique constraint question
In-Reply-To: <7561.1418150595@sss.pgh.pa.us>
* 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