pg.ddx.io pgsql-hackers@postgresql.org mailing list archive help / color / mirror / Atom feed From: Andrei Lepikhov <a.lepikhov@postgrespro.ru>
To: Alexander Lakhin <exclusion@gmail.com>
To: Alexander Korotkov <aekorotkov@gmail.com>
Cc: Gregory Stark (as CFM) <stark.cfm@gmail.com>
Cc: Michał Kłeczek <michal@kleczek.org>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Cc: Richard Guo <guofenglinux@gmail.com>
Subject: Re: Removing unneeded self joins
Date: Fri, 29 Dec 2023 16:58:11 +0700
Message-ID: <0c5f461f-e26f-4fd9-9700-91b0e9c38fd6@postgrespro.ru> (raw )
In-Reply-To: <a89f480f-8143-0965-f22d-0a892777f501@gmail.com >
References: <64486b0b-0404-e39e-322d-0801154901f3@postgrespro.ru >
<05469e80-454b-41d7-d04f-421f42bbd4a9@postgrespro.ru >
<CAPpHfdt9G3EU+vXuqexhweEQoMF-acPUUFuB6hV2Kg0gCy7AjA@mail.gmail.com >
<ebe66358-428c-439f-85dd-da72c9b2b0c2@postgrespro.ru >
<CAPpHfdt-0kVV7O==aJEbjY2iGYBu+XBzTHEbPv_6sVNeC7fffQ@mail.gmail.com >
<48c11b23-9c23-440e-9a3c-020e05196449@postgrespro.ru >
<CAPpHfdsQkY0Gu7bQ9Ekdyzo_5Xxz74TYKbwmk1on1WAASpxXcQ@mail.gmail.com >
<8800ccf2-ae82-461c-92a1-d4c0a22f8cf2@postgrespro.ru >
<CAPpHfdvJ3jBQaJQKrf69K++kXi+Tvzn6TXO-DKXj0KVJjvMebg@mail.gmail.com >
<1bd80083-a13e-4e9f-a7ce-73c3faab521c@postgrespro.ru >
<CAPpHfdsz8E085YF2VoJe2+h4YAShsh7p--t3kQfQTntiCy63Xg@mail.gmail.com >
<922623af-872e-4cdc-9238-129b18bee42d@postgrespro.ru >
<CAPpHfduoAHTp6O7Q8PDe_w=30v0n_06pGe5M65iR4DYcfaYnRw@mail.gmail.com >
<107682e0-137a-d0fb-2733-7fc6e9562690@gmail.com >
<CAPpHfduLxYm4biJrTbjBxTAW6vkxBswuQ2B=gXU+c37QJd6+Ow@mail.gmail.com >
<a89f480f-8143-0965-f22d-0a892777f501@gmail.com >
On 29/12/2023 12:00, Alexander Lakhin wrote:
> Hi Alexander,
>
> 23.10.2023 14:29, Alexander Korotkov wrote:
>> Fixed all of the above. Thank you for catching this!
>
> I've discovered that starting from d3d55ce57 the following query:
> CREATE TABLE t(a int PRIMARY KEY);
>
> WITH tt AS (SELECT * FROM t)
> UPDATE t SET a = tt.a + 1 FROM tt
> WHERE tt.a = t.a RETURNING t.a;
>
> triggers an error "variable not found in subplan target lists".
> (Commits 8a8ed916f and b5fb6736e don't fix this, unfortunately.)
Thanks for the report!
The problem is with the resultRelation field. We forget to replace the
relid here.
Could you check your issue with the patch in the attachment? Does it
resolve this case?
--
regards,
Andrei Lepikhov
Postgres Professional
Attachments:
quickfix.diff (text/plain, 1.1K) download
diff --git a/src/backend/optimizer/plan/analyzejoins.c b/src/backend/optimizer/plan/analyzejoins.c
index 6c02fe8908..f79c673afd 100644
--- a/src/backend/optimizer/plan/analyzejoins.c
+++ b/src/backend/optimizer/plan/analyzejoins.c
@@ -1861,6 +1861,8 @@ remove_self_join_rel(PlannerInfo *root, PlanRowMark *kmark, PlanRowMark *rmark,
/* Replace varno in all the query structures */
query_tree_walker(root->parse, replace_varno_walker, &ctx,
QTW_EXAMINE_SORTGROUP);
+ if (root->parse->resultRelation == toRemove->relid)
+ root->parse->resultRelation = toKeep->relid;
/* Replace links in the planner info */
remove_rel_from_query(root, toRemove, toKeep->relid, NULL, NULL);
@@ -1870,6 +1872,9 @@ remove_self_join_rel(PlannerInfo *root, PlanRowMark *kmark, PlanRowMark *rmark,
toRemove->relid, toKeep->relid);
replace_varno((Node *) root->processed_groupClause,
toRemove->relid, toKeep->relid);
+ replace_relid(root->all_result_relids, toRemove->relid, toKeep->relid);
+ replace_relid(root->leaf_result_relids, toRemove->relid, toKeep->relid);
+
/*
* There may be references to the rel in root->fkey_list, but if so,
view thread (247+ messages) latest in thread
Message-ID: <0c5f461f-e26f-4fd9-9700-91b0e9c38fd6@postgrespro.ru>
Permalink: ../0c5f461f-e26f-4fd9-9700-91b0e9c38fd6@postgrespro.ru/
Also on: postgresql.org/message-id/0c5f461f-e26f-4fd9-9700-91b0e9c38fd6@postgrespro.ru
copy link · copy postgr.es · copy git am
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: a.lepikhov@postgrespro.ru, exclusion@gmail.com, aekorotkov@gmail.com, stark.cfm@gmail.com, michal@kleczek.org, pgsql-hackers@lists.postgresql.org, guofenglinux@gmail.com
Subject: Re: Removing unneeded self joins
In-Reply-To: <0c5f461f-e26f-4fd9-9700-91b0e9c38fd6@postgrespro.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox