Re: BUG #13846: INSERT ON CONFLICTconsumessequencersonconflicts

Hi Dave,
Thanks for the informative reply. We absolutely agree it’s not a bug in implementation, but maybe a bug in conceptualization with regard to usability. 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 don’t quite understand. We stopped using and went back to a manual approach, which also works great because PostgreSQL is just kinda great 😊.

If any related enhancements where to be done, our ‘holy grail’ would be an implementation of the MERGE statement, like this, or this, or this… We can at least ask for it right?? 😉

Thanks for all your guys’ 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 CONFLICTconsumessequencersonconflicts

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.

​