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

Commitd4adff0

Browse files
author
Etsuro Fujita
committed
postgres_fdw: Avoid 'variable not found in subplan target list' error.
The tlist of the EvalPlanQual outer plan for a ForeignScan node isadjusted to produce a tuple whose descriptor matches the scan tuple slotfor the ForeignScan node. But in the case where the outer plan containsan extra Sort node, if the new tlist contained columns required only forevaluating PlaceHolderVars or columns required only for evaluating localconditions, this would cause setrefs.c to fail with the error.The cause of this is that when creating the outer plan by injecting theSort node into an alternative local join plan that could emit such extracolumns as well, we fail to arrange for the outer plan to propagate themup through the Sort node, causing setrefs.c to fail to match up them inthe new tlist to what is available from the outer plan. Repair.Per report from Alexander Pyhalov.Richard Guo and Etsuro Fujita, reviewed by Alexander Pyhalov and Tom Lane.Backpatch to all supported versions.Discussion:http://postgr.es/m/cfb17bf6dfdf876467bd5ef533852d18%40postgrespro.ru
1 parenta9e99ff commitd4adff0

File tree

3 files changed

+150
-0
lines changed

3 files changed

+150
-0
lines changed

‎contrib/postgres_fdw/expected/postgres_fdw.out

Lines changed: 83 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2419,6 +2419,89 @@ SELECT * FROM ft1, ft2, ft4, ft5 WHERE ft1.c1 = ft2.c1 AND ft1.c2 = ft4.c1
24192419

