Re: INSERT ... ON CONFLICT syntax issues

On Wed, Apr 29, 2015 at 4:09 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> I dislike the way that ignoring objections for a period leads them to be
> potentially discarded. I'd prefer to think that as a community we are able
> to listen to people even when they aren't continually present to reinforce
> the original objection(s).

What objection was ignored? Because, I looked just now, and the only
thing I could find of substance that you said on the MySQL syntax [1]
seemed pretty lukewarm. You mostly argued for MERGE.

> Not supporting MySQL syntax will seem like a bizarre choice to people
> watching this from a distance. I accept that the patch implements useful
> behaviour that MySQL does not implement and we thus provide enhanced syntax,
> but the default should be match on PK using the MySQL syntax.

Does it?

We can't use the MERGE syntax, because this isn't MERGE. Everything
else UPSERT-like some new and distinct custom syntax, for various
reasons.

>> Note that the syntax is quite similar to the SQLite
>> syntax of the same feature, that has ON CONFLICT IGNORE (it also has
>> ON CONFLICT REPLACE, but not ON CONFLICT UPDATE).
>
>
> Why are we not also supporting ON CONFLICT REPLACE and IGNORE then?

The point I was trying to make was that CONFLICT also appears as a
more general term than "duplicate key" or whatever.

> If we are using syntax from other products then it should be identical
> syntax, or the argument to use it doesn't stand.

I was making a narrow point about the keyword "CONFLICT". Nothing more.

> We must think about what SQL Standard people are likely to say and do. If we
> act as independently, our thought may be ignored. If we act in support of
> other previous implementations we may draw support to adopt that as the
> standard. Whatever the standard says we will eventually support, so we
> should be acting with an eye to that future.

UPSERT seems like exactly the kind of thing that the SQL standard does
not concern itself with. For example, I have a "unique index inference
specification". The SQL standard does not have anything to say about
indexes. I would be extremely surprised if the SQL standard adopted
MySQL's UPSERT thing. They omit the "SET" on the UPDATE, probably to
fix some parser ambiguity issue. While there are some similarities to
what I have here, it's a bit shoddy.

I have requirements coming out of my ears for this patch, Simon. I
think it's odd that you're taking umbrage because I supposedly ignored
something you said 6 months ago.

[1] http://www.postgresql.org/message-id/CA+U5nMK-efLg00FhCWk=ASbET_77iSS87EGDsptq0uKzQdrV6Q@mail.gmail.com
--
Peter Geoghegan