Do we expect tests to work withdefault_transaction_isolation=serializable

Hi,

I seem to recall that we expect tests to either work with
default_transaction_isolation=serializable, or to set it to a different
level where needed.

Currently that's not the case. When running check-world with PGOPTIONS
set to -c default_transaction_isolation=serializable I get easy to fix
failures (isolation, plpgsql) but also some apparently hanging tests
(003_recovery_targets.pl, 003_standby_2.pl).

Do we expect this to work? If it's desirable I'll set up an animal that
forces it to on.

- Andres


diff -du10 /home/andres/src/postgresql/src/test/isolation/expected/fk-partitioned-2.out
/home/andres/build/postgres/dev-assert/vpath/src/test/isolation/output_iso/results/fk-partitioned-2.out
--- /home/andres/src/postgresql/src/test/isolation/expected/fk-partitioned-2.out        2019-04-16 14:35:39.854303055
-0700
+++ /home/andres/build/postgres/dev-assert/vpath/src/test/isolation/output_iso/results/fk-partitioned-2.out
2019-05-1915:47:05.767861172 -0700
 
@@ -1,20 +1,20 @@
 Parsed test spec with 2 sessions

 starting permutation: s1b s1d s2b s2i s1c s2c
 step s1b: begin;
 step s1d: delete from ppk where a = 1;
 step s2b: begin;
 step s2i: insert into pfk values (1); <waiting ...>
 step s1c: commit;
 step s2i: <... completed>
-error in steps s1c s2i: ERROR:  insert or update on table "pfk1" violates foreign key constraint "pfk_a_fkey"
+error in steps s1c s2i: ERROR:  could not serialize access due to concurrent update
 step s2c: commit;

 starting permutation: s1b s1d s2bs s2i s1c s2c
 step s1b: begin;
 step s1d: delete from ppk where a = 1;
 step s2bs: begin isolation level serializable; select 1;
 ?column?

 1
 step s2i: insert into pfk values (1); <waiting ...>
@@ -23,21 +23,21 @@
 error in steps s1c s2i: ERROR:  could not serialize access due to concurrent update
 step s2c: commit;

 starting permutation: s1b s2b s1d s2i s1c s2c
 step s1b: begin;
 step s2b: begin;
 step s1d: delete from ppk where a = 1;
 step s2i: insert into pfk values (1); <waiting ...>
 step s1c: commit;
 step s2i: <... completed>
-error in steps s1c s2i: ERROR:  insert or update on table "pfk1" violates foreign key constraint "pfk_a_fkey"
+error in steps s1c s2i: ERROR:  could not serialize access due to concurrent update
 step s2c: commit;

 starting permutation: s1b s2bs s1d s2i s1c s2c
 step s1b: begin;
 step s2bs: begin isolation level serializable; select 1;
 ?column?

 1
 step s1d: delete from ppk where a = 1;
 step s2i: insert into pfk values (1); <waiting ...>
diff -du10 /home/andres/src/postgresql/src/test/isolation/expected/lock-update-delete_1.out
/home/andres/build/postgres/dev-assert/vpath/src/test/isolation/output_iso/results/lock-update-delete.out
--- /home/andres/src/postgresql/src/test/isolation/expected/lock-update-delete_1.out    2015-01-30 07:41:22.542718055
-0800
+++ /home/andres/build/postgres/dev-assert/vpath/src/test/isolation/output_iso/results/lock-update-delete.out
2019-05-1915:47:09.242873925 -0700
 
@@ -143,21 +143,23 @@
 step s2b: BEGIN;
 step s1l: SELECT * FROM foo WHERE pg_advisory_xact_lock(0) IS NOT NULL AND key = 1 FOR KEY SHARE; <waiting ...>
 step s2u: UPDATE foo SET value = 2 WHERE key = 1;
 step s2_blocker3: UPDATE foo SET value = 2 WHERE key = 1;
 step s2c: COMMIT;
 step s2_unlock: SELECT pg_advisory_unlock(0);
 pg_advisory_unlock

 t
 step s1l: <... completed>
-error in steps s2_unlock s1l: ERROR:  could not serialize access due to concurrent update
+key            value
+
+1              1

 starting permutation: s2b s1l s2u s2_blocker1 s2r s2_unlock
 pg_advisory_lock


 step s2b: BEGIN;
 step s1l: SELECT * FROM foo WHERE pg_advisory_xact_lock(0) IS NOT NULL AND key = 1 FOR KEY SHARE; <waiting ...>
 step s2u: UPDATE foo SET value = 2 WHERE key = 1;
 step s2_blocker1: DELETE FROM foo;
 step s2r: ROLLBACK;
diff -du10 /home/andres/src/postgresql/src/test/isolation/expected/tuplelock-update.out
/home/andres/build/postgres/dev-assert/vpath/src/test/isolation/output_iso/results/tuplelock-update.out
--- /home/andres/src/postgresql/src/test/isolation/expected/tuplelock-update.out        2018-07-07 13:06:55.644442913
-0700
+++ /home/andres/build/postgres/dev-assert/vpath/src/test/isolation/output_iso/results/tuplelock-update.out
2019-05-1915:47:26.132936176 -0700
 
@@ -16,21 +16,24 @@
 step s1_begin: BEGIN;
 step s1_grablock: SELECT * FROM pktab FOR KEY SHARE;
 id             data

 1              2
 step s1_advunlock1: SELECT pg_advisory_unlock(142857);
 pg_advisory_unlock

 t
 step s2_update: <... completed>
+error in steps s1_advunlock1 s2_update: ERROR:  could not serialize access due to concurrent update
 step s1_advunlock2: SELECT pg_sleep(5), pg_advisory_unlock(285714);
 pg_sleep       pg_advisory_unlock

                t
 step s3_update: <... completed>
+error in steps s1_advunlock2 s3_update: ERROR:  could not serialize access due to concurrent update
 step s1_advunlock3: SELECT pg_sleep(5), pg_advisory_unlock(571428);
 pg_sleep       pg_advisory_unlock

                t
 step s4_update: <... completed>
+error in steps s1_advunlock3 s4_update: ERROR:  could not serialize access due to concurrent update
 step s1_commit: COMMIT;

diff -du10 /home/andres/src/postgresql/src/pl/plpgsql/src/expected/plpgsql_transaction.out
/home/andres/build/postgres/dev-assert/vpath/src/pl/plpgsql/src/results/plpgsql_transaction.out
--- /home/andres/src/postgresql/src/pl/plpgsql/src/expected/plpgsql_transaction.out     2019-04-23 20:22:04.774775860
-0700
+++ /home/andres/build/postgres/dev-assert/vpath/src/pl/plpgsql/src/results/plpgsql_transaction.out     2019-05-19
15:49:18.071358893-0700
 
@@ -455,21 +455,21 @@
     PERFORM 1;
     RAISE INFO '%', current_setting('transaction_isolation');
     COMMIT;
     SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
     RESET TRANSACTION ISOLATION LEVEL;
     PERFORM 1;
     RAISE INFO '%', current_setting('transaction_isolation');
     COMMIT;
 END;
 $$;
-INFO:  read committed
+INFO:  serializable
 INFO:  repeatable read
 INFO:  read committed
 -- error cases
 DO LANGUAGE plpgsql $$
 BEGIN
     SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
 END;
 $$;
 ERROR:  SET TRANSACTION ISOLATION LEVEL must be called before any query
 CONTEXT:  SQL statement "SET TRANSACTION ISOLATION LEVEL REPEATABLE READ"


Greetings,

Andres Freund