24202420
RESET enable_nestloop;
24212421
RESET enable_hashjoin;
2422+
-- test that add_paths_with_pathkeys_for_rel() arranges for the epq_path to
2423+
-- return columns needed by the parent ForeignScan node
2424+
CREATE TABLE local_tbl (c1 int NOT NULL, c2 int NOT NULL, c3 text, CONSTRAINT local_tbl_pkey PRIMARY KEY (c1));
2425+
INSERT INTO local_tbl SELECT id, id % 10, to_char(id, 'FM0000') FROM generate_series(1, 1000) id;
2426+
ANALYZE local_tbl;
2427+
EXPLAIN (VERBOSE, COSTS OFF)
2428+
SELECT * FROM local_tbl LEFT JOIN (SELECT ft1.*, COALESCE(ft1.c3 || ft2.c3, 'foobar') FROM ft1 INNER JOIN ft2 ON (ft1.c1 = ft2.c1 AND ft1.c1 < 100)) ss ON (local_tbl.c1 = ss.c1) ORDER BY local_tbl.c1 FOR UPDATE OF local_tbl;
2429+
QUERY PLAN
2430+
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2431+
LockRows
2432+
Output: local_tbl.c1, local_tbl.c2, local_tbl.c3, ft1.c1, ft1.c2, ft1.c3, ft1.c4, ft1.c5, ft1.c6, ft1.c7, ft1.c8, (COALESCE((ft1.c3 || ft2.c3), 'foobar'::text)), local_tbl.c1, local_tbl.ctid, ft1.*, ft2.*
2433+
-> Merge Left Join
2434+
Output: local_tbl.c1, local_tbl.c2, local_tbl.c3, ft1.c1, ft1.c2, ft1.c3, ft1.c4, ft1.c5, ft1.c6, ft1.c7, ft1.c8, (COALESCE((ft1.c3 || ft2.c3), 'foobar'::text)), local_tbl.c1, local_tbl.ctid, ft1.*, ft2.*
2435+
Merge Cond: (local_tbl.c1 = ft1.c1)
2436+
-> Index Scan using local_tbl_pkey on public.local_tbl
2437+
Output: local_tbl.c1, local_tbl.c2, local_tbl.c3, local_tbl.ctid
2438+
-> Materialize
2439+
Output: ft1.c1, ft1.c2, ft1.c3, ft1.c4, ft1.c5, ft1.c6, ft1.c7, ft1.c8, ft1.*, ft2.*, (COALESCE((ft1.c3 || ft2.c3), 'foobar'::text))
2440+
-> Foreign Scan
2441+
Output: ft1.c1, ft1.c2, ft1.c3, ft1.c4, ft1.c5, ft1.c6, ft1.c7, ft1.c8, ft1.*, ft2.*, COALESCE((ft1.c3 || ft2.c3), 'foobar'::text)
2442+
Relations: (public.ft1) INNER JOIN (public.ft2)
2443+
Remote SQL: SELECT r4."C 1", r4.c2, r4.c3, r4.c4, r4.c5, r4.c6, r4.c7, r4.c8, CASE WHEN (r4.*)::text IS NOT NULL THEN ROW(r4."C 1", r4.c2, r4.c3, r4.c4, r4.c5, r4.c6, r4.c7, r4.c8) END, CASE WHEN (r5.*)::text IS NOT NULL THEN ROW(r5."C 1", r5.c2, r5.c3, r5.c4, r5.c5, r5.c6, r5.c7, r5.c8) END, r5.c3 FROM ("S 1"."T 1" r4 INNER JOIN "S 1"."T 1" r5 ON (((r4."C 1" = r5."C 1")) AND ((r4."C 1" < 100)))) ORDER BY r4."C 1" ASC NULLS LAST
2444+
-> Result
2445+
Output: ft1.c1, ft1.c2, ft1.c3, ft1.c4, ft1.c5, ft1.c6, ft1.c7, ft1.c8, ft1.*, ft2.*, ft2.c3
2446+
-> Sort
2447+
Output: ft1.c1, ft1.c2, ft1.c3, ft1.c4, ft1.c5, ft1.c6, ft1.c7, ft1.c8, ft1.*, ft2.*, (COALESCE((ft1.c3 || ft2.c3), 'foobar'::text)), ft2.c3
2448+
Sort Key: ft1.c1
2449+
-> Hash Join
2450+
Output: ft1.c1, ft1.c2, ft1.c3, ft1.c4, ft1.c5, ft1.c6, ft1.c7, ft1.c8, ft1.*, ft2.*, COALESCE((ft1.c3 || ft2.c3), 'foobar'::text), ft2.c3
2451+
Hash Cond: (ft1.c1 = ft2.c1)
2452+
-> Foreign Scan on public.ft1
2453+
Output: ft1.c1, ft1.c2, ft1.c3, ft1.c4, ft1.c5, ft1.c6, ft1.c7, ft1.c8, ft1.*
2454+
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" < 100))
2455+
-> Hash
2456+
Output: ft2.*, ft2.c1, ft2.c3
2457+
-> Foreign Scan on public.ft2
2458+
Output: ft2.*, ft2.c1, ft2.c3
2459+
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1"
2460+
(29 rows)
2461+
2462+
ALTER SERVER loopback OPTIONS (DROP extensions);
2463+
ALTER SERVER loopback OPTIONS (ADD fdw_startup_cost '10000.0');
2464+
EXPLAIN (VERBOSE, COSTS OFF)
2465+
SELECT * FROM local_tbl LEFT JOIN (SELECT ft1.* FROM ft1 INNER JOIN ft2 ON (ft1.c1 = ft2.c1 AND ft1.c1 < 100 AND ft1.c1 = postgres_fdw_abs(ft2.c2))) ss ON (local_tbl.c3 = ss.c3) ORDER BY local_tbl.c1 FOR UPDATE OF local_tbl;
2466+
QUERY PLAN
2467+
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2468+
LockRows
2469+
Output: local_tbl.c1, local_tbl.c2, local_tbl.c3, ft1.c1, ft1.c2, ft1.c3, ft1.c4, ft1.c5, ft1.c6, ft1.c7, ft1.c8, local_tbl.c1, local_tbl.ctid, ft1.*, ft2.*
2470+
-> Nested Loop Left Join
2471+
Output: local_tbl.c1, local_tbl.c2, local_tbl.c3, ft1.c1, ft1.c2, ft1.c3, ft1.c4, ft1.c5, ft1.c6, ft1.c7, ft1.c8, local_tbl.c1, local_tbl.ctid, ft1.*, ft2.*
2472+
Join Filter: (local_tbl.c3 = ft1.c3)
2473+
-> Index Scan using local_tbl_pkey on public.local_tbl
2474+
Output: local_tbl.c1, local_tbl.c2, local_tbl.c3, local_tbl.ctid
2475+
-> Materialize
2476+
Output: ft1.c1, ft1.c2, ft1.c3, ft1.c4, ft1.c5, ft1.c6, ft1.c7, ft1.c8, ft1.*, ft2.*
2477+
-> Foreign Scan
2478+
Output: ft1.c1, ft1.c2, ft1.c3, ft1.c4, ft1.c5, ft1.c6, ft1.c7, ft1.c8, ft1.*, ft2.*
2479+
Filter: (ft1.c1 = postgres_fdw_abs(ft2.c2))
2480+
Relations: (public.ft1) INNER JOIN (public.ft2)
2481+
Remote SQL: SELECT r4."C 1", r4.c2, r4.c3, r4.c4, r4.c5, r4.c6, r4.c7, r4.c8, CASE WHEN (r4.*)::text IS NOT NULL THEN ROW(r4."C 1", r4.c2, r4.c3, r4.c4, r4.c5, r4.c6, r4.c7, r4.c8) END, CASE WHEN (r5.*)::text IS NOT NULL THEN ROW(r5."C 1", r5.c2, r5.c3, r5.c4, r5.c5, r5.c6, r5.c7, r5.c8) END, r5.c2 FROM ("S 1"."T 1" r4 INNER JOIN "S 1"."T 1" r5 ON (((r4."C 1" = r5."C 1")) AND ((r4."C 1" < 100)))) ORDER BY r4.c3 ASC NULLS LAST
2482+
-> Sort
2483+
Output: ft1.c1, ft1.c2, ft1.c3, ft1.c4, ft1.c5, ft1.c6, ft1.c7, ft1.c8, ft1.*, ft2.*, ft2.c2
2484+
Sort Key: ft1.c3
2485+
-> Merge Join
2486+
Output: ft1.c1, ft1.c2, ft1.c3, ft1.c4, ft1.c5, ft1.c6, ft1.c7, ft1.c8, ft1.*, ft2.*, ft2.c2
2487+
Merge Cond: ((ft1.c1 = (postgres_fdw_abs(ft2.c2))) AND (ft1.c1 = ft2.c1))
2488+
-> Sort
2489+
Output: ft1.c1, ft1.c2, ft1.c3, ft1.c4, ft1.c5, ft1.c6, ft1.c7, ft1.c8, ft1.*
2490+
Sort Key: ft1.c1
2491+
-> Foreign Scan on public.ft1
2492+
Output: ft1.c1, ft1.c2, ft1.c3, ft1.c4, ft1.c5, ft1.c6, ft1.c7, ft1.c8, ft1.*
2493+
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" < 100))
2494+
-> Sort
2495+
Output: ft2.*, ft2.c1, ft2.c2, (postgres_fdw_abs(ft2.c2))
2496+
Sort Key: (postgres_fdw_abs(ft2.c2)), ft2.c1
2497+
-> Foreign Scan on public.ft2
2498+
Output: ft2.*, ft2.c1, ft2.c2, postgres_fdw_abs(ft2.c2)
2499+
Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" ORDER BY "C 1" ASC NULLS LAST
2500+
(32 rows)
2501+
2502+
ALTER SERVER loopback OPTIONS (DROP fdw_startup_cost);
2503+
ALTER SERVER loopback OPTIONS (ADD extensions 'postgres_fdw');
2504+
DROP TABLE local_tbl;
24222505
-- check join pushdown in situations where multiple userids are involved
24232506
CREATE ROLE regress_view_owner;
24242507
CREATE USER MAPPING FOR regress_view_owner SERVER loopback;

