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

Commit66ea95f

Browse files
author
Etsuro Fujita
committed
postgres_fdw: Add more test coverage for EvalPlanQual testing.
postgres_fdw supports EvalPlanQual testing by using the infrastructureprovided by the core with the RecheckForeignScan callback routine (cf.commits5fc4c26 and385f337), but there has been no test coveragefor that, except that recent commit12609fb, which fixed an issue incommit385f337, added a test case to exercise only a code path addedby that commit to the core infrastructure. So let's add test cases toexercise other code paths as well at this time.Like commit12609fb, back-patch to all supported branches.Reported-by: Masahiko Sawada <sawada.mshk@gmail.com>Author: Etsuro Fujita <etsuro.fujita@gmail.com>Discussion:https://postgr.es/m/CAPmGK15%2B6H%3DkDA%3D-y3Y28OAPY7fbAdyMosVofZZ%2BNc769epVTQ%40mail.gmail.comBackpatch-through: 13
1 parentbeba3c2 commit66ea95f

File tree

2 files changed

+155
-14
lines changed

2 files changed

+155
-14
lines changed

‎contrib/postgres_fdw/expected/eval_plan_qual.out‎

Lines changed: 101 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -1,11 +1,105 @@
11
Parsed test spec with 2 sessions
22

3-
starting permutation: s0_begin s0_update s1_begin s1_tuplock s0_commit s1_commit
4-
step s0_begin: BEGIN ISOLATION LEVEL READ COMMITTED;
5-
step s0_update: UPDATE a SET i = i + 1;
6-
step s1_begin: BEGIN ISOLATION LEVEL READ COMMITTED;
7-
step s1_tuplock:
8-
-- Verify if the sub-select has a foreign-join plan
3+
starting permutation: s0_update_l s1_tuplock_l_0 s0_commit s1_commit
4+
step s0_update_l: UPDATE l SET i = i + 1;
5+
step s1_tuplock_l_0:
6+
EXPLAIN (VERBOSE, COSTS OFF)
7+
SELECT l.* FROM l, ft WHERE l.i = ft.i AND l.i = 123 FOR UPDATE OF l;
8+
SELECT l.* FROM l, ft WHERE l.i = ft.i AND l.i = 123 FOR UPDATE OF l;
9+
<waiting ...>
10+
step s0_commit: COMMIT;
11+
step s1_tuplock_l_0: <... completed>
12+
QUERY PLAN
13+
---------------------------------------------------------------------
14+
LockRows
15+
Output: l.i, l.v, l.ctid, ft.*
16+
-> Nested Loop
17+
Output: l.i, l.v, l.ctid, ft.*
18+
-> Seq Scan on public.l
19+
Output: l.i, l.v, l.ctid
20+
Filter: (l.i = 123)
21+
-> Foreign Scan on public.ft
22+
Output: ft.*, ft.i
23+
Remote SQL: SELECT i, v FROM public.t WHERE ((i = 123))
24+
(10 rows)
25+
26+
i|v
27+
-+-
28+
(0 rows)
29+
30+
step s1_commit: COMMIT;
31+
32+
starting permutation: s0_update_l s1_tuplock_l_1 s0_commit s1_commit
33+
step s0_update_l: UPDATE l SET i = i + 1;
34+
step s1_tuplock_l_1:
35+
EXPLAIN (VERBOSE, COSTS OFF)
36+
SELECT l.* FROM l, ft WHERE l.i = ft.i AND l.v = 'foo' FOR UPDATE OF l;
37+
SELECT l.* FROM l, ft WHERE l.i = ft.i AND l.v = 'foo' FOR UPDATE OF l;
38+
<waiting ...>
39+
step s0_commit: COMMIT;
40+
step s1_tuplock_l_1: <... completed>
41+
QUERY PLAN
42+
-----------------------------------------------------------------------------
43+
LockRows
44+
Output: l.i, l.v, l.ctid, ft.*
45+
-> Nested Loop
46+
Output: l.i, l.v, l.ctid, ft.*
47+
-> Seq Scan on public.l
48+
Output: l.i, l.v, l.ctid
49+
Filter: (l.v = 'foo'::text)
50+
-> Foreign Scan on public.ft
51+
Output: ft.*, ft.i
52+
Remote SQL: SELECT i, v FROM public.t WHERE (($1::integer = i))
53+
(10 rows)
54+
55+
i|v
56+
-+-
57+
(0 rows)
58+
59+
step s1_commit: COMMIT;
60+
61+
starting permutation: s0_update_a s1_tuplock_a_0 s0_commit s1_commit
62+
step s0_update_a: UPDATE a SET i = i + 1;
63+
step s1_tuplock_a_0:
64+
EXPLAIN (VERBOSE, COSTS OFF)
65+
SELECT a.i FROM a, fb, fc WHERE a.i = fb.i AND fb.i = fc.i FOR UPDATE OF a;
66+
SELECT a.i FROM a, fb, fc WHERE a.i = fb.i AND fb.i = fc.i FOR UPDATE OF a;
67+
<waiting ...>
68+
step s0_commit: COMMIT;
69+
step s1_tuplock_a_0: <... completed>
70+
QUERY PLAN
71+
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
72+
LockRows
73+
Output: a.i, a.ctid, fb.*, fc.*
74+
-> Nested Loop
75+
Output: a.i, a.ctid, fb.*, fc.*
76+
Join Filter: (fb.i = a.i)
77+
-> Foreign Scan
78+
Output: fb.*, fb.i, fc.*, fc.i
79+
Relations: (public.fb) INNER JOIN (public.fc)
80+
Remote SQL: SELECT CASE WHEN (r2.*)::text IS NOT NULL THEN ROW(r2.i) END, r2.i, CASE WHEN (r3.*)::text IS NOT NULL THEN ROW(r3.i) END, r3.i FROM (public.b r2 INNER JOIN public.c r3 ON (((r2.i = r3.i))))
81+
-> Nested Loop
82+
Output: fb.*, fb.i, fc.*, fc.i
83+
Join Filter: (fb.i = fc.i)
84+
-> Foreign Scan on public.fb
85+
Output: fb.*, fb.i
86+
Remote SQL: SELECT i FROM public.b ORDER BY i ASC NULLS LAST
87+
-> Foreign Scan on public.fc
88+
Output: fc.*, fc.i
89+
Remote SQL: SELECT i FROM public.c
90+
-> Seq Scan on public.a
91+
Output: a.i, a.ctid
92+
(20 rows)
93+
94+
i
95+
-
96+
(0 rows)
97+
98+
step s1_commit: COMMIT;
99+
100+
starting permutation: s0_update_a s1_tuplock_a_1 s0_commit s1_commit
101+
step s0_update_a: UPDATE a SET i = i + 1;
102+
step s1_tuplock_a_1:
9103
EXPLAIN (VERBOSE, COSTS OFF)
10104
SELECT a.i,
11105
(SELECT 1 FROM fb, fc WHERE a.i = fb.i AND fb.i = fc.i)
@@ -15,7 +109,7 @@ step s1_tuplock:
15109
FROM a FOR UPDATE;
16110
<waiting ...>
17111
step s0_commit: COMMIT;
18-
steps1_tuplock: <... completed>
112+
steps1_tuplock_a_1: <... completed>
19113
QUERY PLAN
20114
----------------------------------------------------------------------------------------------------------------------------------------
21115
LockRows

