Over in [1], I became concerned that, although postgres supports
Read Uncommitted transaction isolation (by way of Read Committed
mode), there was very little test coverage for it:
On 12/18/19 10:46 AM, Mark Dilger wrote:
Looking at the regression tests, I'm surprised read uncommitted gets
so little test coverage. There's a test in src/test/isolation but
nothing at all in src/test/regression covering this isolation level.
The one in src/test/isolation doesn't look very comprehensive. I'd
at least expect a test that verifies you don't get a syntax error
when you request READ UNCOMMITTED isolation from SQL.
The attached patch set adds a modicum of test coverage for this.
Do others feel these tests are worth the small run time overhead
they add?
--
Mark Dilger
[1]
https://www.postgresql.org/message-id/CANP8%2Bj%2BmgWfcX9cTPsk7t%2B1kQCxgyGqHTR5R7suht7mCm_x_hA%40mail.gmail.com
diff --git a/src/test/regress/expected/insert_conflict.out b/src/test/regress/expected/insert_conflict.out
index 1338b2b23e..873b81950b 100644
--- a/src/test/regress/expected/insert_conflict.out
+++ b/src/test/regress/expected/insert_conflict.out
@@ -690,6 +690,9 @@ select * from twoconstraints;
drop table twoconstraints;
-- check handling of self-conflicts at various isolation levels
create table selfconflict (f1 int primary key, f2 int);
+begin transaction isolation level read uncommitted;
+insert into selfconflict values (1,1), (1,2) on conflict do nothing;
+commit;
begin transaction isolation level read committed;
insert into selfconflict values (1,1), (1,2) on conflict do nothing;
commit;
@@ -699,6 +702,11 @@ commit;
begin transaction isolation level serializable;
insert into selfconflict values (3,1), (3,2) on conflict do nothing;
commit;
+begin transaction isolation level read uncommitted;
+insert into selfconflict values (4,1), (4,2) on conflict(f1) do update set f2 = 0;
+ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time
+HINT: Ensure that no rows proposed for insertion within the same command have duplicate constrained values.
+commit;
begin transaction isolation level read committed;
insert into selfconflict values (4,1), (4,2) on conflict(f1) do update set f2 = 0;
ERROR: ON CONFLICT DO UPDATE command cannot affect row a second time
diff --git a/src/test/regress/sql/insert_conflict.sql b/src/test/regress/sql/insert_conflict.sql
index 43691cd335..2f68398503 100644
--- a/src/test/regress/sql/insert_conflict.sql
+++ b/src/test/regress/sql/insert_conflict.sql
@@ -421,6 +421,10 @@ drop table twoconstraints;
create table selfconflict (f1 int primary key, f2 int);
+begin transaction isolation level read uncommitted;
+insert into selfconflict values (1,1), (1,2) on conflict do nothing;
+commit;
+
begin transaction isolation level read committed;
insert into selfconflict values (1,1), (1,2) on conflict do nothing;
commit;
@@ -433,6 +437,10 @@ begin transaction isolation level serializable;
insert into selfconflict values (3,1), (3,2) on conflict do nothing;
commit;
+begin transaction isolation level read uncommitted;
+insert into selfconflict values (4,1), (4,2) on conflict(f1) do update set f2 = 0;
+commit;
+
begin transaction isolation level read committed;
insert into selfconflict values (4,1), (4,2) on conflict(f1) do update set f2 = 0;
commit;
diff --git a/src/test/isolation/expected/toast-isolation.out b/src/test/isolation/expected/toast-isolation.out
new file mode 100644
index 0000000000..5db1e281f8
--- /dev/null
+++ b/src/test/isolation/expected/toast-isolation.out
@@ -0,0 +1,42 @@
+Parsed test spec with 2 sessions
+
+starting permutation: s1_insert s2_select s1_update s2_select s1_delete s2_select s1_commit s1_vacuum s2_select s1_reinsert s2_select s1_nullify s2_select s1_vacuum s2_select s2_commit
+step s1_insert: INSERT INTO doc VALUES (3, repeat('3',30000));
+step s2_select: SELECT id, COUNT(*), SUM(length(document)) FROM doc GROUP BY id ORDER BY id ASC;
+id count sum
+
+1 1 10000
+2 1 20000
+step s1_update: UPDATE doc SET id = 4, document = repeat('4',40000);
+step s2_select: SELECT id, COUNT(*), SUM(length(document)) FROM doc GROUP BY id ORDER BY id ASC;
+id count sum
+
+1 1 10000
+2 1 20000
+step s1_delete: DELETE FROM doc;
+step s2_select: SELECT id, COUNT(*), SUM(length(document)) FROM doc GROUP BY id ORDER BY id ASC;
+id count sum
+
+1 1 10000
+2 1 20000
+step s1_commit: COMMIT;
+step s1_vacuum: VACUUM;
+step s2_select: SELECT id, COUNT(*), SUM(length(document)) FROM doc GROUP BY id ORDER BY id ASC;
+id count sum
+
+step s1_reinsert: INSERT INTO doc VALUES (5, repeat('5',50000));
+step s2_select: SELECT id, COUNT(*), SUM(length(document)) FROM doc GROUP BY id ORDER BY id ASC;
+id count sum
+
+5 1 50000
+step s1_nullify: UPDATE doc SET document = NULL;
+step s2_select: SELECT id, COUNT(*), SUM(length(document)) FROM doc GROUP BY id ORDER BY id ASC;
+id count sum
+
+5 1
+step s1_vacuum: VACUUM;
+step s2_select: SELECT id, COUNT(*), SUM(length(document)) FROM doc GROUP BY id ORDER BY id ASC;
+id count sum
+
+5 1
+step s2_commit: COMMIT;
diff --git a/src/test/isolation/isolation_schedule b/src/test/isolation/isolation_schedule
index a2fa19230d..59528b7efd 100644
--- a/src/test/isolation/isolation_schedule
+++ b/src/test/isolation/isolation_schedule
@@ -88,3 +88,4 @@ test: plpgsql-toast
test: truncate-conflict
test: serializable-parallel
test: serializable-parallel-2
+test: toast-isolation
diff --git a/src/test/isolation/specs/toast-isolation.spec b/src/test/isolation/specs/toast-isolation.spec
new file mode 100644
index 0000000000..d6a538f9eb
--- /dev/null
+++ b/src/test/isolation/specs/toast-isolation.spec
@@ -0,0 +1,41 @@
+# READ UNCOMMITTED isolation level is allowed per SQL spec to expose updates in
+# one uncommitted transaction to a reader in another transaction, though the
+# spec does not require such exposure. PostgreSQL implements this isolation
+# level by using READ COMMITTED, which disallows such exposure. As such, we
+# should not see the updates in session s1 when querying from session s2.
+# However, if we ever change the implementation to have a distinct READ
+# UNCOMMITTED mode, it will still be necessary that the toast table holding the
+# documents and the main table holding the IDs work together rationally. Check
+# that here, as a defence against half-baked future implementations of READ
+# UNCOMMITTED isolation.
+
+setup
+{
+ CREATE TABLE doc (id INTEGER, document TEXT);
+ ALTER TABLE doc ALTER document SET STORAGE external;
+ INSERT INTO doc VALUES (1, repeat('1',10000));
+ INSERT INTO doc VALUES (2, repeat('2',20000));
+}
+
+teardown
+{
+ DROP TABLE doc;
+}
+
+session "s1"
+setup { BEGIN TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; }
+step "s1_insert" { INSERT INTO doc VALUES (3, repeat('3',30000)); }
+step "s1_update" { UPDATE doc SET id = 4, document = repeat('4',40000); }
+step "s1_delete" { DELETE FROM doc; }
+step "s1_commit" { COMMIT; }
+step "s1_vacuum" { VACUUM; }
+step "s1_reinsert" { INSERT INTO doc VALUES (5, repeat('5',50000)); }
+step "s1_nullify" { UPDATE doc SET document = NULL; }
+
+session "s2"
+setup { BEGIN TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; }
+step "s2_select" { SELECT id, COUNT(*), SUM(length(document)) FROM doc GROUP BY id ORDER BY id ASC; }
+step "s2_commit" { COMMIT; }
+
+permutation "s1_insert" "s2_select" "s1_update" "s2_select" "s1_delete" "s2_select" "s1_commit" "s1_vacuum" "s2_select" "s1_reinsert" "s2_select" "s1_nullify" "s2_select" "s1_vacuum" "s2_select" "s2_commit"
+
