Do we expect tests to work with default_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-19 15: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-19 15: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-19 15: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