On Mon, Oct 27, 2014 at 5:15 PM, Peter Geoghegan <p...@heroku.com> wrote:
>> Let's see if we can link these two thoughts.
>>
>> 1. You think the biggest problem is the lack of attention to the design.
>>
>> 2. I keep asking you to put the docs in a readable form.
>>
>> If you can't understand the link between those two things, I am at a loss.
>
> You've read the docs. Please be clearer. In what sense are they not
> readable? The main description of the feature appears on the INSERT
> reference page:
>
> http://postgres-benchmarks.s3-website-us-east-1.amazonaws.com/on-conflict-docs/sql-insert.html
I've updated that reference page. I did a fair amount of copy-editing,
but also updated the docs to describe the latest (unpublished)
refinements to the syntax. Which is, as you and Robert requested, that
the target and rejected-for-insertion tuples may be referenced with
magical aliases in the style of OLD.* and NEW.*. I've spelt these
aliases as TARGET.* and EXCLUDED.*, since OLD.* and NEW.* didn't seem
to make much sense here. This requires some special processing during
rewriting (which, as you probably know, is true of DML statements in
general), and is certainly more invasive than what I had before, but
all told isn't too bad. Basically, there is still an ExcludedExpr, but
it only appears in the post-rewrite query tree, and is never created
by the raw grammar or processed during parse analysis.
I attach the doc patch with the relevant changes, in case you'd like a
quick reference to where things are changed.
I have already implemented the two things that you and Robert asked
for most recently: A costing model for unique index inference, and the
above syntax. I've also added IGNORE support to postgres_fdw (so you
can IGNORE if and only if a unique index inference specification is
omitted, just as with updatable views since V1.3).
Currently, I'm working on fixing an issue with RLS that I describe in
detail here:
https://wiki.postgresql.org/wiki/UPSERT#RLS
Once I fix that (provided it doesn't take too long), I'll publish a
V1.4. AFAICT, that'll close out all of the current open issues.
I hope this goes some way towards addressing your concerns.
--
Peter Geoghegan
From b9556286b3710234a32ce48e8d163d5e844154b8 Mon Sep 17 00:00:00 2001
From: Peter Geoghegan <p...@heroku.com>
Date: Fri, 26 Sep 2014 20:59:04 -0700
Subject: [PATCH 6/6] User-visible documentation for INSERT ... ON CONFLICT
{UPDATE | IGNORE}
INSERT ... ON CONFLICT {UPDATE | IGNORE} is documented as a new clause
of the INSERT command. Some potentially surprising interactions with
triggers are noted -- BEFORE INSERT per-row triggers must fire without
the INSERT path necessarily being taken, for example.
All the existing features that INSERT ... ON CONFLICT {UPDATE | IGNORE}
fails to completely play nice with have those limitations noted. (Notes
are added to the existing documentation for those other features,
although some of these cases will need to be revisited). This includes
postgres_fdw, updatable views and table inheritance (although these have
most interesting cases covered, particularly inheritance).
Finally, a user-level description of the new "MVCC violation" that the
ON CONFLICT UPDATE variant sometimes requires has been added to "Chapter
13 - Concurrency Control", beside existing commentary on READ COMMITTED
mode's special handling of concurrent updates. The new "MVCC violation"
introduced seems somewhat distinct from the existing one (i.e. what is
internally referred to as the EvalPlanQual() mechanism), because in READ
COMMITTED mode it is no longer necessary for any row version to be
conventionally visible to the command's MVCC snapshot for an UPDATE of
the row to occur (or for the row to be locked, should the WHERE clause
predicate not be satisfied).
---
doc/src/sgml/ddl.sgml | 23 +++
doc/src/sgml/fdwhandler.sgml | 8 ++
doc/src/sgml/indices.sgml | 11 +-
doc/src/sgml/keywords.sgml | 7 +
doc/src/sgml/mvcc.sgml | 24 ++++
doc/src/sgml/plpgsql.sgml | 14 +-
doc/src/sgml/postgres-fdw.sgml | 8 ++
doc/src/sgml/ref/create_index.sgml | 7 +-
doc/src/sgml/ref/create_rule.sgml | 6 +-
doc/src/sgml/ref/create_table.sgml | 5 +-
doc/src/sgml/ref/create_trigger.sgml | 5 +-
doc/src/sgml/ref/create_view.sgml | 33 ++++-
doc/src/sgml/ref/insert.sgml | 262 +++++++++++++++++++++++++++++++---
doc/src/sgml/ref/set_constraints.sgml | 6 +-
doc/src/sgml/trigger.sgml | 29 +++-
15 files changed, 419 insertions(+), 29 deletions(-)
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index f9dc151..1bf3537 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -2428,9 +2428,27 @@ VALUES ('Albany', NULL, NULL, 'NY');
</para>
<para>
+ There is limited inheritance support for <command>INSERT</command>
+ commands with <literal>ON CONFLICT</> clauses. Tables with
+ children are not generally accepted as targets. One notable
+ exception is that such tables are accepted as targets for
+ <command>INSERT</command> commands with <literal>ON CONFLICT
+ IGNORE</> clauses, provided a unique index inference clause was
+ omitted (which implies that there is no concern about
+ <emphasis>which</> unique index any would-be conflict might arise
+ from). However, tables that happen to be inheritance children are
+ accepted as targets for all variants of <command>INSERT</command>
+ with <literal>ON CONFLICT</>.
+ </para>
+
+ <para>
All check constraints and not-null constraints on a parent table are
automatically inherited by its children. Other types of constraints
(unique, primary key, and foreign key constraints) are not inherited.
+ Therefore, <command>INSERT</command> with <literal>ON CONFLICT</>
+ unique index inference considers only unique constraints/indexes
+ directly associated with the child
+ table.
</para>
<para>
@@ -2515,6 +2533,11 @@ VALUES ('Albany', NULL, NULL, 'NY');
not <literal>INSERT</literal> or <literal>ALTER TABLE ...
RENAME</literal>) typically default to including child tables and
support the <literal>ONLY</literal> notation to exclude them.
+ <literal>INSERT</literal> with an <literal>ON CONFLICT
+ UPDATE</literal> clause does not support the
+ <literal>ONLY</literal> notation, in effect tables with inheritance
+ children are not supported for that <literal>ON CONFLICT</literal>
+ variant.
Commands that do database maintenance and tuning
(e.g., <literal>REINDEX</literal>, <literal>VACUUM</literal>)
typically only work on individual, physical tables and do not
diff --git a/doc/src/sgml/fdwhandler.sgml b/doc/src/sgml/fdwhandler.sgml
index c1daa4b..0c3dcb5 100644
--- a/doc/src/sgml/fdwhandler.sgml
+++ b/doc/src/sgml/fdwhandler.sgml
@@ -1014,6 +1014,14 @@ GetForeignServerByName(const char *name, bool missing_ok);
source provides.
</para>
+ <para>
+ <command>INSERT</> with an <literal>ON CONFLICT</> clause is not supported
+ with a unique index inference specification (this implies that <literal>ON
+ CONFLICT UPDATE</> is never supported, since the specification is
+ mandatory there). When planning an <command>INSERT</>,
+ <function>PlanForeignModify</> should reject these cases.
+ </para>
+
</sect1>
</chapter>
diff --git a/doc/src/sgml/indices.sgml b/doc/src/sgml/indices.sgml
index 64530a1..e6b9112 100644
--- a/doc/src/sgml/indices.sgml
+++ b/doc/src/sgml/indices.sgml
@@ -922,7 +922,16 @@ CREATE UNIQUE INDEX tests_success_constraint ON tests (subject, target)
know when an index might be profitable. Forming this knowledge
requires experience and understanding of how indexes in
<productname>PostgreSQL</> work. In most cases, the advantage of a
- partial index over a regular index will be minimal.
+ partial index over a regular index will be minimal. Also, note
+ that partial unique indexes are not compatible with INSERT with an
+ <literal>ON CONFLICT UPDATE</> clause, if it is expected that a
+ would-be uniqueness violation associated with the partial index
+ should provoke an alternative <literal>UPDATE</> or
+ <literal>IGNORE</> path, and an explicit condition for taking the
+ alternative path was specified (as it must be with the
+ <literal>UPDATE</> variant). Unique index inference will never
+ infer that a partial unique index is appropriate due to
+ implementation-specific restrictions.
</para>
<para>
diff --git a/doc/src/sgml/keywords.sgml b/doc/src/sgml/keywords.sgml
index b0dfd5f..ea58211 100644
--- a/doc/src/sgml/keywords.sgml
+++ b/doc/src/sgml/keywords.sgml
@@ -854,6 +854,13 @@
<entry></entry>
</row>
<row>
+ <entry><token>CONFLICT</token></entry>
+ <entry>non-reserved</entry>
+ <entry></entry>
+ <entry></entry>
+ <entry></entry>
+ </row>
+ <row>
<entry><token>CONNECT</token></entry>
<entry></entry>
<entry>reserved</entry>
diff --git a/doc/src/sgml/mvcc.sgml b/doc/src/sgml/mvcc.sgml
index cd55be8..0248829 100644
--- a/doc/src/sgml/mvcc.sgml
+++ b/doc/src/sgml/mvcc.sgml
@@ -326,6 +326,30 @@
</para>
<para>
+ <command>INSERT</command> with an <literal>ON CONFLICT UPDATE</> clause is
+ another special case. In Read Committed mode, the implementation will
+ either insert or update each row proposed for insertion, with either one of
+ those two outcomes guaranteed. This is a useful guarantee for many
+ use-cases, but it implies that further liberties must be taken with
+ snapshot isolation. Should a conflict originate in another transaction
+ whose effects are not visible to the <command>INSERT</command>, the
+ <command>UPDATE</command> may affect that row, even though it may be the
+ case that <emphasis>no</> version of that row is conventionally visible to
+ the command. In the same vein, if the secondary search condition of the
+ command (an explicit <literal>WHERE</> clause) is supplied, it is only
+ evaluated on the most recent row version, which is not necessarily the
+ version conventionally visible to the command (if indeed there is a row
+ version conventionally visible to the command at all).
+ </para>
+
+ <para>
+ <command>INSERT</command> with an <literal>ON CONFLICT IGNORE</> clause may
+ have insertion not proceed for a row due to the outcome of another
+ transaction whose effects are not visible to the <command>INSERT</command>
+ snapshot. Again, this is only the case in Read Committed mode.
+ </para>
+
+ <para>
Because of the above rule, it is possible for an updating command to see an
inconsistent snapshot: it can see the effects of concurrent updating
commands on the same rows it is trying to update, but it
diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
index f195495..c4d9004 100644
--- a/doc/src/sgml/plpgsql.sgml
+++ b/doc/src/sgml/plpgsql.sgml
@@ -2599,7 +2599,11 @@ END;
<para>
This example uses exception handling to perform either
- <command>UPDATE</> or <command>INSERT</>, as appropriate:
+ <command>UPDATE</> or <command>INSERT</>, as appropriate. It is
+ recommended that applications use <command>INSERT</> with
+ <literal>ON CONFLICT UPDATE</> rather than actually emulating this
+ pattern. This example serves only to illustrate use of
+ <application>PL/pgSQL</application> control flow structures:
<programlisting>
CREATE TABLE db (a INT PRIMARY KEY, b TEXT);
@@ -3754,9 +3758,11 @@ RAISE unique_violation USING MESSAGE = 'Duplicate user ID: ' || user_id;
<command>INSERT</> and <command>UPDATE</> operations, the return value
should be <varname>NEW</>, which the trigger function may modify to
support <command>INSERT RETURNING</> and <command>UPDATE RETURNING</>
- (this will also affect the row value passed to any subsequent triggers).
- For <command>DELETE</> operations, the return value should be
- <varname>OLD</>.
+ (this will also affect the row value passed to any subsequent triggers,
+ or passed to a special <varname>EXCLUDED</> alias reference within
+ an <command>INSERT</> statement with an <literal>ON CONFLICT UPDATE</>
+ clause). For <command>DELETE</> operations, the return
+ value should be <varname>OLD</>.
</para>
<para>
diff --git a/doc/src/sgml/postgres-fdw.sgml b/doc/src/sgml/postgres-fdw.sgml
index 43adb61..fa39661 100644
--- a/doc/src/sgml/postgres-fdw.sgml
+++ b/doc/src/sgml/postgres-fdw.sgml
@@ -69,6 +69,14 @@
</para>
<para>
+ Note that <filename>postgres_fdw</> currently lacks support for
+ <command>INSERT</command> statements with an <literal>ON CONFLICT
+ UPDATE</> clause. However, the <literal>ON CONFLICT IGNORE</>
+ clause is supported, provided a unique index inference specification
+ is omitted.
+ </para>
+
+ <para>
It is generally recommended that the columns of a foreign table be declared
with exactly the same data types, and collations if applicable, as the
referenced columns of the remote table. Although <filename>postgres_fdw</>
diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgml
index 43df32f..1bebf4c 100644
--- a/doc/src/sgml/ref/create_index.sgml
+++ b/doc/src/sgml/ref/create_index.sgml
@@ -72,7 +72,12 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ <replaceable class="parameter">name</
can improve performance by creating an index on just that portion.
Another possible application is to use <literal>WHERE</literal> with
<literal>UNIQUE</literal> to enforce uniqueness over a subset of a
- table. See <xref linkend="indexes-partial"> for more discussion.
+ table. Note, however, that partial unique indexes are not
+ compatible with INSERT with an <literal>ON CONFLICT UPDATE</>
+ clause (if it is expected that a would-be uniqueness violation
+ associated with the partial index should provoke an alternative
+ <literal>UPDATE</>/<literal>IGNORE</> path). See <xref
+ linkend="indexes-partial"> for more discussion.
</para>
<para>
diff --git a/doc/src/sgml/ref/create_rule.sgml b/doc/src/sgml/ref/create_rule.sgml
index 677766a..9b5c740 100644
--- a/doc/src/sgml/ref/create_rule.sgml
+++ b/doc/src/sgml/ref/create_rule.sgml
@@ -136,7 +136,11 @@ CREATE [ OR REPLACE ] RULE <replaceable class="parameter">name</replaceable> AS
<para>
The event is one of <literal>SELECT</literal>,
<literal>INSERT</literal>, <literal>UPDATE</literal>, or
- <literal>DELETE</literal>.
+ <literal>DELETE</literal>. Note that an
+ <command>INSERT</command> containing an <literal>ON
+ CONFLICT</literal> clause is unsupported. Consider using an
+ updatable view instead, which have limited support for
+ <literal>ON CONFLICT IGNORE</literal> only.
</para>
</listitem>
</varlistentry>
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 299cce8..a9c1124 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -708,7 +708,10 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
<literal>EXCLUDE</>, and
<literal>REFERENCES</> (foreign key) constraints accept this
clause. <literal>NOT NULL</> and <literal>CHECK</> constraints are not
- deferrable.
+ deferrable. Note that constraints that were created with this
+ clause cannot be used as arbiters of whether or not to take the
+ alternative path with an <command>INSERT</command> statement
+ that includes an <literal>ON CONFLICT UPDATE</> clause.
</para>
</listitem>
</varlistentry>
diff --git a/doc/src/sgml/ref/create_trigger.sgml b/doc/src/sgml/ref/create_trigger.sgml
index 29b815c..26a0986 100644
--- a/doc/src/sgml/ref/create_trigger.sgml
+++ b/doc/src/sgml/ref/create_trigger.sgml
@@ -76,7 +76,10 @@ CREATE [ CONSTRAINT ] TRIGGER <replaceable class="PARAMETER">name</replaceable>
executes once for any given operation, regardless of how many rows
it modifies (in particular, an operation that modifies zero rows
will still result in the execution of any applicable <literal>FOR
- EACH STATEMENT</literal> triggers).
+ EACH STATEMENT</literal> triggers). Note that since
+ <command>INSERT</command> with an <literal>ON CONFLICT UPDATE</>
+ clause is considered an <command>INSERT</command> statement, no
+ <command>UPDATE</command> statement level trigger will be fired.
</para>
<para>
diff --git a/doc/src/sgml/ref/create_view.sgml b/doc/src/sgml/ref/create_view.sgml
index 2b7a98f..aea8447 100644
--- a/doc/src/sgml/ref/create_view.sgml
+++ b/doc/src/sgml/ref/create_view.sgml
@@ -291,8 +291,9 @@ CREATE VIEW vista AS SELECT text 'Hello World' AS hello;
<para>
Simple views are automatically updatable: the system will allow
<command>INSERT</>, <command>UPDATE</> and <command>DELETE</> statements
- to be used on the view in the same way as on a regular table. A view is
- automatically updatable if it satisfies all of the following conditions:
+ to be used on the view in the same way as on a regular table (aside from
+ the limitations on ON CONFLICT noted below). A view is automatically
+ updatable if it satisfies all of the following conditions:
<itemizedlist>
<listitem>
@@ -388,6 +389,34 @@ CREATE VIEW vista AS SELECT text 'Hello World' AS hello;
not need any permissions on the underlying base relations (see
<xref linkend="rules-privileges">).
</para>
+ <para>
+ <command>INSERT</command> with an <literal>ON CONFLICT</> clause
+ is only supported on updatable views under specific circumstances.
+ If a set of columns/expressions has been provided with which to
+ infer a unique index to consider as the arbiter of whether the
+ statement ultimately takes an alternative path - if a would-be
+ duplicate violation in some particular unique index is tacitly
+ taken as provoking an alternative <command>UPDATE</command> or
+ <literal>IGNORE</> path - then updatable views are not supported.
+ Since this specification is already mandatory for
+ <command>INSERT</command> with <literal>ON CONFLICT UPDATE</>,
+ this implies that only the <literal>ON CONFLICT IGNORE</> variant
+ is supported, and only when there is no such specification. For
+ example:
+ </para>
+ <para>
+<programlisting>
+-- Unsupported:
+INSERT INTO my_updatable_view(key, val) VALUES(1, 'foo') ON CONFLICT (key)
+ UPDATE SET val = EXCLUDED.val;
+INSERT INTO my_updatable_view(key, val) VALUES(1, 'bar') ON CONFLICT (key)
+ IGNORE;
+
+-- Supported (note the omission of "key" column):
+INSERT INTO my_updatable_view(key, val) VALUES(1, 'baz') ON CONFLICT
+ IGNORE;
+</programlisting>
+ </para>
</refsect2>
</refsect1>
diff --git a/doc/src/sgml/ref/insert.sgml b/doc/src/sgml/ref/insert.sgml
index a3cccb9..f5e1541 100644
--- a/doc/src/sgml/ref/insert.sgml
+++ b/doc/src/sgml/ref/insert.sgml
@@ -24,6 +24,14 @@ PostgreSQL documentation
[ WITH [ RECURSIVE ] <replaceable class="parameter">with_query</replaceable> [, ...] ]
INSERT INTO <replaceable class="PARAMETER">table_name</replaceable> [ ( <replaceable class="PARAMETER">column_name</replaceable> [, ...] ) ]
{ DEFAULT VALUES | VALUES ( { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } [, ...] ) [, ...] | <replaceable class="PARAMETER">query</replaceable> }
+ [ ON CONFLICT [ ( { <replaceable class="parameter">column_name_index</replaceable> | ( <replaceable class="parameter">expression_index</replaceable> ) } [, ...] ) ]
+ { IGNORE | UPDATE
+ SET { <replaceable class="PARAMETER">column_name</replaceable> = { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } |
+ ( <replaceable class="PARAMETER">column_name</replaceable> [, ...] ) = ( { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } [, ...] )
+ } [, ...]
+ [ WHERE <replaceable class="PARAMETER">condition</replaceable> ]
+ }
+ ]
[ RETURNING * | <replaceable class="parameter">output_expression</replaceable> [ [ AS ] <replaceable class="parameter">output_name</replaceable> ] [, ...] ]
</synopsis>
</refsynopsisdiv>
@@ -32,9 +40,15 @@ INSERT INTO <replaceable class="PARAMETER">table_name</replaceable> [ ( <replace
<title>Description</title>
<para>
- <command>INSERT</command> inserts new rows into a table.
- One can insert one or more rows specified by value expressions,
- or zero or more rows resulting from a query.
+ <command>INSERT</command> inserts new rows into a table. One can
+ insert one or more rows specified by value expressions, or zero or
+ more rows resulting from a query. An alternative path
+ (<literal>IGNORE</literal> or <literal>UPDATE</literal>) can
+ optionally be specified, to be taken in the event of detecting that
+ proceeding with insertion would result in a uniqueness violation
+ (i.e. a conflicting tuple already exists). The alternative path is
+ considered individually for each row proposed for insertion, and is
+ taken (or not taken) once per row.
</para>
<para>
@@ -59,22 +73,142 @@ INSERT INTO <replaceable class="PARAMETER">table_name</replaceable> [ ( <replace
</para>
<para>
+ The optional <literal>ON CONFLICT</> clause specifies a path to
+ take as an alternative to raising a uniqueness violation error.
+ <literal>ON CONFLICT IGNORE</> simply avoids inserting any
+ individual row when it is determined that a uniqueness violation
+ error would otherwise need to be raised. <literal>ON CONFLICT
+ UPDATE</> has the system take an <command>UPDATE</command> path in
+ respect of such rows instead. <literal>ON CONFLICT UPDATE</>
+ guarantees an atomic <command>INSERT</command> or
+ <command>UPDATE</command> outcome - provided there is no incidental
+ error, one of those two outcomes is guaranteed, even under high
+ concurrency. Note that in the event of an <literal>ON CONFLICT</>
+ path being taken, <literal>RETURNING</> returns no value in respect
+ of any not-inserted rows.
+ </para>
+
+ <para>
+ <literal>ON CONFLICT UPDATE</> optionally accepts a
+ <literal>WHERE</> clause <replaceable>condition</>. When provided,
+ the statement only proceeds with updating if the
+ <replaceable>condition</> is satisfied. Otherwise, unlike a
+ conventional <command>UPDATE</command>, the row is still locked for
+ update. Note that the <replaceable>condition</> is evaluated last,
+ after a conflict has been identified as a candidate to update.
+ </para>
+
+ <para>
+ <literal>ON CONFLICT UPDATE</> is effectively an auxiliary query of
+ its parent <command>INSERT</command>. Two aliases are visible to
+ the auxiliary query only - <varname>TARGET</> and
+ <varname>EXCLUDED</>. The first alias is just a standard alias for
+ the target relation in the context of the auxiliary query, while
+ the second alias refers to rows originally proposed for insertion.
+ Both aliases can be used in the auxiliary query targetlist and
+ <literal>WHERE</> clause. This allows expressions (in particular,
+ assignments) to reference rows originally proposed for insertion.
+ Note that the effects of all per-row <literal>BEFORE INSERT</>
+ triggers are carried forward. This is particularly useful for
+ multi-insert <literal>ON CONFLICT UPDATE</> statements; when
+ inserting or updating multiple rows, constants need only appear
+ once.
+ </para>
+
+ <para>
+ There are several restrictions on the <literal>ON CONFLICT
+ UPDATE</> clause that do not apply to <command>UPDATE</command>
+ statements. Subqueries may not appear in either the
+ <command>UPDATE</command> targetlist, nor its <literal>WHERE</>
+ clause (although simple multi-assignment expressions are
+ supported). <literal>WHERE CURRENT OF</> cannot be used. In
+ general, only columns in the target table, and excluded values
+ originally proposed for insertion may be referenced. Operators and
+ functions may be used freely, though.
+ </para>
+
+ <para>
+ <command>INSERT</command> with an <literal>ON CONFLICT UPDATE</>
+ clause is a <quote>deterministic</quote> statement. This means
+ that the command will not <command>UPDATE</command> any single 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. Note that the ordinary rules
+ for unique indexes with regard to null apply analogously to whether
+ or not an arbitrating unique index indicates if the alternative
+ path should be taken. This means that when a null value appears in
+ any uniquely constrained tuple's attribute in an
+ <command>INSERT</command> statement with <literal>ON CONFLICT
+ UPDATE</literal>, rows proposed for insertion will never take the
+ alternative path (provided that a <literal>BEFORE ROW
+ INSERT</literal> trigger does not make null values non-null before
+ insertion); the statement will always insert, assuming there is no
+ unrelated error. Note that merely locking a row (by having it not
+ satisfy the <literal>WHERE</> clause <replaceable>condition</>)
+ does not count towards whether or not the row has been affected
+ multiple times (and whether or not a cardinality violation error is
+ raised).
+ </para>
+
+ <para>
+ <literal>ON CONFLICT UPDATE</> requires a <emphasis>unique index
+ inference</emphasis> specification, which is an expression
+ containing one or more columns or expressions on columns. These
+ are used to infer a single unique index to limit pre-checking for
+ duplicates to (if no appropriate index is available, an error is
+ raised). <literal>ON CONFLICT IGNORE</> makes this optional.
+ Omitting the specification indicates a total indifference to where
+ any would-be uniqueness violation could occur, which isn't always
+ appropriate; at times, it may be desirable for <literal>ON
+ CONFLICT IGNORE</> to <emphasis>not</emphasis> suppress a duplicate
+ violation within an index where that isn't explicitly anticipated.
+ Note that <literal>ON CONFLICT UPDATE</> assignment may result in a
+ uniqueness violation, just as with a conventional
+ <command>UPDATE</command>.
+ </para>
+
+ <para>
+ The rules for unique index inference are straightforward. Columns
+ and/or expressions specified must match all the columns/expressions
+ of some existing unique index on <replaceable
+ class="PARAMETER">table_name</replaceable>. The order of the
+ columns/expressions in the index definition, or whether or not the
+ index definition specified <literal>NULLS FIRST</> or
+ <literal>NULLS LAST</>, or the internal sort order of each column
+ (whether <literal>DESC</> or <literal>ASC</> were specified) are
+ all irrelevant. However, partial unique indexes are not supported
+ as arbiters of whether an alternative <literal>ON CONFLICT</> path
+ should be taken, nor are deferred unique constraints.
+ </para>
+
+ <para>
The optional <literal>RETURNING</> clause causes <command>INSERT</>
to compute and return value(s) based on each row actually inserted.
This is primarily useful for obtaining values that were supplied by
defaults, such as a serial sequence number. However, any expression
using the table's columns is allowed. The syntax of the
<literal>RETURNING</> list is identical to that of the output list
- of <command>SELECT</>.
+ of <command>SELECT</>. Only rows that were successfully inserted
+ will be returned. Since <literal>RETURNING</> is not part of the
+ <command>UPDATE</> auxiliary query, the special <literal>ON
+ CONFLICT UPDATE</> aliases (<varname>TARGET</> and
+ <varname>EXCLUDED</>) may not be referenced.
</para>
<para>
You must have <literal>INSERT</literal> privilege on a table in
- order to insert into it. If a column list is specified, you only
- need <literal>INSERT</literal> privilege on the listed columns.
- Use of the <literal>RETURNING</> clause requires <literal>SELECT</>
- privilege on all columns mentioned in <literal>RETURNING</>.
- If you use the <replaceable
+ order to insert into it, as well as <literal>UPDATE
+ privilege</literal> if and only if <literal>ON CONFLICT UPDATE</>
+ is specified. If a column list is specified, you only need
+ <literal>INSERT</literal> privilege on the listed columns.
+ Similarly, when <literal>ON CONFLICT UPDATE</> is specified, you
+ only need <literal>UPDATE</> privilege on the column(s) that are
+ listed to be updated, as well as SELECT privilege on any column
+ whose values are read in the <literal>ON CONFLICT UPDATE</>
+ expressions or condition. Use of the <literal>RETURNING</> clause
+ requires <literal>SELECT</> privilege on all columns mentioned in
+ <literal>RETURNING</>. If you use the <replaceable
class="PARAMETER">query</replaceable> clause to insert rows from a
query, you of course need to have <literal>SELECT</literal> privilege on
any table or column used in the query.
@@ -121,7 +255,45 @@ INSERT INTO <replaceable class="PARAMETER">table_name</replaceable> [ ( <replace
The name of a column in the table named by <replaceable class="PARAMETER">table_name</replaceable>.
The column name can be qualified with a subfield name or array
subscript, if needed. (Inserting into only some fields of a
- composite column leaves the other fields null.)
+ composite column leaves the other fields null.) When
+ referencing a column with <literal>ON CONFLICT UPDATE</>, do not
+ include the table's name in the specification of a target
+ column. For example, <literal>INSERT ... ON CONFLICT UPDATE tab
+ SET TARGET.col = 1</> is invalid.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="PARAMETER">column_name_index</replaceable></term>
+ <listitem>
+ <para>
+ The name of a <replaceable
+ class="PARAMETER">table_name</replaceable> column (with several
+ columns potentially named). These are used to infer a
+ particular unique index defined on <replaceable
+ class="PARAMETER">table_name</replaceable>. This requires
+ <literal>ON CONFLICT UPDATE</> and <literal>ON CONFLICT
+ IGNORE</> to assume that all expected sources of uniqueness
+ violations originate within the columns/rows constrained by the
+ unique index. When this is omitted, (which is forbidden with
+ the <literal>ON CONFLICT UPDATE</> variant), the system checks
+ for sources of uniqueness violations ahead of time in all unique
+ indexes. Otherwise, only a single specified unique index is
+ checked ahead of time, and uniqueness violation errors can
+ appear for conflicts originating in any other unique index. If
+ a unique index cannot be inferred, an error is raised.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="PARAMETER">expression_index</replaceable></term>
+ <listitem>
+ <para>
+ Equivalent to <replaceable
+ class="PARAMETER">column_name_index</replaceable>, but used to
+ infer a particular expressional index instead.
</para>
</listitem>
</varlistentry>
@@ -167,12 +339,25 @@ INSERT INTO <replaceable class="PARAMETER">table_name</replaceable> [ ( <replace
</varlistentry>
<varlistentry>
+ <term><replaceable class="PARAMETER">condition</replaceable></term>
+ <listitem>
+ <para>
+ An expression that returns a value of type <type>boolean</type>.
+ Only rows for which this expression returns <literal>true</>
+ will be updated, although all rows will be locked when the
+ <literal>ON CONFLICT UPDATE</> path is taken.
+ </para>
+ </listitem>
+ </varlistentry>
+ <varlistentry>
+
<term><replaceable class="PARAMETER">output_expression</replaceable></term>
<listitem>
<para>
An expression to be computed and returned by the <command>INSERT</>
- command after each row is inserted. The expression can use any
- column names of the table named by <replaceable class="PARAMETER">table_name</replaceable>.
+ command after each row is inserted (not updated). The
+ expression can use any column names of the table named by
+ <replaceable class="PARAMETER">table_name</replaceable>.
Write <literal>*</> to return all columns of the inserted row(s).
</para>
</listitem>
@@ -204,14 +389,16 @@ INSERT <replaceable>oid</replaceable> <replaceable class="parameter">count</repl
<replaceable class="parameter">oid</replaceable> is the
<acronym>OID</acronym> assigned to the inserted row. Otherwise
<replaceable class="parameter">oid</replaceable> is zero.
+ The command tag does not indicate the number of rows updated by
+ <literal>ON CONFLICT UPDATE</>.
</para>
<para>
If the <command>INSERT</> command contains a <literal>RETURNING</>
clause, the result will be similar to that of a <command>SELECT</>
statement containing the columns and values defined in the
- <literal>RETURNING</> list, computed over the row(s) inserted by the
- command.
+ <literal>RETURNING</> list, computed over the row(s) inserted (not
+ updated) by the command.
</para>
</refsect1>
@@ -311,7 +498,49 @@ WITH upd AS (
RETURNING *
)
INSERT INTO employees_log SELECT *, current_timestamp FROM upd;
-</programlisting></para>
+</programlisting>
+ </para>
+ <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:
+<programlisting>
+ INSERT INTO distributors (did, dname)
+ VALUES (5, 'Gizmo transglobal'), (6, 'Doohickey, inc')
+ ON CONFLICT (did) UPDATE
+ SET dname = EXCLUDED.dname || ' (formerly ' || TARGET.dname || ')'
+</programlisting>
+ </para>
+ <para>
+ Insert a distributor, or do nothing for rows proposed for insertion
+ when an existing, excluded row (a row with a matching constrained
+ column or columns after before row insert triggers fire) exists.
+ Assumes a unique index has been defined that constrains values
+ appearing in the <literal>did</literal> column (although since the
+ <literal>IGNORE</> variant was used, the specification of columns
+ to infer a unique index from is not mandatory):
+<programlisting>
+ INSERT INTO distributors (did, dname) VALUES (7, 'Doodad GmbH')
+ ON CONFLICT (did) IGNORE
+</programlisting>
+ </para>
+ <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. <literal>WHERE</> clause is used to
+ limit the rows actually updated (any existing row not updated will
+ still be locked, though):
+<programlisting>
+ -- Don't update any existing row if it was already renamed at some
+ -- earlier stage
+ INSERT INTO distributors (did, dname) VALUES (8, 'Thingamabob Distribution')
+ ON CONFLICT (did) UPDATE
+ SET dname = EXCLUDED.dname || ' (formerly ' || TARGET.dname || ')'
+ WHERE TARGET.dname NOT LIKE '%(formerly %)'
+</programlisting>
+ </para>
</refsect1>
<refsect1>
@@ -321,7 +550,8 @@ INSERT INTO employees_log SELECT *, current_timestamp FROM upd;
<command>INSERT</command> conforms to the SQL standard, except that
the <literal>RETURNING</> clause is a
<productname>PostgreSQL</productname> extension, as is the ability
- to use <literal>WITH</> with <command>INSERT</>.
+ to use <literal>WITH</> with <command>INSERT</>, and the ability to
+ specify an alternative path with <literal>ON CONFLICT</>.
Also, the case in
which a column name list is omitted, but not all the columns are
filled from the <literal>VALUES</> clause or <replaceable>query</>,
diff --git a/doc/src/sgml/ref/set_constraints.sgml b/doc/src/sgml/ref/set_constraints.sgml
index 7c31871..1e0a2f8 100644
--- a/doc/src/sgml/ref/set_constraints.sgml
+++ b/doc/src/sgml/ref/set_constraints.sgml
@@ -69,7 +69,11 @@ SET CONSTRAINTS { ALL | <replaceable class="parameter">name</replaceable> [, ...
<para>
Currently, only <literal>UNIQUE</>, <literal>PRIMARY KEY</>,
<literal>REFERENCES</> (foreign key), and <literal>EXCLUDE</>
- constraints are affected by this setting.
+ constraints are affected by this setting. Note that constraints
+ that were created with this clause cannot be used as arbiters of
+ whether or not to take the alternative path with an
+ <command>INSERT</command> statement that includes an <literal>ON
+ CONFLICT UPDATE</> clause.
<literal>NOT NULL</> and <literal>CHECK</> constraints are
always checked immediately when a row is inserted or modified
(<emphasis>not</> at the end of the statement).
diff --git a/doc/src/sgml/trigger.sgml b/doc/src/sgml/trigger.sgml
index f94aea1..71ddeee 100644
--- a/doc/src/sgml/trigger.sgml
+++ b/doc/src/sgml/trigger.sgml
@@ -40,7 +40,10 @@
On tables and foreign tables, triggers can be defined to execute either
before or after any <command>INSERT</command>, <command>UPDATE</command>,
or <command>DELETE</command> operation, either once per modified row,
- or once per <acronym>SQL</acronym> statement.
+ or once per <acronym>SQL</acronym> statement. If an <command>INSERT</command>
+ contains an <literal>ON CONFLICT UPDATE</> clause, it is possible that the
+ effects of a BEFORE insert trigger and a BEFORE update trigger can both be
+ applied twice, if an <varname>EXCLUDED</> expression appears.
<command>UPDATE</command> triggers can moreover be set to fire only if
certain columns are mentioned in the <literal>SET</literal> clause of the
<command>UPDATE</command> statement.
@@ -119,6 +122,30 @@
</para>
<para>
+ If an <command>INSERT</command> contains an <literal>ON CONFLICT
+ UPDATE</> clause, it is possible that the effects of all row-level
+ <literal>BEFORE</> <command>INSERT</command> triggers and all
+ row-level BEFORE <command>UPDATE</command> triggers can both be
+ applied in a way that is apparent from the final state of the
+ updated row, if an <varname>EXCLUDED</> expression appears. There
+ need not be an <varname>EXCLUDED</> expression for both sets of
+ BEFORE row-level triggers to execute, though. The possibility of
+ surprising outcomes should be considered when there are both
+ <literal>BEFORE</> <command>INSERT</command> and
+ <literal>BEFORE</> <command>UPDATE</command> row-level triggers
+ that both affect a row being inserted/updated (this can still be
+ problematic if the modifications are more or less equivalent if
+ they're not also idempotent). Note that statement-level
+ <command>UPDATE</command> triggers are never executed when
+ <literal>ON CONFLICT UPDATE</> is specified, since technically an
+ UPDATE statement was not executed. <literal>ON CONFLICT UPDATE</>
+ is not supported on views (Only <literal>ON CONFLICT IGNORE</> is
+ supported on updatable views); therefore, unpredictable
+ interactions with <literal>INSTEAD OF</> triggers are not
+ possible.
+ </para>
+
+ <para>
Trigger functions invoked by per-statement triggers should always
return <symbol>NULL</symbol>. Trigger functions invoked by per-row
triggers can return a table row (a value of
--
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
