Skip to content

Commit e7b0c81

Browse files
author
Etsuro Fujita
committed
postgres_fdw: Add more test coverage for EvalPlanQual testing.
postgres_fdw supports EvalPlanQual testing by using the infrastructure provided by the core with the RecheckForeignScan callback routine (cf. commits 5fc4c26 and 385f337), but there has been no test coverage for that, except that recent commit 12609fb, which fixed an issue in commit 385f337, added a test case to exercise only a code path added by that commit to the core infrastructure. So let's add test cases to exercise other code paths as well at this time. Like commit 12609fb, 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.com Backpatch-through: 13
1 parent 2adf303 commit e7b0c81

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-
step s1_tuplock: <... completed>
112+
step s1_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 $$CREATE SERVER loopback FOREIGN DATA WRAPPER postgres_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
CREATE USER MAPPING FOR PUBLIC SERVER loopback;
1415

16+
CREATE TABLE l (i int, v text);
17+
CREATE TABLE t (i int, v text);
18+
CREATE FOREIGN TABLE ft (i int, v text) SERVER loopback OPTIONS (table_name 't');
19+
20+
INSERT INTO l VALUES (123, 'foo'), (456, 'bar'), (789, 'baz');
21+
INSERT INTO t SELECT i, to_char(i, 'FM0000') FROM generate_series(1, 1000) i;
22+
CREATE INDEX t_idx ON t (i);
23+
ANALYZE l, t;
24+
1525
CREATE TABLE a (i int);
1626
CREATE TABLE b (i int);
1727
CREATE TABLE c (i int);
@@ -21,25 +31,52 @@ setup
2131
INSERT INTO a VALUES (1);
2232
INSERT INTO b VALUES (1);
2333
INSERT INTO c VALUES (1);
34+
ANALYZE a, b, c;
2435
}
2536

2637
teardown
2738
{
39+
DROP TABLE l;
40+
DROP TABLE t;
2841
DROP TABLE a;
2942
DROP TABLE b;
3043
DROP TABLE c;
3144
DROP SERVER loopback CASCADE;
3245
}
3346

3447
session s0
35-
step s0_begin { BEGIN ISOLATION LEVEL READ COMMITTED; }
36-
step s0_update { UPDATE a SET i = i + 1; }
48+
setup { BEGIN ISOLATION LEVEL READ COMMITTED; }
49+
step s0_update_l { UPDATE l SET i = i + 1; }
50+
step s0_update_a { UPDATE a SET i = i + 1; }
3751
step s0_commit { COMMIT; }
3852

3953
session s1
40-
step s1_begin { BEGIN ISOLATION LEVEL READ COMMITTED; }
41-
step s1_tuplock {
42-
-- Verify if the sub-select has a foreign-join plan
54+
setup { BEGIN ISOLATION LEVEL READ COMMITTED; }
55+
56+
# Test for EPQ with a foreign scan pushing down a qual
57+
step s1_tuplock_l_0 {
58+
EXPLAIN (VERBOSE, COSTS OFF)
59+
SELECT l.* FROM l, ft WHERE l.i = ft.i AND l.i = 123 FOR UPDATE OF l;
60+
SELECT l.* FROM l, ft WHERE l.i = ft.i AND l.i = 123 FOR UPDATE OF l;
61+
}
62+
63+
# Same test, except that the qual is parameterized
64+
step s1_tuplock_l_1 {
65+
EXPLAIN (VERBOSE, COSTS OFF)
66+
SELECT l.* FROM l, ft WHERE l.i = ft.i AND l.v = 'foo' FOR UPDATE OF l;
67+
SELECT l.* FROM l, ft WHERE l.i = ft.i AND l.v = 'foo' FOR UPDATE OF l;
68+
}
69+
70+
# Test for EPQ with a foreign scan pushing down a join
71+
step s1_tuplock_a_0 {
72+
EXPLAIN (VERBOSE, COSTS OFF)
73+
SELECT a.i FROM a, fb, fc WHERE a.i = fb.i AND fb.i = fc.i FOR UPDATE OF a;
74+
SELECT a.i FROM a, fb, fc WHERE a.i = fb.i AND fb.i = fc.i FOR UPDATE OF a;
75+
}
76+
77+
# Same test, except that the join is contained in a SubLink sub-select, not
78+
# in the main query
79+
step s1_tuplock_a_1 {
4380
EXPLAIN (VERBOSE, COSTS OFF)
4481
SELECT a.i,
4582
(SELECT 1 FROM fb, fc WHERE a.i = fb.i AND fb.i = fc.i)
@@ -48,8 +85,18 @@ step s1_tuplock {
4885
(SELECT 1 FROM fb, fc WHERE a.i = fb.i AND fb.i = fc.i)
4986
FROM a FOR UPDATE;
5087
}
88+
5189
step s1_commit { COMMIT; }
5290

91+
# This test checks the case of rechecking a pushed-down qual.
92+
permutation s0_update_l s1_tuplock_l_0 s0_commit s1_commit
93+
94+
# This test checks the same case, except that the qual is parameterized.
95+
permutation s0_update_l s1_tuplock_l_1 s0_commit s1_commit
96+
97+
# This test checks the case of rechecking a pushed-down join.
98+
permutation s0_update_a s1_tuplock_a_0 s0_commit s1_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-
permutation s0_begin s0_update s1_begin s1_tuplock s0_commit s1_commit
102+
permutation s0_update_a s1_tuplock_a_1 s0_commit s1_commit

0 commit comments

Comments
 (0)