Continuing the discussion at:
https://www.postgresql.org/message-id/26571.1554741097%40sss.pgh.pa.us
Tom wrote:
> It struck me just as I was pushing it that this test doesn't exercise
> EPQ with any of the interesting cases for partition routing (ie where
> the update causes a move to a different partition). It would likely
> be a good idea to have test coverage for all of these scenarios:
>
> * EPQ where the initial update would involve a partition change,
> and that's still true after reapplying the update to the
> concurrently-updated tuple version;
>
> * EPQ where the initial update would *not* require a partition change,
> but we need one after reapplying the update to the
> concurrently-updated tuple version;
>
> * EPQ where the initial update would involve a partition change,
> but that's no longer true after reapplying the update to the
> concurrently-updated tuple version.
Per what Andres mentioned in his reply on the original thread [1], in
scenarios 1 and 2 where the 1st session's update causes a row to move,
session 2 produces the following error when trying to update the same row:
ERROR: tuple to be locked was already moved to another partition due to
concurrent update
Do we want those tests like that (with the error that is) in the
eval-plan-qual isolation suite?
I came up with the attached.
Thanks,
Amit
[1]
https://www.postgresql.org/message-id/20190408164138.izvfg2czwcofg5ev%40alap3.anarazel.de
diff --git a/src/test/isolation/expected/eval-plan-qual.out
b/src/test/isolation/expected/eval-plan-qual.out
index 703f410068..54c766cbe8 100644
--- a/src/test/isolation/expected/eval-plan-qual.out
+++ b/src/test/isolation/expected/eval-plan-qual.out
@@ -641,3 +641,53 @@ step complexpartupdate:
step c1: COMMIT;
step complexpartupdate: <... completed>
step c2: COMMIT;
+
+starting permutation: simplepartupdate_route1to2 complexpartupdate_route_err1
c1 c2
+step simplepartupdate_route1to2:
+ update parttbl set a = 2 where c = 1 returning *;
+
+a b c
+
+2 1 1
+step complexpartupdate_route_err1:
+ with u as (update another_parttbl set a = 1 returning another_parttbl.*)
+ update parttbl p set a = u.a from u where p.a = u.a and p.c = 1
returning p.*;
+ <waiting ...>
+step c1: COMMIT;
+step complexpartupdate_route_err1: <... completed>
+error in steps c1 complexpartupdate_route_err1: ERROR: tuple to be locked was
already moved to another partition due to concurrent update
+step c2: COMMIT;
+
+starting permutation: simplepartupdate_noroute complexpartupdate_route c1 c2
+step simplepartupdate_noroute:
+ update parttbl set b = 2 where c = 1 returning *;
+
+a b c
+
+1 2 1
+step complexpartupdate_route:
+ with u as (update another_parttbl set a = 1 returning another_parttbl.*)
+ update parttbl p set a = p.b from u where p.a = u.a and p.c = 1
returning p.*;
+ <waiting ...>
+step c1: COMMIT;
+step complexpartupdate_route: <... completed>
+a b c
+
+2 2 1
+step c2: COMMIT;
+
+starting permutation: simplepartupdate_route1to2_b2
complexpartupdate_route_err2 c1 c2
+step simplepartupdate_route1to2_b2:
+ update parttbl set a = 2, b = 2 where c = 1 returning *;
+
+a b c
+
+2 2 1
+step complexpartupdate_route_err2:
+ with u as (update another_parttbl set a = 1 returning another_parttbl.*)
+ update parttbl p set a = p.b from u where p.a = u.a and p.c = 1
returning p.*;
+ <waiting ...>
+step c1: COMMIT;
+step complexpartupdate_route_err2: <... completed>
+error in steps c1 complexpartupdate_route_err2: ERROR: tuple to be locked was
already moved to another partition due to concurrent update
+step c2: COMMIT;
diff --git a/src/test/isolation/specs/eval-plan-qual.spec
b/src/test/isolation/specs/eval-plan-qual.spec
index 4744f558b0..82cdedceab 100644
--- a/src/test/isolation/specs/eval-plan-qual.spec
+++ b/src/test/isolation/specs/eval-plan-qual.spec
@@ -33,9 +33,15 @@ setup
CREATE TABLE jointest AS SELECT generate_series(1,10) AS id, 0 AS data;
CREATE INDEX ON jointest(id);
- CREATE TABLE parttbl (a int) PARTITION BY LIST (a);
+ CREATE TABLE parttbl (a int, b int, c int) PARTITION BY LIST (a);
CREATE TABLE parttbl1 PARTITION OF parttbl FOR VALUES IN (1);
- INSERT INTO parttbl VALUES (1);
+ CREATE TABLE parttbl2 PARTITION OF parttbl FOR VALUES IN (2);
+ INSERT INTO parttbl VALUES (1, 1, 1);
+
+ CREATE TABLE another_parttbl (a int, b int, c int) PARTITION BY LIST (a);
+ CREATE TABLE another_parttbl1 PARTITION OF another_parttbl FOR VALUES IN (1);
+ CREATE TABLE another_parttbl2 PARTITION OF another_parttbl FOR VALUES IN (2);
+ INSERT INTO another_parttbl VALUES (1, 1, 1);
}
teardown
@@ -46,6 +52,7 @@ teardown
DROP TABLE p CASCADE;
DROP TABLE table_a, table_b, jointest;
DROP TABLE parttbl;
+ DROP TABLE another_parttbl;
}
session "s1"
@@ -148,6 +155,19 @@ step "simplepartupdate" {
update parttbl set a = a;
}
+# test scenarios where update may cause row movement
+
+step "simplepartupdate_route1to2" {
+ update parttbl set a = 2 where c = 1 returning *;
+}
+
+step "simplepartupdate_noroute" {
+ update parttbl set b = 2 where c = 1 returning *;
+}
+
+step "simplepartupdate_route1to2_b2" {
+ update parttbl set a = 2, b = 2 where c = 1 returning *;
+}
session "s2"
setup { BEGIN ISOLATION LEVEL READ COMMITTED; }
@@ -190,6 +210,21 @@ step "complexpartupdate" {
update parttbl set a = u.a from u;
}
+step "complexpartupdate_route_err1" {
+ with u as (update another_parttbl set a = 1 returning another_parttbl.*)
+ update parttbl p set a = u.a from u where p.a = u.a and p.c = 1
returning p.*;
+}
+
+step "complexpartupdate_route" {
+ with u as (update another_parttbl set a = 1 returning another_parttbl.*)
+ update parttbl p set a = p.b from u where p.a = u.a and p.c = 1
returning p.*;
+}
+
+step "complexpartupdate_route_err2" {
+ with u as (update another_parttbl set a = 1 returning another_parttbl.*)
+ update parttbl p set a = p.b from u where p.a = u.a and p.c = 1
returning p.*;
+}
+
# Use writable CTEs to create self-updated rows, that then are
# (updated|deleted). The *fail versions of the tests additionally
# perform an update, via a function, in a different command, to test
@@ -278,3 +313,6 @@ permutation "wrjt" "selectresultforupdate" "c2" "c1"
permutation "wrtwcte" "multireadwcte" "c1" "c2"
permutation "simplepartupdate" "complexpartupdate" "c1" "c2"
+permutation "simplepartupdate_route1to2" "complexpartupdate_route_err1" "c1"
"c2"
+permutation "simplepartupdate_noroute" "complexpartupdate_route" "c1" "c2"
+permutation "simplepartupdate_route1to2_b2" "complexpartupdate_route_err2"
"c1" "c2"
