Re: explain plans for foreign servers

> 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)