INSERT ... ON CONFLICT UPDATE/IGNORE 4.0

From: Peter Geoghegan <pg(at)heroku(dot)com>
To: hlinnaka <hlinnaka(at)iki(dot)fi>, Andres Freund <andres(at)anarazel(dot)de>, Jeff Janes <jeff(dot)janes(at)gmail(dot)com>, Stephen Frost <sfrost(at)snowman(dot)net>, Dean Rasheed <dean(dot)a(dot)rasheed(at)gmail(dot)com>, Bruce Momjian <bruce(at)momjian(dot)us>
Cc: Pg Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: INSERT ... ON CONFLICT UPDATE/IGNORE 4.0
Date: 2015-04-27 01:02:06
Message-ID: CAM3SWZTGE0z5_E8wRi9wt3qgOiMpSwtwr5uDcZDGhRZ1_pz6vg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin pgsql-hackers

I have pushed my patch, newly rebased, to a new branch on my personal
Github account (branch: insert_conflict_4):

https://github.com/petergeoghegan/postgres/commits/insert_conflict_4

I'm not going to attach a patch here at all. Andres and Heikki should
now push their changes to that branch (or alternatively, Andres can
ask me to merge his stuff into it).

It's make-or-break time for this patch. Please help me get it over the
line in time. Heikki is in Northern California this week, and I think
we'll have time to talk about the patch, which I expect will help (an
in-person chat with Andres in NYC certainly helped *a lot*). But that
also means that he's going to be travelling a long distance, and we
can assume will have reduced availability for the next couple of days.

Notable changes
=============

* Work of Heikki, myself and Andres from the last week or so rebased
to be cumulative (as before, ON CONFLICT IGNORE -> RTE changes -> ON
CONFLICT UPDATE). Would apply cleanly to today's git master branch.

* Improved INSERT documentation [1].

* Minor style tweaks to RTE change commit.

* Improved commit messages. Importantly, these have attribution that I
think fairly reflects everyone's individual contribution. Please let
me know if I missed something.

* Most importantly, RLS changes.

The RLS patch is now significantly simpler than before. In general,
I'm very happy with how the new approach to RLS enforcement, and the
new WCO "kind" field has resulted in a far simpler RLS patch. This
needs the scrutiny of a subject matter expert like Stephen or Dean. I
would be happy to grant either git access to my personal branch, to
push out code changes or tests as they see fit. Just e-mail me
privately with the relevant details.

Remaining challenges
=================

* As discussed in a dedicated thread, we're probably going to have to
tweak the syntax a bit. No need to hold what I have here up for that,
though (provisionally, this version still puts inference WHERE clause
to infer partial indexes in parens). Let's confine the discussion of
that to its dedicated thread. These issues probably apply equally to
the IGNORE variant, and so can be considered a blocker to its commit
(and not just ON CONFLICT UPDATE).

* So far, there has been a lack of scrutiny about what the patch does
in the rewriter (in particular, to support the EXCLUDED.* pseudo-alias
expression) and optimizer (the whole concept of an "auxiliary"
query/plan that share a target RTE, and later target ResultRelation).
If someone took a close look at that, it would be most helpful.
ruleutils.c is also modified for the benefit of EXPLAIN output. This
all applies only to the ON CONFLICT UPDATE patch. A committer could
push out the IGNORE patch before this was 100% firm.

* I privately pointed out to Heikki what I'd said publicly about 6
weeks ago: that there is still a *very* small chance of exclusion
constraints exhibiting "unprincipled deadlocks" (he missed it at the
time). I think that this risk is likely to be acceptable, since it
takes so much to see it happen (and ON CONFLICT UPDATE/nbtree is
unaffected). But let's better characterize the risks, particularly in
light of the changes to store speculative tokens in the c_ctid field
on newly inserted (speculative) tuples. I think that that probably
made the problem significantly less severe, and perhaps it's now
entirely theoretical, but I want to make sure. I'm going to try and
characterize the risks with the patch here today.

Thanks

[1] http://postgres-benchmarks.s3-website-us-east-1.amazonaws.com/on-conflict-docs/sql-insert.html
--
Peter Geoghegan

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Peter Geoghegan 2015-04-27 01:16:04 Re: INSERT ... ON CONFLICT UPDATE/IGNORE 4.0
Previous Message David G. Johnston 2015-04-25 16:25:38 Re: How to drop automatically some databases?

Browse pgsql-hackers by date

  From Date Subject
Next Message Peter Geoghegan 2015-04-27 01:16:04 Re: INSERT ... ON CONFLICT UPDATE/IGNORE 4.0
Previous Message Amit Langote 2015-04-27 00:59:22 Re: Typo in a comment in set_rel_size()