Skip to content

Commit 8b6b043

Browse files
david-rowleydavid-rowley
committed
Re-adjust drop-index-concurrently-1 isolation test
It seems that drop-index-concurrently-1 has started to forget what it was originally meant to be testing. d2d8a22, which added incremental sorts changed the expected plan to be an Index Scan plan instead of a Seq Scan plan. This occurred as the primary key index of the table in question provided presorted input and, because that index happened to be the cheapest input path due to enable_seqscan being disabled, the incremental sort changes just added a Sort on top of that. It seems based on the name of the PREPAREd statement that the intention here is that the query produces a seqscan plan. The reason this test has become broken seems to be due to how the test was originally coded. The test was trying to force a seqscan plan by performing some casting to make it so the test_dc index couldn't be used to perform the required filtering. Trying to coax the planner into using a plan which has costed in a disable_cost seems like it's always going to be flakey as small changes in costs are drowned out by the large disable_cost combined with add_path's STD_FUZZ_FACTOR. Here we get rid of the casts that we're using to try to trick the planner into a seqscan and instead toggle enable_seqscan as and when required to get the desired plan. Additionally, rename a few things in the test and add some additional wording to the comments to try and make it more clear in the future what we expect this test to be doing. Discussion: https://postgr.es/m/CAApHDvrbDhObhLV+=U_K_-t+2Av2av1aL9d+2j_3AO-XndaviA@mail.gmail.com Backpatch-through: 13, where d2d8a22 changed the expected test output
1 parent ac99802 commit 8b6b043

File tree

3 files changed

+44
-40
lines changed

3 files changed

+44
-40
lines changed

‎src/test/isolation/expected/drop-index-concurrently-1.out

Lines changed: 16 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -1,17 +1,17 @@
11
Parsed test spec with 3 sessions
22

3-
starting permutation: noseq chkiso prepi preps begin explaini explains select2 drop insert2 end2 selecti selects end
4-
step noseq: SET enable_seqscan = false;
3+
starting permutation: chkiso prepi preps begin disableseq explaini enableseq explains select2 drop insert2 end2 selecti selects end
54
step chkiso: SELECT (setting in ('read committed','read uncommitted')) AS is_read_committed FROM pg_settings WHERE name = 'default_transaction_isolation';
65
is_read_committed
76
-----------------
87
t
98
(1 row)
109

11-
step prepi: PREPARE getrow_idx AS SELECT * FROM test_dc WHERE data=34 ORDER BY id,data;
12-
step preps: PREPARE getrow_seq AS SELECT * FROM test_dc WHERE data::text=34::text ORDER BY id,data;
10+
step prepi: PREPARE getrow_idxscan AS SELECT * FROM test_dc WHERE data = 34 ORDER BY id,data;
11+
step preps: PREPARE getrow_seqscan AS SELECT * FROM test_dc WHERE data = 34 ORDER BY id,data;
1312
step begin: BEGIN;
14-
step explaini: EXPLAIN (COSTS OFF) EXECUTE getrow_idx;
13+
step disableseq: SET enable_seqscan = false;
14+
step explaini: EXPLAIN (COSTS OFF) EXECUTE getrow_idxscan;
1515
QUERY PLAN
1616
----------------------------------------------
1717
Sort
@@ -20,16 +20,17 @@ Sort
2020
Index Cond: (data = 34)
2121
(4 rows)
2222

23-
step explains: EXPLAIN (COSTS OFF) EXECUTE getrow_seq;
24-
QUERY PLAN
25-
----------------------------------------------
26-
Sort
27-
Sort Key: id, data
28-
-> Index Scan using test_dc_pkey on test_dc
29-
Filter: ((data)::text = '34'::text)
23+
step enableseq: SET enable_seqscan = true;
24+
step explains: EXPLAIN (COSTS OFF) EXECUTE getrow_seqscan;
25+
QUERY PLAN
26+
---------------------------
27+
Sort
28+
Sort Key: id
29+
-> Seq Scan on test_dc
30+
Filter: (data = 34)
3031
(4 rows)
3132

32-
step select2: SELECT * FROM test_dc WHERE data=34 ORDER BY id,data;
33+
step select2: SELECT * FROM test_dc WHERE data = 34 ORDER BY id,data;
3334
id|data
3435
--+----
3536
34| 34
@@ -38,14 +39,14 @@ id|data
3839
step drop: DROP INDEX CONCURRENTLY test_dc_data; <waiting ...>
3940
step insert2: INSERT INTO test_dc(data) SELECT * FROM generate_series(1, 100);
4041
step end2: COMMIT;
41-
step selecti: EXECUTE getrow_idx;
42+
step selecti: EXECUTE getrow_idxscan;
4243
id|data
4344
---+----
4445
34| 34
4546
134| 34
4647
(2 rows)
4748

48-
step selects: EXECUTE getrow_seq;
49+
step selects: EXECUTE getrow_seqscan;
4950
id|data
5051
---+----
5152
34| 34

‎src/test/isolation/expected/drop-index-concurrently-1_2.out

Lines changed: 16 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -1,17 +1,17 @@
11
Parsed test spec with 3 sessions
22

3-
starting permutation: noseq chkiso prepi preps begin explaini explains select2 drop insert2 end2 selecti selects end
4-
step noseq: SET enable_seqscan = false;
3+
starting permutation: chkiso prepi preps begin disableseq explaini enableseq explains select2 drop insert2 end2 selecti selects end
54
step chkiso: SELECT (setting in ('read committed','read uncommitted')) AS is_read_committed FROM pg_settings WHERE name = 'default_transaction_isolation';
65
is_read_committed
76
-----------------
87
f
98
(1 row)
109

