From 2c3693c640b654235933ec02e3c1eaf45b4e9f3a Mon Sep 17 00:00:00 2001 From: Peter Geoghegan 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; + + + INSERT statements with 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 + INSERT. In particular, trigger functions + have no way to determine how the INSERT + command's author might have intended redirected + inserts to handle conflicts in child tables. Even the + involvement of an ON CONFLICT clause is not exposed + to trigger functions. + + + 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 table_name [ AS You must have INSERT privilege on a table in order to insert into it. If ON CONFLICT DO UPDATE is - present the UPDATE privilege is also required. + present, UPDATE privilege on the table is also + required. @@ -161,10 +162,7 @@ INSERT INTO table_name [ AS 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 ON CONFLICT DO UPDATE into a table - named excluded as that's also the name of the - pseudo-relation containing the proposed row. + completely hides the actual name of the table. @@ -395,19 +393,23 @@ INSERT INTO table_name [ AS conflict_target describes which conflicts are handled by the ON CONFLICT clause. Either a unique index inference clause or an explicitly - named constraint can be used. For ON CONFLICT DO - NOTHING, it is optional to specify a - conflict_target; when omitted, conflicts - with all usable constraints (and unique indexes) are handled. For - ON CONFLICT DO UPDATE, a conflict target - must be specified. + named constraint can be used. These constraints and/or unique + indexes are arbiter indexes. For + ON CONFLICT DO NOTHING, it is optional to + specify a conflict_target; when omitted, + conflicts with all usable constraints (and unique indexes) are + handled. For ON CONFLICT DO UPDATE, a + conflict_target must be + provided. Every time an insertion without ON CONFLICT would ordinarily raise an error due to violating one of the - inferred (or explicitly named) constraints, a conflict (as in - ON CONFLICT) occurs, and the alternative action, - as specified by conflict_action is taken. - This happens on a row-by-row basis. + inferred constraints/indexes (or an explicitly named constraint), a + conflict (as in ON CONFLICT) occurs, and the + alternative action, as specified by + conflict_action is taken. This happens on a + row-by-row basis. Only NOT DEFERRABLE + constraints are supported as arbiters. @@ -425,17 +427,28 @@ INSERT INTO table_name [ AS index_predicate 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 ON CONFLICT as an arbiter) if such + an index satisfying every other criteria is available. - 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. + + + A unique index inference clause should be preferred over naming a + constraint directly using ON CONFLICT ON + CONSTRAINT + constraint_name. 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. + + + conflict_action defines the action to be taken in case of conflict. ON CONFLICT DO @@ -447,11 +460,8 @@ INSERT INTO table_name [ AS ON CONFLICT DO UPDATE guarantees an atomic INSERT or UPDATE outcome - provided there is no independent error, one of those two outcomes is guaranteed, - even under high concurrency. This feature is also known as - UPSERT. - - Note that exclusion constraints are not supported with - ON CONFLICT DO UPDATE. + even under high concurrency. This is also known as + UPSERT — UPDATE or INSERT. @@ -466,14 +476,15 @@ INSERT INTO table_name [ AS The SET and WHERE clauses in - ON CONFLICT UPDATE have access to the existing - row, using the table's name, and to the row - proposed for insertion, using the excluded - alias. The excluded alias requires - SELECT privilege on any column whose values are read. + ON CONFLICT DO UPDATE have access to the + existing row using the table's name (or an alias), and to the row + proposed for insertion using the special + EXCLUDED table. SELECT privilege is + required on any column in the target table where corresponding + EXCLUDED columns are read. Note that the effects of all per-row BEFORE INSERT - triggers are reflected in excluded values, since those + triggers are reflected in EXCLUDED values, since those effects may have contributed to the row being excluded from insertion. @@ -483,8 +494,10 @@ INSERT INTO table_name [ AS ON CONFLICT DO + UPDATE. @@ -617,12 +630,12 @@ INSERT INTO employees_log SELECT *, current_timestamp FROM upd; Insert or update new distributors as appropriate. Assumes a unique index has been defined that constrains values appearing in the - did column. Note that an EXCLUDED - expression is used to reference values originally proposed for - insertion: + did column. Note that the special + EXCLUDED table is used to reference values originally + proposed for insertion: 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; -- 1.9.1