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

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

rasmus@mindplay.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.