Accessing parameters of a prepared query inside an FDW
Hi Folks,
Is it possible to get the parameterized prepared query inside an FDW such
that it can be prepared/bind'd/execute'd on the receiving end of the FDW?
For example, if I `PREPARE stmt(int) AS SELECT * from fdwrapped_tbl where
pk = $1;` then `execute stmt(1);` I want my FDW be aware that the query was
prepared.
Right now, if given the above, and I walk through the postgres_fdw code and
output the parse tree from root->parse->query (the PlannerInfo node), I see
no PARAM nodes - inside the OPEXPR is just the VAR & CONST (in this case,
the CONST is 1). Note that if I call ereport() I do see the correct
prepared statement (eg, with the $1) in the output (as debug_query_string
has that statement in it).
I cannot find an example of an FDW that supports passing on prepared
statements. Any help appreciated!
-Adam
On Wednesday, February 14, 2024, Adam Fletcher <adamfblahblah@gmail.com>
wrote:
Is it possible to get the parameterized prepared query inside an FDW such
that it can be prepared/bind'd/execute'd on the receiving end of the FDW?For example, if I `PREPARE stmt(int) AS SELECT * from fdwrapped_tbl where
pk = $1;` then `execute stmt(1);` I want my FDW be aware that the query was
prepared.
That isn’t how the separation of responsibilities works in PostgreSQL.
Execute is capable of producing a custom plan where instead of adding in
parameters and then planning around those unknowns the newly created plan
uses the supplied constants while planning.
I do suspect that if a generic plan is chosen you will see the expected
parse nodes and can thus build a generic access plan to your foreign server
accordingly.
You can control this for ease of testing via plan_cache_mode
https://www.postgresql.org/docs/current/runtime-config-query.html#GUC-PLAN-CACHE-MODE
David J.
On Wed, Feb 14, 2024 at 7:43 PM David G. Johnston <
david.g.johnston@gmail.com> wrote:
On Wednesday, February 14, 2024, Adam Fletcher <adamfblahblah@gmail.com>
wrote:Is it possible to get the parameterized prepared query inside an FDW such
that it can be prepared/bind'd/execute'd on the receiving end of the FDW?For example, if I `PREPARE stmt(int) AS SELECT * from fdwrapped_tbl where
pk = $1;` then `execute stmt(1);` I want my FDW be aware that the query was
prepared.That isn’t how the separation of responsibilities works in PostgreSQL.
Execute is capable of producing a custom plan where instead of adding in
parameters and then planning around those unknowns the newly created plan
uses the supplied constants while planning.I do suspect that if a generic plan is chosen you will see the expected
parse nodes and can thus build a generic access plan to your foreign server
accordingly.You can control this for ease of testing via plan_cache_mode
https://www.postgresql.org/docs/current/runtime-config-query.html#GUC-PLAN-CACHE-MODE
Thanks David, this solved my problem - here’s the reasons I asked:
https://github.com/EnterpriseDB/mysql_fdw/pull/293 - this adds parameter
forwarding to the MySQL FDW.
Thanks again!
<https://www.postgresql.org/docs/current/runtime-config-query.html#GUC-PLAN-CACHE-MODE>