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

On 27 September 2014 23:23, Peter Geoghegan <pg(at)heroku(dot)com> wrote:
> On Thu, Sep 25, 2014 at 1:48 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
>> I hate the fact
>> that you have written no user facing documentation for this feature.
>
> Attached patch adds a commit to the existing patchset. For the
> convenience of reviewers, I've uploaded and made publicly accessible a
> html build of the documentation. This page is of most interest:
>
> http://postgres-benchmarks.s3-website-us-east-1.amazonaws.com/on-conflict-docs/sql-insert.html

My request was for the following...

Agree command semantics by producing these things
* Explanatory documentation (Ch6.4 Data Manipulation - Upsert)
* SQL Reference Documentation (INSERT)
* Test cases for feature
* Test cases for concurrency
* Test cases for pgbench

because it forces you to show in detail how the command works. Adding
a few paragraphs to the INSERT page with two quick examples is not the
same level of detail at all and leaves me with the strong impression
my input has been assessed as ON CONFLICT IGNORE.

Examples of the following are needed

"ON CONFLICT UPDATE optionally accepts a WHERE clause condition. When
provided, the statement only procedes with updating if the condition
is satisfied. Otherwise, unlike a conventional UPDATE, the row is
still locked for update. Note that the condition is evaluated last,
after a conflict has been identified as a candidate to update."
Question arising: do you need to specify location criteria, or is this
an additional filter? When/why would we want that?

"Failure to anticipate and prevent would-be unique violations
originating in some other unique index than the single unique index
that was anticipated as the sole source of would-be uniqueness
violations can result in updating a row other than an existing row
with conflicting values (if any)."
In English, please

How would you do "if colA = 3 then ignore else update"?

No explanation of why the CONFLICTING() syntax differs from OLD./NEW.
syntax used in triggers

The page makes no mention of the upsert problem, nor is any previous
code mentioned.

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