Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit8b6b043

Browse files
committed
Re-adjust drop-index-concurrently-1 isolation test
It seems that drop-index-concurrently-1 has started to forget what it wasoriginally meant to be testing.d2d8a22, which added incremental sortschanged the expected plan to be an Index Scan plan instead of a Seq Scanplan. This occurred as the primary key index of the table in questionprovided presorted input and, because that index happened to be thecheapest input path due to enable_seqscan being disabled, the incrementalsort changes just added a Sort on top of that. It seems based on the nameof the PREPAREd statement that the intention here is that the queryproduces a seqscan plan.The reason this test has become broken seems to be due to how the test wasoriginally coded. The test was trying to force a seqscan plan byperforming some casting to make it so the test_dc index couldn't be usedto perform the required filtering. Trying to coax the planner into usinga plan which has costed in a disable_cost seems like it's always going tobe flakey as small changes in costs are drowned out by the largedisable_cost combined with add_path's STD_FUZZ_FACTOR. Here we get rid ofthe casts that we're using to try to trick the planner into a seqscan andinstead toggle enable_seqscan as and when required to get the desiredplan.Additionally, rename a few things in the test and add some additionalwording to the comments to try and make it more clear in the future whatwe expect this test to be doing.Discussion:https://postgr.es/m/CAApHDvrbDhObhLV+=U_K_-t+2Av2av1aL9d+2j_3AO-XndaviA@mail.gmail.comBackpatch-through: 13, whered2d8a22 changed the expected test output
1 parentac99802 commit8b6b043

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: PREPAREgetrow_idx AS SELECT * FROM test_dc WHERE data=34 ORDER BY id,data;
12-
step preps: PREPAREgetrow_seq AS SELECT * FROM test_dc WHERE data::text=34::text ORDER BY id,data;
10+
step prepi: PREPAREgetrow_idxscan AS SELECT * FROM test_dc WHERE data =34 ORDER BY id,data;
11+
step preps: PREPAREgetrow_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: EXECUTEgetrow_idx;
42+
step selecti: EXECUTEgetrow_idxscan;
4243
id|data
4344
---+----
4445
34| 34
4546
134| 34
4647
(2 rows)
4748

48-
step selects: EXECUTEgetrow_seq;
49+
step selects: EXECUTEgetrow_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: PREPAREgetrow_idx AS SELECT * FROM test_dc WHERE data=34 ORDER BY id,data;
12-
step preps: PREPAREgetrow_seq AS SELECT * FROM test_dc WHERE data::text=34::text ORDER BY id,data;
10+
step prepi: PREPAREgetrow_idxscan AS SELECT * FROM test_dc WHERE data =34 ORDER BY id,data;
11+
step preps: PREPAREgetrow_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: EXECUTEgetrow_idx;
42+
step selecti: EXECUTEgetrow_idxscan;
4243
id|data
4344
--+----
4445
34| 34
4546
(1 row)
4647

47-
step selects: EXECUTEgetrow_seq;
48+
step selects: EXECUTEgetrow_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
sessions1
21-
stepnoseq{SETenable_seqscan=false;}
2222
stepchkiso{SELECT(settingin('read committed','read uncommitted'))ASis_read_committedFROMpg_settingsWHEREname='default_transaction_isolation';}
23-
stepprepi{PREPAREgetrow_idxASSELECT *FROMtest_dcWHEREdata=34ORDERBYid,data;}
24-
steppreps{PREPAREgetrow_seqASSELECT *FROMtest_dcWHEREdata::text=34::textORDERBYid,data;}
23+
stepprepi{PREPAREgetrow_idxscanASSELECT *FROMtest_dcWHEREdata=34ORDERBYid,data;}
24+
steppreps{PREPAREgetrow_seqscanASSELECT *FROMtest_dcWHEREdata=34ORDERBYid,data;}
2525
stepbegin{BEGIN;}
26-
stepexplaini{EXPLAIN(COSTSOFF)EXECUTEgetrow_idx;}
27-
stepexplains{EXPLAIN(COSTSOFF)EXECUTEgetrow_seq;}
28-
stepselecti{EXECUTEgetrow_idx;}
29-
stepselects{EXECUTEgetrow_seq;}
26+
stepdisableseq{SETenable_seqscan=false;}
27+
stepexplaini{EXPLAIN(COSTSOFF)EXECUTEgetrow_idxscan;}
28+
stepenableseq{SETenable_seqscan=true;}
29+
stepexplains{EXPLAIN(COSTSOFF)EXECUTEgetrow_seqscan;}
30+
stepselecti{EXECUTEgetrow_idxscan;}
31+
stepselects{EXECUTEgetrow_seqscan;}
3032
stepend{COMMIT;}
3133

3234
sessions2
3335
setup{ BEGIN;}
34-
stepselect2{SELECT *FROMtest_dcWHEREdata=34ORDERBYid,data;}
36+
stepselect2{SELECT *FROMtest_dcWHEREdata=34ORDERBYid,data;}
3537
stepinsert2{INSERTINTOtest_dc(data)SELECT *FROMgenerate_series(1,100);}
3638
stepend2{COMMIT;}
3739

3840
sessions3
3941
stepdrop{DROPINDEXCONCURRENTLYtest_dc_data;}
4042

41-
permutationnoseqchkisoprepiprepsbeginexplainiexplainsselect2dropinsert2end2selectiselectsend
43+
permutationchkisoprepiprepsbegindisableseqexplainienableseqexplainsselect2dropinsert2end2selectiselectsend

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp