Attached documentation patch is intended to close-out the INSERT ...
ON CONFLICT documentation items from the 9.5 open item list. I also
attach a patch that makes a minor adjustment to an error message
concerning deferred constraints; the problem came to my attention as I
worked on the documentation patch (where the same minor inaccuracy is
corrected).
The documentation patch improves the generally readability of the
INSERT documentation, while fixing a number of minor errors. It also
adds a "Tip" box that explains that unique index inference should be
preferred over explicitly naming a constraint. Early signs are that
some users are naming constraints directly where that isn't necessary
at all, which seems bad because it gives up all the flexibility of
inference for no benefit. Inference is very forgiving in the event of
migrations where there may be multiple more-or-less equivalent unique
indexes, an edge-case that I worked hard to make inference handle
well. Inference also does the right thing with a partial unique index
and otherwise equivalent non-partial unique index -- one can imagine
that occurring when an application refines a business rule as part of
application refactoring. Naming a constraint directly was always
understood to be a kind of escape hatch, as I believe Heikki put it at
one point.
The docpatch also addresses limitations of INSERT ... ON CONFLICT with
partitioning based on table inheritance. These was some concern
expressed about this [1], which I also mean to address with the
documentation patch.
[1]
http://www.postgresql.org/message-id/flat/cahgqgwfucwwsu7dtc2ardrk73ztyr_jy5cpoyts+k8xkj92...@mail.gmail.com#cahgqgwfucwwsu7dtc2ardrk73ztyr_jy5cpoyts+k8xkj92...@mail.gmail.com
--
Peter Geoghegan
From 2c3693c640b654235933ec02e3c1eaf45b4e9f3a Mon Sep 17 00:00:00 2001
From: Peter Geoghegan <peter.geoghega...@gmail.com>
Date: Sun, 11 Oct 2015 11:13:24 -0700
Subject: [PATCH 2/2] Minor copy-editing of INSERT documentation
In passing, document limitations around ON CONFLICT and
inheritance-bases table partitioning.
---
doc/src/sgml/ddl.sgml | 15 ++++++++
doc/src/sgml/ref/insert.sgml | 89 +++++++++++++++++++++++++-------------------
2 files changed, 66 insertions(+), 38 deletions(-)
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index a889ffc..375d6fd 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -3246,6 +3246,21 @@ ANALYZE measurement;
</para>
</listitem>
+ <listitem>
+ <para>
+ <command>INSERT</command> statements with <literal>ON
+ CONFLICT</> clauses do not work sensibly with the partitioning
+ schemes shown here, since trigger functions are not currently
+ capable of examining the structure of the original
+ <command>INSERT</command>. In particular, trigger functions
+ have no way to determine how the <command>INSERT</command>
+ command's author might have intended <quote>redirected</>
+ inserts to handle conflicts in child tables. Even the
+ involvement of an <literal>ON CONFLICT</> clause is not exposed
+ to trigger functions.
+ </para>
+ </listitem>
+
</itemizedlist>
</para>
diff --git a/doc/src/sgml/ref/insert.sgml b/doc/src/sgml/ref/insert.sgml
index 8caf5fe..0794acb3 100644
--- a/doc/src/sgml/ref/insert.sgml
+++ b/doc/src/sgml/ref/insert.sgml
@@ -99,7 +99,8 @@ INSERT INTO <replaceable class="PARAMETER">table_name</replaceable> [ AS <replac
<para>
You must have <literal>INSERT</literal> privilege on a table in
order to insert into it. If <literal>ON CONFLICT DO UPDATE</> is
- present the <literal>UPDATE</literal> privilege is also required.
+ present, <literal>UPDATE</literal> privilege on the table is also
+ required.
</para>
<para>
@@ -161,10 +162,7 @@ INSERT INTO <replaceable class="PARAMETER">table_name</replaceable> [ AS <replac
<listitem>
<para>
A substitute name for the target table. When an alias is provided, it
- completely hides the actual name of the table. This is particularly
- useful when using <literal>ON CONFLICT DO UPDATE</literal> into a table
- named <literal>excluded</literal> as that's also the name of the
- pseudo-relation containing the proposed row.
+ completely hides the actual name of the table.
</para>
</listitem>
</varlistentry>
@@ -395,19 +393,23 @@ INSERT INTO <replaceable class="PARAMETER">table_name</replaceable> [ AS <replac
<parameter>conflict_target</parameter> describes which conflicts
are handled by the <literal>ON CONFLICT</literal> clause. Either a
<emphasis>unique index inference</emphasis> clause or an explicitly
- named constraint can be used. For <literal>ON CONFLICT DO
- NOTHING</literal>, it is optional to specify a
- <parameter>conflict_target</parameter>; when omitted, conflicts
- with all usable constraints (and unique indexes) are handled. For
- <literal>ON CONFLICT DO UPDATE</literal>, a conflict target
- <emphasis>must</emphasis> be specified.
+ named constraint can be used. These constraints and/or unique
+ indexes are <firstterm>arbiter indexes</firstterm>. For
+ <literal>ON CONFLICT DO NOTHING</literal>, it is optional to
+ specify a <parameter>conflict_target</parameter>; when omitted,
+ conflicts with all usable constraints (and unique indexes) are
+ handled. For <literal>ON CONFLICT DO UPDATE</literal>, a
+ <parameter>conflict_target</parameter> <emphasis>must</emphasis> be
+ provided.
Every time an insertion without <literal>ON CONFLICT</literal>
would ordinarily raise an error due to violating one of the
- inferred (or explicitly named) constraints, a conflict (as in
- <literal>ON CONFLICT</literal>) occurs, and the alternative action,
- as specified by <parameter>conflict_action</parameter> is taken.
- This happens on a row-by-row basis.
+ inferred constraints/indexes (or an explicitly named constraint), a
+ conflict (as in <literal>ON CONFLICT</literal>) occurs, and the
+ alternative action, as specified by
+ <parameter>conflict_action</parameter> is taken. This happens on a
+ row-by-row basis. Only <literal>NOT DEFERRABLE</literal>
+ constraints are supported as arbiters.
</para>
<para>
@@ -425,17 +427,28 @@ INSERT INTO <replaceable class="PARAMETER">table_name</replaceable> [ AS <replac
specified columns/expressions and, if specified, whose predicate
implies the <replaceable class="PARAMETER">
index_predicate</replaceable> are chosen as arbiter indexes. Note
- that this means an index without a predicate will be used if a
- non-partial index matching every other criteria happens to be
- available.
+ that this means a unique index without a predicate will be inferred
+ (and used by <literal>ON CONFLICT</literal> as an arbiter) if such
+ an index satisfying every other criteria is available.
</para>
<para>
- If no index matches the inference clause (nor is there a constraint
- explicitly named), an error is raised. Deferred constraints are
- not supported as arbiters.
+ If no index can be inferred when a unique index inference clause is
+ given, an error is raised.
</para>
+ <tip>
+ <para>
+ A unique index inference clause should be preferred over naming a
+ constraint directly using <literal>ON CONFLICT ON
+ CONSTRAINT</literal> <replaceable class="PARAMETER">
+ constraint_name</replaceable>. Unique index inference is
+ adaptable to nonsignificant changes in the available constraints.
+ Furthermore, it is possible for more than one constraint and/or
+ unique index to be inferred for the same statement.
+ </para>
+ </tip>
+
<para>
<parameter>conflict_action</parameter> defines the action to be
taken in case of conflict. <literal>ON CONFLICT DO
@@ -447,11 +460,8 @@ INSERT INTO <replaceable class="PARAMETER">table_name</replaceable> [ AS <replac
<literal>ON CONFLICT DO UPDATE</literal> guarantees an atomic
<command>INSERT</command> or <command>UPDATE</command> outcome - provided
there is no independent error, one of those two outcomes is guaranteed,
- even under high concurrency. This feature is also known as
- <firstterm>UPSERT</firstterm>.
-
- Note that exclusion constraints are not supported with
- <literal>ON CONFLICT DO UPDATE</literal>.
+ even under high concurrency. This is also known as
+ <firstterm>UPSERT</firstterm> — <quote>UPDATE or INSERT</quote>.
</para>
<para>
@@ -466,14 +476,15 @@ INSERT INTO <replaceable class="PARAMETER">table_name</replaceable> [ AS <replac
<para>
The <literal>SET</literal> and <literal>WHERE</literal> clauses in
- <literal>ON CONFLICT UPDATE</literal> have access to the existing
- row, using the table's name, and to the row
- proposed for insertion, using the <varname>excluded</varname>
- alias. The <varname>excluded</varname> alias requires
- <literal>SELECT</> privilege on any column whose values are read.
+ <literal>ON CONFLICT DO UPDATE</literal> have access to the
+ existing row using the table's name (or an alias), and to the row
+ proposed for insertion using the special
+ <varname>EXCLUDED</varname> table. <literal>SELECT</> privilege is
+ required on any column in the target table where corresponding
+ <varname>EXCLUDED</varname> columns are read.
Note that the effects of all per-row <literal>BEFORE INSERT</literal>
- triggers are reflected in <varname>excluded</varname> values, since those
+ triggers are reflected in <varname>EXCLUDED</varname> values, since those
effects may have contributed to the row being excluded from insertion.
</para>
@@ -483,8 +494,10 @@ INSERT INTO <replaceable class="PARAMETER">table_name</replaceable> [ AS <replac
that the command will not be allowed to affect any single existing
row more than once; a cardinality violation error will be raised
when this situation arises. Rows proposed for insertion should not
- duplicate each other in terms of attributes constrained by the
- conflict-arbitrating unique index.
+ duplicate each other in terms of attributes constrained by a
+ conflict-arbitrating index or constraint. Note that exclusion
+ constraints are not supported with <literal>ON CONFLICT DO
+ UPDATE</literal>.
</para>
</refsect1>
@@ -617,12 +630,12 @@ INSERT INTO employees_log SELECT *, current_timestamp FROM upd;
<para>
Insert or update new distributors as appropriate. Assumes a unique
index has been defined that constrains values appearing in the
- <literal>did</literal> column. Note that an <varname>EXCLUDED</>
- expression is used to reference values originally proposed for
- insertion:
+ <literal>did</literal> column. Note that the special
+ <varname>EXCLUDED</> table is used to reference values originally
+ proposed for insertion:
<programlisting>
INSERT INTO distributors (did, dname)
- VALUES (5, 'Gizmo transglobal'), (6, 'Associated Computing, inc')
+ VALUES (5, 'Gizmo Transglobal'), (6, 'Associated Computing, inc')
ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname;
</programlisting>
</para>
--
1.9.1
From 353fcc4773cd81f526adeae263d51e903411ca96 Mon Sep 17 00:00:00 2001
From: Peter Geoghegan <peter.geoghega...@gmail.com>
Date: Sun, 11 Oct 2015 15:55:21 -0700
Subject: [PATCH 1/2] Adjust deferrable constraints error message
The previous message indicated that deferred constraints were
unsupported, which might be interpreted to mean that ON CONFLICT
supported deferrable constraints provided immediate mode enforcement was
used. Make it clear that ON CONFLICT does not support DEFERRABLE
INITIALLY IMMEDIATE constraints (or any kind of deferrable constraint).
---
src/backend/executor/execIndexing.c | 4 ++--
1 file changed, 2 insertions(+), 2 deletions(-)
diff --git a/src/backend/executor/execIndexing.c b/src/backend/executor/execIndexing.c
index f42bd8f..7299329 100644
--- a/src/backend/executor/execIndexing.c
+++ b/src/backend/executor/execIndexing.c
@@ -535,8 +535,8 @@ ExecCheckIndexConstraints(TupleTableSlot *slot,
if (!indexRelation->rd_index->indimmediate)
ereport(ERROR,
- (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("ON CONFLICT does not support deferred unique constraints/exclusion constraints as arbiters"),
+ (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("ON CONFLICT does not support deferrable unique constraints/exclusion constraints as arbiters"),
errtableconstraint(heapRelation,
RelationGetRelationName(indexRelation))));
--
1.9.1
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
