From c3eec94fc1a02a67ee51cdd781f24f6f28ad5b36 Mon Sep 17 00:00:00 2001 From: Etsuro Fujita Date: Thu, 6 Nov 2025 12:15:00 +0900 Subject: 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 5fc4c26db and 385f337c9), but there has been no test coverage for that, except that recent commit 12609fbac, which fixed an issue in commit 385f337c9, 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 12609fbac, back-patch to all supported branches. Reported-by: Masahiko Sawada Author: Etsuro Fujita Discussion: https://postgr.es/m/CAPmGK15%2B6H%3DkDA%3D-y3Y28OAPY7fbAdyMosVofZZ%2BNc769epVTQ%40mail.gmail.com Backpatch-through: 13 --- contrib/postgres_fdw/specs/eval_plan_qual.spec | 61 +++++++++++++++++++++++--- 1 file changed, 54 insertions(+), 7 deletions(-) (limited to 'contrib/postgres_fdw/specs') diff --git a/contrib/postgres_fdw/specs/eval_plan_qual.spec b/contrib/postgres_fdw/specs/eval_plan_qual.spec index 30a83e04058..9f52270db69 100644 --- a/contrib/postgres_fdw/specs/eval_plan_qual.spec +++ b/contrib/postgres_fdw/specs/eval_plan_qual.spec @@ -6,12 +6,22 @@ setup BEGIN EXECUTE $$CREATE SERVER loopback FOREIGN DATA WRAPPER postgres_fdw OPTIONS (dbname '$$||current_database()||$$', - port '$$||current_setting('port')||$$' + port '$$||current_setting('port')||$$', + use_remote_estimate 'true' )$$; END; $d$; CREATE USER MAPPING FOR PUBLIC SERVER loopback; + CREATE TABLE l (i int, v text); + CREATE TABLE t (i int, v text); + CREATE FOREIGN TABLE ft (i int, v text) SERVER loopback OPTIONS (table_name 't'); + + INSERT INTO l VALUES (123, 'foo'), (456, 'bar'), (789, 'baz'); + INSERT INTO t SELECT i, to_char(i, 'FM0000') FROM generate_series(1, 1000) i; + CREATE INDEX t_idx ON t (i); + ANALYZE l, t; + CREATE TABLE a (i int); CREATE TABLE b (i int); CREATE TABLE c (i int); @@ -21,10 +31,13 @@ setup INSERT INTO a VALUES (1); INSERT INTO b VALUES (1); INSERT INTO c VALUES (1); + ANALYZE a, b, c; } teardown { + DROP TABLE l; + DROP TABLE t; DROP TABLE a; DROP TABLE b; DROP TABLE c; @@ -32,14 +45,38 @@ teardown } session s0 -step s0_begin { BEGIN ISOLATION LEVEL READ COMMITTED; } -step s0_update { UPDATE a SET i = i + 1; } +setup { BEGIN ISOLATION LEVEL READ COMMITTED; } +step s0_update_l { UPDATE l SET i = i + 1; } +step s0_update_a { UPDATE a SET i = i + 1; } step s0_commit { COMMIT; } session s1 -step s1_begin { BEGIN ISOLATION LEVEL READ COMMITTED; } -step s1_tuplock { - -- Verify if the sub-select has a foreign-join plan +setup { BEGIN ISOLATION LEVEL READ COMMITTED; } + +# Test for EPQ with a foreign scan pushing down a qual +step s1_tuplock_l_0 { + EXPLAIN (VERBOSE, COSTS OFF) + SELECT l.* FROM l, ft WHERE l.i = ft.i AND l.i = 123 FOR UPDATE OF l; + SELECT l.* FROM l, ft WHERE l.i = ft.i AND l.i = 123 FOR UPDATE OF l; +} + +# Same test, except that the qual is parameterized +step s1_tuplock_l_1 { + EXPLAIN (VERBOSE, COSTS OFF) + SELECT l.* FROM l, ft WHERE l.i = ft.i AND l.v = 'foo' FOR UPDATE OF l; + SELECT l.* FROM l, ft WHERE l.i = ft.i AND l.v = 'foo' FOR UPDATE OF l; +} + +# Test for EPQ with a foreign scan pushing down a join +step s1_tuplock_a_0 { + EXPLAIN (VERBOSE, COSTS OFF) + SELECT a.i FROM a, fb, fc WHERE a.i = fb.i AND fb.i = fc.i FOR UPDATE OF a; + SELECT a.i FROM a, fb, fc WHERE a.i = fb.i AND fb.i = fc.i FOR UPDATE OF a; +} + +# Same test, except that the join is contained in a SubLink sub-select, not +# in the main query +step s1_tuplock_a_1 { EXPLAIN (VERBOSE, COSTS OFF) SELECT a.i, (SELECT 1 FROM fb, fc WHERE a.i = fb.i AND fb.i = fc.i) @@ -48,8 +85,18 @@ step s1_tuplock { (SELECT 1 FROM fb, fc WHERE a.i = fb.i AND fb.i = fc.i) FROM a FOR UPDATE; } + step s1_commit { COMMIT; } +# This test checks the case of rechecking a pushed-down qual. +permutation s0_update_l s1_tuplock_l_0 s0_commit s1_commit + +# This test checks the same case, except that the qual is parameterized. +permutation s0_update_l s1_tuplock_l_1 s0_commit s1_commit + +# This test checks the case of rechecking a pushed-down join. +permutation s0_update_a s1_tuplock_a_0 s0_commit s1_commit + # This test exercises EvalPlanQual with a SubLink sub-select (which should # be unaffected by any EPQ recheck behavior in the outer query). -permutation s0_begin s0_update s1_begin s1_tuplock s0_commit s1_commit +permutation s0_update_a s1_tuplock_a_1 s0_commit s1_commit -- cgit v1.2.3