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

Commitbe6c3d1

Browse files
committed
Improve regression test coverage for TID scanning.
TidScan plan nodes were not systematically tested before. These additionsraise the LOC coverage number for the basic regression tests from 52% to92% in nodeTidscan.c, and from 60% to 93% in tidpath.c.Andres Freund, tweaked a bit by meDiscussion:https://postgr.es/m/20170320062511.hp5qeurtxrwsvfxr@alap3.anarazel.de
1 parent9cf6033 commitbe6c3d1

File tree

4 files changed

+247
-1
lines changed

4 files changed

+247
-1
lines changed

‎src/test/regress/expected/tidscan.out

Lines changed: 179 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,179 @@
1+
-- tests for tidscans
2+
CREATE TABLE tidscan(id integer);
3+
-- only insert a few rows, we don't want to spill onto a second table page
4+
INSERT INTO tidscan VALUES (1), (2), (3);
5+
-- show ctids
6+
SELECT ctid, * FROM tidscan;
7+
ctid | id
8+
-------+----
9+
(0,1) | 1
10+
(0,2) | 2
11+
(0,3) | 3
12+
(3 rows)
13+
14+
-- ctid equality - implemented as tidscan
15+
EXPLAIN (COSTS OFF)
16+
SELECT ctid, * FROM tidscan WHERE ctid = '(0,1)';
17+
QUERY PLAN
18+
-----------------------------------
19+
Tid Scan on tidscan
20+
TID Cond: (ctid = '(0,1)'::tid)
21+
(2 rows)
22+
23+
SELECT ctid, * FROM tidscan WHERE ctid = '(0,1)';
24+
ctid | id
25+
-------+----
26+
(0,1) | 1
27+
(1 row)
28+
29+
EXPLAIN (COSTS OFF)
30+
SELECT ctid, * FROM tidscan WHERE '(0,1)' = ctid;
31+
QUERY PLAN
32+
-----------------------------------
33+
Tid Scan on tidscan
34+
TID Cond: ('(0,1)'::tid = ctid)
35+
(2 rows)
36+
37+
SELECT ctid, * FROM tidscan WHERE '(0,1)' = ctid;
38+
ctid | id
39+
-------+----
40+
(0,1) | 1
41+
(1 row)
42+
43+
-- ctid = ScalarArrayOp - implemented as tidscan
44+
EXPLAIN (COSTS OFF)
45+
SELECT ctid, * FROM tidscan WHERE ctid = ANY(ARRAY['(0,1)', '(0,2)']::tid[]);
46+
QUERY PLAN
47+
-------------------------------------------------------
48+
Tid Scan on tidscan
49+
TID Cond: (ctid = ANY ('{"(0,1)","(0,2)"}'::tid[]))
50+
(2 rows)
51+
52+
SELECT ctid, * FROM tidscan WHERE ctid = ANY(ARRAY['(0,1)', '(0,2)']::tid[]);
53+
ctid | id
54+
-------+----
55+
(0,1) | 1
56+
(0,2) | 2
57+
(2 rows)
58+
59+
-- ctid != ScalarArrayOp - can't be implemented as tidscan
60+
EXPLAIN (COSTS OFF)
61+
SELECT ctid, * FROM tidscan WHERE ctid != ANY(ARRAY['(0,1)', '(0,2)']::tid[]);
62+
QUERY PLAN
63+
------------------------------------------------------
64+
Seq Scan on tidscan
65+
Filter: (ctid <> ANY ('{"(0,1)","(0,2)"}'::tid[]))
66+
(2 rows)
67+
68+
SELECT ctid, * FROM tidscan WHERE ctid != ANY(ARRAY['(0,1)', '(0,2)']::tid[]);
69+
ctid | id
70+
-------+----
71+
(0,1) | 1
72+
(0,2) | 2
73+
(0,3) | 3
74+
(3 rows)
75+
76+
-- tid equality extracted from sub-AND clauses
77+
EXPLAIN (COSTS OFF)
78+
SELECT ctid, * FROM tidscan
79+
WHERE (id = 3 AND ctid IN ('(0,2)', '(0,3)')) OR (ctid = '(0,1)' AND id = 1);
80+
QUERY PLAN
81+
--------------------------------------------------------------------------------------------------------------
82+
Tid Scan on tidscan
83+
TID Cond: ((ctid = ANY ('{"(0,2)","(0,3)"}'::tid[])) OR (ctid = '(0,1)'::tid))
84+
Filter: (((id = 3) AND (ctid = ANY ('{"(0,2)","(0,3)"}'::tid[]))) OR ((ctid = '(0,1)'::tid) AND (id = 1)))
85+
(3 rows)
86+
87+
SELECT ctid, * FROM tidscan
88+
WHERE (id = 3 AND ctid IN ('(0,2)', '(0,3)')) OR (ctid = '(0,1)' AND id = 1);
89+
ctid | id
90+
-------+----
91+
(0,1) | 1
92+
(0,3) | 3
93+
(2 rows)
94+
95+
-- exercise backward scan and rewind
96+
BEGIN;
97+
DECLARE c CURSOR FOR
98+
SELECT ctid, * FROM tidscan WHERE ctid = ANY(ARRAY['(0,1)', '(0,2)']::tid[]);
99+
FETCH ALL FROM c;
100+
ctid | id
101+
-------+----
102+
(0,1) | 1
103+
(0,2) | 2
104+
(2 rows)
105+
106+
FETCH BACKWARD 1 FROM c;
107+
ctid | id
108+
-------+----
109+
(0,2) | 2
110+
(1 row)
111+
112+
FETCH FIRST FROM c;
113+
ctid | id
114+
-------+----
115+
(0,1) | 1
116+
(1 row)
117+
118+
ROLLBACK;
119+
-- tidscan via CURRENT OF
120+
BEGIN;
121+
DECLARE c CURSOR FOR SELECT ctid, * FROM tidscan;
122+
FETCH NEXT FROM c; -- skip one row
123+
ctid | id
124+
-------+----
125+
(0,1) | 1
126+
(1 row)
127+
128+
FETCH NEXT FROM c;
129+
ctid | id
130+
-------+----
131+
(0,2) | 2
132+
(1 row)
133+
134+
-- perform update
135+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
136+
UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *;
137+
QUERY PLAN
138+
---------------------------------------------------
139+
Update on tidscan (actual rows=1 loops=1)
140+
-> Tid Scan on tidscan (actual rows=1 loops=1)
141+
TID Cond: CURRENT OF c
142+
(3 rows)
143+
144+
FETCH NEXT FROM c;
145+
ctid | id
146+
-------+----
147+
(0,3) | 3
148+
(1 row)
149+
150+
-- perform update
151+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
152+
UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *;
153+
QUERY PLAN
154+
---------------------------------------------------
155+
Update on tidscan (actual rows=1 loops=1)
156+
-> Tid Scan on tidscan (actual rows=1 loops=1)
157+
TID Cond: CURRENT OF c
158+
(3 rows)
159+
160+
SELECT * FROM tidscan;
161+
id
162+
----
163+
1
164+
-2
165+
-3
166+
(3 rows)
167+
168+
-- position cursor past any rows
169+
FETCH NEXT FROM c;
170+
ctid | id
171+
------+----
172+
(0 rows)
173+
174+
-- should error out
175+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
176+
UPDATE tidscan SET id = -id WHERE CURRENT OF c RETURNING *;
177+
ERROR: cursor "c" is not positioned on a row
178+
ROLLBACK;
179+
DROP TABLE tidscan;

