| From: | zengman <zengman(at)halodbtech(dot)com> |
|---|---|
| To: | pgsql-hackers <pgsql-hackers(at)postgresql(dot)org> |
| Cc: | Peter Eisentraut <peter(at)eisentraut(dot)org>, Ashutosh Bapat <ashutosh(dot)bapat(dot)oss(at)gmail(dot)com>, Junwang Zhao <zhjwpku(at)gmail(dot)com>, assam258 <assam258(at)gmail(dot)com> |
| Subject: | [PATCH] rewriteGraphTable: Fix missing RTEs in FROM clause by setting inFromCl=true |
| Date: | 2026-03-19 13:17:07 |
| Message-ID: | tencent_40CF78D67AF8FCFA50C453A7@qq.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
Hi all,
I made a super simple extension `https://github.com/Z-Xiao-M/pg_pgq2sql` to get the equivalent SQL of PGQ queries – it simply calls `pg_get_querydef` after `QueryRewrite` to fetch the SQL text.
However, I noticed that some FROM clauses were missing in the generated SQL statements.
```
postgres=# SELECT * FROM pg_pgq2sql($$
SELECT common_name
FROM GRAPH_TABLE (
social_graph
MATCH (a IS users)-[]->(x IS users)<-[]-(b IS users)
WHERE a.name = 'Alice' AND b.name = 'Bob'
COLUMNS (x.name AS common_name)
)
$$);
pg_pgq2sql
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SELECT common_name +
FROM LATERAL ( SELECT users_1.name AS common_name +
WHERE users.id = follows.follower AND users_1.id = follows.following AND users_2.id = follows_1.follower AND users_1.id = follows_1.following AND users.name::text = 'Alice'::text AND users_2.name::text = 'Bob'::text) "graph_table"
(1 row)
```
I did a quick check and found the issue: some `rte->inFromCl` were set to `false` (should be `true` here). Here’s a quick patch to fix it:
```
postgres(at)zxm-VMware-Virtual-Platform:~/code/postgres$ git diff
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);
```
After applying the patch, the FROM clause is now complete:
```
postgres=# SELECT * FROM pg_pgq2sql($$
SELECT common_name
FROM GRAPH_TABLE (
social_graph
MATCH (a IS users)-[]->(x IS users)<-[]-(b IS users)
WHERE a.name = 'Alice' AND b.name = 'Bob'
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, +
follows follows_1, +
users users_2 +
WHERE users.id = follows.follower AND users_1.id = follows.following AND users_2.id = follows_1.follower AND users_1.id = follows_1.following AND users.name::text = 'Alice'::text AND users_2.name::text = 'Bob'::text) "graph_table"
(1 row)
postgres=#
postgres=# SELECT common_name
FROM GRAPH_TABLE (
social_graph
MATCH (a IS users)-[]->(x IS users)<-[]-(b IS users)
WHERE a.name = 'Alice' AND b.name = 'Bob'
COLUMNS (x.name AS common_name)
) ;
common_name
-------------
Charlie
(1 row)
postgres=# SELECT common_name
FROM LATERAL ( SELECT users_1.name AS common_name
FROM users,
follows,
users users_1,
follows follows_1,
users users_2
WHERE users.id = follows.follower AND users_1.id = follows.following AND users_2.id = follows_1.follower AND users_1.id = follows_1.following AND users.name::text = 'Alice'::text AND users_2.name::text = 'Bob'::text) "graph_table";
common_name
-------------
Charlie
(1 row)
```
Curious to hear your thoughts/suggestions on this.
--
regards,
Man Zeng
| Attachment | Content-Type | Size |
|---|---|---|
| 0001-Correcting-the-value-of-rte-inFromCl-in-SQL-PGQ.patch | application/octet-stream | 1.2 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Robert Haas | 2026-03-19 13:36:36 | Re: Better shared data structure management and resizable shared data structures |
| Previous Message | David Rowley | 2026-03-19 12:56:38 | Re: More speedups for tuple deformation |