public inbox for [email protected]
help / color / mirror / Atom feedrewriteGraphTable: Fix missing RTEs in FROM clause by setting inFromCl=true
2+ messages / 1 participants
[nested] [flat]
* rewriteGraphTable: Fix missing RTEs in FROM clause by setting inFromCl=true
@ 2026-03-20 14:15 =?ISO-8859-1?B?emVuZ21hbg==?= <[email protected]>
2026-03-24 04:13 ` Re:rewriteGraphTable: Fix missing RTEs in FROM clause by setting inFromCl=true =?ISO-8859-1?B?emVuZ21hbg==?= <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: =?ISO-8859-1?B?emVuZ21hbg==?= @ 2026-03-20 14:15 UTC (permalink / raw)
To: =?ISO-8859-1?B?YXNzYW0yNTg=?= <[email protected]>; +Cc: pgsql-hackers; =?ISO-8859-1?B?SnVud2FuZyBaaGFv?= <[email protected]>; =?ISO-8859-1?B?QXNodXRvc2ggQmFwYXQ=?= <[email protected]>; =?ISO-8859-1?B?UGV0ZXIgRWlzZW50cmF1dA==?= <[email protected]>
> > Would you consider adding this to the commitfest?
>
> Hi Henson,
>
> Thank you for reminding me.
> ```
> https://commitfest.postgresql.org/patch/6602/
> ```
> I discovered that due to my incorrect operation, the two themes were merged together, which is quite embarrassing.
Hi,
Sorry to bother everyone, I forgot to update the pg_overexplain.out file in version 0001.
Also, I'm starting a new topic here to avoid interfering with testing on another topic.
```
https://www.postgresql.org/message-id/flat/tencent_40CF78D67AF8FCFA50C453A7%40qq.com#7cace1a418dcbab...
```
--
regards,
Man Zeng
Attachments:
[application/octet-stream] 0002-Correcting-the-value-of-rte-inFromCl-in-SQL-PGQ.patch (2.1K, 2-0002-Correcting-the-value-of-rte-inFromCl-in-SQL-PGQ.patch)
download | inline diff:
From c4ec73409f09aebdc43c2a9820792ad432be0557 Mon Sep 17 00:00:00 2001
From: Man Zeng <[email protected]>
Date: Fri, 20 Mar 2026 21:59:25 +0800
Subject: [PATCH] Correcting the value of rte->inFromCl in (SQL/PGQ)
Setting it to false in the commit statement 2f094e7ac691abc9d2fe0f4dcf0feac4a6ce1d9c is incorrect.
It should actually be set to true to ensure an equivalent SQL statement is obtained.
---
contrib/pg_overexplain/expected/pg_overexplain.out | 4 ++--
src/backend/rewrite/rewriteGraphTable.c | 2 +-
2 files changed, 3 insertions(+), 3 deletions(-)
diff --git a/contrib/pg_overexplain/expected/pg_overexplain.out b/contrib/pg_overexplain/expected/pg_overexplain.out
index 05c6686d677..f4ad26df1ae 100644
--- a/contrib/pg_overexplain/expected/pg_overexplain.out
+++ b/contrib/pg_overexplain/expected/pg_overexplain.out
@@ -649,14 +649,14 @@ SELECT * FROM GRAPH_TABLE (vegetables_graph MATCH (v1 IS vegetables) WHERE v1.ge
RTI 3 (subquery):
Eref: unnamed_subquery (name)
Lateral: true
- RTI 4 (relation):
+ RTI 4 (relation, in-from-clause):
Subplan: unnamed_subquery
Eref: daucus (id, name, genus)
Relation: daucus
Relation Kind: relation
Relation Lock Mode: AccessShareLock
Permission Info Index: 2
- RTI 5 (relation):
+ RTI 5 (relation, in-from-clause):
Subplan: unnamed_subquery_1
Eref: brassica (id, name, genus)
Relation: brassica
diff --git a/src/backend/rewrite/rewriteGraphTable.c b/src/backend/rewrite/rewriteGraphTable.c
index 06f2f3442d8..d43704ef233 100644
--- a/src/backend/rewrite/rewriteGraphTable.c
+++ b/src/backend/rewrite/rewriteGraphTable.c
@@ -498,7 +498,7 @@ generate_query_for_graph_path(RangeTblEntry *rte, List *graph_path)
*/
rel = table_open(pe->reloid, AccessShareLock);
pni = addRangeTableEntryForRelation(make_parsestate(NULL), rel, AccessShareLock,
- NULL, true, false);
+ NULL, true, true);
table_close(rel, NoLock);
path_query->rtable = lappend(path_query->rtable, pni->p_rte);
path_query->rteperminfos = lappend(path_query->rteperminfos, pni->p_perminfo);
--
2.45.2
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re:rewriteGraphTable: Fix missing RTEs in FROM clause by setting inFromCl=true
2026-03-20 14:15 rewriteGraphTable: Fix missing RTEs in FROM clause by setting inFromCl=true =?ISO-8859-1?B?emVuZ21hbg==?= <[email protected]>
@ 2026-03-24 04:13 ` =?ISO-8859-1?B?emVuZ21hbg==?= <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: =?ISO-8859-1?B?emVuZ21hbg==?= @ 2026-03-24 04:13 UTC (permalink / raw)
To: =?ISO-8859-1?B?QXNodXRvc2ggQmFwYXQ=?= <[email protected]>; +Cc: pgsql-hackers; =?ISO-8859-1?B?SnVud2FuZyBaaGFv?= <[email protected]>; =?ISO-8859-1?B?UGV0ZXIgRWlzZW50cmF1dA==?= <[email protected]>; =?ISO-8859-1?B?YXNzYW0yNTg=?= <[email protected]>
> I would look at the code which adds subqueries corresponding to the
> views when rewriting queries. Do these subquery RTEs have their
> inFromCl set to true? A few instances I examined, set inFromCl = false
> and rightly so since they are not part of the original from clause. I
> think setting it for subqueries derived for GRAPH_TABLE.
>
> I also think that the proposed fix isn't traversing the UNION query
> tree. Have you tried a GRAPH_TABLE clause which resuts into UNION of
> JOINs.
>
> For the sake of the extension, (which looks useful), inFromCl can be
> set to true for the desired RTEs after fetching rewritten query and
> copying it. You would need query tree mutator for the same.
Hi Ashutosh,
I understand your point and thank you very much for suggesting the second solution.
However, I'm thinking that if the kernel could accept this modification, it would be much more convenient for plugins like `pg_pgq2sql` and `pg_duckdb`,
especially `pg_duckdb`. Its working principle is to obtain the rewritten statement through `pg_get_querydef` and then hand it over to `duckdb` for processing.
This means it might help `PostgreSQL` handle very complex graph queries, which is worth considering.
Additionally, I have tested the `UNION` operation in various scenarios, and it works correctly. Here is a simple example:
```
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(50),
user_type VARCHAR(20)
);
CREATE TABLE follows (
id INT PRIMARY KEY,
follower INT REFERENCES users(id),
following INT REFERENCES users(id)
);
CREATE TABLE premium_follows (
id INT PRIMARY KEY,
follower INT REFERENCES users(id),
following INT REFERENCES users(id)
);
INSERT INTO users VALUES
(1, 'Alice', 'regular'),
(2, 'Bob', 'regular'),
(3, 'Charlie', 'premium'),
(4, 'David', 'premium');
INSERT INTO follows VALUES
(1, 1, 2),
(2, 1, 3);
INSERT INTO premium_follows VALUES
(1, 3, 4),
(2, 2, 3);
CREATE PROPERTY GRAPH social_graph_multi
VERTEX TABLES (users)
EDGE TABLES (
follows
SOURCE KEY (follower) REFERENCES users(id)
DESTINATION KEY (following) REFERENCES users(id)
LABEL connections,
premium_follows
SOURCE KEY (follower) REFERENCES users(id)
DESTINATION KEY (following) REFERENCES users(id)
LABEL connections
);
SELECT * FROM pg_pgq2sql($$
SELECT common_name
FROM GRAPH_TABLE (
social_graph_multi
MATCH (a IS users)-[IS connections]->(x IS users)
WHERE a.name = 'Alice'
COLUMNS (x.name AS common_name)
)
$$);
pg_pgq2sql
----------------------------------------------------------------------------------------------------------------------------------------------------
SELECT common_name +
FROM LATERAL ( SELECT users_1.name AS common_name +
FROM users, +
follows, +
users users_1 +
WHERE users.id = follows.follower AND users_1.id = follows.following AND users.name::text = 'Alice'::text +
UNION ALL +
SELECT users_1.name AS common_name +
FROM users, +
premium_follows, +
users users_1 +
WHERE users.id = premium_follows.follower AND users_1.id = premium_follows.following AND users.name::text = 'Alice'::text) "graph_table"
(1 row)
SELECT * FROM pg_pgq2sql_info($$
SELECT common_name
FROM GRAPH_TABLE (
social_graph_multi
MATCH (a IS users)-[IS connections]->(x IS users)
WHERE a.name = 'Alice'
COLUMNS (x.name AS common_name)
)
$$);
INFO:
SELECT common_name
FROM LATERAL ( SELECT users_1.name AS common_name
FROM users,
follows,
users users_1
WHERE users.id = follows.follower AND users_1.id = follows.following AND users.name::text = 'Alice'::text
UNION ALL
SELECT users_1.name AS common_name
FROM users,
premium_follows,
users users_1
WHERE users.id = premium_follows.follower AND users_1.id = premium_follows.following AND users.name::text = 'Alice'::text) "graph_table";
pg_pgq2sql_info
-----------------
(1 row)
SELECT common_name
FROM LATERAL ( SELECT users_1.name AS common_name
FROM users,
follows,
users users_1
WHERE users.id = follows.follower AND users_1.id = follows.following AND users.name::text = 'Alice'::text
UNION ALL
SELECT users_1.name AS common_name
FROM users,
premium_follows,
users users_1
WHERE users.id = premium_follows.follower AND users_1.id = premium_follows.following AND users.name::text = 'Alice'::text) "graph_table";
common_name
-------------
Bob
Charlie
(2 rows)
```
--
regards,
Man Zeng
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2026-03-24 04:13 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-03-20 14:15 rewriteGraphTable: Fix missing RTEs in FROM clause by setting inFromCl=true =?ISO-8859-1?B?emVuZ21hbg==?= <[email protected]>
2026-03-24 04:13 ` =?ISO-8859-1?B?emVuZ21hbg==?= <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox