Thread: ON CONFLICT does not support deferrable unique constraints

On Wed, Aug 24, 2016 at 6:05 AM, Andreas Joseph Krogh
<andreas@visena.com> wrote:
>
> Hope some -hackers read this...
>
> Are there any plans to lift this restriction?

I'm trying to figure out a method for making this work in my head.
These two things seem kind of opposed to each other.

On Wed, Aug 24, 2016 at 6:26 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Wed, Aug 24, 2016 at 6:05 AM, Andreas Joseph Krogh
>> Are there any plans to lift this restriction?
> I'm trying to figure out a method for making this work in my head.
> These two things seem kind of opposed to each other.

He probably wants to just treat is as non-deferrable just during the
upsert. I do not know if he has thought this opens a can of worms (
like, the constraint may be already broken due to precious DML ).

Francisco Olarte.

On Wed, Aug 24, 2016 at 6:26 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Wed, Aug 24, 2016 at 6:05 AM, Andreas Joseph Krogh
>> Are there any plans to lift this restriction?
> I'm trying to figure out a method for making this work in my head.
> These two things seem kind of opposed to each other.

He probably wants to just treat is as non-deferrable just during the
upsert. I do not know if he has thought this opens a can of worms (
like, the constraint may be already broken due to precious DML ).

Francisco Olarte.

--

Andreas Joseph Krogh

CTO / Partner - Visena AS

Mobile: +47 909 56 963

andreas@visena.com

www.visena.com

On Wed, Aug 24, 2016 at 9:22 PM, Andreas Joseph Krogh
<andreas@visena.com> wrote:
> As a developer I want it to "just work", if there's an error of any kind then abort the transaction, just as it was
non-deferrable.

Everybody wants everything to "just work", for their own ( in a lot of
cases unspecified even to the themselves ) definition of JW.
Developers normally want to be told what "just work" means when
developing something.

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.

Francisco Olarte.

On Thu, Aug 25, 2016 at 12:29 AM, Francisco Olarte
<folarte@peoplecall.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