On Thu, Sep 25, 2014 at 1:48 PM, Simon Riggs <si...@2ndquadrant.com> wrote:
> I hate the fact
> that you have written no user facing documentation for this feature.
Attached patch adds a commit to the existing patchset. For the
convenience of reviewers, I've uploaded and made publicly accessible a
html build of the documentation. This page is of most interest:
http://postgres-benchmarks.s3-website-us-east-1.amazonaws.com/on-conflict-docs/sql-insert.html
See also:
http://postgres-benchmarks.s3-website-us-east-1.amazonaws.com/on-conflict-docs/transaction-iso.html
http://postgres-benchmarks.s3-website-us-east-1.amazonaws.com/on-conflict-docs/ddl-inherit.html
http://postgres-benchmarks.s3-website-us-east-1.amazonaws.com/on-conflict-docs/sql-createrule.html
http://postgres-benchmarks.s3-website-us-east-1.amazonaws.com/on-conflict-docs/trigger-definition.html
http://postgres-benchmarks.s3-website-us-east-1.amazonaws.com/on-conflict-docs/sql-createtrigger.html
http://postgres-benchmarks.s3-website-us-east-1.amazonaws.com/on-conflict-docs/index-unique-checks.html
http://postgres-benchmarks.s3-website-us-east-1.amazonaws.com/on-conflict-docs/sql-createview.html
http://postgres-benchmarks.s3-website-us-east-1.amazonaws.com/on-conflict-docs/postgres-fdw.html
--
Peter Geoghegan
From 005eb2760b356c7383c591bb92294cc9626baabe 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 -- statement level UPDATE triggers will not fire when
INSERT ... ON CONFLICT UPDATE is executed, 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. In principle it is
the responsibility of writable foreign data wrapper authors to provide
appropriate support for this new clause (although it's hard to see how
the optional "WITHIN `unique_index`" clause could work there).
Finally, a user-level description of the new "MVCC violation" that
INSERT ... ON CONFLICT {UPDATE | IGNORE} sometimes requires has been
added to "Chapter 13 - Concurrency Control", beside existing commentary
on Read Committed mode's special handling of concurrent updates, and the
implications for snapshot isolation (i.e. what is internally referred
to as the EvalPlanQual() mechanism). The new "MVCC violation"
introduced seems somewhat distinct from the existing one, 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).
---
doc/src/sgml/ddl.sgml | 14 +++
doc/src/sgml/mvcc.sgml | 43 ++++++--
doc/src/sgml/plpgsql.sgml | 14 +--
doc/src/sgml/postgres-fdw.sgml | 8 ++
doc/src/sgml/ref/create_rule.sgml | 6 +-
doc/src/sgml/ref/create_trigger.sgml | 5 +-
doc/src/sgml/ref/create_view.sgml | 15 ++-
doc/src/sgml/ref/insert.sgml | 203 ++++++++++++++++++++++++++++++++---
doc/src/sgml/trigger.sgml | 30 +++++-
9 files changed, 302 insertions(+), 36 deletions(-)
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index c07f5a2..2910890 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -2292,6 +2292,20 @@ VALUES ('Albany', NULL, NULL, 'NY');
but in the meantime considerable care is needed in deciding whether
inheritance is useful for your application.
</para>
+ <para>
+ <!-- XXX: This limitation can probably be removed, at least for
+ the simple case where a unique index constrains an attribute
+ or attributes that are effectively contained within the
+ (implied) partitioning key. Clearly doing anything more
+ advanced than that would require a large overhaul of
+ partitioning in PostgreSQL.
+ -->
+ Since unique indexes do not constrain every child table in an
+ inheritance hierarchy, inheritance is not supported for
+ <command>INSERT</command> statements that contain an <literal>ON
+ CONFLICT UPDATE</> clause, or an <literal>ON CONFLICT IGNORE</>
+ clause.
+ </para>
</sect2>
</sect1>
diff --git a/doc/src/sgml/mvcc.sgml b/doc/src/sgml/mvcc.sgml
index cd55be8..dd05cfe 100644
--- a/doc/src/sgml/mvcc.sgml
+++ b/doc/src/sgml/mvcc.sgml
@@ -326,14 +326,41 @@
</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
- does not see effects of those commands on other rows in the database.
- This behavior makes Read Committed mode unsuitable for commands that
- involve complex search conditions; however, it is just right for simpler
- cases. For example, consider updating bank balances with transactions
- like:
+ <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 rules, 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 does not see effects of those commands on other
+ rows in the database. This behavior makes Read Committed mode
+ unsuitable for commands that involve complex search conditions;
+ however, it is just right for simpler cases. For example,
+ consider updating bank balances with transactions like:
<screen>
BEGIN;
diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml
index f008e93..8fbf4f2 100644
--- a/doc/src/sgml/plpgsql.sgml
+++ b/doc/src/sgml/plpgsql.sgml
@@ -3751,12 +3751,14 @@ RAISE unique_violation USING MESSAGE = 'Duplicate user ID: ' || user_id;
<command>INSERT</>/<command>UPDATE</>/<command>DELETE</>).
Otherwise a nonnull value should be returned, to signal
that the trigger performed the requested operation. For
- <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</>.
+ <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, or passed to a <literal>CONFLICTING</>
+ expression within an <command>INSERT</> 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..c414ecf 100644
--- a/doc/src/sgml/postgres-fdw.sgml
+++ b/doc/src/sgml/postgres-fdw.sgml
@@ -69,6 +69,14 @@
</para>
<para>
+ <!-- XXX: Actually supporting this is more than a matter of adding
+ the necessary deparsing logic, but not much more -->
+ Note that <filename>postgres_fdw</> currently lacks support for
+ <command>INSERT</command> statements with an <literal>ON CONFLICT
+ UPDATE</> clause.
+ </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_rule.sgml b/doc/src/sgml/ref/create_rule.sgml
index 677766a..a7a975e 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
+ UPDATE</literal> clause is a simple <command>INSERT</command>
+ for the purposes of rules. Rule expansion will not occur
+ separately for the <literal>UPDATE</literal> portion.
</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..3e13a08 100644
--- a/doc/src/sgml/ref/create_view.sgml
+++ b/doc/src/sgml/ref/create_view.sgml
@@ -245,6 +245,12 @@ CREATE VIEW <replaceable>name</> AS WITH RECURSIVE <replaceable>name</> (<replac
<title>Notes</title>
<para>
+ <!-- revisit - may not be necessary -->
+ <command>INSERT</command> with an <literal>ON CONFLICT UPDATE</>
+ clause is not supported on updatable views.
+ </para>
+
+ <para>
Use the <xref linkend="sql-dropview">
statement to drop views.
</para>
@@ -290,9 +296,12 @@ 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:
+ <command>INSERT</>, <command>UPDATE</> and <command>DELETE</>
+ statements to be used on the view in the same way as on a regular
+ table (although <command>INSERT</command> may not use an
+ <literal>ON CONFLICT UPDATE</> clause for such a view). A view is
+ automatically updatable if it satisfies all of the following
+ conditions:
<itemizedlist>
<listitem>
diff --git a/doc/src/sgml/ref/insert.sgml b/doc/src/sgml/ref/insert.sgml
index a3cccb9..ac4c2d1 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 [ WITHIN <replaceable class="PARAMETER">unique_index_name</replaceable> ]
+ { IGNORE | UPDATE
+ SET { <replaceable class="PARAMETER">column_name</replaceable> = { CONFLICTING(<replaceable class="PARAMETER">column_name</replaceable>) | <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } |
+ ( <replaceable class="PARAMETER">column_name</replaceable> [, ...] ) = ( { CONFLICTING(<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>
@@ -34,7 +42,9 @@ INSERT INTO <replaceable class="PARAMETER">table_name</replaceable> [ ( <replace
<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.
+ or zero or more rows resulting from a query. An alternative path
+ can be specified in the event of detecting that proceeding with any
+ row's insertion would result in a uniqueness violation.
</para>
<para>
@@ -65,16 +75,101 @@ INSERT INTO <replaceable class="PARAMETER">table_name</replaceable> [ ( <replace
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.
+ </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. While rows may be updated, the
+ top-level statement is still an <command>INSERT</command>, which is
+ significant for the purposes of statement-level triggers and the
+ rules system. 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 procedes 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</> accepts <literal>CONFLICTING</>
+ expressions in both its 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 merging 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 conflicting values
+ originally proposed for insertion may be referenced. Operators and
+ functions may be used freely, though.
+ </para>
+
+ <para>
+ <literal>ON CONFLICT UPDATE</> also optionally accepts a
+ <literal>WITHIN</> clause, which can be used to limit pre-checking
+ for duplicates to one specific unique index,
+ <replaceable>unique_index_name</>. If this clause is omitted, it
+ is assumed that there can only be one source of uniqueness
+ violations, and so the first indication of a would-be uniqueness
+ violation is assumed to be the appropriate condition to take the
+ alternative <literal>UPDATE</> or <literal>IGNORE</> path on
+ (implying that insertion cannot directly cause uniqueness
+ violations under any circumstances, possibly including unforeseen
+ circumstances in which it is actually appropriate to do so).
+ Failure to anticipate and prevent would-be unique violations
+ originating in some other unique index than the single unique index
+ that was anticipated as the sole source of would-be uniqueness
+ violations can result in updating a row other than an existing row
+ with conflicting values (if any).
+ </para>
+
+ <para>
+ In general, it is good practice to include this clause when
+ inserting into a table with more than a single non-trivial unique
+ index. (A serial primary key unique index is considered a trivial
+ unique index). Note that the UPDATE assignment may result in a
+ unique violation, just as with a conventional
+ <command>UPDATE</command>.
</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 +216,29 @@ 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 tab.col = 1</> is invalid.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><replaceable class="PARAMETER">unique_index_name</replaceable></term>
+ <listitem>
+ <para>
+ The name of a unique index defined on the table named by
+ <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, 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.
</para>
</listitem>
</varlistentry>
@@ -140,6 +257,18 @@ INSERT INTO <replaceable class="PARAMETER">table_name</replaceable> [ ( <replace
<listitem>
<para>
An expression or value to assign to the corresponding column.
+ Within <literal>ON CONFLICT UPDATE</>, this may be a
+ <literal>CONFLICTING</> expression, which allows the update's
+ targetlist (or <literal>WHERE</> clause) to reference a value
+ appearing in the corresponding row proposed for insertion. Note
+ that the effects of <literal>BEFORE INSERT</> triggers are
+ carried forward when <literal>CONFLICTING</> is used.
+ </para>
+ <para>
+ As with the <literal>ON CONFLICT UPDATE</> WHERE clause
+ <replaceable class="parameter">condition</replaceable>, within
+ <literal>ON CONFLICT UPDATE</> SET targetlists, subquery
+ expressions are disallowed.
</para>
</listitem>
</varlistentry>
@@ -167,12 +296,29 @@ 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. Note that
+ subqueries are disallowed within the expression. Only columns
+ appearing in the target table, or, by using a
+ <literal>CONFLICTING</> expression, values originally proposed
+ for insertion may be referenced.
+ </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>.
+ An expression to be computed and returned by the
+ <command>INSERT</> 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 +350,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 +459,31 @@ 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 a <literal>CONFLICTING</>
+ 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 UPDATE SET dname = CONFLICTING(dname) || ' (formerly ' || dname || ')'
+</programlisting>
+ </para>
+ <para>
+ Insert a distributor, or do nothing for rows proposed for insertion
+ when an existing, conflicting (a row with a matching constrained
+ column or columns) exists. Assumes a unique index has been defined
+ that constrains values appearing in the <literal>did</literal>
+ column:
+<programlisting>
+ INSERT INTO distributors (did, dname) VALUES (7, 'Doodad GmbH')
+ ON CONFLICT IGNORE
+</programlisting>
+ </para>
</refsect1>
<refsect1>
@@ -321,7 +493,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/trigger.sgml b/doc/src/sgml/trigger.sgml
index f94aea1..711741d 100644
--- a/doc/src/sgml/trigger.sgml
+++ b/doc/src/sgml/trigger.sgml
@@ -39,8 +39,12 @@
<para>
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 <command>DELETE</command> operation, either once per modified
+ row, 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 a CONFLICTING 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 +123,28 @@
</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 a CONFLICTING expression appears. There need not
+ be a CONFLICTING 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; 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
