Re: INSERT ... ON CONFLICT IGNORE (and UPDATE) 3.0

On Tue, Mar 31, 2015 at 7:02 AM, Peter Geoghegan <pg@heroku.com> wrote:
>> Andres' wish to do things that way is at least partially motivated by
>> having logical decoding just work.

Attached revision, V3.3, adds actual support for ON CONFLICT with
logical decoding. I've implemented something along the lines Andres
wanted. There is now coordination across WAL records - the insert WAL
record, and an in-place update record that affirms that the
speculative insertion was successful. This does not involve
redundantly logging either the heap tuple header (which only appears
in the first of two records), or the tuple contents (which only
appears in the second of the two records). I stash the tuple header
metadata within the inserting xact's ReorderBufferTXN. This approach
appears to be robust.

Other changes:

* Significant refactoring of infer_arbiter_indexes() within the
optimizer. More tests, simpler code.

* Reworking of ExecInsert() comments, per feedback in NYC from Andres.
Also made the control flow there a little simpler.

* ON CONFLICT IGNORE is now fully supported on updatable views - an
inference specification can be included. ON CONFLICT UPDATE remains
unsupported (this can be revisited in a later release IMV).

* Dedicated inference element primnode - it wasn't cool that the
CREATE INDEX IndexElem parse node made its way into the optimizer
(where unique index inference occurs). This simplified some code, too.

* Per discussion in NYC with Andres, arbiter indexes now always appear
in explain output (not just for non-text explain output). I've also
made the changes to explain.c a lot simpler (I've further isolated the
kludge used to display quals from the implementation level sequential
scan that can appear in auxiliary UPDATE plans).

* Misc polishing. Obsolete comments were found in a few places. Fixed
build of contrib/pageinspect, that didn't get the memo about the
t_ctid union change.

I talked privately with Stephen about RLS. It seems likely that that
needs some behavioral changes, but nothing too invasive. I haven't got
around to implementing those yet, but I think they're about to make it
to the top of my todo list. In any case, that is still split up into a
separate commit (I anticipated that it would be a good idea to do
that, since the RLS discussion has yet to fully settle down). Nothing
has changed about that commit, though.

With a concerted effort, I think we can get this over the line for 9.5.

Andres: Please take a look at the logical decoding/WAL stuff. Your
input on those aspects would be particularly useful now.

I'm now pushing code to github regularly. The feature branch in
question is: https://github.com/petergeoghegan/postgres/commits/insert_conflict_ignore

Thanks
--
Peter Geoghegan