On Thu, Mar 19, 2026 at 8:35 PM Junwang Zhao <[email protected]> wrote: > > 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.
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. -- Best Wishes, Ashutosh Bapat
