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

On 28 September 2014 08:40, Peter Geoghegan <pg@heroku.com> wrote:
> On Sat, Sep 27, 2014 at 11:21 PM, Simon Riggs <simon@2ndquadrant.com> wrote:
>> My request was for the following...
>>
>> Agree command semantics by producing these things
>> * Explanatory documentation (Ch6.4 Data Manipulation - Upsert)
>
> Do you really think I could get an entire chapter out of this?

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?

The new docs are scattered across many pages and there are very few
examples. It was very difficult to read like that.


>> * SQL Reference Documentation (INSERT)
>> * Test cases for feature
>> * Test cases for concurrency
>
> 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.

>> Question arising: do you need to specify location criteria, or is this
>> an additional filter? When/why would we want that?
>
> 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.


>> How would you do "if colA = 3 then ignore else update"?
>
> Technically, you can't do that exact thing. IGNORE is just for quickly
> dealing with ETL-type problems (and it is reasonable to use it without
> one particular unique index in mind, unlike ON CONFLICT UPDATE) -
> think pgloader. But if you did this:
>
> 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?

Because it would be a principled approach to do that.

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.

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

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.

>> The page makes no mention of the upsert problem, nor is any previous
>> code mentioned.
>
> What's the upsert problem? I mean, apart from the fact that we don't
> have it. Note that it is documented that one of the two outcomes is
> guaranteed.
>
> I should have updated the plpgsql looping subxact example, though.

That's what I meant.

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