Re: ON CONFLICT does not support deferrable unique constraints

On Thu, Aug 25, 2016 at 12:29 AM, Francisco Olarte
<folarte(at)peoplecall(dot)com> wrote:
> That been said, I'm not sure making it ( deferred constraint act like
> immediate ones during upserts ) work is even a good idea. If it can be
> conditionally enabled with a simple set and implemented in very few (
> < 20 ) lines of code, ok for me , otherwise I would prefer the reduced
> bug surface.

But UPSERT doesn't support a DEFERRABLE constraint, even when the
system is set to IMMEDIATE enforcement of constraints. I think that
it's not hard to see that UPSERT on a deferred (not just DEFERRABLE)
constraint doesn't make much sense, but that's not how I understood
the OP's complaint.

IMMEDIATE enforcement just makes the second pass for verification
occur immediately, rather than at the end of the transaction. That's a
very small difference, at least from an implementation perspective; in
particular, the constraint will not actually behave like (say) a
uniqueness constraint that was not declared DEFERRABLE in the first
place. Enforcement will not occur in the path of insertion, as it does
for B-Tree.

The reason for the broad restriction on DEFERRABLE constraints is that
it's not clear how the implementation of UPSERT should handle
*somebody else's* unconfirmed DEFERRABLE-mode insertion (this may
happen even if the upserter is forbidden from treating the constraint
as DEFERRABLE). I'm not saying that it's impossible, but it's far more
complicated than it first appears, if you expect UPSERT to worry about
lock starvation, "unprincipled deadlocks" [1], and other problems like
that.

[1] https://wiki.postgresql.org/wiki/Value_locking#.22Unprincipled_Deadlocking.22_and_value_locking
--
Peter Geoghegan