> So "generic_plan" as a mandatory option may be the best way to proceed, > and only make the remote_plans option available to remote versions that > support this option. > Maybe others have a better way?
I agree, I found use of generic_plan convenient here. Attached patch 2 considers this. I have added an error message if the remote postgres server is not compatible with options being sent. Also updated documentation with limitations. Thanks a lot for feedback, Sami. Please review the attached patch and share feedback. On Sat, Jan 10, 2026 at 5:46 AM Sami Imseih <[email protected]> wrote: > > Supporting remote_plans options for inserts: > > Only "explain insert" are executed with bind variables > > (verified by logging all sqls while running make check) and while > executing > > that on remote is erroring out with error "there is no parameter $1". We > can > > either NOT support remote plans for insert statements > > or always use generic_plan option on remote sql. Using "generic_plan" on > > remote comes with an additional check if remote supports > > this option or not in case remote shard is older postgres. > > I prefer not supporting remote_plans for inserts as there is nothing > much that > > goes in insert statement plans unless its "insert into..select". > > User can always run explain on that select separately. Appreciate your > > inputs on this. > > After looking at this a bit more, I don't think the INSERT case is the > only one. > > Here is an example: > ``` > -- Setup foreign server and table > CREATE EXTENSION postgres_fdw; > CREATE SERVER remote_server FOREIGN DATA WRAPPER postgres_fdw > OPTIONS (host 'localhost', port '5432', dbname 'postgres'); > CREATE USER MAPPING FOR CURRENT_USER SERVER remote_server > OPTIONS (user 'postgres', password 'password'); > > CREATE TABLE local_table (id int, name text); > CREATE FOREIGN TABLE remote_table ( > id int, > name text > ) SERVER remote_server OPTIONS (table_name 'local_table'); > > postgres=# load 'postgres_fdw'; > LOAD > postgres=# explain (remote_plans, verbose) select * from remote_table > where id = (select 1); > ERROR: there is no parameter $1 > CONTEXT: remote SQL command: EXPLAIN ( > FORMAT TEXT, VERBOSE > 1, COSTS 1, SETTINGS > 0) SELECT id, name FROM public.local_table WHERE ((id = > $1::integer)) > postgres=# > > postgres=# explain (verbose) select * from remote_table where id = (select > 1); > QUERY PLAN > > ---------------------------------------------------------------------------------- > Foreign Scan on public.remote_table (cost=100.01..128.54 rows=7 width=36) > Output: remote_table.id, remote_table.name > Remote SQL: SELECT id, name FROM public.local_table WHERE ((id = > $1::integer)) > InitPlan expr_1 > -> Result (cost=0.00..0.01 rows=1 width=4) > Output: 1 > (6 rows) > > ```` > > The above is due to the value of the subquery is sent as a parameter; see > `printRemoteParam`in deparse.c. > > Also see this comment in deparse.c: > > ``` > * This is used when we're just trying to EXPLAIN the remote query. > * We don't have the actual value of the runtime parameter yet, and we don't > * want the remote planner to generate a plan that depends on such a value > * anyway. Thus, we can't do something simple like "$1::paramtype". > * Instead, we emit "((SELECT null::paramtype)::paramtype)". > ``` > > The above comment is related to the EXPLAIN being sent remotely when > use_remote_estimate is enabled. But the point is, it will not be possible > to > send the runtime parameters to the remote EXPLAIN. > > So "generic_plan" as a mandatory option may be the best way to proceed, > and only make the remote_plans option available to remote versions that > support this option. > > Maybe others have a better way? > > > > About decision which explain options we should forward to remote shard: > > This is because local and remote postgres could be different and we still > > need to address what all options we send in remote sql as remote shard > > might not even support them. We can forward only limited options to > > remote which are widely supported (pg >= 9) i.e. verbose, costs, buffers, > > format only. If we need to support all possible options, we need to query > > the version of remote postgres and then prepare remote sql. Thoughts? > > I think if we try to forward an option that is on the source side but not > on > the remote side, it's fair to just error out with "ERROR: > unrecognized EXPLAIN option..." > > That should be acceptable, because the user will know better not to use > that > option. right? > > -- > Sami Imseih > Amazon Web Services (AWS) >
0002-postgres_fdw-show-remote-EXPLAIN-plans-via-REMOTE_PL.patch
Description: Binary data
