Fix deletion of speculatively inserted TOAST on conflict

INSERT ..  ON CONFLICT runs a pre-check of the possible conflicting
constraints before performing the actual speculative insertion.  In case
the inserted tuple included TOASTed columns the ON CONFLICT condition
would be handled correctly in case the conflict was caught by the
pre-check, but if two transactions entered the speculative insertion
phase at the same time, one would have to re-try, and the code for
aborting a speculative insertion did not handle deleting the
speculatively inserted TOAST datums correctly.

TOAST deletion would fail with "ERROR: attempted to delete invisible
tuple" as we attempted to remove the TOAST tuples using
simple_heap_delete which reasoned that the given tuples should not be
visible to the command that wrote them.

This commit updates the heap_abort_speculative() function which aborts
the conflicting tuple to use itself, via toast_delete, for deleting
associated TOAST datums.  Like before, the inserted toast rows are not
marked as being speculative.

This commit also adds a isolationtester spec test, exercising the
relevant code path. Unfortunately 9.5 cannot handle two waiting
sessions, and thus cannot execute this test.

Reported-By: Viren Negi, Oskari Saarenmaa
Author: Oskari Saarenmaa, edited a bit by me
Bug: #14150
Discussion: <20160519123338.12513.20271@wrigleys.postgresql.org>
Backpatch: 9.5, where ON CONFLICT was introduced

1 files changed, 51 insertions, 0 deletions

diff --git a/src/test/isolation/specs/insert-conflict-toast.spec b/src/test/isolation/specs/insert-conflict-toast.spec
new file mode 100644
index 00000000000..c5e39ef9e31
--- /dev/null
+++ b/src/test/isolation/specs/insert-conflict-toast.spec
@@ -0,0 +1,51 @@
+# INSERT...ON CONFLICT test on table with TOAST
+#
+# This test verifies that speculatively inserted toast rows do not
+# cause conflicts. It does so by using expression index over a
+# function which acquires an advisory lock, triggering two index
+# insertions to happen almost at the same time. This is not guaranteed
+# to lead to a failed speculative insertion, but makes one quite
+# likely.
+
+setup
+{
+  CREATE TABLE ctoast (key int primary key, val text);
+  CREATE OR REPLACE FUNCTION ctoast_lock_func(int) RETURNS INT IMMUTABLE LANGUAGE SQL AS 'select pg_advisory_xact_lock_shared(1); select $1;';
+  CREATE OR REPLACE FUNCTION ctoast_large_val() RETURNS TEXT LANGUAGE SQL AS 'select array_agg(md5(g::text))::text from generate_series(1, 256) g';
+  CREATE UNIQUE INDEX ctoast_lock_idx ON ctoast (ctoast_lock_func(key));
+}
+
+teardown
+{
+  DROP TABLE ctoast;
+  DROP FUNCTION ctoast_lock_func(int);
+  DROP FUNCTION ctoast_large_val();
+}
+
+session "s1"
+setup
+{
+  BEGIN ISOLATION LEVEL READ COMMITTED;
+  SELECT pg_advisory_xact_lock(1);
+}
+step "s1commit" { COMMIT; }
+
+session "s2"
+setup
+{
+  SET default_transaction_isolation = 'read committed';
+}
+step "s2insert" {
+  INSERT INTO ctoast (key, val) VALUES (1, ctoast_large_val()) ON CONFLICT DO NOTHING;
+}
+
+session "s3"
+setup
+{
+  SET default_transaction_isolation = 'read committed';
+}
+step "s3insert" {
+  INSERT INTO ctoast (key, val) VALUES (1, ctoast_large_val()) ON CONFLICT DO NOTHING;
+}
+
+permutation "s2insert" "s3insert" "s1commit"