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

On Tue, Sep 30, 2014 at 8:30 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>>>>> No explanation of why the CONFLICTING() syntax differs from OLD./NEW.
>>>>> syntax used in triggers
>>>>
>>>> Why should it be the same?
>>>
>>> Because it would be a principled approach to do that.
>>
>> That is just an assertion. The MERGE syntax doesn't use that either.
>
> MERGE allows "AS row" which then allow you to refer to row.x for
> column x of the input.

It does, but that isn't what you suggested. You talked about the
OLD.*/NEW.* syntax.

>> I don't care if we change the spelling to "WHEN MATCHED
>> UPDATE/IGNORE". That seems fine. But MERGE is talking about a join,
>> not the presence of a would-be duplicate violation.
>
> I don't understand that comment.

I just mean that if you want to replace ON CONFLICT UPDATE with WHEN
MATCHED UPDATE - that little part of the grammar - that seems okay.

>> Multi-assignment updates (with or without CONFLICTING()) are supported, FWIW.
>
> If I want the incoming row to overwrite the old row, it would be good
> to have syntax to support that easily.

Well, maybe I'll get around to that when things settle down. That's
clearly in the realm of "nice to have", though.

> Why doesn't
> INSERT INTO UNIQUE_TBL VALUES (1, 'a'), (2, 'b'), (2, 'b') ON
> CONFLICT UPDATE SET t = 'fails';
> end up with this in the table?
>
> 1 a
> 2 fails

A "cardinality violation" - just like MERGE. As with MERGE, the final
value of every row needs to be deterministic (within the command).

> What happens with this?
>
> BEGIN;
> INSERT INTO UNIQUE_TBL VALUES (2, 'b') ON CONFLICT UPDATE SET t = 'fails';
> INSERT INTO UNIQUE_TBL VALUES (2, 'b') ON CONFLICT UPDATE SET t = 'fails';
> COMMIT;

It works fine. No cardinality violation with two separate commands.
See the new ExecLockUpdateTuple() function within nodeModifyTable.c
for extensive discussion on how this is handled.

--
Peter Geoghegan