On Thu, Mar 19, 2026 at 9:17 PM zengman <[email protected]> wrote: > > 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@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.
I checked the comment: * inFromCl marks those range variables that are listed in the FROM clause. * It's false for RTEs that are added to a query behind the scenes, such * as the NEW and OLD variables for a rule, or the subqueries of a UNION. Even the RTEs are not directly listed in FROM, but as we are rewriting the match pattern using join and where clause, I tend to agree with the fix. > > -- > regards, > Man Zeng -- Regards Junwang Zhao
