Back-patch fcff8a575198478023ada8a48e13b50f70054766 as a bug fix.

When there is both a serialization failure and a unique violation,
throw the former rather than the latter.  When initially pushed,
this was viewed as a feature to assist application framework
developers, so that they could more accurately determine when to
retry a failed transaction, but a test case presented by Ian
Jackson has shown that this patch can prevent serialization
anomalies in some cases where a unique violation is caught within a
subtransaction, the work of that subtransaction is discarded, and
no error is thrown.  That makes this a bug fix, so it is being
back-patched to all supported branches where it is not already
present (i.e., 9.2 to 9.5).

Discussion: https://postgr.es/m/1481307991-16971-1-git-send-email-ian.jackson@eu.citrix.com
Discussion: https://postgr.es/m/22607.56276.807567.924144@mariner.uk.xensource.com

diff --git a/doc/src/sgml/mvcc.sgml b/doc/src/sgml/mvcc.sgml
index 097987a0875e1d94c9e5a9c0e0a871a8b41caee8..76142e537b4a4ce03e127039df990de3e36462af 100644 (file)
--- a/doc/src/sgml/mvcc.sgml
+++ b/doc/src/sgml/mvcc.sgml
@@ -594,7 +594,7 @@ ERROR:  could not serialize access due to read/write dependencies among transact
     first.  In <productname>PostgreSQL</productname> these locks do not
     cause any blocking and therefore can <emphasis>not</> play any part in
     causing a deadlock.  They are used to identify and flag dependencies
-    among concurrent serializable transactions which in certain combinations
+    among concurrent Serializable transactions which in certain combinations
     can lead to serialization anomalies.  In contrast, a Read Committed or
     Repeatable Read transaction which wants to ensure data consistency may
     need to take out a lock on an entire table, which could block other
@@ -629,12 +629,13 @@ ERROR:  could not serialize access due to read/write dependencies among transact
 
    <para>
     Consistent use of Serializable transactions can simplify development.
-    The guarantee that any set of concurrent serializable transactions will
-    have the same effect as if they were run one at a time means that if
-    you can demonstrate that a single transaction, as written, will do the
-    right thing when run by itself, you can have confidence that it will
-    do the right thing in any mix of serializable transactions, even without
-    any information about what those other transactions might do.  It is
+    The guarantee that any set of successfully committed concurrent
+    Serializable transactions will have the same effect as if they were run
+    one at a time means that if you can demonstrate that a single transaction,
+    as written, will do the right thing when run by itself, you can have
+    confidence that it will do the right thing in any mix of Serializable
+    transactions, even without any information about what those other
+    transactions might do, or it will not successfully commit.  It is
     important that an environment which uses this technique have a
     generalized way of handling serialization failures (which always return
     with a SQLSTATE value of '40001'), because it will be very hard to
@@ -648,6 +649,26 @@ ERROR:  could not serialize access due to read/write dependencies among transact
     for some environments.
    </para>
 
+   <para>
+    While <productname>PostgreSQL</>'s Serializable transaction isolation
+    level only allows concurrent transactions to commit if it can prove there
+    is a serial order of execution that would produce the same effect, it
+    doesn't always prevent errors from being raised that would not occur in
+    true serial execution.  In particular, it is possible to see unique
+    constraint violations caused by conflicts with overlapping Serializable
+    transactions even after explicitly checking that the key isn't present
+    before attempting to insert it.  This can be avoided by making sure
+    that <emphasis>all</> Serializable transactions that insert potentially
+    conflicting keys explicitly check if they can do so first.  For example,
+    imagine an application that asks the user for a new key and then checks
+    that it doesn't exist already by trying to select it first, or generates
+    a new key by selecting the maximum existing key and adding one.  If some
+    Serializable transactions insert new keys directly without following this
+    protocol, unique constraints violations might be reported even in cases
+    where they could not occur in a serial execution of the concurrent
+    transactions.
+   </para>
+
    <para>
     For optimal performance when relying on Serializable transactions for
     concurrency control, these issues should be considered:

diff --git a/src/backend/access/nbtree/nbtinsert.c b/src/backend/access/nbtree/nbtinsert.c
index 122b519c228b506b97764f522f835ee84b12731e..41f7bc6f26f8d31be46991512f64f772c5213a40 100644 (file)
--- a/src/backend/access/nbtree/nbtinsert.c
+++ b/src/backend/access/nbtree/nbtinsert.c
@@ -369,6 +369,14 @@ _bt_check_unique(Relation rel, IndexTuple itup, Relation heapRel,
                        break;
                    }
 