‎contrib/postgres_fdw/postgres_fdw.c

Lines changed: 49 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -4348,6 +4348,55 @@ add_paths_with_pathkeys_for_rel(PlannerInfo *root, RelOptInfo *rel,
43484348

43494349
useful_pathkeys_list=get_useful_pathkeys_for_relation(root,rel);
43504350

4351+
/*
4352+
* Before creating sorted paths, arrange for the passed-in EPQ path, if
4353+
* any, to return columns needed by the parent ForeignScan node so that
4354+
* they will propagate up through Sort nodes injected below, if necessary.
4355+
*/
4356+
if (epq_path!=NULL&&useful_pathkeys_list!=NIL)
4357+
{
4358+
PgFdwRelationInfo*fpinfo= (PgFdwRelationInfo*)rel->fdw_private;
4359+
PathTarget*target=copy_pathtarget(epq_path->pathtarget);
4360+
4361+
/* Include columns required for evaluating PHVs in the tlist. */
4362+
add_new_columns_to_pathtarget(target,
4363+
pull_var_clause((Node*)target->exprs,
4364+
PVC_RECURSE_PLACEHOLDERS));
4365+
4366+
/* Include columns required for evaluating the local conditions. */
4367+
foreach(lc,fpinfo->local_conds)
4368+
{
4369+
RestrictInfo*rinfo=lfirst_node(RestrictInfo,lc);
4370+
4371+
add_new_columns_to_pathtarget(target,
4372+
pull_var_clause((Node*)rinfo->clause,
4373+
PVC_RECURSE_PLACEHOLDERS));
4374+
}
4375+
4376+
/*
4377+
* If we have added any new columns, adjust the tlist of the EPQ path.
4378+
*
4379+
* Note: the plan created using this path will only be used to execute
4380+
* EPQ checks, where accuracy of the plan cost and width estimates
4381+
* would not be important, so we do not do set_pathtarget_cost_width()
4382+
* for the new pathtarget here. See also postgresGetForeignPlan().
4383+
*/
4384+
if (list_length(target->exprs)>list_length(epq_path->pathtarget->exprs))
4385+
{
4386+
/* The EPQ path is a join path, so it is projection-capable. */
4387+
Assert(is_projection_capable_path(epq_path));
4388+
4389+
/*
4390+
* Use create_projection_path() here, so as to avoid modifying it
4391+
* in place.
4392+
*/
4393+
epq_path= (Path*)create_projection_path(root,
4394+
rel,
4395+
epq_path,
4396+
target);
4397+
}
4398+
}
4399+
43514400
/* Create one path for each set of pathkeys we found above. */
43524401
foreach(lc,useful_pathkeys_list)
43534402
{

‎contrib/postgres_fdw/sql/postgres_fdw.sql

Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -580,6 +580,24 @@ SELECT * FROM ft1, ft2, ft4, ft5 WHERE ft1.c1 = ft2.c1 AND ft1.c2 = ft4.c1
580580
RESET enable_nestloop;
581581
RESET enable_hashjoin;
582582

583+
-- test that add_paths_with_pathkeys_for_rel() arranges for the epq_path to
584+
-- return columns needed by the parent ForeignScan node
585+
CREATETABLElocal_tbl (c1intNOT NULL, c2intNOT NULL, c3text,CONSTRAINT local_tbl_pkeyPRIMARY KEY (c1));
586+
INSERT INTO local_tblSELECT id, id %10, to_char(id,'FM0000')FROM generate_series(1,1000) id;
587+
ANALYZE local_tbl;
588+
589+
EXPLAIN (VERBOSE, COSTS OFF)
590+
SELECT*FROM local_tblLEFT JOIN (SELECT ft1.*, COALESCE(ft1.c3||ft2.c3,'foobar')FROM ft1INNER JOIN ft2ON (ft1.c1=ft2.c1ANDft1.c1<100)) ssON (local_tbl.c1=ss.c1)ORDER BYlocal_tbl.c1 FORUPDATE OF local_tbl;
591+
592+
ALTER SERVER loopback OPTIONS (DROP extensions);
593+
ALTER SERVER loopback OPTIONS (ADD fdw_startup_cost'10000.0');
594+
EXPLAIN (VERBOSE, COSTS OFF)
595+
SELECT*FROM local_tblLEFT JOIN (SELECT ft1.*FROM ft1INNER JOIN ft2ON (ft1.c1=ft2.c1ANDft1.c1<100ANDft1.c1= postgres_fdw_abs(ft2.c2))) ssON (local_tbl.c3=ss.c3)ORDER BYlocal_tbl.c1 FORUPDATE OF local_tbl;
596+
ALTER SERVER loopback OPTIONS (DROP fdw_startup_cost);
597+
ALTER SERVER loopback OPTIONS (ADD extensions'postgres_fdw');
598+
599+
DROPTABLE local_tbl;
600+
583601
-- check join pushdown in situations where multiple userids are involved
584602
CREATE ROLE regress_view_owner;
585603
CREATEUSERMAPPING FOR regress_view_owner SERVER loopback;

0 commit comments

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