more isolation tests for update tuple routing
Continuing the discussion at:
/messages/by-id/26571.1554741097@sss.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]/messages/by-id/20190408164138.izvfg2czwcofg5ev@alap3.anarazel.de, 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]: /messages/by-id/20190408164138.izvfg2czwcofg5ev@alap3.anarazel.de
/messages/by-id/20190408164138.izvfg2czwcofg5ev@alap3.anarazel.de
Attachments:
parted-update-EPQ-more-isolation-tests-1.patchtext/plain; charset=UTF-8; name=parted-update-EPQ-more-isolation-tests-1.patchDownload
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"
Amit Langote <Langote_Amit_f8@lab.ntt.co.jp> writes:
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?
Sure, but I think one such test is enough.
I came up with the attached.
I changed the last case so it actually did what I had in mind
(initial state of the update would be a partition move, but after
fetching up-to-date tuple it isn't) and pushed it. Thanks for
doing the legwork!
regards, tom lane
On 2019/04/10 0:45, Tom Lane wrote:
Amit Langote <Langote_Amit_f8@lab.ntt.co.jp> writes:
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 updateDo we want those tests like that (with the error that is) in the
eval-plan-qual isolation suite?Sure, but I think one such test is enough.
I came up with the attached.
I changed the last case so it actually did what I had in mind
(initial state of the update would be a partition move, but after
fetching up-to-date tuple it isn't) and pushed it. Thanks for
doing the legwork!
Thank you.
Regards,
Amit