‎src/test/regress/parallel_schedule

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -89,7 +89,7 @@ test: brin gin gist spgist privileges init_privs security_label collate matview
8989
# ----------
9090
# Another group of parallel tests
9191
# ----------
92-
test: alter_generic alter_operator misc psql async dbsize misc_functions sysviews tsrf
92+
test: alter_generic alter_operator misc psql async dbsize misc_functions sysviews tsrf tidscan
9393

9494
# rules cannot run concurrently with any test that creates a view
9595
test: rules psql_crosstab amutils

‎src/test/regress/serial_schedule

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -129,6 +129,7 @@ test: dbsize
129129
test: misc_functions
130130
test: sysviews
131131
test: tsrf
132+
test: tidscan
132133
test: rules
133134
test: psql_crosstab
134135
test: select_parallel

‎src/test/regress/sql/tidscan.sql

Lines changed: 66 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,66 @@
1+
-- tests for tidscans
2+
3+
CREATETABLEtidscan(idinteger);
4+
5+
-- only insert a few rows, we don't want to spill onto a second table page
6+
INSERT INTO tidscanVALUES (1), (2), (3);
7+
8+
-- show ctids
9+
SELECT ctid,*FROM tidscan;
10+
11+
-- ctid equality - implemented as tidscan
12+
EXPLAIN (COSTS OFF)
13+
SELECT ctid,*FROM tidscanWHERE ctid='(0,1)';
14+
SELECT ctid,*FROM tidscanWHERE ctid='(0,1)';
15+
16+
EXPLAIN (COSTS OFF)
17+
SELECT ctid,*FROM tidscanWHERE'(0,1)'= ctid;
18+
SELECT ctid,*FROM tidscanWHERE'(0,1)'= ctid;
19+
20+
-- ctid = ScalarArrayOp - implemented as tidscan
21+
EXPLAIN (COSTS OFF)
22+
SELECT ctid,*FROM tidscanWHERE ctid= ANY(ARRAY['(0,1)','(0,2)']::tid[]);
23+
SELECT ctid,*FROM tidscanWHERE ctid= ANY(ARRAY['(0,1)','(0,2)']::tid[]);
24+
25+
-- ctid != ScalarArrayOp - can't be implemented as tidscan
26+
EXPLAIN (COSTS OFF)
27+
SELECT ctid,*FROM tidscanWHERE ctid!= ANY(ARRAY['(0,1)','(0,2)']::tid[]);
28+
SELECT ctid,*FROM tidscanWHERE ctid!= ANY(ARRAY['(0,1)','(0,2)']::tid[]);
29+
30+
-- tid equality extracted from sub-AND clauses
31+
EXPLAIN (COSTS OFF)
32+
SELECT ctid,*FROM tidscan
33+
WHERE (id=3AND ctidIN ('(0,2)','(0,3)'))OR (ctid='(0,1)'AND id=1);
34+
SELECT ctid,*FROM tidscan
35+
WHERE (id=3AND ctidIN ('(0,2)','(0,3)'))OR (ctid='(0,1)'AND id=1);
36+
37+
-- exercise backward scan and rewind
38+
BEGIN;
39+
DECLARE c CURSOR FOR
40+
SELECT ctid,*FROM tidscanWHERE ctid= ANY(ARRAY['(0,1)','(0,2)']::tid[]);
41+
FETCH ALLFROM c;
42+
FETCH BACKWARD1FROM c;
43+
FETCH FIRSTFROM c;
44+
ROLLBACK;
45+
46+
-- tidscan via CURRENT OF
47+
BEGIN;
48+
DECLARE c CURSOR FORSELECT ctid,*FROM tidscan;
49+
FETCH NEXTFROM c;-- skip one row
50+
FETCH NEXTFROM c;
51+
-- perform update
52+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
53+
UPDATE tidscanSET id=-idWHERE CURRENT OF c RETURNING*;
54+
FETCH NEXTFROM c;
55+
-- perform update
56+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
57+
UPDATE tidscanSET id=-idWHERE CURRENT OF c RETURNING*;
58+
SELECT*FROM tidscan;
59+
-- position cursor past any rows
60+
FETCH NEXTFROM c;
61+
-- should error out
62+
EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
63+
UPDATE tidscanSET id=-idWHERE CURRENT OF c RETURNING*;
64+
ROLLBACK;
65+
66+
DROPTABLE tidscan;

0 commit comments

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