Re: BUG #14596: False primary/unique key constraint violations

On Wed, Mar 22, 2017 at 7:45 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> rasmus(at)mindplay(dot)dk writes:
> > It looks like constraints are being checked row-by-row while the udpate
> is
> > happening?
>
> This is documented somewhere ... ah, here, in the COMPATIBILITY section of
> the CREATE TABLE reference page:
>
> Non-deferred Uniqueness Constraints
>
> When a UNIQUE or PRIMARY KEY constraint is not deferrable, PostgreSQL
> checks for uniqueness immediately whenever a row is inserted or
> modified. The SQL standard says that uniqueness should be enforced only
> at the end of the statement; this makes a difference when, for example,
> a single command updates multiple key values. To obtain
> standard-compliant behavior, declare the constraint as DEFERRABLE but
> not deferred (i.e., INITIALLY IMMEDIATE). Be aware that this can be
> significantly slower than immediate uniqueness checking.
>
>
​"SET CONSTRAINTS" is also required if using the standard behavior due to
the "initially immediate" specification.​

​The need for foresight is the only troubling piece of all of this.​ Given
that "update tbl set pk = pk + 1;" is so rare most people would not think
to define their uniqueness constraints with deferrability. In the rare
case that you then need the behavior you are forced to drop and recreate
the constraint and backing index because ALTER TABLE ... ALTER CONSTRAINT
can only be used on foreign key constraints (which means exclusion
constraints are also problematic).

Given that the default SET CONSTRAINT behavior is IMMEDIATE, and that
triggers are defined DEFERRABLE, what harm would there be to default to the
standard mandated behavior noted above?

You can add a deferrable constraint to a pre-existing unique index which
suggests that if changing the default is not desirable someone motivated
enough could devise a way to "detach the unique index from the constraint,
drop/update the constraint, then add/re-attach the constraint to the index"
- or, more simply put, make alter table ... alter constraint work when
targeting pk/unique constraints. This might extend to exclusion
constraints too...

David J.