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
On Mon, May 20, 2019 at 10:55 AM Andres Freund <andres@anarazel.de> wrote:
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.
Here are a couple of bits where that is no longer necessary after bb16aba5.
--
Thomas Munro
https://enterprisedb.com
Attachments:
remove-bogus-iso-change.patchapplication/octet-stream; name=remove-bogus-iso-change.patchDownload
diff --git a/src/test/regress/expected/select_parallel.out b/src/test/regress/expected/select_parallel.out
index 0eca76cb41..2712853e67 100644
--- a/src/test/regress/expected/select_parallel.out
+++ b/src/test/regress/expected/select_parallel.out
@@ -3,9 +3,7 @@
--
create function sp_parallel_restricted(int) returns int as
$$begin return $1; end$$ language plpgsql parallel restricted;
--- Serializable isolation would disable parallel query, so explicitly use an
--- arbitrary other level.
-begin isolation level repeatable read;
+begin;
-- encourage use of parallel plans
set parallel_setup_cost=0;
set parallel_tuple_cost=0;
diff --git a/src/test/regress/expected/write_parallel.out b/src/test/regress/expected/write_parallel.out
index 0c4da2591a..0d12bf4812 100644
--- a/src/test/regress/expected/write_parallel.out
+++ b/src/test/regress/expected/write_parallel.out
@@ -1,9 +1,7 @@
--
-- PARALLEL
--
--- Serializable isolation would disable parallel query, so explicitly use an
--- arbitrary other level.
-begin isolation level repeatable read;
+begin;
-- encourage use of parallel plans
set parallel_setup_cost=0;
set parallel_tuple_cost=0;
diff --git a/src/test/regress/sql/select_parallel.sql b/src/test/regress/sql/select_parallel.sql
index 03c056b8b7..0677775194 100644
--- a/src/test/regress/sql/select_parallel.sql
+++ b/src/test/regress/sql/select_parallel.sql
@@ -5,9 +5,7 @@
create function sp_parallel_restricted(int) returns int as
$$begin return $1; end$$ language plpgsql parallel restricted;
--- Serializable isolation would disable parallel query, so explicitly use an
--- arbitrary other level.
-begin isolation level repeatable read;
+begin;
-- encourage use of parallel plans
set parallel_setup_cost=0;
diff --git a/src/test/regress/sql/write_parallel.sql b/src/test/regress/sql/write_parallel.sql
index 78b479cedf..3259d3696b 100644
--- a/src/test/regress/sql/write_parallel.sql
+++ b/src/test/regress/sql/write_parallel.sql
@@ -2,9 +2,7 @@
-- PARALLEL
--
--- Serializable isolation would disable parallel query, so explicitly use an
--- arbitrary other level.
-begin isolation level repeatable read;
+begin;
-- encourage use of parallel plans
set parallel_setup_cost=0;
On Sun, May 19, 2019 at 03:55:06PM -0700, Andres Freund wrote:
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.
I'm +1 for making it a project expectation, with an animal to confirm. It's
not expected to work today.
On Sat, Jun 15, 2019 at 11:47:39AM -0700, Noah Misch wrote:
On Sun, May 19, 2019 at 03:55:06PM -0700, Andres Freund wrote:
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).
These sound strange and may point to actual bugs.
Do we expect this to work? If it's desirable I'll set up an animal that
forces it to on.I'm +1 for making it a project expectation, with an animal to confirm. It's
not expected to work today.
+1.
--
Michael