+                   /*
+                    * Check for a conflict-in as we would if we were going to
+                    * write to this page.  We aren't actually going to write,
+                    * but we want a chance to report SSI conflicts that would
+                    * otherwise be masked by this unique constraint violation.
+                    */
+                   CheckForSerializableConflictIn(rel, NULL, buf);
+
                    /*
                     * This is a definite conflict.  Break the tuple down into
                     * datums and report the error.  But first, make sure we

diff --git a/src/test/isolation/expected/read-write-unique-2.out b/src/test/isolation/expected/read-write-unique-2.out
new file mode 100644 (file)
index 0000000..5e27f0a
--- /dev/null
+++ b/src/test/isolation/expected/read-write-unique-2.out
@@ -0,0 +1,29 @@
+Parsed test spec with 2 sessions
+
+starting permutation: r1 r2 w1 w2 c1 c2
+step r1: SELECT * FROM test WHERE i = 42;
+i              
+
+step r2: SELECT * FROM test WHERE i = 42;
+i              
+
+step w1: INSERT INTO test VALUES (42);
+step w2: INSERT INTO test VALUES (42); <waiting ...>
+step c1: COMMIT;
+step w2: <... completed>
+error in steps c1 w2: ERROR:  could not serialize access due to read/write dependencies among transactions
+step c2: COMMIT;
+
+starting permutation: r1 w1 c1 r2 w2 c2
+step r1: SELECT * FROM test WHERE i = 42;
+i              
+
+step w1: INSERT INTO test VALUES (42);
+step c1: COMMIT;
+step r2: SELECT * FROM test WHERE i = 42;
+i              
+
+42             
+step w2: INSERT INTO test VALUES (42);
+ERROR:  duplicate key value violates unique constraint "test_pkey"
+step c2: COMMIT;

diff --git a/src/test/isolation/expected/read-write-unique-3.out b/src/test/isolation/expected/read-write-unique-3.out
new file mode 100644 (file)
index 0000000..edd3558
--- /dev/null
+++ b/src/test/isolation/expected/read-write-unique-3.out
@@ -0,0 +1,12 @@
+Parsed test spec with 2 sessions
+
+starting permutation: rw1 rw2 c1 c2
+step rw1: SELECT insert_unique(1, '1');
+insert_unique  
+
+               
+step rw2: SELECT insert_unique(1, '2'); <waiting ...>
+step c1: COMMIT;
+step rw2: <... completed>
+error in steps c1 rw2: ERROR:  could not serialize access due to read/write dependencies among transactions
+step c2: COMMIT;

diff --git a/src/test/isolation/expected/read-write-unique-4.out b/src/test/isolation/expected/read-write-unique-4.out
new file mode 100644 (file)
index 0000000..64ff157
--- /dev/null
+++ b/src/test/isolation/expected/read-write-unique-4.out
@@ -0,0 +1,41 @@
+Parsed test spec with 2 sessions
+
+starting permutation: r1 r2 w1 w2 c1 c2
+step r1: SELECT COALESCE(MAX(invoice_number) + 1, 1) FROM invoice WHERE year = 2016;
+coalesce       
+
+3              
+step r2: SELECT COALESCE(MAX(invoice_number) + 1, 1) FROM invoice WHERE year = 2016;
+coalesce       
+
+3              
+step w1: INSERT INTO invoice VALUES (2016, 3);
+step w2: INSERT INTO invoice VALUES (2016, 3); <waiting ...>
+step c1: COMMIT;
+step w2: <... completed>
+error in steps c1 w2: ERROR:  could not serialize access due to read/write dependencies among transactions
+step c2: COMMIT;
+
+starting permutation: r1 w1 w2 c1 c2
+step r1: SELECT COALESCE(MAX(invoice_number) + 1, 1) FROM invoice WHERE year = 2016;
+coalesce       
+
+3              
+step w1: INSERT INTO invoice VALUES (2016, 3);
+step w2: INSERT INTO invoice VALUES (2016, 3); <waiting ...>
+step c1: COMMIT;
+step w2: <... completed>
+error in steps c1 w2: ERROR:  duplicate key value violates unique constraint "invoice_pkey"
+step c2: COMMIT;
+
+starting permutation: r2 w1 w2 c1 c2
+step r2: SELECT COALESCE(MAX(invoice_number) + 1, 1) FROM invoice WHERE year = 2016;
+coalesce       
+
+3              
+step w1: INSERT INTO invoice VALUES (2016, 3);
+step w2: INSERT INTO invoice VALUES (2016, 3); <waiting ...>
+step c1: COMMIT;
+step w2: <... completed>
+error in steps c1 w2: ERROR:  duplicate key value violates unique constraint "invoice_pkey"
+step c2: COMMIT;

diff --git a/src/test/isolation/expected/read-write-unique.out b/src/test/isolation/expected/read-write-unique.out
new file mode 100644 (file)
index 0000000..fb32ec3
--- /dev/null
+++ b/src/test/isolation/expected/read-write-unique.out
@@ -0,0 +1,29 @@
+Parsed test spec with 2 sessions
+
+starting permutation: r1 r2 w1 w2 c1 c2
+step r1: SELECT * FROM test;
+i              
+
+step r2: SELECT * FROM test;
+i              
+
+step w1: INSERT INTO test VALUES (42);
+step w2: INSERT INTO test VALUES (42); <waiting ...>
+step c1: COMMIT;
+step w2: <... completed>
+error in steps c1 w2: ERROR:  could not serialize access due to read/write dependencies among transactions
+step c2: COMMIT;
+
+starting permutation: r1 w1 c1 r2 w2 c2
+step r1: SELECT * FROM test;
+i              
+
+step w1: INSERT INTO test VALUES (42);
+step c1: COMMIT;
+step r2: SELECT * FROM test;
+i              
+
+42             
+step w2: INSERT INTO test VALUES (42);
+ERROR:  duplicate key value violates unique constraint "test_pkey"
+step c2: COMMIT;

diff --git a/src/test/isolation/isolation_schedule b/src/test/isolation/isolation_schedule
index e77e8d3586ede115112581cf8ea893562f499feb..46134c4a77eb7f629a6f922650fa2f0d935da49f 100644 (file)
--- a/src/test/isolation/isolation_schedule
+++ b/src/test/isolation/isolation_schedule
@@ -1,3 +1,7 @@
+test: read-write-unique
+test: read-write-unique-2
+test: read-write-unique-3
+test: read-write-unique-4
 test: simple-write-skew
 test: receipt-report
 test: temporal-range-integrity

diff --git a/src/test/isolation/specs/read-write-unique-2.spec b/src/test/isolation/specs/read-write-unique-2.spec
new file mode 100644 (file)
index 0000000..5e7cbf2
--- /dev/null
+++ b/src/test/isolation/specs/read-write-unique-2.spec
@@ -0,0 +1,36 @@
+# Read-write-unique test.
+
+setup
+{
+  CREATE TABLE test (i integer PRIMARY KEY);
+}
+
+teardown
+{
+  DROP TABLE test;
+}
+
+session "s1"
+setup { BEGIN ISOLATION LEVEL SERIALIZABLE; }
+step "r1" { SELECT * FROM test WHERE i = 42; }
+step "w1" { INSERT INTO test VALUES (42); }
+step "c1" { COMMIT; }
+
+session "s2"
+setup { BEGIN ISOLATION LEVEL SERIALIZABLE; }
+step "r2" { SELECT * FROM test WHERE i = 42; }
+step "w2" { INSERT INTO test VALUES (42); }
+step "c2" { COMMIT; }
+
+# Two SSI transactions see that there is no row with value 42
+# in the table, then try to insert that value; T1 inserts,
+# and then T2 blocks waiting for T1 to commit.  Finally,
+# T2 reports a serialization failure.
+
+permutation "r1" "r2" "w1" "w2" "c1" "c2"
+
+# If the value is already visible before T2 begins, then a
+# regular unique constraint violation should still be raised
+# by T2.
+
+permutation "r1" "w1" "c1" "r2" "w2" "c2"

diff --git a/src/test/isolation/specs/read-write-unique-3.spec b/src/test/isolation/specs/read-write-unique-3.spec
new file mode 100644 (file)
index 0000000..52d2877
--- /dev/null
+++ b/src/test/isolation/specs/read-write-unique-3.spec
@@ -0,0 +1,33 @@
+# Read-write-unique test.
+# From bug report 9301.
+
+setup
+{
+  CREATE TABLE test (
+    key   integer UNIQUE,
+    val   text
+  );
+
+  CREATE OR REPLACE FUNCTION insert_unique(k integer, v text) RETURNS void
+  LANGUAGE SQL AS $$
+    INSERT INTO test (key, val) SELECT k, v WHERE NOT EXISTS (SELECT key FROM test WHERE key = k);
+  $$;
+}
+
+teardown
+{
+  DROP FUNCTION insert_unique(integer, text);
+  DROP TABLE test;
+}
+
+session "s1"
+setup { BEGIN ISOLATION LEVEL SERIALIZABLE; }
+step "rw1" { SELECT insert_unique(1, '1'); }
+step "c1" { COMMIT; }
+
+session "s2"
+setup { BEGIN ISOLATION LEVEL SERIALIZABLE; }
+step "rw2" { SELECT insert_unique(1, '2'); }
+step "c2" { COMMIT; }
+
+permutation "rw1" "rw2" "c1" "c2"

diff --git a/src/test/isolation/specs/read-write-unique-4.spec b/src/test/isolation/specs/read-write-unique-4.spec
new file mode 100644 (file)
index 0000000..ec44782
--- /dev/null
+++ b/src/test/isolation/specs/read-write-unique-4.spec
@@ -0,0 +1,48 @@
+# Read-write-unique test.
+# Implementing a gapless sequence of ID numbers for each year.
+
+setup
+{
+  CREATE TABLE invoice (
+    year int,
+    invoice_number int,
+    PRIMARY KEY (year, invoice_number)
+  );
+
+  INSERT INTO invoice VALUES (2016, 1), (2016, 2);
+}
+
+teardown
+{
+  DROP TABLE invoice;
+}
+
+session "s1"
+setup { BEGIN ISOLATION LEVEL SERIALIZABLE; }
+step "r1" { SELECT COALESCE(MAX(invoice_number) + 1, 1) FROM invoice WHERE year = 2016; }
+step "w1" { INSERT INTO invoice VALUES (2016, 3); }
+step "c1" { COMMIT; }
+
+session "s2"
+setup { BEGIN ISOLATION LEVEL SERIALIZABLE; }
+step "r2" { SELECT COALESCE(MAX(invoice_number) + 1, 1) FROM invoice WHERE year = 2016; }
+step "w2" { INSERT INTO invoice VALUES (2016, 3); }
+step "c2" { COMMIT; }
+
+# if they both read first then there should be an SSI conflict
+permutation "r1" "r2" "w1" "w2" "c1" "c2"
+
+# cases where one session doesn't explicitly read before writing:
+
+# if s2 doesn't explicitly read, then trying to insert the value
+# generates a unique constraint violation after s1 commits, as if s2
+# ran after s1
+permutation "r1" "w1" "w2" "c1" "c2"
+
+# if s1 doesn't explicitly read, but s2 does, then s1 inserts and
+# commits first, should s2 experience an SSI failure instead of a
+# unique constraint violation?  there is no serial order of operations
+# (s1, s2) or (s2, s1) where s1 succeeds, and s2 doesn't see the row
+# in an explicit select but then fails to insert due to unique
+# constraint violation
+permutation "r2" "w1" "w2" "c1" "c2"

diff --git a/src/test/isolation/specs/read-write-unique.spec b/src/test/isolation/specs/read-write-unique.spec
new file mode 100644 (file)
index 0000000..c782f10
--- /dev/null
+++ b/src/test/isolation/specs/read-write-unique.spec
@@ -0,0 +1,39 @@
+# Read-write-unique test.
+
+setup
+{
+  CREATE TABLE test (i integer PRIMARY KEY);
+}
+
+teardown
+{
+  DROP TABLE test;
+}
+
+session "s1"
+setup { BEGIN ISOLATION LEVEL SERIALIZABLE; }
+step "r1" { SELECT * FROM test; }
+step "w1" { INSERT INTO test VALUES (42); }
+step "c1" { COMMIT; }
+
+session "s2"
+setup { BEGIN ISOLATION LEVEL SERIALIZABLE; }
+step "r2" { SELECT * FROM test; }
+step "w2" { INSERT INTO test VALUES (42); }
+step "c2" { COMMIT; }
+
+# Two SSI transactions see that there is no row with value 42
+# in the table, then try to insert that value; T1 inserts,
+# and then T2 blocks waiting for T1 to commit.  Finally,
+# T2 reports a serialization failure.
+#
+# (In an earlier version of Postgres, T2 would report a unique
+# constraint violation).
+
+permutation "r1" "r2" "w1" "w2" "c1" "c2"
+
+# If the value is already visible before T2 begins, then a
+# regular unique constraint violation should still be raised
+# by T2.
+
+permutation "r1" "w1" "c1" "r2" "w2" "c2"