11-
step prepi: PREPARE getrow_idx AS SELECT * FROM test_dc WHERE data=34 ORDER BY id,data;
12-
step preps: PREPARE getrow_seq AS SELECT * FROM test_dc WHERE data::text=34::text ORDER BY id,data;
10+
step prepi: PREPARE getrow_idxscan AS SELECT * FROM test_dc WHERE data = 34 ORDER BY id,data;
11+
step preps: PREPARE getrow_seqscan AS SELECT * FROM test_dc WHERE data = 34 ORDER BY id,data;
1312
step begin: BEGIN;
14-
step explaini: EXPLAIN (COSTS OFF) EXECUTE getrow_idx;
13+
step disableseq: SET enable_seqscan = false;
14+
step explaini: EXPLAIN (COSTS OFF) EXECUTE getrow_idxscan;
1515
QUERY PLAN
1616
----------------------------------------------
1717
Sort
@@ -20,16 +20,17 @@ Sort
2020
Index Cond: (data = 34)
2121
(4 rows)
2222

23-
step explains: EXPLAIN (COSTS OFF) EXECUTE getrow_seq;
24-
QUERY PLAN
25-
----------------------------------------------
26-
Sort
27-
Sort Key: id, data
28-
-> Index Scan using test_dc_pkey on test_dc
29-
Filter: ((data)::text = '34'::text)
23+
step enableseq: SET enable_seqscan = true;
24+
step explains: EXPLAIN (COSTS OFF) EXECUTE getrow_seqscan;
25+
QUERY PLAN
26+
---------------------------
27+
Sort
28+
Sort Key: id
29+
-> Seq Scan on test_dc
30+
Filter: (data = 34)
3031
(4 rows)
3132

32-
step select2: SELECT * FROM test_dc WHERE data=34 ORDER BY id,data;
33+
step select2: SELECT * FROM test_dc WHERE data = 34 ORDER BY id,data;
3334
id|data
3435
--+----
3536
34| 34
@@ -38,13 +39,13 @@ id|data
3839
step drop: DROP INDEX CONCURRENTLY test_dc_data; <waiting ...>
3940
step insert2: INSERT INTO test_dc(data) SELECT * FROM generate_series(1, 100);
4041
step end2: COMMIT;
41-
step selecti: EXECUTE getrow_idx;
42+
step selecti: EXECUTE getrow_idxscan;
4243
id|data
4344
--+----
4445
34| 34
4546
(1 row)
4647

47-
step selects: EXECUTE getrow_seq;
48+
step selects: EXECUTE getrow_seqscan;
4849
id|data
4950
--+----
5051
34| 34

‎src/test/isolation/specs/drop-index-concurrently-1.spec

Lines changed: 12 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -3,7 +3,8 @@
33
# This test shows that the concurrent write behaviour works correctly
44
# with the expected output being 2 rows at the READ COMMITTED and READ
55
# UNCOMMITTED transaction isolation levels, and 1 row at the other
6-
# transaction isolation levels.
6+
# transaction isolation levels. We ensure this is the case by checking
7+
# the returned rows in an index scan plan and a seq scan plan.
78
#
89
setup
910
{
@@ -18,24 +19,25 @@ teardown
1819
}
1920

2021
session s1
21-
step noseq { SET enable_seqscan = false; }
2222
step chkiso { SELECT (setting in ('read committed','read uncommitted')) AS is_read_committed FROM pg_settings WHERE name = 'default_transaction_isolation'; }
23-
step prepi { PREPARE getrow_idx AS SELECT * FROM test_dc WHERE data=34 ORDER BY id,data; }
24-
step preps { PREPARE getrow_seq AS SELECT * FROM test_dc WHERE data::text=34::text ORDER BY id,data; }
23+
step prepi { PREPARE getrow_idxscan AS SELECT * FROM test_dc WHERE data = 34 ORDER BY id,data; }
24+
step preps { PREPARE getrow_seqscan AS SELECT * FROM test_dc WHERE data = 34 ORDER BY id,data; }
2525
step begin { BEGIN; }
26-
step explaini { EXPLAIN (COSTS OFF) EXECUTE getrow_idx; }
27-
step explains { EXPLAIN (COSTS OFF) EXECUTE getrow_seq; }
28-
step selecti { EXECUTE getrow_idx; }
29-
step selects { EXECUTE getrow_seq; }
26+
step disableseq { SET enable_seqscan = false; }
27+
step explaini { EXPLAIN (COSTS OFF) EXECUTE getrow_idxscan; }
28+
step enableseq { SET enable_seqscan = true; }
29+
step explains { EXPLAIN (COSTS OFF) EXECUTE getrow_seqscan; }
30+
step selecti { EXECUTE getrow_idxscan; }
31+
step selects { EXECUTE getrow_seqscan; }
3032
step end { COMMIT; }
3133

3234
session s2
3335
setup { BEGIN; }
34-
step select2 { SELECT * FROM test_dc WHERE data=34 ORDER BY id,data; }
36+
step select2 { SELECT * FROM test_dc WHERE data = 34 ORDER BY id,data; }
3537
step insert2 { INSERT INTO test_dc(data) SELECT * FROM generate_series(1, 100); }
3638
step end2 { COMMIT; }
3739

3840
session s3
3941
step drop { DROP INDEX CONCURRENTLY test_dc_data; }
4042

41-
permutation noseq chkiso prepi preps begin explaini explains select2 drop insert2 end2 selecti selects end
43+
permutation chkiso prepi preps begin disableseq explaini enableseq explains select2 drop insert2 end2 selecti selects end

0 commit comments

Comments
 (0)