Re: BUG #13846: INSERT ON CONFLICT consumessequencersonconflicts

On Wed, Jan 6, 2016 at 5:14 PM, Paul <paul(at)salesintel(dot)com> wrote:

> If I had a BEFORE INSERT trigger, it would only execute for records that
> were actually going to be inserted. However, you’re now stating that the
> INSERT ON CONFLICT will execute a BEFORE INSERT trigger even for records
> that are not inserted?? That doesn’t seem quite logical, and contrary to
> ‘old fashioned’ upsert logic. Does this also mean that the BEFORE UPDATE
> trigger is always called as well, or is it never called?
>

​This seems to boil down to the two possible ways of manually implementing
UPSERT:

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

In the later the before insert trigger fires and influences whether the
insert​

​fails. In the former you are already pretty certain the insert will
​succeed because the UPDATE found no records.

We've implemented INSERT, if failing UPDATE. The insert has to be
attempted 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
INSERT" would need to be designed. The insert portion would specify
"DEFAULT" for sequence columns and would execute nextval() only if the ON
MISSING portion is executed.

Fundamentally, the difference is that ON MISSING is considerably less
complicated than ON CONFLICT. What is wanted here is an ON MISSING
interpretation but what we've implemented is ON CONFLICT. It seems that
the hackers are in agreement that our implementation of ON CONFLICT is
consistent with its definition. That it doesn't efficiently solve problems
better handled by ON MISSING - while unfortunate - doesn't constitute a
bug: only an opportunity for future enhancement.

David J.

​