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

Thanks for the detailed replies, folks!

I had no idea "deferrable" was even a thing.

Well, the default behavior is still surprising, I think - and it sounds
like this may deviate from the standard behavior?

If so, maybe a future release could align better with the standard behavior
on this point - even if this has performance implications, in my opinion,
fewer surprises is better; someone could of course still optimize by using
NOT DEFERRED.

I guess this would be a breaking change however?

On Wed, Mar 22, 2017 at 4:31 PM, David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> 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.
>
>