‎contrib/postgres_fdw/specs/eval_plan_qual.spec‎

Lines changed: 54 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -6,12 +6,22 @@ setup
66
BEGIN
77
EXECUTE $$CREATESERVERloopbackFOREIGNDATAWRAPPERpostgres_fdw
88
OPTIONS (dbname'$$||current_database()||$$',
9-
port'$$||current_setting('port')||$$'
9+
port'$$||current_setting('port')||$$',
10+
use_remote_estimate'true'
1011
)$$;
1112
END;
1213
$d$;
1314
CREATEUSERMAPPINGFORPUBLICSERVERloopback;
1415

16+
CREATETABLEl (iint,vtext);
17+
CREATETABLEt (iint,vtext);
18+
CREATEFOREIGNTABLEft (iint,vtext)SERVERloopbackOPTIONS (table_name't');
19+
20+
INSERTINTOlVALUES (123,'foo'), (456,'bar'), (789,'baz');
21+
INSERTINTOtSELECTi,to_char(i,'FM0000')FROMgenerate_series(1,1000)i;
22+
CREATEINDEXt_idxONt (i);
23+
ANALYZEl,t;
24+
1525
CREATETABLEa (iint);
1626
CREATETABLEb (iint);
1727
CREATETABLEc (iint);
@@ -21,25 +31,52 @@ setup
2131
INSERTINTOaVALUES (1);
2232
INSERTINTObVALUES (1);
2333
INSERTINTOcVALUES (1);
34+
ANALYZEa,b,c;
2435
}
2536

