Re: Make EXPLAIN generate a generic plan for a parameterized query

On Tue, 2023-01-31 at 13:49 -0500, Tom Lane wrote:
> Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> writes:
> > [ 0001-Add-EXPLAIN-option-GENERIC_PLAN.v4.patch ]
>
> I took a closer look at this patch, and didn't like the implementation
> much.  You're not matching the behavior of PREPARE at all: for example,
> this patch is content to let $1 be resolved with different types in
> different places.  We should be using the existing infrastructure that
> parse_analyze_varparams uses.
>
> Also, I believe that in contexts such as plpgsql, it is possible that
> there's an external source of $N definitions, which we should probably
> continue to honor even with GENERIC_PLAN.
>
> So that leads me to think the code should be more like this.  I'm not
> sure if it's worth spending documentation and testing effort on the
> case where we don't override an existing p_paramref_hook.

Thanks, that looks way cleaner.

I played around with it, and I ran into a problem with partitions that
are foreign tables:

CREATE TABLE loc1 (id integer NOT NULL, key integer NOT NULL CHECK (key = 1), value text);

CREATE TABLE loc2 (id integer NOT NULL, key integer NOT NULL CHECK (key = 2), value text);

CREATE TABLE looppart (id integer GENERATED ALWAYS AS IDENTITY, key integer NOT NULL, value text) PARTITION BY LIST (key);

CREATE FOREIGN TABLE looppart1 PARTITION OF looppart FOR VALUES IN (1) SERVER loopback OPTIONS (table_name 'loc1');

CREATE FOREIGN TABLE looppart2 PARTITION OF looppart FOR VALUES IN (2) SERVER loopback OPTIONS (table_name 'loc2');

EXPLAIN (GENERIC_PLAN) SELECT * FROM looppart WHERE key = $1;
ERROR: no value found for parameter 1

The solution could be to set up a dynamic parameter hook in the
ExprContext in ecxt_param_list_info->paramFetch so that
ExecEvalParamExtern doesn't complain about the missing parameter.

Does that make sense? How do I best hook into the executor to set that up?

Yours,
Laurenz Albe