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

On Mon, Sep 29, 2014 at 7:21 AM, Simon Riggs <simon@2ndquadrant.com> wrote:
> If you were an ORM developer reading the PostgreSQL Release Notes for
> 9.5, which URL would you visit to see a complete description of the
> new feature, including how it works concurrently, locking and other
> aspects. How would you check whether some strange behaviour was a bug,
> or intentional?

We don't do that with UPDATE, so why would we do it with this? There
is an existing structure to the documentation that needs to be
respected. This is the case even though the EvalPlanQual() mechanism
is a total Postgres-ism, which can potentially violate snapshot
isolation (this is not true of Oracle's READ COMMITTED, for example).
You have to go out of your way to find that out at the moment. But I
know ORM authors, and the majority probably don't understand this
stuff - that ought to be okay.

>> All of these were added. There are two new sets of isolation tests,
>> one per variant of the new clause (IGNORE/UPDATE).
>
> When you say "added", what do you mean? You posted one new doc patch,
> with no tests in it.

I mean that there was a commit (not included with the documentation,
but with the original patchset) with many tests. I don't know why
you're suggesting that I don't have "concurrency tests". There are
isolation tests in that commit. There are also many regression tests.

>> It is an additional way to specify a predicate/condition to UPDATE on.
>> There might be a kind of redundancy, if you decided to repeat the
>> constrained values in the predicate too, but if you're using the WHERE
>> clause sensibly there shouldn't be. So your UPDATE's "full predicate"
>> is sort of the union of the constrained values that the conflict path
>> was taken for, plus whatever you put in the WHERE clause, but not
>> quite because they're evaluated at different times (as explained
>> within transaction-iso.html).
>
> I think we should leave that out of the first commit. I'm not sure why
> that exists. If you wish to push down that route, then I recommend
> using the MERGE syntax because it caters for this much better than
> this.

Why leave it out? People are going to "push the predicate into the
targetlist" if I do, and the effect is exactly the same.

>>> 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.

> If we aren't going to use MERGE syntax, it would make sense to at
> least use the same terminology.
>
> e.g.
> INSERT ....
> WHEN MATCHED
> UPDATE
>
> The concept of "matched" is identical between MERGE and UPSERT and it
> will be confusing to have two words for the same thing.

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.

> There seems to be a good reason not to use the MySQL syntax of ON
> DUPLICATE KEY UPDATE, which doesn't allow you to specify UPDATE
> operations other than a replace, so no deltas, e.g. SET a = a + x

That isn't true, actually. It clearly does.

> Having said that, it would be much nicer to have a mode that allows
> you to just say the word "UPDATE" and have it copy the data into the
> correct columns, like MySQL does. That is very intuitive, even if it
> isn't very flexible.

Multi-assignment updates (with or without CONFLICTING()) are supported, FWIW.

-- 
Peter Geoghegan