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

On Fri, Dec 26, 2014 at 4:22 PM, Peter Geoghegan <pg(at)heroku(dot)com> wrote:
> So looking at the way the system deals with its dependence on default
> operator classes, I have a hard time justifying all this extra
> overhead for the common case.

Attached pair of revised patch sets, V1.8:

* Explicitly leaves undefined what happens when a non-default opclass
index *with an alternative notion of not just sort order, but
equality* exists. In practice it depends on the available unique
indexes. I really found it impossible to justify imposing any
restriction here, given the total lack of a scenario in which this
even *could* matter, let alone will. This is a minor wart, but I think
it's acceptable.

* Allows "unique index inference specification" clause to have a WHERE
clause (this is distinct from the WHERE clause that might also appear
in the UPDATE auxiliary query). This can be used to infer partial
unique indexes. I really didn't want to give up support for partial
indexes with the UPDATE variant (recall that the UPDATE variant
*requires* an inference clause), since partial unique indexes are
particularly useful.

Note that the unique index must actually cover the tuple at insert
time, or an error is raised. An example of this that appears in the
regression tests is:

insert into insertconflicttest values (23, 'Uncovered by Index') on
conflict (key where fruit like '%berry') ignore;
ERROR: partial arbiter unique index has predicate that does not cover
tuple proposed for insertion
DETAIL: ON CONFLICT inference clause implies that the tuple proposed
for insertion actually be covered by partial predicate for index
"partial_key_index".
HINT: ON CONFLICT inference clause must infer a unique index that
covers the final tuple, after BEFORE ROW INSERT triggers fire.

* New documentation reflecting the above. A couple of paragraphs in
the INSERT SQL reference page now covers these topics.

* Fix Jeff Jane's bug by added sanitizing code [1]. Certain illegal
queries now correctly rejected during parse analysis.

* Fixed another tiny buglet in EXPLAIN ANALYZE output with a RETURNING
clause, by making sure auxiliary query plan from update also has
plan-level targetlist set.

* Minor clean-up to code comments here and there (in particular, for
the ExcludedExpr primnode used to implement the EXCLUDED.*
pseudo-alias thing).

* Better serialization failure error messages.

I recommend looking at my mirror of the modified documentation:
http://postgres-benchmarks.s3-website-us-east-1.amazonaws.com/on-conflict-docs/sql-insert.html
to get up to speed on how unique index inference specification clause
have been extended to support partial unique indexes. As I mentioned,
apart from that, the INSERT SQL reference page now covers the
definition of a "CONFLICT"/opclass semantics issues.

I really hope that this deals with all semantics/syntax related loose
ends, allowing discussion of this patch to take a more low-level
focus, which is what is really needed. I feel that further
improvements may be possible, and that the syntax can be even more
flexible, but it's already flexible enough for our first iteration of
this feature. Importantly, we have something that is enormously more
flexible than any equivalent feature in any other system, which
includes the flexibility to extend the syntax in various other
directions (e.g. specifying particular exclusion constraints).

[1] http://archives.postgresql.org/message-id/CAM3SWZT=HptrGyihZiyT39sPBhp+CXOTW=MhNFzXiLf-Jh4QVA@mail.gmail.com
--
Peter Geoghegan