Re: [PATCH] rewriteGraphTable: Fix missing RTEs in FROM clause by setting inFromCl=true - Mailing list pgsql-hackers

On Thu, Mar 19, 2026 at 9:17 PM zengman <zengman@halodbtech.com> 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 –
itsimply 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
ANDusers_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
quickpatch 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
ANDusers_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
ANDusers_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