2637
teardown
2738
{
39+
DROPTABLEl;
40+
DROPTABLEt;
2841
DROPTABLEa;
2942
DROPTABLEb;
3043
DROPTABLEc;
3144
DROPSERVERloopbackCASCADE;
3245
}
3346

3447
sessions0
35-
steps0_begin {BEGINISOLATIONLEVELREADCOMMITTED; }
36-
steps0_update {UPDATEaSETi=i+1; }
48+
setup {BEGINISOLATIONLEVELREADCOMMITTED; }
49+
steps0_update_l {UPDATElSETi=i+1; }
50+
steps0_update_a {UPDATEaSETi=i+1; }
3751
steps0_commit {COMMIT; }
3852

3953
sessions1
40-
steps1_begin {BEGINISOLATIONLEVELREADCOMMITTED; }
41-
steps1_tuplock {
42-
--Verifyifthesub-selecthasaforeign-joinplan
54+
setup {BEGINISOLATIONLEVELREADCOMMITTED; }
55+
56+
# Test for EPQ with a foreign scan pushing down a qual
57+
steps1_tuplock_l_0 {
58+
EXPLAIN (VERBOSE,COSTSOFF)
59+
SELECTl.*FROMl,ftWHEREl.i=ft.iANDl.i=123FORUPDATEOFl;
60+
SELECTl.*FROMl,ftWHEREl.i=ft.iANDl.i=123FORUPDATEOFl;
61+
}
62+
63+
# Same test, except that the qual is parameterized
64+
steps1_tuplock_l_1 {
65+
EXPLAIN (VERBOSE,COSTSOFF)
66+
SELECTl.*FROMl,ftWHEREl.i=ft.iANDl.v='foo'FORUPDATEOFl;
67+
SELECTl.*FROMl,ftWHEREl.i=ft.iANDl.v='foo'FORUPDATEOFl;
68+
}
69+
70+
# Test for EPQ with a foreign scan pushing down a join
71+
steps1_tuplock_a_0 {
72+
EXPLAIN (VERBOSE,COSTSOFF)
73+
SELECTa.iFROMa,fb,fcWHEREa.i=fb.iANDfb.i=fc.iFORUPDATEOFa;
74+
SELECTa.iFROMa,fb,fcWHEREa.i=fb.iANDfb.i=fc.iFORUPDATEOFa;
75+
}
76+
77+
# Same test, except that the join is contained in a SubLink sub-select, not
78+
# in the main query
79+
steps1_tuplock_a_1 {
4380
EXPLAIN (VERBOSE,COSTSOFF)
4481
SELECTa.i,
4582
(SELECT1FROMfb,fcWHEREa.i=fb.iANDfb.i=fc.i)
@@ -48,8 +85,18 @@ step s1_tuplock {
4885
(SELECT1FROMfb,fcWHEREa.i=fb.iANDfb.i=fc.i)
4986
FROMaFORUPDATE;
5087
}
88+
5189
steps1_commit {COMMIT; }
5290

91+
# This test checks the case of rechecking a pushed-down qual.
92+
permutations0_update_ls1_tuplock_l_0s0_commits1_commit
93+
94+
# This test checks the same case, except that the qual is parameterized.
95+
permutations0_update_ls1_tuplock_l_1s0_commits1_commit
96+
97+
# This test checks the case of rechecking a pushed-down join.
98+
permutations0_update_as1_tuplock_a_0s0_commits1_commit
99+
53100
# This test exercises EvalPlanQual with a SubLink sub-select (which should
54101
# be unaffected by any EPQ recheck behavior in the outer query).
55-
permutations0_begins0_updates1_begins1_tuplocks0_commits1_commit
102+
permutations0_update_as1_tuplock_a_1s0_commits1_commit

0 commit comments

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