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

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)

Do you really think I could get an entire chapter out of this?

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

> * Test cases for pgbench

They're not part of the patch proper, but as I've already mentioned I
have pgbench based stress-tests on Github. There is a variety of
test-cases that test the feature under high concurrency:

https://github.com/petergeoghegan/upsert

> Examples of the following are needed
>
> "ON CONFLICT UPDATE optionally accepts a WHERE clause condition.

Yes, I realized I missed an example of that one the second I hit
"send". The MVCC interactions of this are discussed within
transaction-iso.html, FWIW.

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

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

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

--
Peter Geoghegan