Hi hackers,
Right now isolation test alter-table-4.spec fails if prepared statement
is used:
Whats going on:
- There are two inherited tables "p" and "c1".
- Session 1 starts transactions and drops inheritance
- Session 2 prepares and executes statement which selects data from
"p". It is blocked because table is locked by transaction in session 1.
- Session 1 commits transaction.
- Session 2 receives invalidation message.
- Session 2 completes query execution and shows result which assumes
inheritance between two tables (according to expected result
alter-table-4.out it is assumed to be correct).
- Session 2 repeat execution of query. It returns the SAME result. And
it is not correct because now tables are not inherited.
The problem is that backend handles invalidated message in the context
where schema changes are not yet visible. So statement is prepared for
the state of database
preceding schema changes. And since invalidation message is already
received and handled, this prepared statement will never be invalidated.
Is it considered as expected and acceptable behavior?
What seems to be suspicious to me is that schema changes are treated in
different ways.
If you perform select from some table using the same scenario and
concurrently alter this table by adding some extra columns, then result
of the query includes this new columns. I.e. statement is compiled and
executed according to the new schema.
But if we alter inheritance, then statement is compiled and executed as
if inheritance didn't change (old schema is used).
Such behavior seems to be contradictory and error prone.
Patch for alter-table-4 test is attached to this mail.
And difference between expected and actual output of the test is the
following:
! starting permutation: s1b s1delc1 s2sel s1c s2sel
step s1b: BEGIN;
step s1delc1: ALTER TABLE c1 NO INHERIT p;
! step s2sel: SELECT SUM(a) FROM p; <waiting ...>
step s1c: COMMIT;
step s2sel: <... completed>
sum
11
! step s2sel: SELECT SUM(a) FROM p;
sum
! 1
--- 1,31 ----
! starting permutation: s1b s1delc1 s2prep s2sel s1c s2sel
step s1b: BEGIN;
step s1delc1: ALTER TABLE c1 NO INHERIT p;
! step s2prep: PREPARE summa as SELECT SUM(a) FROM p;
! step s2sel: EXECUTE summa; <waiting ...>
step s1c: COMMIT;
step s2sel: <... completed>
sum
11
! step s2sel: EXECUTE summa;
sum
! 11
--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
diff --git a/src/test/isolation/specs/alter-table-4.spec b/src/test/isolation/specs/alter-table-4.spec
index a9c1a93..e64c6e3 100644
--- a/src/test/isolation/specs/alter-table-4.spec
+++ b/src/test/isolation/specs/alter-table-4.spec
@@ -24,11 +24,12 @@ step "s1dropc1" { DROP TABLE c1; }
step "s1c" { COMMIT; }
session "s2"
-step "s2sel" { SELECT SUM(a) FROM p; }
+step "s2prep" { PREPARE summa as SELECT SUM(a) FROM p; }
+step "s2sel" { EXECUTE summa; }
# NO INHERIT will not be visible to concurrent select,
# since we identify children before locking them
-permutation "s1b" "s1delc1" "s2sel" "s1c" "s2sel"
+permutation "s1b" "s1delc1" "s2prep" "s2sel" "s1c" "s2sel"
# adding inheritance likewise is not seen if s1 commits after s2 locks p
permutation "s1b" "s1delc1" "s1addc2" "s2sel" "s1c" "s2sel"
# but we do cope with DROP on a child table
