From: Alexander Pyhalov <a.pyhalov@postgrespro.ru>
To: Richard Guo <guofenglinux@gmail.com>
Cc: Etsuro Fujita <etsuro.fujita@gmail.com>
Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Subject: Re: foreign join error "variable not found in subplan target list"
Date: Tue, 09 Aug 2022 14:54:14 +0300
Message-ID: <fcc74118ee33ca5e213fa855ba70ff22@postgrespro.ru> (raw)
In-Reply-To: <CAMbWs4_k4yFmOJ+U-Ap7BvQTbdUYhKmVchV=yJFHdtw_10nQZg@mail.gmail.com>
References: <cfb17bf6dfdf876467bd5ef533852d18@postgrespro.ru>
<CAPmGK16mosKq6h=TbQP8wKJu4HFdCBjrxAV+TLYfovuNmfNhHA@mail.gmail.com>
<CAMbWs4_k4yFmOJ+U-Ap7BvQTbdUYhKmVchV=yJFHdtw_10nQZg@mail.gmail.com>
Richard Guo писал 2022-08-09 06:51:
> On Tue, Aug 9, 2022 at 9:59 AM Etsuro Fujita <etsuro.fujita@gmail.com>
> wrote:
>
>> On Tue, Aug 9, 2022 at 12:26 AM Alexander Pyhalov
>> <a.pyhalov@postgrespro.ru> wrote:
>>> Using the following patch I'm able to get
>>> "ERROR: variable not found in subplan target list"
>>> on foreign join pushdown for update returning.
>>
>> Reproduced here. Will look into this.
>>
>> Thanks for the report!
>
> A rough look shows to me that the part of plan that causes problem
> looks
> like:
>
> -> Result
> Output: ft2.ctid, ft2.*, ft4.*, ft4.c1, ft2.c2
> -> Sort
> Output: ft2.ctid, ft2.*, ft4.*, ft4.c1
> Sort Key: ft4.c1
>
> Note that for node 'Result', one of its target entries, 'ft2.c2', does
> not appear in the target list of its subplan, i.e. node 'Sort'.
>
> I think something goes wrong in postgresGetForeignPlan() when we build
> the list of columns to be fetched from the foreign server, and fix the
> subplan's tlist with that, where we would include in columns specified
> in local conditions. In this case the local condition is 'ft2.c2 ===
> ft4.c1', and that's how we have entry 'ft2.c2' in Result's target
> list.
>
> Thanks
> Richard
Hi.
The issue seems to appear due to the fact that Sort path doesn't provide
all vars, needed by local_conds.
What if we check for this condition specifically?
--
Best regards,
Alexander Pyhalov,
Postgres Professional
Attachments:
[text/x-diff] 0001-postgres_fdw-should-avoid-sorted-paths-which-don-t-p.patch (2.6K, ../fcc74118ee33ca5e213fa855ba70ff22@postgrespro.ru/2-0001-postgres_fdw-should-avoid-sorted-paths-which-don-t-p.patch)
download | inline diff:
From e0cf7be1682feb8dbca80adefbaac06ef8315ba4 Mon Sep 17 00:00:00 2001
From: Alexander Pyhalov <a.pyhalov@postgrespro.ru>
Date: Tue, 9 Aug 2022 14:47:16 +0300
Subject: [PATCH] postgres_fdw should avoid sorted paths which don't provide
all required vars
---
contrib/postgres_fdw/postgres_fdw.c | 56 +++++++++++++++++++++++++++++
1 file changed, 56 insertions(+)
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.cindex 16320170cee..e21ddd7485f 100644--- a/contrib/postgres_fdw/postgres_fdw.c+++ b/contrib/postgres_fdw/postgres_fdw.c@@ -541,6 +541,7 @@ static void merge_fdw_options(PgFdwRelationInfo *fpinfo,
const PgFdwRelationInfo *fpinfo_o,
const PgFdwRelationInfo *fpinfo_i);
static int get_batch_size_option(Relation rel);
+static bool path_provides_local_vars(Path *path, PgFdwRelationInfo *fpinfo);
/*
@@ -5775,6 +5776,54 @@ foreign_join_ok(PlannerInfo *root, RelOptInfo *joinrel, JoinType jointype,
return true;
}
+/*+ * Determines if all vars, used by fpinfo->local_conds,+ * are provided by path.+ */+static bool+path_provides_local_vars(Path *path, PgFdwRelationInfo *fpinfo)+{+ List *vars_provided;+ ListCell *lc1;+ ListCell *lc2;+ ListCell *lc3;++ if (path == NULL)+ return false;++ /* Get all vars, provided by path */+ vars_provided = pull_var_clause((Node *) path->pathtarget->exprs, PVC_INCLUDE_PLACEHOLDERS);++ foreach(lc1, fpinfo->local_conds)+ {+ RestrictInfo *ri = (RestrictInfo *) lfirst(lc1);+ List *vars_used;++ /* Get vars, used by this restriction clause */+ vars_used = pull_var_clause((Node *) ri->clause, PVC_INCLUDE_PLACEHOLDERS);+ foreach(lc2, vars_used)+ {+ Var *used = lfirst(lc2);+ bool found = false;++ foreach(lc3, vars_provided)+ {+ Var *provided = lfirst(lc3);++ if (provided->varno == used->varno && provided->varattno == used->varattno)+ {+ found = true;+ break;+ }+ }+ if (!found)+ return false;+ }+ }++ return true;+}+
static void
add_paths_with_pathkeys_for_rel(PlannerInfo *root, RelOptInfo *rel,
Path *epq_path)
@@ -5812,6 +5861,13 @@ add_paths_with_pathkeys_for_rel(PlannerInfo *root, RelOptInfo *rel,
useful_pathkeys,
-1.0);
+ /*+ * Sorted path should provide all vars needed by local conditions or+ * we'll not be able to use it+ */+ if (sorted_epq_path != NULL && !path_provides_local_vars(sorted_epq_path, (PgFdwRelationInfo *) rel->fdw_private))+ return;+
if (IS_SIMPLE_REL(rel))
add_path(rel, (Path *)
create_foreignscan_path(root, rel,
--
2.34.1
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: a.pyhalov@postgrespro.ru, guofenglinux@gmail.com, etsuro.fujita@gmail.com, pgsql-bugs@lists.postgresql.org
Subject: Re: foreign join error "variable not found in subplan target list"
In-Reply-To: <fcc74118ee33ca5e213fa855ba70ff22@postgrespro.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox