Re: INSERT ... ON CONFLICT {UPDATE | IGNORE}

On 28 September 2014 08:40, Peter Geoghegan <pg(at)heroku(dot)com> wrote:
> On Sat, Sep 27, 2014 at 11:21 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>> My request was for the following...
>>
>> Agree command semantics by producing these things
>> * Explanatory documentation (Ch6.4 Data Manipulation - Upsert)

...

> INSERT INTO tab(colB) values('foo') ON CONFLICT UPDATE set colB =
> CONFLICTING(colB) WHERE colA != 3
>
> Then you would achieve almost the same thing. You wouldn't have
> inserted or updated anything if the only rows considered had a colA of
> 3, but any such rows considered would be locked, which isn't the same
> as IGNOREing them.
>
>> No explanation of why the CONFLICTING() syntax differs from OLD./NEW.
>> syntax used in triggers
>
> Why should it be the same?

Good question. What could be wrong with making up new syntax?

The obvious answer is because we would simply have nothing to guide
us. No principles that can be applied, just opinions.

My considered opinion is that the above syntax is
* non-standard
* inconsistent with what we have elsewhere
* an additional item for implementors to handle

I could use more emotive words here, but the above should suffice to
cover my unease at inventing new SQL constructs. This is Postgres.

What worries me the most is that ORM implementors everywhere will
simply ignore our efforts, leaving us with something we'd much rather
we didn't have. As a possible committer of this feature, I would not
wish to put my name to that. You will need one a committer who will do
that.

Which brings me back to the SQL Standard, which is MERGE. We already
know the MERGE command does not fully and usefully define its
concurrent behaviour; I raised this 6 years ago. It's not clear to me
that that we couldn't more closely define the behaviour for a subset
of the command.

If we implement MERGE, then we will help ORM developers do less work
to support Postgres, which will encourage adoption.

My proposal would be to implement only a very limited syntax for MERGE
in this release, replacing this

> INSERT INTO tab(colB) values('foo') ON CONFLICT UPDATE set colB =
> CONFLICTING(colB) WHERE colA != 3

with this...

MERGE INTO tab USING VALUES ('foo')
WHEN NOT MATCHED THEN
INSERT (colB)
WHEN MATCHED THEN
UPDATE SET colB = NEW.p1

and throwing "ERROR: full syntax for MERGE not implemented yet" if
people stretch too far.

If there is some deviation from the standard, it can be explained
clearly, though I don't see we would need to do that - we can extend
beyond the standard to explain the concurrent behaviour. And we will
be a lot closer to getting full MERGE also.

Doing MERGE syntax is probably about 2 weeks work, which is better
than 2 weeks per ORM to support the new Postgres-only syntax.

Thanks for your efforts to bring this to a conclusion.

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services