pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedforeign join error "variable not found in subplan target list"
23+ messages / 4 participants
[nested] [flat]
* foreign join error "variable not found in subplan target list"
@ 2022-08-08 15:26 Alexander Pyhalov <a.pyhalov@postgrespro.ru>
2022-08-09 01:59 ` Re: foreign join error "variable not found in subplan target list" Etsuro Fujita <etsuro.fujita@gmail.com>
0 siblings, 1 reply; 23+ messages in thread
From: Alexander Pyhalov @ 2022-08-08 15:26 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
Hi.
Using the following patch I'm able to get
"ERROR: variable not found in subplan target list"
on foreign join pushdown for update returning.
It seems that we generate Result node which has 7 vars in targetlist
over Sort plan node, which has only 6 vars in targetlist.
So far not sure what causes this.
--
Best regards,
Alexander Pyhalov,
Postgres Professional
Attachments:
[text/x-diff] costs_tweaks.diff (1.1K, ../../cfb17bf6dfdf876467bd5ef533852d18@postgrespro.ru/2-costs_tweaks.diff)
download | inline diff:
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 42735ae78a9..5eb387ef14f 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -1355,6 +1355,9 @@ INSERT INTO ft2 (c1,c2,c3)
EXPLAIN (verbose, costs off)
UPDATE ft2 SET c3 = 'bar' WHERE postgres_fdw_abs(c1) > 2000 RETURNING *; -- can't be pushed down
UPDATE ft2 SET c3 = 'bar' WHERE postgres_fdw_abs(c1) > 2000 RETURNING *;
+ALTER SERVER loopback OPTIONS (add fdw_startup_cost '10000.0');
+ALTER SERVER loopback OPTIONS (add fdw_tuple_cost '1.0');
+SET enable_nestloop TO false;
EXPLAIN (verbose, costs off)
UPDATE ft2 SET c3 = 'baz'
FROM ft4 INNER JOIN ft5 ON (ft4.c1 = ft5.c1)
@@ -1364,6 +1367,9 @@ UPDATE ft2 SET c3 = 'baz'
FROM ft4 INNER JOIN ft5 ON (ft4.c1 = ft5.c1)
WHERE ft2.c1 > 2000 AND ft2.c2 === ft4.c1
RETURNING ft2.*, ft4.*, ft5.*;
+RESET enable_nestloop;
+ALTER SERVER loopback OPTIONS (drop fdw_startup_cost);
+ALTER SERVER loopback OPTIONS (drop fdw_tuple_cost);
EXPLAIN (verbose, costs off)
DELETE FROM ft2
USING ft4 INNER JOIN ft5 ON (ft4.c1 === ft5.c1)
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: foreign join error "variable not found in subplan target list"
2022-08-08 15:26 foreign join error "variable not found in subplan target list" Alexander Pyhalov <a.pyhalov@postgrespro.ru>
@ 2022-08-09 01:59 ` Etsuro Fujita <etsuro.fujita@gmail.com>
2022-08-09 03:51 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
0 siblings, 1 reply; 23+ messages in thread
From: Etsuro Fujita @ 2022-08-09 01:59 UTC (permalink / raw)
To: Alexander Pyhalov <a.pyhalov@postgrespro.ru>; +Cc: pgsql-bugs@lists.postgresql.org
Hi Alexander,
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!
Best regards,
Etsuro Fujita
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: foreign join error "variable not found in subplan target list"
2022-08-08 15:26 foreign join error "variable not found in subplan target list" Alexander Pyhalov <a.pyhalov@postgrespro.ru>
2022-08-09 01:59 ` Re: foreign join error "variable not found in subplan target list" Etsuro Fujita <etsuro.fujita@gmail.com>
@ 2022-08-09 03:51 ` Richard Guo <guofenglinux@gmail.com>
2022-08-09 11:54 ` Re: foreign join error "variable not found in subplan target list" Alexander Pyhalov <a.pyhalov@postgrespro.ru>
0 siblings, 1 reply; 23+ messages in thread
From: Richard Guo @ 2022-08-09 03:51 UTC (permalink / raw)
To: Etsuro Fujita <etsuro.fujita@gmail.com>; +Cc: Alexander Pyhalov <a.pyhalov@postgrespro.ru>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: foreign join error "variable not found in subplan target list"
2022-08-08 15:26 foreign join error "variable not found in subplan target list" Alexander Pyhalov <a.pyhalov@postgrespro.ru>
2022-08-09 01:59 ` Re: foreign join error "variable not found in subplan target list" Etsuro Fujita <etsuro.fujita@gmail.com>
2022-08-09 03:51 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
@ 2022-08-09 11:54 ` Alexander Pyhalov <a.pyhalov@postgrespro.ru>
2022-08-10 02:15 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
0 siblings, 1 reply; 23+ messages in thread
From: Alexander Pyhalov @ 2022-08-09 11:54 UTC (permalink / raw)
To: Richard Guo <guofenglinux@gmail.com>; +Cc: Etsuro Fujita <etsuro.fujita@gmail.com>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
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.c
index 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
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: foreign join error "variable not found in subplan target list"
2022-08-08 15:26 foreign join error "variable not found in subplan target list" Alexander Pyhalov <a.pyhalov@postgrespro.ru>
2022-08-09 01:59 ` Re: foreign join error "variable not found in subplan target list" Etsuro Fujita <etsuro.fujita@gmail.com>
2022-08-09 03:51 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
2022-08-09 11:54 ` Re: foreign join error "variable not found in subplan target list" Alexander Pyhalov <a.pyhalov@postgrespro.ru>
@ 2022-08-10 02:15 ` Richard Guo <guofenglinux@gmail.com>
2022-08-10 05:28 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
0 siblings, 1 reply; 23+ messages in thread
From: Richard Guo @ 2022-08-10 02:15 UTC (permalink / raw)
To: Alexander Pyhalov <a.pyhalov@postgrespro.ru>; +Cc: Etsuro Fujita <etsuro.fujita@gmail.com>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Tue, Aug 9, 2022 at 7:54 PM Alexander Pyhalov <a.pyhalov@postgrespro.ru>
wrote:
> 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?
I think this change may make us miss some EPQ path with pathkeys, which
seems not good. Also, Sort path would always have the same pathtarget as
its subpath. So if we really want to do this, we can do it earlier in
the caller of add_paths_with_pathkeys_for_rel.
Currently the outer_plan used in postgresGetForeignPlan() can only be
'Join' or 'Sort + Join'. I'm wondering whether we can take this
knowledge into consideration when we fix the outer_plan's tlist, to also
fix the Join's tlist if it is below the Sort node.
Thanks
Richard
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: foreign join error "variable not found in subplan target list"
2022-08-08 15:26 foreign join error "variable not found in subplan target list" Alexander Pyhalov <a.pyhalov@postgrespro.ru>
2022-08-09 01:59 ` Re: foreign join error "variable not found in subplan target list" Etsuro Fujita <etsuro.fujita@gmail.com>
2022-08-09 03:51 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
2022-08-09 11:54 ` Re: foreign join error "variable not found in subplan target list" Alexander Pyhalov <a.pyhalov@postgrespro.ru>
2022-08-10 02:15 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
@ 2022-08-10 05:28 ` Richard Guo <guofenglinux@gmail.com>
2022-08-10 07:06 ` Re: foreign join error "variable not found in subplan target list" Alexander Pyhalov <a.pyhalov@postgrespro.ru>
2022-08-21 12:31 ` Re: foreign join error "variable not found in subplan target list" Etsuro Fujita <etsuro.fujita@gmail.com>
0 siblings, 2 replies; 23+ messages in thread
From: Richard Guo @ 2022-08-10 05:28 UTC (permalink / raw)
To: Alexander Pyhalov <a.pyhalov@postgrespro.ru>; +Cc: Etsuro Fujita <etsuro.fujita@gmail.com>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Wed, Aug 10, 2022 at 10:15 AM Richard Guo <guofenglinux@gmail.com> wrote:
> Currently the outer_plan used in postgresGetForeignPlan() can only be
> 'Join' or 'Sort + Join'. I'm wondering whether we can take this
> knowledge into consideration when we fix the outer_plan's tlist, to also
> fix the Join's tlist if it is below the Sort node.
>
Alternatively, how about we include in the EPQ path's pathtarget the
columns required for evaluating the local conditions when we consider
EPQ paths with pathkeys? Something like attached.
Thanks
Richard
Attachments:
[application/octet-stream] v1-0001-Include-local-conds-in-epq-pathtarget.patch (1.3K, ../../CAMbWs49bW4i06Daf8eb1+tsbJVymA4U-3v6rRoNvqKaJSm459Q@mail.gmail.com/3-v1-0001-Include-local-conds-in-epq-pathtarget.patch)
download | inline diff:
From 9a360fb23a30a7b79076dac8800963c0197f239a Mon Sep 17 00:00:00 2001
From: Richard Guo <guofenglinux@gmail.com>
Date: Wed, 10 Aug 2022 11:28:57 +0800
Subject: [PATCH v1] Include local conds in epq pathtarget
---
contrib/postgres_fdw/postgres_fdw.c | 14 ++++++++++++++
1 file changed, 14 insertions(+)
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index 16320170ce..4f16cfc7a1 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -5779,11 +5779,25 @@ static void
add_paths_with_pathkeys_for_rel(PlannerInfo *root, RelOptInfo *rel,
Path *epq_path)
{
+ PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) rel->fdw_private;
List *useful_pathkeys_list = NIL; /* List of all pathkeys */
ListCell *lc;
useful_pathkeys_list = get_useful_pathkeys_for_relation(root, rel);
+ if (epq_path != NULL && useful_pathkeys_list != NIL)
+ {
+ /* Include columns required for evaluating the local conditions */
+ foreach(lc, fpinfo->local_conds)
+ {
+ RestrictInfo *rinfo = lfirst_node(RestrictInfo, lc);
+
+ add_new_columns_to_pathtarget(epq_path->pathtarget,
+ pull_var_clause((Node *) rinfo->clause,
+ PVC_RECURSE_PLACEHOLDERS));
+ }
+ }
+
/* Create one path for each set of pathkeys we found above. */
foreach(lc, useful_pathkeys_list)
{
--
2.31.0
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: foreign join error "variable not found in subplan target list"
2022-08-08 15:26 foreign join error "variable not found in subplan target list" Alexander Pyhalov <a.pyhalov@postgrespro.ru>
2022-08-09 01:59 ` Re: foreign join error "variable not found in subplan target list" Etsuro Fujita <etsuro.fujita@gmail.com>
2022-08-09 03:51 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
2022-08-09 11:54 ` Re: foreign join error "variable not found in subplan target list" Alexander Pyhalov <a.pyhalov@postgrespro.ru>
2022-08-10 02:15 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
2022-08-10 05:28 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
@ 2022-08-10 07:06 ` Alexander Pyhalov <a.pyhalov@postgrespro.ru>
2022-08-10 08:36 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
1 sibling, 1 reply; 23+ messages in thread
From: Alexander Pyhalov @ 2022-08-10 07:06 UTC (permalink / raw)
To: Richard Guo <guofenglinux@gmail.com>; +Cc: Etsuro Fujita <etsuro.fujita@gmail.com>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Richard Guo писал 2022-08-10 08:28:
> On Wed, Aug 10, 2022 at 10:15 AM Richard Guo <guofenglinux@gmail.com>
> wrote:
>
>> Currently the outer_plan used in postgresGetForeignPlan() can only
>> be
>> 'Join' or 'Sort + Join'. I'm wondering whether we can take this
>> knowledge into consideration when we fix the outer_plan's tlist, to
>> also
>> fix the Join's tlist if it is below the Sort node.
>
> Alternatively, how about we include in the EPQ path's pathtarget
> thecolumns required for evaluating the local conditions when we
> consider
> EPQ paths with pathkeys? Something like attached.
>
> Thanks
> Richard
Hi.
Why are we sure that epq_path can provide all vars from restrictinfo?
--
Best regards,
Alexander Pyhalov,
Postgres Professional
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: foreign join error "variable not found in subplan target list"
2022-08-08 15:26 foreign join error "variable not found in subplan target list" Alexander Pyhalov <a.pyhalov@postgrespro.ru>
2022-08-09 01:59 ` Re: foreign join error "variable not found in subplan target list" Etsuro Fujita <etsuro.fujita@gmail.com>
2022-08-09 03:51 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
2022-08-09 11:54 ` Re: foreign join error "variable not found in subplan target list" Alexander Pyhalov <a.pyhalov@postgrespro.ru>
2022-08-10 02:15 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
2022-08-10 05:28 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
2022-08-10 07:06 ` Re: foreign join error "variable not found in subplan target list" Alexander Pyhalov <a.pyhalov@postgrespro.ru>
@ 2022-08-10 08:36 ` Richard Guo <guofenglinux@gmail.com>
2022-08-10 15:16 ` Re: foreign join error "variable not found in subplan target list" Alexander Pyhalov <a.pyhalov@postgrespro.ru>
0 siblings, 1 reply; 23+ messages in thread
From: Richard Guo @ 2022-08-10 08:36 UTC (permalink / raw)
To: Alexander Pyhalov <a.pyhalov@postgrespro.ru>; +Cc: Etsuro Fujita <etsuro.fujita@gmail.com>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Wed, Aug 10, 2022 at 3:06 PM Alexander Pyhalov <a.pyhalov@postgrespro.ru>
wrote:
> Richard Guo писал 2022-08-10 08:28:
> > On Wed, Aug 10, 2022 at 10:15 AM Richard Guo <guofenglinux@gmail.com>
> > wrote:
> >
> >> Currently the outer_plan used in postgresGetForeignPlan() can only
> >> be
> >> 'Join' or 'Sort + Join'. I'm wondering whether we can take this
> >> knowledge into consideration when we fix the outer_plan's tlist, to
> >> also
> >> fix the Join's tlist if it is below the Sort node.
> >
> > Alternatively, how about we include in the EPQ path's pathtarget
> > thecolumns required for evaluating the local conditions when we
> > consider
> > EPQ paths with pathkeys? Something like attached.
> >
> > Thanks
> > Richard
>
> Hi.
> Why are we sure that epq_path can provide all vars from restrictinfo?
The local conditions come from the joinrel's restrictlist, which
contains all the clauses that syntactically belong at the join level. So
I think the join path for EPQ checks should be able to provide all the
exprs needed by local_conds.
Thanks
Richard
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: foreign join error "variable not found in subplan target list"
2022-08-08 15:26 foreign join error "variable not found in subplan target list" Alexander Pyhalov <a.pyhalov@postgrespro.ru>
2022-08-09 01:59 ` Re: foreign join error "variable not found in subplan target list" Etsuro Fujita <etsuro.fujita@gmail.com>
2022-08-09 03:51 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
2022-08-09 11:54 ` Re: foreign join error "variable not found in subplan target list" Alexander Pyhalov <a.pyhalov@postgrespro.ru>
2022-08-10 02:15 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
2022-08-10 05:28 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
2022-08-10 07:06 ` Re: foreign join error "variable not found in subplan target list" Alexander Pyhalov <a.pyhalov@postgrespro.ru>
2022-08-10 08:36 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
@ 2022-08-10 15:16 ` Alexander Pyhalov <a.pyhalov@postgrespro.ru>
2022-08-12 03:09 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
0 siblings, 1 reply; 23+ messages in thread
From: Alexander Pyhalov @ 2022-08-10 15:16 UTC (permalink / raw)
To: Richard Guo <guofenglinux@gmail.com>; +Cc: Etsuro Fujita <etsuro.fujita@gmail.com>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Richard Guo писал 2022-08-10 11:36:
> On Wed, Aug 10, 2022 at 3:06 PM Alexander Pyhalov
> <a.pyhalov@postgrespro.ru> wrote:
>
>> Richard Guo писал 2022-08-10 08:28:
>>> On Wed, Aug 10, 2022 at 10:15 AM Richard Guo
>> <guofenglinux@gmail.com>
>>> wrote:
>>>
>>>> Currently the outer_plan used in postgresGetForeignPlan() can
>> only
>>>> be
>>>> 'Join' or 'Sort + Join'. I'm wondering whether we can take this
>>>> knowledge into consideration when we fix the outer_plan's tlist,
>> to
>>>> also
>>>> fix the Join's tlist if it is below the Sort node.
>>>
>>> Alternatively, how about we include in the EPQ path's pathtarget
>>> thecolumns required for evaluating the local conditions when we
>>> consider
>>> EPQ paths with pathkeys? Something like attached.
>>>
>>> Thanks
>>> Richard
>>
>> Hi.
>> Why are we sure that epq_path can provide all vars from
>> restrictinfo?
>
> The local conditions come from the joinrel's restrictlist, which
> contains all the clauses that syntactically belong at the join level.
> So
> I think the join path for EPQ checks should be able to provide all the
> exprs needed by local_conds.
>
> Thanks
> Richard
OK. It looks good to me. The only thing which surprised me that in test
case we see unnecessary sort in remote query. However, it's explained by
selected costs and STD_FUZZ_FACTOR, so that sorted path has essentially
the same cost as non-sorted one according to
compare_path_costs_fuzzily().
EXPLAIN (verbose, costs off)
UPDATE ft2 SET c3 = 'baz'
FROM ft4 INNER JOIN ft5 ON (ft4.c1 = ft5.c1)
WHERE ft2.c1 > 2000 AND ft2.c2 === ft4.c1
RETURNING ft2.*, ft4.*, ft5.*;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Update on public.ft2
Output: ft2.c1, ft2.c2, ft2.c3, ft2.c4, ft2.c5, ft2.c6, ft2.c7,
ft2.c8, ft4.c1, ft4.c2, ft4.c3, ft5.c1, ft5.c2, ft5.c3
Remote SQL: UPDATE "S 1"."T 1" SET c3 = $2 WHERE ctid = $1 RETURNING
"C 1", c2, c3, c4, c5, c6, c7, c8
-> Hash Join
Output: 'baz'::text, ft2.ctid, ft2.*, ft4.*, ft5.*, ft4.c1,
ft4.c2, ft4.c3, ft5.c1, ft5.c2, ft5.c3
Hash Cond: (ft5.c1 = ft4.c1)
-> Foreign Scan on public.ft5
Output: ft5.*, ft5.c1, ft5.c2, ft5.c3
Remote SQL: SELECT c1, c2, c3 FROM "S 1"."T 4"
-> Hash
Output: ft2.ctid, ft2.*, ft4.*, ft4.c1, ft4.c2, ft4.c3,
ft2.c2
-> Foreign Scan
Output: ft2.ctid, ft2.*, ft4.*, ft4.c1, ft4.c2,
ft4.c3, ft2.c2
Filter: (ft2.c2 === ft4.c1)
Relations: (public.ft2) INNER JOIN (public.ft4)
Remote SQL: SELECT r1.ctid, CASE WHEN (r1.*)::text
IS NOT NULL THEN ROW(r1."C 1", r1.c2, r1.c3, r1.c4, r1.c5, r1.c6, r1.c7,
r1.c8) END, CASE WHEN (r2.*)::text IS NOT NULL THEN ROW(r2.c1, r2.c2,
r2.c3) END, r2.c1, r2.c2, r2.c3, r1.c2 FROM ("S 1"."T 1" r1 INNER JOIN
"S 1"."T 3" r2 ON (((r1."C 1" > 2000)))) ORDER BY r2.c1 ASC NULLS LAST
FOR UPDATE OF r1
-> Sort
Output: ft2.ctid, ft2.*, ft4.*, ft4.c1,
ft4.c2, ft4.c3, ft2.c2
Sort Key: ft4.c1
-> Nested Loop
Output: ft2.ctid, ft2.*, ft4.*, ft4.c1,
ft4.c2, ft4.c3, ft2.c2
Join Filter: (ft2.c2 === ft4.c1)
-> Foreign Scan on public.ft2
Output: ft2.ctid, ft2.*, ft2.c2
Remote SQL: SELECT "C 1", c2, c3,
c4, c5, c6, c7, c8, ctid FROM "S 1"."T 1" WHERE (("C 1" > 2000)) FOR
UPDATE
-> Foreign Scan on public.ft4
Output: ft4.*, ft4.c1, ft4.c2,
ft4.c3
Remote SQL: SELECT c1, c2, c3
FROM "S 1"."T 3"
--
Best regards,
Alexander Pyhalov,
Postgres Professional
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: foreign join error "variable not found in subplan target list"
2022-08-08 15:26 foreign join error "variable not found in subplan target list" Alexander Pyhalov <a.pyhalov@postgrespro.ru>
2022-08-09 01:59 ` Re: foreign join error "variable not found in subplan target list" Etsuro Fujita <etsuro.fujita@gmail.com>
2022-08-09 03:51 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
2022-08-09 11:54 ` Re: foreign join error "variable not found in subplan target list" Alexander Pyhalov <a.pyhalov@postgrespro.ru>
2022-08-10 02:15 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
2022-08-10 05:28 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
2022-08-10 07:06 ` Re: foreign join error "variable not found in subplan target list" Alexander Pyhalov <a.pyhalov@postgrespro.ru>
2022-08-10 08:36 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
2022-08-10 15:16 ` Re: foreign join error "variable not found in subplan target list" Alexander Pyhalov <a.pyhalov@postgrespro.ru>
@ 2022-08-12 03:09 ` Richard Guo <guofenglinux@gmail.com>
0 siblings, 0 replies; 23+ messages in thread
From: Richard Guo @ 2022-08-12 03:09 UTC (permalink / raw)
To: Alexander Pyhalov <a.pyhalov@postgrespro.ru>; +Cc: Etsuro Fujita <etsuro.fujita@gmail.com>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Wed, Aug 10, 2022 at 11:17 PM Alexander Pyhalov <a.pyhalov@postgrespro.ru>
wrote:
> OK. It looks good to me. The only thing which surprised me that in test
> case we see unnecessary sort in remote query. However, it's explained by
> selected costs and STD_FUZZ_FACTOR, so that sorted path has essentially
> the same cost as non-sorted one according to
> compare_path_costs_fuzzily().
Yeah, and meanwhile the sorted path is considered to have better
pathkeys than the non-sorted one. So it wins in add_path().
Thanks
Richard
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: foreign join error "variable not found in subplan target list"
2022-08-08 15:26 foreign join error "variable not found in subplan target list" Alexander Pyhalov <a.pyhalov@postgrespro.ru>
2022-08-09 01:59 ` Re: foreign join error "variable not found in subplan target list" Etsuro Fujita <etsuro.fujita@gmail.com>
2022-08-09 03:51 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
2022-08-09 11:54 ` Re: foreign join error "variable not found in subplan target list" Alexander Pyhalov <a.pyhalov@postgrespro.ru>
2022-08-10 02:15 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
2022-08-10 05:28 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
@ 2022-08-21 12:31 ` Etsuro Fujita <etsuro.fujita@gmail.com>
2022-08-22 03:29 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
1 sibling, 1 reply; 23+ messages in thread
From: Etsuro Fujita @ 2022-08-21 12:31 UTC (permalink / raw)
To: Richard Guo <guofenglinux@gmail.com>; +Cc: Alexander Pyhalov <a.pyhalov@postgrespro.ru>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Hi Richard,
On Wed, Aug 10, 2022 at 2:28 PM Richard Guo <guofenglinux@gmail.com> wrote:
> On Wed, Aug 10, 2022 at 10:15 AM Richard Guo <guofenglinux@gmail.com> wrote:
>> Currently the outer_plan used in postgresGetForeignPlan() can only be
>> 'Join' or 'Sort + Join'. I'm wondering whether we can take this
>> knowledge into consideration when we fix the outer_plan's tlist, to also
>> fix the Join's tlist if it is below the Sort node.
> Alternatively, how about we include in the EPQ path's pathtarget the
> columns required for evaluating the local conditions when we consider
> EPQ paths with pathkeys? Something like attached.
Thanks for the patch! I reviewed the patch. I think the patch goes
in the right direction.
+ if (epq_path != NULL && useful_pathkeys_list != NIL)
+ {
+ /* Include columns required for evaluating the local conditions */
+ foreach(lc, fpinfo->local_conds)
+ {
+ RestrictInfo *rinfo = lfirst_node(RestrictInfo, lc);
+
+ add_new_columns_to_pathtarget(epq_path->pathtarget,
+ pull_var_clause((Node *)
rinfo->clause,
+ PVC_RECURSE_PLACEHOLDERS));
+ }
+ }
* I think we should avoid modifying the pathtarget, because it would
be the default pathtarget, which other paths might reference. I think
it’s safe to use a copied pathtarget, like the attached.
* I think this issue occurs also when there are PlaceHolderVars in the
relation’s reltarget. Here is an example:
EXPLAIN (VERBOSE, COSTS OFF)
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;
ERROR: variable not found in subplan target list
where local_tbl, ft1, and ft2 are local/foreign tables defined as in
postgres_fdw.sql. To fix, I modified the patch so that we add to the
pathtarget not only columns required for evaluating the local
conditions but columns required for evaluating the PlaceHoderVars.
* The test case reported in this thread produces the planner error
only in v14 and later, but I think this issue exists since v9.6. So I
created/added new test cases, including the above one, that would
otherwise produce the error even in previous versions.
Best regards,
Etsuro Fujita
Attachments:
[application/octet-stream] v1-0001-Include-local-conds-in-epq-pathtarget-efujita.patch (11.7K, ../../CAPmGK15Ay2BPYzyyCowP8bpNBDucnpLDGoMLEHvp2b7Z4EtZDA@mail.gmail.com/2-v1-0001-Include-local-conds-in-epq-pathtarget-efujita.patch)
download | inline diff:
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 7bf35602b0..a368f75e77 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -2474,6 +2474,85 @@ SELECT * FROM ft1, ft2, ft4, ft5, local_tbl WHERE ft1.c1 = ft2.c1 AND ft1.c2 = f
RESET enable_nestloop;
RESET enable_hashjoin;
+-- test that add_paths_with_pathkeys_for_rel() adjusts the epq_path's
+-- pathtarget properly
+EXPLAIN (VERBOSE, COSTS OFF)
+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;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ LockRows
+ 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.ctid, ft1.*, ft2.*
+ -> Merge Left Join
+ 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.ctid, ft1.*, ft2.*
+ Merge Cond: (local_tbl.c1 = ft1.c1)
+ -> Index Scan using local_tbl_pkey on public.local_tbl
+ Output: local_tbl.c1, local_tbl.c2, local_tbl.c3, local_tbl.ctid
+ -> Materialize
+ 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))
+ -> Foreign Scan
+ 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)
+ Relations: (public.ft1) INNER JOIN (public.ft2)
+ 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
+ -> Result
+ Output: ft1.c1, ft1.c2, ft1.c3, ft1.c4, ft1.c5, ft1.c6, ft1.c7, ft1.c8, ft1.*, ft2.*, ft2.c3
+ -> Sort
+ 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
+ Sort Key: ft1.c1
+ -> Hash Join
+ 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
+ Hash Cond: (ft1.c1 = ft2.c1)
+ -> Foreign Scan on public.ft1
+ Output: ft1.c1, ft1.c2, ft1.c3, ft1.c4, ft1.c5, ft1.c6, ft1.c7, ft1.c8, ft1.*
+ Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" < 100))
+ -> Hash
+ Output: ft2.*, ft2.c1, ft2.c3
+ -> Foreign Scan on public.ft2
+ Output: ft2.*, ft2.c1, ft2.c3
+ Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1"
+(29 rows)
+
+ALTER SERVER loopback OPTIONS (DROP extensions);
+ALTER SERVER loopback OPTIONS (ADD fdw_startup_cost '10000.0');
+EXPLAIN (VERBOSE, COSTS OFF)
+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;
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ LockRows
+ 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.ctid, ft1.*, ft2.*
+ -> Nested Loop Left Join
+ 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.ctid, ft1.*, ft2.*
+ Join Filter: (local_tbl.c3 = ft1.c3)
+ -> Index Scan using local_tbl_pkey on public.local_tbl
+ Output: local_tbl.c1, local_tbl.c2, local_tbl.c3, local_tbl.ctid
+ -> Materialize
+ Output: ft1.c1, ft1.c2, ft1.c3, ft1.c4, ft1.c5, ft1.c6, ft1.c7, ft1.c8, ft1.*, ft2.*
+ -> Foreign Scan
+ Output: ft1.c1, ft1.c2, ft1.c3, ft1.c4, ft1.c5, ft1.c6, ft1.c7, ft1.c8, ft1.*, ft2.*
+ Filter: (ft1.c1 = postgres_fdw_abs(ft2.c2))
+ Relations: (public.ft1) INNER JOIN (public.ft2)
+ 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
+ -> Sort
+ Output: ft1.c1, ft1.c2, ft1.c3, ft1.c4, ft1.c5, ft1.c6, ft1.c7, ft1.c8, ft1.*, ft2.*, ft2.c2
+ Sort Key: ft1.c3
+ -> Merge Join
+ Output: ft1.c1, ft1.c2, ft1.c3, ft1.c4, ft1.c5, ft1.c6, ft1.c7, ft1.c8, ft1.*, ft2.*, ft2.c2
+ Merge Cond: ((ft1.c1 = (postgres_fdw_abs(ft2.c2))) AND (ft1.c1 = ft2.c1))
+ -> Sort
+ Output: ft1.c1, ft1.c2, ft1.c3, ft1.c4, ft1.c5, ft1.c6, ft1.c7, ft1.c8, ft1.*
+ Sort Key: ft1.c1
+ -> Foreign Scan on public.ft1
+ Output: ft1.c1, ft1.c2, ft1.c3, ft1.c4, ft1.c5, ft1.c6, ft1.c7, ft1.c8, ft1.*
+ Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" < 100))
+ -> Sort
+ Output: ft2.*, ft2.c1, ft2.c2, (postgres_fdw_abs(ft2.c2))
+ Sort Key: (postgres_fdw_abs(ft2.c2)), ft2.c1
+ -> Foreign Scan on public.ft2
+ Output: ft2.*, ft2.c1, ft2.c2, postgres_fdw_abs(ft2.c2)
+ Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" ORDER BY "C 1" ASC NULLS LAST
+(32 rows)
+
+ALTER SERVER loopback OPTIONS (DROP fdw_startup_cost);
+ALTER SERVER loopback OPTIONS (ADD extensions 'postgres_fdw');
DROP TABLE local_tbl;
-- check join pushdown in situations where multiple userids are involved
CREATE ROLE regress_view_owner SUPERUSER;
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index 16320170ce..833ccdc483 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -5784,6 +5784,34 @@ add_paths_with_pathkeys_for_rel(PlannerInfo *root, RelOptInfo *rel,
useful_pathkeys_list = get_useful_pathkeys_for_relation(root, rel);
+ /*
+ * Before creating sorted paths, adjust the pathtarget for the epq_path,
+ * if any.
+ */
+ if (epq_path != NULL && useful_pathkeys_list != NIL)
+ {
+ PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) rel->fdw_private;
+
+ Assert(is_projection_capable_path(epq_path));
+
+ epq_path->pathtarget = copy_pathtarget(epq_path->pathtarget);
+
+ /* Include columns required for evaluating PHVs in the pathtarget. */
+ add_new_columns_to_pathtarget(epq_path->pathtarget,
+ pull_var_clause((Node *) epq_path->pathtarget->exprs,
+ PVC_RECURSE_PLACEHOLDERS));
+
+ /* Include columns required for evaluating the local conditions. */
+ foreach(lc, fpinfo->local_conds)
+ {
+ RestrictInfo *rinfo = lfirst_node(RestrictInfo, lc);
+
+ add_new_columns_to_pathtarget(epq_path->pathtarget,
+ pull_var_clause((Node *) rinfo->clause,
+ PVC_RECURSE_PLACEHOLDERS));
+ }
+ }
+
/* Create one path for each set of pathkeys we found above. */
foreach(lc, useful_pathkeys_list)
{
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 42735ae78a..e75d54db57 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -672,6 +672,21 @@ SELECT * FROM ft1, ft2, ft4, ft5, local_tbl WHERE ft1.c1 = ft2.c1 AND ft1.c2 = f
AND ft1.c2 = ft5.c1 AND ft1.c2 = local_tbl.c1 AND ft1.c1 < 100 AND ft2.c1 < 100 FOR UPDATE;
RESET enable_nestloop;
RESET enable_hashjoin;
+
+-- test that add_paths_with_pathkeys_for_rel() adjusts the epq_path's
+-- pathtarget properly
+EXPLAIN (VERBOSE, COSTS OFF)
+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;
+
+ALTER SERVER loopback OPTIONS (DROP extensions);
+ALTER SERVER loopback OPTIONS (ADD fdw_startup_cost '10000.0');
+
+EXPLAIN (VERBOSE, COSTS OFF)
+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;
+
+ALTER SERVER loopback OPTIONS (DROP fdw_startup_cost);
+ALTER SERVER loopback OPTIONS (ADD extensions 'postgres_fdw');
+
DROP TABLE local_tbl;
-- check join pushdown in situations where multiple userids are involved
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: foreign join error "variable not found in subplan target list"
2022-08-08 15:26 foreign join error "variable not found in subplan target list" Alexander Pyhalov <a.pyhalov@postgrespro.ru>
2022-08-09 01:59 ` Re: foreign join error "variable not found in subplan target list" Etsuro Fujita <etsuro.fujita@gmail.com>
2022-08-09 03:51 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
2022-08-09 11:54 ` Re: foreign join error "variable not found in subplan target list" Alexander Pyhalov <a.pyhalov@postgrespro.ru>
2022-08-10 02:15 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
2022-08-10 05:28 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
2022-08-21 12:31 ` Re: foreign join error "variable not found in subplan target list" Etsuro Fujita <etsuro.fujita@gmail.com>
@ 2022-08-22 03:29 ` Richard Guo <guofenglinux@gmail.com>
2022-08-29 06:18 ` Re: foreign join error "variable not found in subplan target list" Etsuro Fujita <etsuro.fujita@gmail.com>
0 siblings, 1 reply; 23+ messages in thread
From: Richard Guo @ 2022-08-22 03:29 UTC (permalink / raw)
To: Etsuro Fujita <etsuro.fujita@gmail.com>; +Cc: Alexander Pyhalov <a.pyhalov@postgrespro.ru>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Sun, Aug 21, 2022 at 8:31 PM Etsuro Fujita <etsuro.fujita@gmail.com>
wrote:
> * I think we should avoid modifying the pathtarget, because it would
> be the default pathtarget, which other paths might reference. I think
> it’s safe to use a copied pathtarget, like the attached.
Yeah, that's right. The EPQ path is a shallow copy of the original. A
copied pathtarget should be saner here.
>
> * I think this issue occurs also when there are PlaceHolderVars in the
> relation’s reltarget. Here is an example:
>
> EXPLAIN (VERBOSE, COSTS OFF)
> 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;
> ERROR: variable not found in subplan target list
>
> where local_tbl, ft1, and ft2 are local/foreign tables defined as in
> postgres_fdw.sql. To fix, I modified the patch so that we add to the
> pathtarget not only columns required for evaluating the local
> conditions but columns required for evaluating the PlaceHoderVars.
Correct. Good catch! I can reproduce this error regarding PHVs in master
and verify that this change can fix it. It makes the logic of adjusting
the pathtarget for the epq_path in add_paths_with_pathkeys_for_rel be
more consistent with the logic in build_tlist_to_deparse, which is good.
>
> * The test case reported in this thread produces the planner error
> only in v14 and later, but I think this issue exists since v9.6. So I
> created/added new test cases, including the above one, that would
> otherwise produce the error even in previous versions.
+1 for the tests.
Thanks
Richard
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: foreign join error "variable not found in subplan target list"
2022-08-08 15:26 foreign join error "variable not found in subplan target list" Alexander Pyhalov <a.pyhalov@postgrespro.ru>
2022-08-09 01:59 ` Re: foreign join error "variable not found in subplan target list" Etsuro Fujita <etsuro.fujita@gmail.com>
2022-08-09 03:51 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
2022-08-09 11:54 ` Re: foreign join error "variable not found in subplan target list" Alexander Pyhalov <a.pyhalov@postgrespro.ru>
2022-08-10 02:15 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
2022-08-10 05:28 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
2022-08-21 12:31 ` Re: foreign join error "variable not found in subplan target list" Etsuro Fujita <etsuro.fujita@gmail.com>
2022-08-22 03:29 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
@ 2022-08-29 06:18 ` Etsuro Fujita <etsuro.fujita@gmail.com>
2022-08-29 09:45 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
0 siblings, 1 reply; 23+ messages in thread
From: Etsuro Fujita @ 2022-08-29 06:18 UTC (permalink / raw)
To: Richard Guo <guofenglinux@gmail.com>; +Cc: Alexander Pyhalov <a.pyhalov@postgrespro.ru>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Mon, Aug 22, 2022 at 12:29 PM Richard Guo <guofenglinux@gmail.com> wrote:
> On Sun, Aug 21, 2022 at 8:31 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
>> * I think we should avoid modifying the pathtarget, because it would
>> be the default pathtarget, which other paths might reference. I think
>> it’s safe to use a copied pathtarget, like the attached.
> Yeah, that's right. The EPQ path is a shallow copy of the original. A
> copied pathtarget should be saner here.
One thing I noticed while re-reading the patch is that we should use
create_projection_path() here, to avoid modifying the epq_path in
place, as it is already used for an unsorted join-pushdown path before
we get here. So I modified the patch as such. Updated patch
attached.
Thanks for reviewing!
Best regards,
Etsuro Fujita
Attachments:
[application/octet-stream] v1-0001-Include-local-conds-in-epq-pathtarget-efujita-2.patch (12.1K, ../../CAPmGK15Z3G-MLSqmS5sz=X7=g3DzReWiRwprc4UW-kTDw6oOZg@mail.gmail.com/2-v1-0001-Include-local-conds-in-epq-pathtarget-efujita-2.patch)
download | inline diff:
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 7bf35602b0..1cf54456e5 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -2474,6 +2474,85 @@ SELECT * FROM ft1, ft2, ft4, ft5, local_tbl WHERE ft1.c1 = ft2.c1 AND ft1.c2 = f
RESET enable_nestloop;
RESET enable_hashjoin;
+-- test that add_paths_with_pathkeys_for_rel() arranges for the epq_path to
+-- return columns needed by the parent ForeignScan node
+EXPLAIN (VERBOSE, COSTS OFF)
+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;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ LockRows
+ 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.ctid, ft1.*, ft2.*
+ -> Merge Left Join
+ 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.ctid, ft1.*, ft2.*
+ Merge Cond: (local_tbl.c1 = ft1.c1)
+ -> Index Scan using local_tbl_pkey on public.local_tbl
+ Output: local_tbl.c1, local_tbl.c2, local_tbl.c3, local_tbl.ctid
+ -> Materialize
+ 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))
+ -> Foreign Scan
+ 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)
+ Relations: (public.ft1) INNER JOIN (public.ft2)
+ 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
+ -> Result
+ Output: ft1.c1, ft1.c2, ft1.c3, ft1.c4, ft1.c5, ft1.c6, ft1.c7, ft1.c8, ft1.*, ft2.*, ft2.c3
+ -> Sort
+ 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
+ Sort Key: ft1.c1
+ -> Hash Join
+ 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
+ Hash Cond: (ft1.c1 = ft2.c1)
+ -> Foreign Scan on public.ft1
+ Output: ft1.c1, ft1.c2, ft1.c3, ft1.c4, ft1.c5, ft1.c6, ft1.c7, ft1.c8, ft1.*
+ Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" < 100))
+ -> Hash
+ Output: ft2.*, ft2.c1, ft2.c3
+ -> Foreign Scan on public.ft2
+ Output: ft2.*, ft2.c1, ft2.c3
+ Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1"
+(29 rows)
+
+ALTER SERVER loopback OPTIONS (DROP extensions);
+ALTER SERVER loopback OPTIONS (ADD fdw_startup_cost '10000.0');
+EXPLAIN (VERBOSE, COSTS OFF)
+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;
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ LockRows
+ 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.ctid, ft1.*, ft2.*
+ -> Nested Loop Left Join
+ 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.ctid, ft1.*, ft2.*
+ Join Filter: (local_tbl.c3 = ft1.c3)
+ -> Index Scan using local_tbl_pkey on public.local_tbl
+ Output: local_tbl.c1, local_tbl.c2, local_tbl.c3, local_tbl.ctid
+ -> Materialize
+ Output: ft1.c1, ft1.c2, ft1.c3, ft1.c4, ft1.c5, ft1.c6, ft1.c7, ft1.c8, ft1.*, ft2.*
+ -> Foreign Scan
+ Output: ft1.c1, ft1.c2, ft1.c3, ft1.c4, ft1.c5, ft1.c6, ft1.c7, ft1.c8, ft1.*, ft2.*
+ Filter: (ft1.c1 = postgres_fdw_abs(ft2.c2))
+ Relations: (public.ft1) INNER JOIN (public.ft2)
+ 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
+ -> Sort
+ Output: ft1.c1, ft1.c2, ft1.c3, ft1.c4, ft1.c5, ft1.c6, ft1.c7, ft1.c8, ft1.*, ft2.*, ft2.c2
+ Sort Key: ft1.c3
+ -> Merge Join
+ Output: ft1.c1, ft1.c2, ft1.c3, ft1.c4, ft1.c5, ft1.c6, ft1.c7, ft1.c8, ft1.*, ft2.*, ft2.c2
+ Merge Cond: ((ft1.c1 = (postgres_fdw_abs(ft2.c2))) AND (ft1.c1 = ft2.c1))
+ -> Sort
+ Output: ft1.c1, ft1.c2, ft1.c3, ft1.c4, ft1.c5, ft1.c6, ft1.c7, ft1.c8, ft1.*
+ Sort Key: ft1.c1
+ -> Foreign Scan on public.ft1
+ Output: ft1.c1, ft1.c2, ft1.c3, ft1.c4, ft1.c5, ft1.c6, ft1.c7, ft1.c8, ft1.*
+ Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" < 100))
+ -> Sort
+ Output: ft2.*, ft2.c1, ft2.c2, (postgres_fdw_abs(ft2.c2))
+ Sort Key: (postgres_fdw_abs(ft2.c2)), ft2.c1
+ -> Foreign Scan on public.ft2
+ Output: ft2.*, ft2.c1, ft2.c2, postgres_fdw_abs(ft2.c2)
+ Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" ORDER BY "C 1" ASC NULLS LAST
+(32 rows)
+
+ALTER SERVER loopback OPTIONS (DROP fdw_startup_cost);
+ALTER SERVER loopback OPTIONS (ADD extensions 'postgres_fdw');
DROP TABLE local_tbl;
-- check join pushdown in situations where multiple userids are involved
CREATE ROLE regress_view_owner SUPERUSER;
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index 16320170ce..f66b04fc31 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -5784,6 +5784,42 @@ add_paths_with_pathkeys_for_rel(PlannerInfo *root, RelOptInfo *rel,
useful_pathkeys_list = get_useful_pathkeys_for_relation(root, rel);
+ /*
+ * Before creating sorted paths, we arrange for the passed-in EPQ path, if
+ * any, to return columns needed by the parent ForeignScan node.
+ */
+ if (epq_path != NULL && useful_pathkeys_list != NIL)
+ {
+ PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) rel->fdw_private;
+ PathTarget *target = copy_pathtarget(epq_path->pathtarget);
+
+ /* Include columns required for evaluating PHVs in the tlist. */
+ add_new_columns_to_pathtarget(target,
+ pull_var_clause((Node *) target->exprs,
+ PVC_RECURSE_PLACEHOLDERS));
+
+ /* Include columns required for evaluating the local conditions. */
+ foreach(lc, fpinfo->local_conds)
+ {
+ RestrictInfo *rinfo = lfirst_node(RestrictInfo, lc);
+
+ add_new_columns_to_pathtarget(target,
+ pull_var_clause((Node *) rinfo->clause,
+ PVC_RECURSE_PLACEHOLDERS));
+ }
+
+ /*
+ * The passed-in EPQ path is a join path, so it is projection-capable,
+ * but we use create_projection_path() here, so as to avoid modifying
+ * it in place.
+ */
+ Assert(is_projection_capable_path(epq_path));
+ epq_path = (Path *) create_projection_path(root,
+ rel,
+ epq_path,
+ target);
+ }
+
/* Create one path for each set of pathkeys we found above. */
foreach(lc, useful_pathkeys_list)
{
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 42735ae78a..9a493502f1 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -672,6 +672,19 @@ SELECT * FROM ft1, ft2, ft4, ft5, local_tbl WHERE ft1.c1 = ft2.c1 AND ft1.c2 = f
AND ft1.c2 = ft5.c1 AND ft1.c2 = local_tbl.c1 AND ft1.c1 < 100 AND ft2.c1 < 100 FOR UPDATE;
RESET enable_nestloop;
RESET enable_hashjoin;
+
+-- test that add_paths_with_pathkeys_for_rel() arranges for the epq_path to
+-- return columns needed by the parent ForeignScan node
+EXPLAIN (VERBOSE, COSTS OFF)
+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;
+
+ALTER SERVER loopback OPTIONS (DROP extensions);
+ALTER SERVER loopback OPTIONS (ADD fdw_startup_cost '10000.0');
+EXPLAIN (VERBOSE, COSTS OFF)
+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;
+ALTER SERVER loopback OPTIONS (DROP fdw_startup_cost);
+ALTER SERVER loopback OPTIONS (ADD extensions 'postgres_fdw');
+
DROP TABLE local_tbl;
-- check join pushdown in situations where multiple userids are involved
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: foreign join error "variable not found in subplan target list"
2022-08-08 15:26 foreign join error "variable not found in subplan target list" Alexander Pyhalov <a.pyhalov@postgrespro.ru>
2022-08-09 01:59 ` Re: foreign join error "variable not found in subplan target list" Etsuro Fujita <etsuro.fujita@gmail.com>
2022-08-09 03:51 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
2022-08-09 11:54 ` Re: foreign join error "variable not found in subplan target list" Alexander Pyhalov <a.pyhalov@postgrespro.ru>
2022-08-10 02:15 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
2022-08-10 05:28 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
2022-08-21 12:31 ` Re: foreign join error "variable not found in subplan target list" Etsuro Fujita <etsuro.fujita@gmail.com>
2022-08-22 03:29 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
2022-08-29 06:18 ` Re: foreign join error "variable not found in subplan target list" Etsuro Fujita <etsuro.fujita@gmail.com>
@ 2022-08-29 09:45 ` Richard Guo <guofenglinux@gmail.com>
2022-08-30 01:18 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
2022-08-30 07:50 ` Re: foreign join error "variable not found in subplan target list" Etsuro Fujita <etsuro.fujita@gmail.com>
0 siblings, 2 replies; 23+ messages in thread
From: Richard Guo @ 2022-08-29 09:45 UTC (permalink / raw)
To: Etsuro Fujita <etsuro.fujita@gmail.com>; +Cc: Alexander Pyhalov <a.pyhalov@postgrespro.ru>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Mon, Aug 29, 2022 at 2:17 PM Etsuro Fujita <etsuro.fujita@gmail.com>
wrote:
> One thing I noticed while re-reading the patch is that we should use
> create_projection_path() here, to avoid modifying the epq_path in
> place, as it is already used for an unsorted join-pushdown path before
> we get here. So I modified the patch as such. Updated patch
> attached.
The new change looks reasonable to me as there is other ref to this
epq_path.
I'm looking again on how we adjust the PathTarget, and I think we may
need to update the cost and width fields if there are any new columns
added. Maybe we can leverage set_pathtarget_cost_width for that.
Thanks
Richard
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: foreign join error "variable not found in subplan target list"
2022-08-08 15:26 foreign join error "variable not found in subplan target list" Alexander Pyhalov <a.pyhalov@postgrespro.ru>
2022-08-09 01:59 ` Re: foreign join error "variable not found in subplan target list" Etsuro Fujita <etsuro.fujita@gmail.com>
2022-08-09 03:51 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
2022-08-09 11:54 ` Re: foreign join error "variable not found in subplan target list" Alexander Pyhalov <a.pyhalov@postgrespro.ru>
2022-08-10 02:15 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
2022-08-10 05:28 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
2022-08-21 12:31 ` Re: foreign join error "variable not found in subplan target list" Etsuro Fujita <etsuro.fujita@gmail.com>
2022-08-22 03:29 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
2022-08-29 06:18 ` Re: foreign join error "variable not found in subplan target list" Etsuro Fujita <etsuro.fujita@gmail.com>
2022-08-29 09:45 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
@ 2022-08-30 01:18 ` Richard Guo <guofenglinux@gmail.com>
2022-08-30 02:04 ` Re: foreign join error "variable not found in subplan target list" Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 23+ messages in thread
From: Richard Guo @ 2022-08-30 01:18 UTC (permalink / raw)
To: Etsuro Fujita <etsuro.fujita@gmail.com>; +Cc: Alexander Pyhalov <a.pyhalov@postgrespro.ru>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Mon, Aug 29, 2022 at 5:45 PM Richard Guo <guofenglinux@gmail.com> wrote:
>
> On Mon, Aug 29, 2022 at 2:17 PM Etsuro Fujita <etsuro.fujita@gmail.com>
> wrote:
>
>> One thing I noticed while re-reading the patch is that we should use
>> create_projection_path() here, to avoid modifying the epq_path in
>> place, as it is already used for an unsorted join-pushdown path before
>> we get here. So I modified the patch as such. Updated patch
>> attached.
>
>
> The new change looks reasonable to me as there is other ref to this
> epq_path.
>
> I'm looking again on how we adjust the PathTarget, and I think we may
> need to update the cost and width fields if there are any new columns
> added. Maybe we can leverage set_pathtarget_cost_width for that.
>
To be concrete, I mean something like this:
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -5808,6 +5808,10 @@ add_paths_with_pathkeys_for_rel(PlannerInfo *root,
RelOptInfo *rel,
PVC_RECURSE_PLACEHOLDERS));
}
+ /* Update the cost and width fields if we have added any
new columns. */
+ if (!equal(epq_path->pathtarget->exprs, target->exprs))
+ set_pathtarget_cost_width(root, target);
+
/*
* The passed-in EPQ path is a join path, so it is
projection-capable,
* but we use create_projection_path() here, so as to avoid
modifying
Thanks
Richard
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: foreign join error "variable not found in subplan target list"
2022-08-08 15:26 foreign join error "variable not found in subplan target list" Alexander Pyhalov <a.pyhalov@postgrespro.ru>
2022-08-09 01:59 ` Re: foreign join error "variable not found in subplan target list" Etsuro Fujita <etsuro.fujita@gmail.com>
2022-08-09 03:51 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
2022-08-09 11:54 ` Re: foreign join error "variable not found in subplan target list" Alexander Pyhalov <a.pyhalov@postgrespro.ru>
2022-08-10 02:15 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
2022-08-10 05:28 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
2022-08-21 12:31 ` Re: foreign join error "variable not found in subplan target list" Etsuro Fujita <etsuro.fujita@gmail.com>
2022-08-22 03:29 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
2022-08-29 06:18 ` Re: foreign join error "variable not found in subplan target list" Etsuro Fujita <etsuro.fujita@gmail.com>
2022-08-29 09:45 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
2022-08-30 01:18 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
@ 2022-08-30 02:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-08-30 02:21 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
0 siblings, 1 reply; 23+ messages in thread
From: Tom Lane @ 2022-08-30 02:04 UTC (permalink / raw)
To: Richard Guo <guofenglinux@gmail.com>; +Cc: Etsuro Fujita <etsuro.fujita@gmail.com>; Alexander Pyhalov <a.pyhalov@postgrespro.ru>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Richard Guo <guofenglinux@gmail.com> writes:
> To be concrete, I mean something like this:
> + /* Update the cost and width fields if we have added any
> new columns. */
> + if (!equal(epq_path->pathtarget->exprs, target->exprs))
> + set_pathtarget_cost_width(root, target);
Wouldn't a list_length() comparison be sufficient? equal() seems
like overkill.
regards, tom lane
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: foreign join error "variable not found in subplan target list"
2022-08-08 15:26 foreign join error "variable not found in subplan target list" Alexander Pyhalov <a.pyhalov@postgrespro.ru>
2022-08-09 01:59 ` Re: foreign join error "variable not found in subplan target list" Etsuro Fujita <etsuro.fujita@gmail.com>
2022-08-09 03:51 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
2022-08-09 11:54 ` Re: foreign join error "variable not found in subplan target list" Alexander Pyhalov <a.pyhalov@postgrespro.ru>
2022-08-10 02:15 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
2022-08-10 05:28 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
2022-08-21 12:31 ` Re: foreign join error "variable not found in subplan target list" Etsuro Fujita <etsuro.fujita@gmail.com>
2022-08-22 03:29 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
2022-08-29 06:18 ` Re: foreign join error "variable not found in subplan target list" Etsuro Fujita <etsuro.fujita@gmail.com>
2022-08-29 09:45 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
2022-08-30 01:18 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
2022-08-30 02:04 ` Re: foreign join error "variable not found in subplan target list" Tom Lane <tgl@sss.pgh.pa.us>
@ 2022-08-30 02:21 ` Richard Guo <guofenglinux@gmail.com>
0 siblings, 0 replies; 23+ messages in thread
From: Richard Guo @ 2022-08-30 02:21 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Etsuro Fujita <etsuro.fujita@gmail.com>; Alexander Pyhalov <a.pyhalov@postgrespro.ru>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Tue, Aug 30, 2022 at 10:04 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Richard Guo <guofenglinux@gmail.com> writes:
> > To be concrete, I mean something like this:
>
> > + /* Update the cost and width fields if we have added any
> > new columns. */
> > + if (!equal(epq_path->pathtarget->exprs, target->exprs))
> > + set_pathtarget_cost_width(root, target);
>
> Wouldn't a list_length() comparison be sufficient? equal() seems
> like overkill.
Yeah, list_length comparison would do, as we only append new columns
here. Thanks for the suggestion!
Thanks
Richard
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: foreign join error "variable not found in subplan target list"
2022-08-08 15:26 foreign join error "variable not found in subplan target list" Alexander Pyhalov <a.pyhalov@postgrespro.ru>
2022-08-09 01:59 ` Re: foreign join error "variable not found in subplan target list" Etsuro Fujita <etsuro.fujita@gmail.com>
2022-08-09 03:51 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
2022-08-09 11:54 ` Re: foreign join error "variable not found in subplan target list" Alexander Pyhalov <a.pyhalov@postgrespro.ru>
2022-08-10 02:15 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
2022-08-10 05:28 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
2022-08-21 12:31 ` Re: foreign join error "variable not found in subplan target list" Etsuro Fujita <etsuro.fujita@gmail.com>
2022-08-22 03:29 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
2022-08-29 06:18 ` Re: foreign join error "variable not found in subplan target list" Etsuro Fujita <etsuro.fujita@gmail.com>
2022-08-29 09:45 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
@ 2022-08-30 07:50 ` Etsuro Fujita <etsuro.fujita@gmail.com>
2022-08-31 09:26 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
1 sibling, 1 reply; 23+ messages in thread
From: Etsuro Fujita @ 2022-08-30 07:50 UTC (permalink / raw)
To: Richard Guo <guofenglinux@gmail.com>; +Cc: Alexander Pyhalov <a.pyhalov@postgrespro.ru>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Mon, Aug 29, 2022 at 6:46 PM Richard Guo <guofenglinux@gmail.com> wrote:
> I'm looking again on how we adjust the PathTarget, and I think we may
> need to update the cost and width fields if there are any new columns
> added. Maybe we can leverage set_pathtarget_cost_width for that.
I'm not really sure we really need to make the estimates accurate,
because 1) the resulting EPQ plan is used only for rechecks, so the
estimates would not be that important IMO, and 2) in fact, we don't
adjust the estimates of a given EPQ plan in postgresGetForeignPlan()
even when replacing the plan's tlist and/or removing local conditions
from the plan. Is it worth expending the cycles here?
Sorry for the late reply.
Best regards,
Etsuro Fujita
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: foreign join error "variable not found in subplan target list"
2022-08-08 15:26 foreign join error "variable not found in subplan target list" Alexander Pyhalov <a.pyhalov@postgrespro.ru>
2022-08-09 01:59 ` Re: foreign join error "variable not found in subplan target list" Etsuro Fujita <etsuro.fujita@gmail.com>
2022-08-09 03:51 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
2022-08-09 11:54 ` Re: foreign join error "variable not found in subplan target list" Alexander Pyhalov <a.pyhalov@postgrespro.ru>
2022-08-10 02:15 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
2022-08-10 05:28 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
2022-08-21 12:31 ` Re: foreign join error "variable not found in subplan target list" Etsuro Fujita <etsuro.fujita@gmail.com>
2022-08-22 03:29 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
2022-08-29 06:18 ` Re: foreign join error "variable not found in subplan target list" Etsuro Fujita <etsuro.fujita@gmail.com>
2022-08-29 09:45 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
2022-08-30 07:50 ` Re: foreign join error "variable not found in subplan target list" Etsuro Fujita <etsuro.fujita@gmail.com>
@ 2022-08-31 09:26 ` Richard Guo <guofenglinux@gmail.com>
2022-08-31 09:52 ` Re: foreign join error "variable not found in subplan target list" Etsuro Fujita <etsuro.fujita@gmail.com>
0 siblings, 1 reply; 23+ messages in thread
From: Richard Guo @ 2022-08-31 09:26 UTC (permalink / raw)
To: Etsuro Fujita <etsuro.fujita@gmail.com>; +Cc: Alexander Pyhalov <a.pyhalov@postgrespro.ru>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Tue, Aug 30, 2022 at 3:50 PM Etsuro Fujita <etsuro.fujita@gmail.com>
wrote:
> On Mon, Aug 29, 2022 at 6:46 PM Richard Guo <guofenglinux@gmail.com>
> wrote:
> > I'm looking again on how we adjust the PathTarget, and I think we may
> > need to update the cost and width fields if there are any new columns
> > added. Maybe we can leverage set_pathtarget_cost_width for that.
>
> I'm not really sure we really need to make the estimates accurate,
> because 1) the resulting EPQ plan is used only for rechecks, so the
> estimates would not be that important IMO, and 2) in fact, we don't
> adjust the estimates of a given EPQ plan in postgresGetForeignPlan()
> even when replacing the plan's tlist and/or removing local conditions
> from the plan. Is it worth expending the cycles here?
Hmm, yes, the EPQ path does not need to compete with others in add_path,
so its cost does not matter too much. And the comment just above
GetExistingLocalJoinPath says:
* Since the plan created using this path will presumably only be used to
* execute EPQ checks, efficiency of the path is not a concern.
But still I feel it's not a good practice to not update the cost and
width fields after calling add_new_columns_to_pathtarget(). How about we
add some comments here explaining why we do not need to adjust the
estimates for the EPQ path?
Thanks
Richard
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: foreign join error "variable not found in subplan target list"
2022-08-08 15:26 foreign join error "variable not found in subplan target list" Alexander Pyhalov <a.pyhalov@postgrespro.ru>
2022-08-09 01:59 ` Re: foreign join error "variable not found in subplan target list" Etsuro Fujita <etsuro.fujita@gmail.com>
2022-08-09 03:51 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
2022-08-09 11:54 ` Re: foreign join error "variable not found in subplan target list" Alexander Pyhalov <a.pyhalov@postgrespro.ru>
2022-08-10 02:15 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
2022-08-10 05:28 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
2022-08-21 12:31 ` Re: foreign join error "variable not found in subplan target list" Etsuro Fujita <etsuro.fujita@gmail.com>
2022-08-22 03:29 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
2022-08-29 06:18 ` Re: foreign join error "variable not found in subplan target list" Etsuro Fujita <etsuro.fujita@gmail.com>
2022-08-29 09:45 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
2022-08-30 07:50 ` Re: foreign join error "variable not found in subplan target list" Etsuro Fujita <etsuro.fujita@gmail.com>
2022-08-31 09:26 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
@ 2022-08-31 09:52 ` Etsuro Fujita <etsuro.fujita@gmail.com>
2022-09-12 04:20 ` Re: foreign join error "variable not found in subplan target list" Etsuro Fujita <etsuro.fujita@gmail.com>
0 siblings, 1 reply; 23+ messages in thread
From: Etsuro Fujita @ 2022-08-31 09:52 UTC (permalink / raw)
To: Richard Guo <guofenglinux@gmail.com>; +Cc: Alexander Pyhalov <a.pyhalov@postgrespro.ru>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Wed, Aug 31, 2022 at 6:27 PM Richard Guo <guofenglinux@gmail.com> wrote:
> On Tue, Aug 30, 2022 at 3:50 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
>> On Mon, Aug 29, 2022 at 6:46 PM Richard Guo <guofenglinux@gmail.com> wrote:
>> > I'm looking again on how we adjust the PathTarget, and I think we may
>> > need to update the cost and width fields if there are any new columns
>> > added. Maybe we can leverage set_pathtarget_cost_width for that.
>>
>> I'm not really sure we really need to make the estimates accurate,
>> because 1) the resulting EPQ plan is used only for rechecks, so the
>> estimates would not be that important IMO, and 2) in fact, we don't
>> adjust the estimates of a given EPQ plan in postgresGetForeignPlan()
>> even when replacing the plan's tlist and/or removing local conditions
>> from the plan. Is it worth expending the cycles here?
> Hmm, yes, the EPQ path does not need to compete with others in add_path,
> so its cost does not matter too much. And the comment just above
> GetExistingLocalJoinPath says:
>
> * Since the plan created using this path will presumably only be used to
> * execute EPQ checks, efficiency of the path is not a concern.
>
> But still I feel it's not a good practice to not update the cost and
> width fields after calling add_new_columns_to_pathtarget(). How about we
> add some comments here explaining why we do not need to adjust the
> estimates for the EPQ path?
I agree with you on that point. I’ll update the patch as such in the
next version.
Best regards,
Etsuro Fujita
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: foreign join error "variable not found in subplan target list"
2022-08-08 15:26 foreign join error "variable not found in subplan target list" Alexander Pyhalov <a.pyhalov@postgrespro.ru>
2022-08-09 01:59 ` Re: foreign join error "variable not found in subplan target list" Etsuro Fujita <etsuro.fujita@gmail.com>
2022-08-09 03:51 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
2022-08-09 11:54 ` Re: foreign join error "variable not found in subplan target list" Alexander Pyhalov <a.pyhalov@postgrespro.ru>
2022-08-10 02:15 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
2022-08-10 05:28 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
2022-08-21 12:31 ` Re: foreign join error "variable not found in subplan target list" Etsuro Fujita <etsuro.fujita@gmail.com>
2022-08-22 03:29 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
2022-08-29 06:18 ` Re: foreign join error "variable not found in subplan target list" Etsuro Fujita <etsuro.fujita@gmail.com>
2022-08-29 09:45 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
2022-08-30 07:50 ` Re: foreign join error "variable not found in subplan target list" Etsuro Fujita <etsuro.fujita@gmail.com>
2022-08-31 09:26 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
2022-08-31 09:52 ` Re: foreign join error "variable not found in subplan target list" Etsuro Fujita <etsuro.fujita@gmail.com>
@ 2022-09-12 04:20 ` Etsuro Fujita <etsuro.fujita@gmail.com>
2022-09-13 02:28 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
0 siblings, 1 reply; 23+ messages in thread
From: Etsuro Fujita @ 2022-09-12 04:20 UTC (permalink / raw)
To: Richard Guo <guofenglinux@gmail.com>; +Cc: Alexander Pyhalov <a.pyhalov@postgrespro.ru>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Wed, Aug 31, 2022 at 6:52 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
> On Wed, Aug 31, 2022 at 6:27 PM Richard Guo <guofenglinux@gmail.com> wrote:
> > Hmm, yes, the EPQ path does not need to compete with others in add_path,
> > so its cost does not matter too much. And the comment just above
> > GetExistingLocalJoinPath says:
> >
> > * Since the plan created using this path will presumably only be used to
> > * execute EPQ checks, efficiency of the path is not a concern.
> >
> > But still I feel it's not a good practice to not update the cost and
> > width fields after calling add_new_columns_to_pathtarget(). How about we
> > add some comments here explaining why we do not need to adjust the
> > estimates for the EPQ path?
>
> I agree with you on that point. I’ll update the patch as such in the
> next version.
Here is an updated patch for that. Other changes:
* I modified the patch so that we adjust the tlist of the EPQ path if
necessary, using the idea discussed upthread.
* I tweaked other comments a little bit.
Best regards,
Etsuro Fujita
Attachments:
[application/octet-stream] v1-0001-Include-local-conds-in-epq-pathtarget-efujita-3.patch (12.6K, ../../CAPmGK16Sqqm_s9sXZBCCvsK_F-kTQZvDT6j2+Gxg7VAcdwaQiw@mail.gmail.com/2-v1-0001-Include-local-conds-in-epq-pathtarget-efujita-3.patch)
download | inline diff:
diff --git a/contrib/postgres_fdw/expected/postgres_fdw.out b/contrib/postgres_fdw/expected/postgres_fdw.out
index 7bf35602b0..1cf54456e5 100644
--- a/contrib/postgres_fdw/expected/postgres_fdw.out
+++ b/contrib/postgres_fdw/expected/postgres_fdw.out
@@ -2474,6 +2474,85 @@ SELECT * FROM ft1, ft2, ft4, ft5, local_tbl WHERE ft1.c1 = ft2.c1 AND ft1.c2 = f
RESET enable_nestloop;
RESET enable_hashjoin;
+-- test that add_paths_with_pathkeys_for_rel() arranges for the epq_path to
+-- return columns needed by the parent ForeignScan node
+EXPLAIN (VERBOSE, COSTS OFF)
+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;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ LockRows
+ 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.ctid, ft1.*, ft2.*
+ -> Merge Left Join
+ 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.ctid, ft1.*, ft2.*
+ Merge Cond: (local_tbl.c1 = ft1.c1)
+ -> Index Scan using local_tbl_pkey on public.local_tbl
+ Output: local_tbl.c1, local_tbl.c2, local_tbl.c3, local_tbl.ctid
+ -> Materialize
+ 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))
+ -> Foreign Scan
+ 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)
+ Relations: (public.ft1) INNER JOIN (public.ft2)
+ 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
+ -> Result
+ Output: ft1.c1, ft1.c2, ft1.c3, ft1.c4, ft1.c5, ft1.c6, ft1.c7, ft1.c8, ft1.*, ft2.*, ft2.c3
+ -> Sort
+ 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
+ Sort Key: ft1.c1
+ -> Hash Join
+ 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
+ Hash Cond: (ft1.c1 = ft2.c1)
+ -> Foreign Scan on public.ft1
+ Output: ft1.c1, ft1.c2, ft1.c3, ft1.c4, ft1.c5, ft1.c6, ft1.c7, ft1.c8, ft1.*
+ Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" < 100))
+ -> Hash
+ Output: ft2.*, ft2.c1, ft2.c3
+ -> Foreign Scan on public.ft2
+ Output: ft2.*, ft2.c1, ft2.c3
+ Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1"
+(29 rows)
+
+ALTER SERVER loopback OPTIONS (DROP extensions);
+ALTER SERVER loopback OPTIONS (ADD fdw_startup_cost '10000.0');
+EXPLAIN (VERBOSE, COSTS OFF)
+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;
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ LockRows
+ 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.ctid, ft1.*, ft2.*
+ -> Nested Loop Left Join
+ 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.ctid, ft1.*, ft2.*
+ Join Filter: (local_tbl.c3 = ft1.c3)
+ -> Index Scan using local_tbl_pkey on public.local_tbl
+ Output: local_tbl.c1, local_tbl.c2, local_tbl.c3, local_tbl.ctid
+ -> Materialize
+ Output: ft1.c1, ft1.c2, ft1.c3, ft1.c4, ft1.c5, ft1.c6, ft1.c7, ft1.c8, ft1.*, ft2.*
+ -> Foreign Scan
+ Output: ft1.c1, ft1.c2, ft1.c3, ft1.c4, ft1.c5, ft1.c6, ft1.c7, ft1.c8, ft1.*, ft2.*
+ Filter: (ft1.c1 = postgres_fdw_abs(ft2.c2))
+ Relations: (public.ft1) INNER JOIN (public.ft2)
+ 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
+ -> Sort
+ Output: ft1.c1, ft1.c2, ft1.c3, ft1.c4, ft1.c5, ft1.c6, ft1.c7, ft1.c8, ft1.*, ft2.*, ft2.c2
+ Sort Key: ft1.c3
+ -> Merge Join
+ Output: ft1.c1, ft1.c2, ft1.c3, ft1.c4, ft1.c5, ft1.c6, ft1.c7, ft1.c8, ft1.*, ft2.*, ft2.c2
+ Merge Cond: ((ft1.c1 = (postgres_fdw_abs(ft2.c2))) AND (ft1.c1 = ft2.c1))
+ -> Sort
+ Output: ft1.c1, ft1.c2, ft1.c3, ft1.c4, ft1.c5, ft1.c6, ft1.c7, ft1.c8, ft1.*
+ Sort Key: ft1.c1
+ -> Foreign Scan on public.ft1
+ Output: ft1.c1, ft1.c2, ft1.c3, ft1.c4, ft1.c5, ft1.c6, ft1.c7, ft1.c8, ft1.*
+ Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" WHERE (("C 1" < 100))
+ -> Sort
+ Output: ft2.*, ft2.c1, ft2.c2, (postgres_fdw_abs(ft2.c2))
+ Sort Key: (postgres_fdw_abs(ft2.c2)), ft2.c1
+ -> Foreign Scan on public.ft2
+ Output: ft2.*, ft2.c1, ft2.c2, postgres_fdw_abs(ft2.c2)
+ Remote SQL: SELECT "C 1", c2, c3, c4, c5, c6, c7, c8 FROM "S 1"."T 1" ORDER BY "C 1" ASC NULLS LAST
+(32 rows)
+
+ALTER SERVER loopback OPTIONS (DROP fdw_startup_cost);
+ALTER SERVER loopback OPTIONS (ADD extensions 'postgres_fdw');
DROP TABLE local_tbl;
-- check join pushdown in situations where multiple userids are involved
CREATE ROLE regress_view_owner SUPERUSER;
diff --git a/contrib/postgres_fdw/postgres_fdw.c b/contrib/postgres_fdw/postgres_fdw.c
index 16320170ce..549ed01afc 100644
--- a/contrib/postgres_fdw/postgres_fdw.c
+++ b/contrib/postgres_fdw/postgres_fdw.c
@@ -5784,6 +5784,55 @@ add_paths_with_pathkeys_for_rel(PlannerInfo *root, RelOptInfo *rel,
useful_pathkeys_list = get_useful_pathkeys_for_relation(root, rel);
+ /*
+ * Before creating sorted paths, arrange for the passed-in EPQ path, if
+ * any, to return columns needed by the parent ForeignScan node so that
+ * they will propagate up through Sort nodes injected below.
+ */
+ if (epq_path != NULL && useful_pathkeys_list != NIL)
+ {
+ PgFdwRelationInfo *fpinfo = (PgFdwRelationInfo *) rel->fdw_private;
+ PathTarget *target = copy_pathtarget(epq_path->pathtarget);
+
+ /* Include columns required for evaluating PHVs in the tlist. */
+ add_new_columns_to_pathtarget(target,
+ pull_var_clause((Node *) target->exprs,
+ PVC_RECURSE_PLACEHOLDERS));
+
+ /* Include columns required for evaluating the local conditions. */
+ foreach(lc, fpinfo->local_conds)
+ {
+ RestrictInfo *rinfo = lfirst_node(RestrictInfo, lc);
+
+ add_new_columns_to_pathtarget(target,
+ pull_var_clause((Node *) rinfo->clause,
+ PVC_RECURSE_PLACEHOLDERS));
+ }
+
+ /*
+ * Adjust the tlist of the EPQ path if necessary.
+ *
+ * Note: the plan created using this path will only be used to execute
+ * EPQ checks, where accuracy of the plan cost and width estimates
+ * would not be important, so we do not do set_pathtarget_cost_width()
+ * for the new pathtarget here. See also postgresGetForeignPlan().
+ */
+ if (list_length(target->exprs) > list_length(epq_path->pathtarget->exprs))
+ {
+ /* The EPQ path is a join path, so it is projection-capable. */
+ Assert(is_projection_capable_path(epq_path));
+
+ /*
+ * Use create_projection_path() here, so as to avoid modifying it
+ * in place.
+ */
+ epq_path = (Path *) create_projection_path(root,
+ rel,
+ epq_path,
+ target);
+ }
+ }
+
/* Create one path for each set of pathkeys we found above. */
foreach(lc, useful_pathkeys_list)
{
diff --git a/contrib/postgres_fdw/sql/postgres_fdw.sql b/contrib/postgres_fdw/sql/postgres_fdw.sql
index 42735ae78a..9a493502f1 100644
--- a/contrib/postgres_fdw/sql/postgres_fdw.sql
+++ b/contrib/postgres_fdw/sql/postgres_fdw.sql
@@ -672,6 +672,19 @@ SELECT * FROM ft1, ft2, ft4, ft5, local_tbl WHERE ft1.c1 = ft2.c1 AND ft1.c2 = f
AND ft1.c2 = ft5.c1 AND ft1.c2 = local_tbl.c1 AND ft1.c1 < 100 AND ft2.c1 < 100 FOR UPDATE;
RESET enable_nestloop;
RESET enable_hashjoin;
+
+-- test that add_paths_with_pathkeys_for_rel() arranges for the epq_path to
+-- return columns needed by the parent ForeignScan node
+EXPLAIN (VERBOSE, COSTS OFF)
+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;
+
+ALTER SERVER loopback OPTIONS (DROP extensions);
+ALTER SERVER loopback OPTIONS (ADD fdw_startup_cost '10000.0');
+EXPLAIN (VERBOSE, COSTS OFF)
+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;
+ALTER SERVER loopback OPTIONS (DROP fdw_startup_cost);
+ALTER SERVER loopback OPTIONS (ADD extensions 'postgres_fdw');
+
DROP TABLE local_tbl;
-- check join pushdown in situations where multiple userids are involved
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: foreign join error "variable not found in subplan target list"
2022-08-08 15:26 foreign join error "variable not found in subplan target list" Alexander Pyhalov <a.pyhalov@postgrespro.ru>
2022-08-09 01:59 ` Re: foreign join error "variable not found in subplan target list" Etsuro Fujita <etsuro.fujita@gmail.com>
2022-08-09 03:51 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
2022-08-09 11:54 ` Re: foreign join error "variable not found in subplan target list" Alexander Pyhalov <a.pyhalov@postgrespro.ru>
2022-08-10 02:15 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
2022-08-10 05:28 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
2022-08-21 12:31 ` Re: foreign join error "variable not found in subplan target list" Etsuro Fujita <etsuro.fujita@gmail.com>
2022-08-22 03:29 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
2022-08-29 06:18 ` Re: foreign join error "variable not found in subplan target list" Etsuro Fujita <etsuro.fujita@gmail.com>
2022-08-29 09:45 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
2022-08-30 07:50 ` Re: foreign join error "variable not found in subplan target list" Etsuro Fujita <etsuro.fujita@gmail.com>
2022-08-31 09:26 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
2022-08-31 09:52 ` Re: foreign join error "variable not found in subplan target list" Etsuro Fujita <etsuro.fujita@gmail.com>
2022-09-12 04:20 ` Re: foreign join error "variable not found in subplan target list" Etsuro Fujita <etsuro.fujita@gmail.com>
@ 2022-09-13 02:28 ` Richard Guo <guofenglinux@gmail.com>
2022-09-14 10:01 ` Re: foreign join error "variable not found in subplan target list" Etsuro Fujita <etsuro.fujita@gmail.com>
0 siblings, 1 reply; 23+ messages in thread
From: Richard Guo @ 2022-09-13 02:28 UTC (permalink / raw)
To: Etsuro Fujita <etsuro.fujita@gmail.com>; +Cc: Alexander Pyhalov <a.pyhalov@postgrespro.ru>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Mon, Sep 12, 2022 at 12:19 PM Etsuro Fujita <etsuro.fujita@gmail.com>
wrote:
> On Wed, Aug 31, 2022 at 6:52 PM Etsuro Fujita <etsuro.fujita@gmail.com>
> wrote:
> > On Wed, Aug 31, 2022 at 6:27 PM Richard Guo <guofenglinux@gmail.com>
> wrote:
> > > Hmm, yes, the EPQ path does not need to compete with others in
> add_path,
> > > so its cost does not matter too much. And the comment just above
> > > GetExistingLocalJoinPath says:
> > >
> > > * Since the plan created using this path will presumably only be used
> to
> > > * execute EPQ checks, efficiency of the path is not a concern.
> > >
> > > But still I feel it's not a good practice to not update the cost and
> > > width fields after calling add_new_columns_to_pathtarget(). How about
> we
> > > add some comments here explaining why we do not need to adjust the
> > > estimates for the EPQ path?
> >
> > I agree with you on that point. I’ll update the patch as such in the
> > next version.
>
> Here is an updated patch for that. Other changes:
>
> * I modified the patch so that we adjust the tlist of the EPQ path if
> necessary, using the idea discussed upthread.
> * I tweaked other comments a little bit.
+1. The new patch looks good to me.
Thanks
Richard
^ permalink raw reply [nested|flat] 23+ messages in thread
* Re: foreign join error "variable not found in subplan target list"
2022-08-08 15:26 foreign join error "variable not found in subplan target list" Alexander Pyhalov <a.pyhalov@postgrespro.ru>
2022-08-09 01:59 ` Re: foreign join error "variable not found in subplan target list" Etsuro Fujita <etsuro.fujita@gmail.com>
2022-08-09 03:51 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
2022-08-09 11:54 ` Re: foreign join error "variable not found in subplan target list" Alexander Pyhalov <a.pyhalov@postgrespro.ru>
2022-08-10 02:15 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
2022-08-10 05:28 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
2022-08-21 12:31 ` Re: foreign join error "variable not found in subplan target list" Etsuro Fujita <etsuro.fujita@gmail.com>
2022-08-22 03:29 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
2022-08-29 06:18 ` Re: foreign join error "variable not found in subplan target list" Etsuro Fujita <etsuro.fujita@gmail.com>
2022-08-29 09:45 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
2022-08-30 07:50 ` Re: foreign join error "variable not found in subplan target list" Etsuro Fujita <etsuro.fujita@gmail.com>
2022-08-31 09:26 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
2022-08-31 09:52 ` Re: foreign join error "variable not found in subplan target list" Etsuro Fujita <etsuro.fujita@gmail.com>
2022-09-12 04:20 ` Re: foreign join error "variable not found in subplan target list" Etsuro Fujita <etsuro.fujita@gmail.com>
2022-09-13 02:28 ` Re: foreign join error "variable not found in subplan target list" Richard Guo <guofenglinux@gmail.com>
@ 2022-09-14 10:01 ` Etsuro Fujita <etsuro.fujita@gmail.com>
0 siblings, 0 replies; 23+ messages in thread
From: Etsuro Fujita @ 2022-09-14 10:01 UTC (permalink / raw)
To: Richard Guo <guofenglinux@gmail.com>; +Cc: Alexander Pyhalov <a.pyhalov@postgrespro.ru>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
On Tue, Sep 13, 2022 at 11:28 AM Richard Guo <guofenglinux@gmail.com> wrote:
> On Mon, Sep 12, 2022 at 12:19 PM Etsuro Fujita <etsuro.fujita@gmail.com> wrote:
>> Here is an updated patch for that.
> +1. The new patch looks good to me.
Pushed after tweaking comments a little bit further.
Thanks!
Best regards,
Etsuro Fujita
^ permalink raw reply [nested|flat] 23+ messages in thread
end of thread, other threads:[~2022-09-14 10:01 UTC | newest]
Thread overview: 23+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-08-08 15:26 foreign join error "variable not found in subplan target list" Alexander Pyhalov <a.pyhalov@postgrespro.ru>
2022-08-09 01:59 ` Etsuro Fujita <etsuro.fujita@gmail.com>
2022-08-09 03:51 ` Richard Guo <guofenglinux@gmail.com>
2022-08-09 11:54 ` Alexander Pyhalov <a.pyhalov@postgrespro.ru>
2022-08-10 02:15 ` Richard Guo <guofenglinux@gmail.com>
2022-08-10 05:28 ` Richard Guo <guofenglinux@gmail.com>
2022-08-10 07:06 ` Alexander Pyhalov <a.pyhalov@postgrespro.ru>
2022-08-10 08:36 ` Richard Guo <guofenglinux@gmail.com>
2022-08-10 15:16 ` Alexander Pyhalov <a.pyhalov@postgrespro.ru>
2022-08-12 03:09 ` Richard Guo <guofenglinux@gmail.com>
2022-08-21 12:31 ` Etsuro Fujita <etsuro.fujita@gmail.com>
2022-08-22 03:29 ` Richard Guo <guofenglinux@gmail.com>
2022-08-29 06:18 ` Etsuro Fujita <etsuro.fujita@gmail.com>
2022-08-29 09:45 ` Richard Guo <guofenglinux@gmail.com>
2022-08-30 01:18 ` Richard Guo <guofenglinux@gmail.com>
2022-08-30 02:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2022-08-30 02:21 ` Richard Guo <guofenglinux@gmail.com>
2022-08-30 07:50 ` Etsuro Fujita <etsuro.fujita@gmail.com>
2022-08-31 09:26 ` Richard Guo <guofenglinux@gmail.com>
2022-08-31 09:52 ` Etsuro Fujita <etsuro.fujita@gmail.com>
2022-09-12 04:20 ` Etsuro Fujita <etsuro.fujita@gmail.com>
2022-09-13 02:28 ` Richard Guo <guofenglinux@gmail.com>
2022-09-14 10:01 ` Etsuro Fujita <etsuro.fujita@gmail.com>
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