Re: BUG #13846: INSERT ON CONFLICTconsumessequencersonconflicts

Hi Dave,=20
Thanks for the informative reply. We absolutely agree it=E2=80=99s not a bu=
g in implementation, but maybe a bug in conceptualization with regard to us=
ability. Not knowing the initial motive for the feature, we assumed it was =
meant for an intuitively simpler form of the rather common upsert pattern, =
but it seems INSERT ON CONFLICT was meant for some other purpose that we do=
n=E2=80=99t quite understand. We stopped using and went back to a manual ap=
proach, which also works great because PostgreSQL is just kinda great =F0=
=9F=98=8A.

If any related enhancements where to be done, our =E2=80=98holy grail=E2=80=
=99 would be an implementation of the MERGE statement, like this, or this, =
or this=E2=80=A6 We can at least ask for it right?? =F0=9F=98=89

Thanks for all your guys=E2=80=99 hard work

-p

From: David G. Johnston
Sent: Friday, May 6, 2016 1:02 PM
To: Paul
Cc: Andres Freund; Peter Geoghegan; pgsql-bugs
Subject: Re: [BUGS] BUG #13846: INSERT ON CONFLICTconsumessequencersonconfl=
icts

On Wed, Jan 6, 2016 at 5:14 PM, Paul <paul@salesintel.com> wrote:
If I had a BEFORE INSERT trigger, it would only execute for records that we=
re actually going to be inserted. However, you=E2=80=99re now stating that =
the INSERT ON CONFLICT will execute a BEFORE INSERT trigger even for record=
s that are not inserted?? That doesn=E2=80=99t seem quite logical, and cont=
rary to =E2=80=98old fashioned=E2=80=99 upsert logic. Does this also mean t=
hat the BEFORE UPDATE trigger is always called as well, or is it never call=
ed?

=E2=80=8BThis seems to boil down to the two possible ways of manually imple=
menting UPSERT:

UPDATE, if not present, INSERT
INSERT, if failing, UPDATE

In the later the before insert trigger fires and influences whether the ins=
ert=E2=80=8B
=C2=A0
=E2=80=8Bfails.=C2=A0 In the former you are already pretty certain the inse=
rt will =E2=80=8Bsucceed because the UPDATE found no records.

We've implemented INSERT, if failing UPDATE.=C2=A0 The insert has to be att=
empted and right now there is no concept of targeted partial deferrability =
when constructing the record to be inserted.

To solve this situation it is likely that some form of "UPDATE ON MISSING I=
NSERT" would need to be designed.=C2=A0 The insert portion would specify "D=
EFAULT" for sequence columns and would execute nextval() only if the ON MIS=
SING portion is executed.

Fundamentally, the difference is that ON MISSING is considerably less compl=
icated than ON CONFLICT.=C2=A0 What is wanted here is an ON MISSING interpr=
etation but what we've implemented is ON CONFLICT.=C2=A0 It seems that the =
hackers are in agreement that our implementation of ON CONFLICT is consiste=
nt with its definition.=C2=A0 That it doesn't efficiently solve problems be=
tter handled by ON MISSING - while unfortunate - doesn't constitute a bug: =
only an opportunity for future enhancement.

David J.


=E2=80=8B