pg.ddx.io pgsql-hackers@postgresql.org mailing list archive
help / color / mirror / Atom feed"variable not found in subplan target list"
15+ messages / 5 participants
[nested] [flat]
Commits from this thread:
2026-10-21 456ee18045 Fix sorting a child rel by an expression containing a PlaceHolderVar
2023-05-04 5472743d9e Revert "Move PartitionPruneInfo out of plan nodes into PlannedStmt"
2023-03-30 589bb81649 Fix setrefs.c code for adjusting partPruneInfos
2023-03-28 bf5c4b3d9d 326a33a289 Fix corner-case planner failure for MERGE.
* "variable not found in subplan target list"
@ 2023-03-28 11:22 Alvaro Herrera <alvherre@alvh.no-ip.org>
2023-03-28 11:38 ` Re: "variable not found in subplan target list" Tom Lane <tgl@sss.pgh.pa.us>
2023-03-28 12:56 ` Re: "variable not found in subplan target list" Tom Lane <tgl@sss.pgh.pa.us>
2023-03-28 18:19 ` Re: "variable not found in subplan target list" Alvaro Herrera <alvherre@alvh.no-ip.org>
0 siblings, 3 replies; 15+ messages in thread
From: Alvaro Herrera @ 2023-03-28 11:22 UTC (permalink / raw)
To: Pg Hackers <pgsql-hackers@lists.postgresql.org>
I have to run now so can't dissect it, but while running sqlsmith on the
SQL/JSON patch after Justin's report, I got $SUBJECT in this query:
MERGE INTO public.target_parted as target_0
USING (select
subq_0.c5 as c0,
subq_0.c0 as c1,
ref_0.a as c2,
subq_0.c1 as c3,
subq_0.c9 as c4,
(select c from public.prt2_m_p3 limit 1 offset 1)
as c5,
subq_0.c8 as c6,
ref_0.a as c7,
subq_0.c7 as c8,
subq_0.c1 as c9,
pg_catalog.system_user() as c10
from
public.itest1 as ref_0
left join (select
ref_1.matches as c0,
ref_1.typ as c1,
ref_1.colname as c2,
(select slotname from public.iface limit 1 offset 44)
as c3,
ref_1.matches as c4,
ref_1.op as c5,
ref_1.matches as c6,
ref_1.value as c7,
ref_1.op as c8,
ref_1.op as c9,
ref_1.typ as c10
from
public.brinopers_multi as ref_1
where cast(null as polygon) <@ (select polygon from public.tab_core_types limit 1 offset 22)
) as subq_0
on (cast(null as macaddr8) >= cast(null as macaddr8))
where subq_0.c10 > subq_0.c2
limit 49) as subq_1
ON target_0.b = subq_1.c2
WHEN MATCHED
AND (cast(null as box) |>> cast(null as box))
or (cast(null as lseg) ?-| (select s from public.lseg_tbl limit 1 offset 6)
)
THEN DELETE
WHEN NOT MATCHED AND (EXISTS (
select
21 as c0,
subq_2.c0 as c1
from
public.itest14 as sample_0 tablesample system (3.6)
inner join public.num_exp_sqrt as sample_1 tablesample bernoulli (0.3)
on (cast(null as "char") <= cast(null as "char")),
lateral (select
sample_1.id as c0
from
public.a as ref_2
where (cast(null as lseg) <@ cast(null as line))
or ((select b3 from public.bit_defaults limit 1 offset 80)
<> (select b3 from public.bit_defaults limit 1 offset 4)
)
limit 158) as subq_2
where (cast(null as name) !~ (select t from public.test_tsvector limit 1 offset 5)
)
and ((select bool from public.tab_core_types limit 1 offset 61)
< (select pg_catalog.bool_or(v) from public.rtest_view1)
)))
or (18 is NULL)
THEN INSERT VALUES ( pg_catalog.int4um(
cast(public.func_with_bad_set() as int4)), 13)
WHEN MATCHED AND ((24 is not NULL)
or (true))
or (cast(null as "timestamp") <= cast(null as timestamptz))
THEN UPDATE set
b = target_0.b
Ugh.
I got no more SQL/JSON related crashes so far.
--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
"No hay ausente sin culpa ni presente sin disculpa" (Prov. francés)
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: "variable not found in subplan target list"
2023-03-28 11:22 "variable not found in subplan target list" Alvaro Herrera <alvherre@alvh.no-ip.org>
@ 2023-03-28 11:38 ` Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 15+ messages in thread
From: Tom Lane @ 2023-03-28 11:38 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@alvh.no-ip.org>; +Cc: Pg Hackers <pgsql-hackers@lists.postgresql.org>
Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> I have to run now so can't dissect it, but while running sqlsmith on the
> SQL/JSON patch after Justin's report, I got $SUBJECT in this query:
Reproduces in HEAD and v15 too (once you replace pg_catalog.system_user
with some function that exists in v15). So it's not the fault of the
JSON patch, nor of my outer-join hacking which had been my first thought.
regards, tom lane
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: "variable not found in subplan target list"
2023-03-28 11:22 "variable not found in subplan target list" Alvaro Herrera <alvherre@alvh.no-ip.org>
@ 2023-03-28 12:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-03-28 13:17 ` Re: "variable not found in subplan target list" Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 1 reply; 15+ messages in thread
From: Tom Lane @ 2023-03-28 12:56 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@alvh.no-ip.org>; +Cc: Pg Hackers <pgsql-hackers@lists.postgresql.org>
Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> I have to run now so can't dissect it, but while running sqlsmith on the
> SQL/JSON patch after Justin's report, I got $SUBJECT in this query:
I reduced this down to
MERGE INTO public.target_parted as target_0
USING public.itest1 as ref_0
ON target_0.b = ref_0.a
WHEN NOT MATCHED
THEN INSERT VALUES (42, 13);
The critical moving part seems to just be that the MERGE target
is a partitioned table ... but surely somebody tested that before?
regards, tom lane
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: "variable not found in subplan target list"
2023-03-28 11:22 "variable not found in subplan target list" Alvaro Herrera <alvherre@alvh.no-ip.org>
2023-03-28 12:56 ` Re: "variable not found in subplan target list" Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-03-28 13:17 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-03-28 14:46 ` Re: "variable not found in subplan target list" Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 15+ messages in thread
From: Tom Lane @ 2023-03-28 13:17 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@alvh.no-ip.org>; +Cc: Pg Hackers <pgsql-hackers@lists.postgresql.org>
I wrote:
> I reduced this down to
> MERGE INTO public.target_parted as target_0
> USING public.itest1 as ref_0
> ON target_0.b = ref_0.a
> WHEN NOT MATCHED
> THEN INSERT VALUES (42, 13);
> The critical moving part seems to just be that the MERGE target
> is a partitioned table ... but surely somebody tested that before?
Oh, it's not just any partitioned table:
regression=# \d+ target_parted
Partitioned table "public.target_parted"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
--------+---------+-----------+----------+---------+---------+-------------+--------------+-------------
a | integer | | | | plain | | |
b | integer | | | | plain | | |
Partition key: LIST (a)
Number of partitions: 0
The planner is reducing the scan of target_parted to
a dummy scan, as is reasonable, but it forgets to
provide ctid as an output from that scan; then the
parent join node is unhappy because it does have
a ctid output. So it looks like the problem is some
shortcut we take while creating the dummy scan.
I suppose that without the planner bug, this'd fail at
runtime for lack of a partition to put (42,13) into.
Because of that, the case isn't really interesting
for production, which may explain the lack of reports.
regards, tom lane
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: "variable not found in subplan target list"
2023-03-28 11:22 "variable not found in subplan target list" Alvaro Herrera <alvherre@alvh.no-ip.org>
2023-03-28 12:56 ` Re: "variable not found in subplan target list" Tom Lane <tgl@sss.pgh.pa.us>
2023-03-28 13:17 ` Re: "variable not found in subplan target list" Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-03-28 14:46 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 15+ messages in thread
From: Tom Lane @ 2023-03-28 14:46 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@alvh.no-ip.org>; +Cc: Pg Hackers <pgsql-hackers@lists.postgresql.org>
I wrote:
> The planner is reducing the scan of target_parted to
> a dummy scan, as is reasonable, but it forgets to
> provide ctid as an output from that scan; then the
> parent join node is unhappy because it does have
> a ctid output. So it looks like the problem is some
> shortcut we take while creating the dummy scan.
Oh, actually the problem is in distribute_row_identity_vars,
which is supposed to handle this case, but it thinks it doesn't
have to back-fill the rel's reltarget. Wrong. Now that I see
the problem, I wonder if we can't reproduce a similar symptom
without MERGE, which would mean that v14 has the issue too.
The attached seems to fix it, but I'm going to look for a
non-MERGE test case before pushing.
regards, tom lane
Attachments:
fix-missing-ctid-variable.patch (text/x-diff, 1.6K) download
diff --git a/src/backend/optimizer/util/appendinfo.c b/src/backend/optimizer/util/appendinfo.c
index 9d377385f1..c1b1557570 100644
--- a/src/backend/optimizer/util/appendinfo.c
+++ b/src/backend/optimizer/util/appendinfo.c
@@ -21,6 +21,7 @@
#include "nodes/nodeFuncs.h"
#include "optimizer/appendinfo.h"
#include "optimizer/pathnode.h"
+#include "optimizer/planmain.h"
#include "parser/parsetree.h"
#include "utils/lsyscache.h"
#include "utils/rel.h"
@@ -994,9 +995,10 @@ distribute_row_identity_vars(PlannerInfo *root)
* certainly process no rows. Handle this edge case by re-opening the top
* result relation and adding the row identity columns it would have used,
* as preprocess_targetlist() would have done if it weren't marked "inh".
- * (This is a bit ugly, but it seems better to confine the ugliness and
- * extra cycles to this unusual corner case.) We needn't worry about
- * fixing the rel's reltarget, as that won't affect the finished plan.
+ * Then re-run build_base_rel_tlists() to ensure that the added columns
+ * get propagated to the relation's reltarget. (This is a bit ugly, but
+ * it seems better to confine the ugliness and extra cycles to this
+ * unusual corner case.)
*/
if (root->row_identity_vars == NIL)
{
@@ -1006,6 +1008,8 @@ distribute_row_identity_vars(PlannerInfo *root)
add_row_identity_columns(root, result_relation,
target_rte, target_relation);
table_close(target_relation, NoLock);
+ build_base_rel_tlists(root, root->processed_tlist);
+ /* There are no ROWID_VAR Vars in this case, so we're done. */
return;
}
=
* Re: "variable not found in subplan target list"
2023-03-28 11:22 "variable not found in subplan target list" Alvaro Herrera <alvherre@alvh.no-ip.org>
@ 2023-03-28 18:19 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2023-03-28 18:39 ` Re: "variable not found in subplan target list" Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 1 reply; 15+ messages in thread
From: Alvaro Herrera @ 2023-03-28 18:19 UTC (permalink / raw)
To: Pg Hackers <pgsql-hackers@lists.postgresql.org>
So I'm back home and found a couple more weird errors in the log:
MERGE INTO public.idxpart2 as target_0
USING (select 1
from
public.xmltest2 as ref_0
inner join public.prt1_l_p1 as sample_0
inner join fkpart4.droppk as ref_1
on (sample_0.a = ref_1.a )
on (true)
limit 50) as subq_0
left join information_schema.transforms as ref_2
left join public.transition_table_status as sample_1
on (ref_2.transform_type is not NULL)
on (true)
ON target_0.a = sample_1.level
WHEN MATCHED
THEN UPDATE set a = target_0.a;
ERROR: mismatching PartitionPruneInfo found at part_prune_index 0
DETALLE: plan node relids (b 1), pruneinfo relids (b 36)
This one is probably my fault, will look later.
select
pg_catalog.pg_stat_get_buf_fsync_backend() as c9
from
public.tenk2 as ref_0
where (ref_0.stringu2 is NULL)
and (EXISTS (
select 1 from fkpart5.fk1 as ref_1
where pg_catalog.current_date() < (select pg_catalog.max(filler3) from public.mcv_lists))) ;
ERROR: subplan "InitPlan 1 (returns $1)" was not initialized
CONTEXTO: parallel worker
select 1 as c0
from
(select
subq_0.c9 as c5,
subq_0.c8 as c9
from
public.iso8859_5_inputs as ref_0,
lateral (select
ref_1.ident as c2,
ref_0.description as c8,
ref_1.used_bytes as c9
from
pg_catalog.pg_backend_memory_contexts as ref_1
where true
) as subq_0
where subq_0.c2 is not NULL) as subq_1
inner join pg_catalog.pg_class as sample_0
on (subq_1.c5 = public.int8alias1in(
cast(case when subq_1.c9 is not NULL then null end
as cstring)))
where true;
ERROR: could not find commutator for operator 53286
There were quite a few of those "variable not found" ones, both
mentioning singular "targetlist" and others that said "targetlists". I
reran them with your patch and they no longer error out, so I guess it's
all the same bug.
--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
"I must say, I am absolutely impressed with what pgsql's implementation of
VALUES allows me to do. It's kind of ridiculous how much "work" goes away in
my code. Too bad I can't do this at work (Oracle 8/9)." (Tom Allison)
http://archives.postgresql.org/pgsql-general/2007-06/msg00016.php
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: "variable not found in subplan target list"
2023-03-28 11:22 "variable not found in subplan target list" Alvaro Herrera <alvherre@alvh.no-ip.org>
2023-03-28 18:19 ` Re: "variable not found in subplan target list" Alvaro Herrera <alvherre@alvh.no-ip.org>
@ 2023-03-28 18:39 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-03-29 08:28 ` Re: "variable not found in subplan target list" Amit Langote <amitlangote09@gmail.com>
0 siblings, 1 reply; 15+ messages in thread
From: Tom Lane @ 2023-03-28 18:39 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@alvh.no-ip.org>; +Cc: Pg Hackers <pgsql-hackers@lists.postgresql.org>
Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> So I'm back home and found a couple more weird errors in the log:
> ERROR: mismatching PartitionPruneInfo found at part_prune_index 0
> DETALLE: plan node relids (b 1), pruneinfo relids (b 36)
This one reproduces for me.
> select
> pg_catalog.pg_stat_get_buf_fsync_backend() as c9
> from
> public.tenk2 as ref_0
> where (ref_0.stringu2 is NULL)
> and (EXISTS (
> select 1 from fkpart5.fk1 as ref_1
> where pg_catalog.current_date() < (select pg_catalog.max(filler3) from public.mcv_lists))) ;
> ERROR: subplan "InitPlan 1 (returns $1)" was not initialized
> CONTEXTO: parallel worker
Hmph, I couldn't reproduce that, not even with other settings of
debug_parallel_query. Are you running it with non-default
planner parameters?
> select 1 as c0
> ...
> ERROR: could not find commutator for operator 53286
I got a slightly different error:
ERROR: missing support function 1(195306,195306) in opfamily 1976
where
regression=# select 195306::regtype;
regtype
------------
int8alias1
(1 row)
So that one is related to the intentionally-somewhat-broken
int8 opclass configuration that equivclass.sql leaves behind.
I've always had mixed emotions about whether leaving that
set up that way was a good idea or not. In principle nothing
really bad should happen, but it can lead to confusing errors
like this one. Maybe it'd be better to roll that back?
regards, tom lane
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: "variable not found in subplan target list"
2023-03-28 11:22 "variable not found in subplan target list" Alvaro Herrera <alvherre@alvh.no-ip.org>
2023-03-28 18:19 ` Re: "variable not found in subplan target list" Alvaro Herrera <alvherre@alvh.no-ip.org>
2023-03-28 18:39 ` Re: "variable not found in subplan target list" Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-03-29 08:28 ` Amit Langote <amitlangote09@gmail.com>
2023-03-30 10:53 ` Re: "variable not found in subplan target list" Alvaro Herrera <alvherre@alvh.no-ip.org>
0 siblings, 1 reply; 15+ messages in thread
From: Amit Langote @ 2023-03-29 08:28 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>; Pg Hackers <pgsql-hackers@lists.postgresql.org>
On Wed, Mar 29, 2023 at 3:39 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> > So I'm back home and found a couple more weird errors in the log:
>
> > ERROR: mismatching PartitionPruneInfo found at part_prune_index 0
> > DETALLE: plan node relids (b 1), pruneinfo relids (b 36)
>
> This one reproduces for me.
I've looked into this one and the attached patch fixes it for me.
Turns out set_plan_refs()'s idea of when the entries from
PlannerInfo.partPruneInfos are transferred into
PlannerGlobal.partPruneInfo was wrong.
Though, I wonder if we need to keep ec386948948 that introduced the
notion of part_prune_index around if the project that needed it [1]
has moved on to an entirely different approach altogether, one that
doesn't require hacking up the pruning code.
--
Thanks, Amit Langote
EDB: http://www.enterprisedb.com
[1] https://commitfest.postgresql.org/42/3478/
Attachments:
fix-wrong-part_prune_index.patch (application/x-patch, 3.8K) download
diff --git a/src/backend/optimizer/plan/setrefs.c b/src/backend/optimizer/plan/setrefs.c
index 5cc8366af6..bd82960169 100644
--- a/src/backend/optimizer/plan/setrefs.c
+++ b/src/backend/optimizer/plan/setrefs.c
@@ -350,6 +350,29 @@ set_plan_references(PlannerInfo *root, Plan *plan)
palloc0(list_length(glob->subplans) * sizeof(bool));
}
+ /* Also fix up the information in PartitionPruneInfos. */
+ foreach(lc, root->partPruneInfos)
+ {
+ PartitionPruneInfo *pruneinfo = lfirst(lc);
+ ListCell *l;
+
+ pruneinfo->root_parent_relids =
+ offset_relid_set(pruneinfo->root_parent_relids, rtoffset);
+ foreach(l, pruneinfo->prune_infos)
+ {
+ List *prune_infos = lfirst(l);
+ ListCell *l2;
+
+ foreach(l2, prune_infos)
+ {
+ PartitionedRelPruneInfo *pinfo = lfirst(l2);
+
+ /* RT index of the table to which the pinfo belongs. */
+ pinfo->rtindex += rtoffset;
+ }
+ }
+ }
+
/* Now fix the Plan tree */
result = set_plan_refs(root, plan, rtoffset);
@@ -378,31 +401,6 @@ set_plan_references(PlannerInfo *root, Plan *plan)
}
}
- /* Also fix up the information in PartitionPruneInfos. */
- foreach(lc, root->partPruneInfos)
- {
- PartitionPruneInfo *pruneinfo = lfirst(lc);
- ListCell *l;
-
- pruneinfo->root_parent_relids =
- offset_relid_set(pruneinfo->root_parent_relids, rtoffset);
- foreach(l, pruneinfo->prune_infos)
- {
- List *prune_infos = lfirst(l);
- ListCell *l2;
-
- foreach(l2, prune_infos)
- {
- PartitionedRelPruneInfo *pinfo = lfirst(l2);
-
- /* RT index of the table to which the pinfo belongs. */
- pinfo->rtindex += rtoffset;
- }
- }
-
- glob->partPruneInfos = lappend(glob->partPruneInfos, pruneinfo);
- }
-
return result;
}
@@ -1718,6 +1716,28 @@ set_customscan_references(PlannerInfo *root,
cscan->custom_relids = offset_relid_set(cscan->custom_relids, rtoffset);
}
+/*
+ * fix_part_prune_index
+ * Adds the PartitionPruneInfo present in root->partPruneIndex at given
+ * index into the list in PlannerGlobal and returns the index in the new
+ * list.
+ */
+static int
+fix_part_prune_index(int part_prune_index, PlannerInfo *root)
+{
+ PlannerGlobal *glob = root->glob;
+ PartitionPruneInfo *pruneinfo;
+
+ Assert(part_prune_index >= 0 &&
+ part_prune_index < list_length(root->partPruneInfos));
+ pruneinfo = list_nth_node(PartitionPruneInfo, root->partPruneInfos,
+ part_prune_index);
+
+ glob->partPruneInfos = lappend(glob->partPruneInfos, pruneinfo);
+
+ return list_length(glob->partPruneInfos) - 1;
+}
+
/*
* set_append_references
* Do set_plan_references processing on an Append
@@ -1771,11 +1791,11 @@ set_append_references(PlannerInfo *root,
aplan->apprelids = offset_relid_set(aplan->apprelids, rtoffset);
/*
- * PartitionPruneInfos will be added to a list in PlannerGlobal, so update
- * the index.
+ * Add PartitionPruneInfo, if any, to PlannerGlobal and update the index.
*/
if (aplan->part_prune_index >= 0)
- aplan->part_prune_index += list_length(root->glob->partPruneInfos);
+ aplan->part_prune_index =
+ fix_part_prune_index(aplan->part_prune_index, root);
/* We don't need to recurse to lefttree or righttree ... */
Assert(aplan->plan.lefttree == NULL);
@@ -1838,11 +1858,11 @@ set_mergeappend_references(PlannerInfo *root,
mplan->apprelids = offset_relid_set(mplan->apprelids, rtoffset);
/*
- * PartitionPruneInfos will be added to a list in PlannerGlobal, so update
- * the index.
+ * Add PartitionPruneInfo, if any, to PlannerGlobal and update the index.
*/
if (mplan->part_prune_index >= 0)
- mplan->part_prune_index += list_length(root->glob->partPruneInfos);
+ mplan->part_prune_index =
+ fix_part_prune_index(mplan->part_prune_index, root);
/* We don't need to recurse to lefttree or righttree ... */
Assert(mplan->plan.lefttree == NULL);
* Re: "variable not found in subplan target list"
2023-03-28 11:22 "variable not found in subplan target list" Alvaro Herrera <alvherre@alvh.no-ip.org>
2023-03-28 18:19 ` Re: "variable not found in subplan target list" Alvaro Herrera <alvherre@alvh.no-ip.org>
2023-03-28 18:39 ` Re: "variable not found in subplan target list" Tom Lane <tgl@sss.pgh.pa.us>
2023-03-29 08:28 ` Re: "variable not found in subplan target list" Amit Langote <amitlangote09@gmail.com>
@ 2023-03-30 10:53 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2023-05-02 17:54 ` Re: "variable not found in subplan target list" Alvaro Herrera <alvherre@alvh.no-ip.org>
0 siblings, 1 reply; 15+ messages in thread
From: Alvaro Herrera @ 2023-03-30 10:53 UTC (permalink / raw)
To: Amit Langote <amitlangote09@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Pg Hackers <pgsql-hackers@lists.postgresql.org>
On 2023-Mar-29, Amit Langote wrote:
> On Wed, Mar 29, 2023 at 3:39 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> > > So I'm back home and found a couple more weird errors in the log:
> >
> > > ERROR: mismatching PartitionPruneInfo found at part_prune_index 0
> > > DETALLE: plan node relids (b 1), pruneinfo relids (b 36)
> >
> > This one reproduces for me.
>
> I've looked into this one and the attached patch fixes it for me.
> Turns out set_plan_refs()'s idea of when the entries from
> PlannerInfo.partPruneInfos are transferred into
> PlannerGlobal.partPruneInfo was wrong.
Thanks for the patch. I've pushed it to github for CI testing, and if
there are no problems I'll put it in.
> Though, I wonder if we need to keep ec386948948 that introduced the
> notion of part_prune_index around if the project that needed it [1]
> has moved on to an entirely different approach altogether, one that
> doesn't require hacking up the pruning code.
Hmm, that's indeed tempting.
--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: "variable not found in subplan target list"
2023-03-28 11:22 "variable not found in subplan target list" Alvaro Herrera <alvherre@alvh.no-ip.org>
2023-03-28 18:19 ` Re: "variable not found in subplan target list" Alvaro Herrera <alvherre@alvh.no-ip.org>
2023-03-28 18:39 ` Re: "variable not found in subplan target list" Tom Lane <tgl@sss.pgh.pa.us>
2023-03-29 08:28 ` Re: "variable not found in subplan target list" Amit Langote <amitlangote09@gmail.com>
2023-03-30 10:53 ` Re: "variable not found in subplan target list" Alvaro Herrera <alvherre@alvh.no-ip.org>
@ 2023-05-02 17:54 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2023-05-04 10:44 ` Re: "variable not found in subplan target list" Alvaro Herrera <alvherre@alvh.no-ip.org>
0 siblings, 1 reply; 15+ messages in thread
From: Alvaro Herrera @ 2023-05-02 17:54 UTC (permalink / raw)
To: Amit Langote <amitlangote09@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Pg Hackers <pgsql-hackers@lists.postgresql.org>
Hi Amit,
On 2023-Mar-30, Alvaro Herrera wrote:
> On 2023-Mar-29, Amit Langote wrote:
> > Though, I wonder if we need to keep ec386948948 that introduced the
> > notion of part_prune_index around if the project that needed it [1]
> > has moved on to an entirely different approach altogether, one that
> > doesn't require hacking up the pruning code.
>
> Hmm, that's indeed tempting.
We have an open item about this, and I see no reason not to do it. I
checked, and putting things back is just a matter of reverting
589bb816499e and ec386948948, cleaning up some trivial pgindent-induced
conflicts, and bumping catversion once more. Would you like to do that
yourself, or do you prefer that I do it? Ideally, we'd do it before
beta1.
--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: "variable not found in subplan target list"
2023-03-28 11:22 "variable not found in subplan target list" Alvaro Herrera <alvherre@alvh.no-ip.org>
2023-03-28 18:19 ` Re: "variable not found in subplan target list" Alvaro Herrera <alvherre@alvh.no-ip.org>
2023-03-28 18:39 ` Re: "variable not found in subplan target list" Tom Lane <tgl@sss.pgh.pa.us>
2023-03-29 08:28 ` Re: "variable not found in subplan target list" Amit Langote <amitlangote09@gmail.com>
2023-03-30 10:53 ` Re: "variable not found in subplan target list" Alvaro Herrera <alvherre@alvh.no-ip.org>
2023-05-02 17:54 ` Re: "variable not found in subplan target list" Alvaro Herrera <alvherre@alvh.no-ip.org>
@ 2023-05-04 10:44 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2023-05-04 13:55 ` Re: "variable not found in subplan target list" Amit Langote <amitlangote09@gmail.com>
0 siblings, 1 reply; 15+ messages in thread
From: Alvaro Herrera @ 2023-05-04 10:44 UTC (permalink / raw)
To: Amit Langote <amitlangote09@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Pg Hackers <pgsql-hackers@lists.postgresql.org>
On 2023-May-02, Alvaro Herrera wrote:
> We have an open item about this, and I see no reason not to do it. I
> checked, and putting things back is just a matter of reverting
> 589bb816499e and ec386948948, cleaning up some trivial pgindent-induced
> conflicts, and bumping catversion once more. Would you like to do that
> yourself, or do you prefer that I do it? Ideally, we'd do it before
> beta1.
I have pushed the revert now.
--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: "variable not found in subplan target list"
2023-03-28 11:22 "variable not found in subplan target list" Alvaro Herrera <alvherre@alvh.no-ip.org>
2023-03-28 18:19 ` Re: "variable not found in subplan target list" Alvaro Herrera <alvherre@alvh.no-ip.org>
2023-03-28 18:39 ` Re: "variable not found in subplan target list" Tom Lane <tgl@sss.pgh.pa.us>
2023-03-29 08:28 ` Re: "variable not found in subplan target list" Amit Langote <amitlangote09@gmail.com>
2023-03-30 10:53 ` Re: "variable not found in subplan target list" Alvaro Herrera <alvherre@alvh.no-ip.org>
2023-05-02 17:54 ` Re: "variable not found in subplan target list" Alvaro Herrera <alvherre@alvh.no-ip.org>
2023-05-04 10:44 ` Re: "variable not found in subplan target list" Alvaro Herrera <alvherre@alvh.no-ip.org>
@ 2023-05-04 13:55 ` Amit Langote <amitlangote09@gmail.com>
0 siblings, 0 replies; 15+ messages in thread
From: Amit Langote @ 2023-05-04 13:55 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@alvh.no-ip.org>; +Cc: Pg Hackers <pgsql-hackers@lists.postgresql.org>; Tom Lane <tgl@sss.pgh.pa.us>
Hi Alvaro,
On Thu, May 4, 2023 at 19:44 Alvaro Herrera <alvherre@alvh.no-ip.org> wrote:
> On 2023-May-02, Alvaro Herrera wrote:
>
> > We have an open item about this, and I see no reason not to do it. I
> > checked, and putting things back is just a matter of reverting
> > 589bb816499e and ec386948948, cleaning up some trivial pgindent-induced
> > conflicts, and bumping catversion once more. Would you like to do that
> > yourself, or do you prefer that I do it? Ideally, we'd do it before
> > beta1.
>
> I have pushed the revert now.
Thanks for taking care of it.
(Wouldn’t have been able to get to it till Monday myself.)
> --
Thanks, Amit Langote
EDB: http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 15+ messages in thread
* "variable not found in subplan target lists" with partitionwise join
@ 2026-10-19 06:56 Richard Guo <guofenglinux@gmail.com>
2026-10-19 08:14 ` Re: "variable not found in subplan target lists" with partitionwise join wenhui qiu <qiuwenhuifx@gmail.com>
0 siblings, 1 reply; 15+ messages in thread
From: Richard Guo @ 2026-10-19 06:56 UTC (permalink / raw)
To: Pg Hackers <pgsql-hackers@lists.postgresql.org>
This can be reproduced with the query below in v14 to master.
create table pt (a int, b int) partition by list (a);
create table pt1 partition of pt for values in (1);
create table pt2 partition of pt for values in (2);
create index on pt (a);
create index on pt (b);
create table d (a int);
set enable_partitionwise_join = on;
select s.x + 1 from d
left join (select t1.b, coalesce(t2.b, t1.b) as x
from pt t1 join pt t2 on t1.a = t2.a) s on true
where s.b = s.x + 1 order by 1;
ERROR: variable not found in subplan target lists
In this query, the coalesce() is wrapped in a PHV, and we get a
MergeAppend over the partitionwise child joins, which needs each child
to emit "PHV + 1" as the sort column. The child join's tlist only has
the translated PHV, so prepare_sort_from_pathkeys() calls
find_computable_ec_member() to look for an EC member that can be
computed from that tlist.
find_computable_ec_member() ignores child members that do not belong
to the given relids, but nothing prevents it from choosing a parent
member. Usually this is fine, because the parent member's Vars cannot
appear in the child's tlist. But PHVs are different. equal()
compares PHVs only by phid, so the parent PHV is considered to match
the translated PHV in the child's tlist. So we choose the parent
member, and add the untranslated parent expression to the child join's
tlist. Then setrefs.c cannot find the parent Vars inside it, and we
get this error.
I think the fix is to also ignore parent members that do not belong to
the given relids, if relids is given. This should not change anything
for non-child rels, because any member that is computable from the
rel's tlist must belong to that rel anyway. If relids is NULL, we
still only filter child members, as before.
Attached is a patch for this fix.
- Richard
Attachments:
v1-0001-Fix-sorting-a-child-rel-by-an-expression-containi.patch (application/octet-stream, 8.0K) download
From cb70a2df99bb4286b11f0a2a22511ce92a450f1c Mon Sep 17 00:00:00 2001
From: Richard Guo <guofenglinux@gmail.com>
Date: Mon, 19 Oct 2026 11:41:20 +0900
Subject: [PATCH v1] Fix sorting a child rel by an expression containing a
PlaceHolderVar
When building sort keys for a child relation, such as the per-child
sort columns of a MergeAppend over partitionwise child joins,
prepare_sort_from_pathkeys() may need to compute an EquivalenceClass
member from the child's targetlist. find_computable_ec_member()
accepts a member if all the Vars and quasi-Vars it contains appear in
that targetlist. It ignores child members that don't belong to the
given relids, but it can still choose a parent member. Usually that
is harmless, since a parent member's Vars never appear in a child's
targetlist. PlaceHolderVars are different: equal() compares them only
by phid, ignoring phexpr and phrels, so a parent PlaceHolderVar
matches its translated copy in the child's targetlist. As a result,
an untranslated parent expression could be added to the child plan's
targetlist, leading to "variable not found in subplan target lists"
errors from setrefs.c.
To fix, when relids is given, also ignore parent members that don't
belong to it. This changes nothing for a non-child relation, because
any member computable from its targetlist belongs to it anyway. When
relids is NULL, only child members are filtered, as before.
Also update the comments for prepare_sort_from_pathkeys() to reflect
that 'relids' can identify a non-child relation being sorted, which
has long been the case.
Back-patch to all supported branches.
---
src/backend/optimizer/path/equivclass.c | 11 +++--
src/backend/optimizer/plan/createplan.c | 6 ++-
src/test/regress/expected/partition_join.out | 44 ++++++++++++++++++++
src/test/regress/sql/partition_join.sql | 11 +++++
4 files changed, 67 insertions(+), 5 deletions(-)
diff --git a/src/backend/optimizer/path/equivclass.c b/src/backend/optimizer/path/equivclass.c
index 553d9d13dc5..e6c4c9cc52f 100644
--- a/src/backend/optimizer/path/equivclass.c
+++ b/src/backend/optimizer/path/equivclass.c
@@ -982,7 +982,8 @@ find_ec_member_matching_expr(EquivalenceClass *ec,
* compute an expression in this way, setrefs.c is going to insist on exact
* matches of Vars to the source tlist.
*
- * Child EC members are ignored unless they belong to given 'relids'.
+ * If 'relids' is given, EC members are ignored unless they belong to it. If
+ * 'relids' is NULL, all child members are ignored.
* Also, non-parallel-safe expressions are ignored if 'require_parallel_safe'.
*
* Note: some callers pass root == NULL for notational reasons. This is OK
@@ -1024,9 +1025,13 @@ find_computable_ec_member(PlannerInfo *root,
continue;
/*
- * Ignore child members unless they belong to the requested rel.
+ * Ignore child members unless they belong to the requested rel. If
+ * 'relids' is given, ignore parent members that don't belong to it
+ * either: for a child rel, a parent member's PlaceHolderVars would
+ * otherwise match the translated ones in "exprs", since equal()
+ * compares PlaceHolderVars only by phid.
*/
- if (em->em_is_child &&
+ if ((em->em_is_child || relids != NULL) &&
!bms_is_subset(em->em_relids, relids))
continue;
diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c
index 2f8a08ad78e..282435788b6 100644
--- a/src/backend/optimizer/plan/createplan.c
+++ b/src/backend/optimizer/plan/createplan.c
@@ -6176,7 +6176,7 @@ make_incrementalsort(Plan *lefttree, int numCols, int nPresortedCols,
* Input parameters:
* 'lefttree' is the plan node which yields input tuples
* 'pathkeys' is the list of pathkeys by which the result is to be sorted
- * 'relids' identifies the child relation being sorted, if any
+ * 'relids' identifies the relation being sorted, or is NULL
* 'reqColIdx' is NULL or an array of required sort key column numbers
* 'adjust_tlist_in_place' is true if lefttree must be modified in-place
*
@@ -6188,7 +6188,9 @@ make_incrementalsort(Plan *lefttree, int numCols, int nPresortedCols,
* When looking for matches to an EquivalenceClass's members, we will only
* consider child EC members if they belong to given 'relids'. This protects
* against possible incorrect matches to child expressions that contain no
- * Vars.
+ * Vars. When computing a sort expression for a child relation, we will not
+ * use parent EC members either, since their PlaceHolderVars could falsely
+ * match the translated ones in the child's tlist.
*
* If reqColIdx isn't NULL then it contains sort key column numbers that
* we should match. This is used when making child plans for a MergeAppend;
diff --git a/src/test/regress/expected/partition_join.out b/src/test/regress/expected/partition_join.out
index a7e26afa793..ad051a92a5e 100644
--- a/src/test/regress/expected/partition_join.out
+++ b/src/test/regress/expected/partition_join.out
@@ -829,6 +829,50 @@ SELECT t1.a, t1.c, t2.a, t2.c FROM prt4 t1 LEFT JOIN
RESET enable_hashjoin;
RESET enable_mergejoin;
+-- bug with sorting a child join by an expression containing a PlaceHolderVar
+SET enable_seqscan TO false;
+EXPLAIN (COSTS OFF)
+SELECT s.x + 0 FROM prt1 t0 LEFT JOIN
+ (SELECT t1.a, COALESCE(t2.a, t1.b) AS x FROM prt1 t1 JOIN prt2 t2 ON t1.a = t2.b) s
+ ON true WHERE s.a = s.x + 0 AND t0.a = 0 ORDER BY 1;
+ QUERY PLAN
+----------------------------------------------------------------------
+ Nested Loop
+ -> Merge Append
+ Sort Key: ((COALESCE(t2.a, t1.b) + 0))
+ -> Merge Join
+ Merge Cond: (t1_1.a = t2_1.b)
+ Join Filter: (t1_1.a = (COALESCE(t2_1.a, t1_1.b) + 0))
+ -> Index Scan using iprt1_p1_a on prt1_p1 t1_1
+ -> Index Scan using iprt2_p1_b on prt2_p1 t2_1
+ -> Merge Join
+ Merge Cond: (t1_2.a = t2_2.b)
+ Join Filter: (t1_2.a = (COALESCE(t2_2.a, t1_2.b) + 0))
+ -> Index Scan using iprt1_p2_a on prt1_p2 t1_2
+ -> Index Scan using iprt2_p2_b on prt2_p2 t2_2
+ -> Nested Loop
+ Join Filter: (t1_3.a = (COALESCE(t2_3.a, t1_3.b) + 0))
+ -> Index Scan using iprt2_p3_b on prt2_p3 t2_3
+ -> Index Scan using iprt1_p3_a on prt1_p3 t1_3
+ Index Cond: (a = t2_3.b)
+ -> Materialize
+ -> Index Only Scan using iprt1_p1_a on prt1_p1 t0
+ Index Cond: (a = 0)
+(21 rows)
+
+SELECT s.x + 0 FROM prt1 t0 LEFT JOIN
+ (SELECT t1.a, COALESCE(t2.a, t1.b) AS x FROM prt1 t1 JOIN prt2 t2 ON t1.a = t2.b) s
+ ON true WHERE s.a = s.x + 0 AND t0.a = 0 ORDER BY 1;
+ ?column?
+----------
+ 0
+ 6
+ 12
+ 18
+ 24
+(5 rows)
+
+RESET enable_seqscan;
-- bug in freeing the SpecialJoinInfo of a child-join
EXPLAIN (COSTS OFF)
SELECT * FROM prt1 t1 JOIN prt1 t2 ON t1.a = t2.a WHERE t1.a IN (SELECT a FROM prt1 t3);
diff --git a/src/test/regress/sql/partition_join.sql b/src/test/regress/sql/partition_join.sql
index 3dc3e0bbc93..31b0e8a455c 100644
--- a/src/test/regress/sql/partition_join.sql
+++ b/src/test/regress/sql/partition_join.sql
@@ -191,6 +191,17 @@ SELECT t1.a, t1.c, t2.a, t2.c FROM prt4 t1 LEFT JOIN
RESET enable_hashjoin;
RESET enable_mergejoin;
+-- bug with sorting a child join by an expression containing a PlaceHolderVar
+SET enable_seqscan TO false;
+EXPLAIN (COSTS OFF)
+SELECT s.x + 0 FROM prt1 t0 LEFT JOIN
+ (SELECT t1.a, COALESCE(t2.a, t1.b) AS x FROM prt1 t1 JOIN prt2 t2 ON t1.a = t2.b) s
+ ON true WHERE s.a = s.x + 0 AND t0.a = 0 ORDER BY 1;
+SELECT s.x + 0 FROM prt1 t0 LEFT JOIN
+ (SELECT t1.a, COALESCE(t2.a, t1.b) AS x FROM prt1 t1 JOIN prt2 t2 ON t1.a = t2.b) s
+ ON true WHERE s.a = s.x + 0 AND t0.a = 0 ORDER BY 1;
+RESET enable_seqscan;
+
-- bug in freeing the SpecialJoinInfo of a child-join
EXPLAIN (COSTS OFF)
SELECT * FROM prt1 t1 JOIN prt1 t2 ON t1.a = t2.a WHERE t1.a IN (SELECT a FROM prt1 t3);
--
2.37.1 (Apple Git-137.1)
* Re: "variable not found in subplan target lists" with partitionwise join
2026-10-19 06:56 "variable not found in subplan target lists" with partitionwise join Richard Guo <guofenglinux@gmail.com>
@ 2026-10-19 08:14 ` wenhui qiu <qiuwenhuifx@gmail.com>
2026-10-21 09:16 ` Re: "variable not found in subplan target lists" with partitionwise join Richard Guo <guofenglinux@gmail.com>
0 siblings, 1 reply; 15+ messages in thread
From: wenhui qiu @ 2026-10-19 08:14 UTC (permalink / raw)
To: Richard Guo <guofenglinux@gmail.com>; +Cc: Pg Hackers <pgsql-hackers@lists.postgresql.org>
Hi Richard,
The fix looks reasonable to me. Checking em_relids for parent members
prevents an untranslated parent expression from being selected for a child
relation, while preserving the existing behavior when relids is NULL. I
reproduced the reported error on a local 20devel build based on 1863452.
After applying the core change and adding the regression case, the query
produces the expected plan and results, and the partition_join regression
test passes. I haven't tested the back branches.
One minor wording suggestion: the new comment says that equal() compares
PlaceHolderVars “only by phid”, but phlevelsup and phnullingrels are also
compared. The relevant point here is that phexpr and phrels are ignored, so
perhaps:
* otherwise match the translated ones in "exprs", since equal()
* ignores phexpr and phrels when comparing PlaceHolderVars.
What do you think?
Thanks!
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: "variable not found in subplan target lists" with partitionwise join
2026-10-19 06:56 "variable not found in subplan target lists" with partitionwise join Richard Guo <guofenglinux@gmail.com>
2026-10-19 08:14 ` Re: "variable not found in subplan target lists" with partitionwise join wenhui qiu <qiuwenhuifx@gmail.com>
@ 2026-10-21 09:16 ` Richard Guo <guofenglinux@gmail.com>
0 siblings, 0 replies; 15+ messages in thread
From: Richard Guo @ 2026-10-21 09:16 UTC (permalink / raw)
To: wenhui qiu <qiuwenhuifx@gmail.com>; +Cc: Pg Hackers <pgsql-hackers@lists.postgresql.org>
On Mon, Oct 19, 2026 at 5:14 PM wenhui qiu <qiuwenhuifx@gmail.com> wrote:
> One minor wording suggestion: the new comment says that equal() compares PlaceHolderVars “only by phid”, but phlevelsup and phnullingrels are also compared. The relevant point here is that phexpr and phrels are ignored, so perhaps:
> * otherwise match the translated ones in "exprs", since equal()
> * ignores phexpr and phrels when comparing PlaceHolderVars.
> What do you think?
Thanks! I've updated the comment, and then pushed and back-patched
it to v14.
- Richard
^ permalink raw reply [nested|flat] 15+ messages in thread
end of thread, other threads:[~2026-10-21 09:16 UTC | newest]
Thread overview: 15+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-03-28 11:22 "variable not found in subplan target list" Alvaro Herrera <alvherre@alvh.no-ip.org>
2023-03-28 11:38 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-03-28 12:56 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-03-28 13:17 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-03-28 14:46 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-03-28 18:19 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2023-03-28 18:39 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-03-29 08:28 ` Amit Langote <amitlangote09@gmail.com>
2023-03-30 10:53 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2023-05-02 17:54 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2023-05-04 10:44 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2023-05-04 13:55 ` Amit Langote <amitlangote09@gmail.com>
2026-10-19 06:56 "variable not found in subplan target lists" with partitionwise join Richard Guo <guofenglinux@gmail.com>
2026-10-19 08:14 ` Re: "variable not found in subplan target lists" with partitionwise join wenhui qiu <qiuwenhuifx@gmail.com>
2026-10-21 09:16 ` Re: "variable not found in subplan target lists" with partitionwise join Richard Guo <guofenglinux@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