Thread: SPI/backend equivalent of extended-query Describe(statement)?

The longer version of $subject is: how would one go about, in the
backend using SPI (or SPI and maybe other server APIs as needed),
obtaining the same inferred parameter information that a front-end
client can get with the Describe (statement variant) extended-query
message?

I ask because I've stumbled on this 14½ year old (earliest days!) comment
in PL/Java's PreparedStatement.getParameterMetaData():

* Due to the design of the <code>SPI_prepare</code>, it is currently
* impossible to obtain the correct parameter meta data before all the
* parameters have been set, hence a ParameterMetaData obtained prior to
* setting the paramteres will have all parameters set to the default type
* {@link Types#VARCHAR}.
* Once the parameters have been set, a fair attempt is made to generate this
* object based on the supplied values.

Apparently it seemed difficult at the time to implement a
getParameterMetaData that could tell you the thing you want to know
(what parameter types are expected so you can supply them), but less
difficult to implement something that can't tell you until you've
supplied them and then tells you what you supplied. Problem solved,
in a way that's entertaining but a little short on utility. :)

I'll admit that in my own perusal of the SPI docs, I don't see an
obvious advertised way to get that information about a prepared
statement, either. But the information has to exist somewhere, and
a remote client is able to ask for it, so where should I be looking
for the means of retrieving it in the backend?

-Chap

Chapman Flack <chap@anastigmatix.net> writes:
> The longer version of $subject is: how would one go about, in the
> backend using SPI (or SPI and maybe other server APIs as needed),
> obtaining the same inferred parameter information that a front-end
> client can get with the Describe (statement variant) extended-query
> message?

If you're talking about the plan's input parameters, don't SPI_getargcount
and SPI_getargtypeid do what you need?

If you want to know about the output column types, SPI doesn't seem to
have a nice way of getting that in advance of execution, but you could
emulate what Describe does:

* Use SPI_plan_get_plan_sources to get a list of CachedPlanSources
(up to you to decide what to do if there's more or less than one CPS)

* For each/selected CPS, look at the resultDesc and/or targetlist
similarly to SendRowDescriptionMessage.

            regards, tom lane

On 05/14/18 11:29, Tom Lane wrote:
> Chapman Flack <chap@anastigmatix.net> writes:
>> The longer version of $subject is: how would one go about, in the
>> backend using SPI (or SPI and maybe other server APIs as needed),
>> obtaining the same inferred parameter information that a front-end
>> client can get with the Describe (statement variant) extended-query
>> message?
> 
> If you're talking about the plan's input parameters, don't SPI_getargcount
> and SPI_getargtypeid do what you need?

Huh ... let me go read those again. Sure sound promising. Right there on
the SPI contents page, too.  I wonder if I was so suggestible that I read
that comment and it blinded me to them, or I saw them and assumed they
didn't do what you'd expect, because of the comment....

The story gets better. Those two functions were added to SPI in March '04,
in 65a0db1, submitted by Thomas Hallgren himself.

Before being pushed in core, they had existed in PL/Java since January '04,
d917e46, eight days after he wrote that comment. They disappeared in
October '05 when he dropped support for PG < 8. And he had reworked
ExecutionPlan.c to make use of them (that is, after the Java code has
already bound Java values to the parameters, the native code does consult
SPI_getargtypeid while converting those to PG datums).

But he never removed that comment about getParameterMetaData, and I think
it's still correct (except for the blame-SPI part), because it looks like
he never got around to updating /that/ code to pass along the
SPI_getargtypeid info mapped to Java types so the Java caller can ask
what types to supply.

That looks like something I could fix....

Where should I look to learn more about the capabilities of the type
inference done in planning? Not everything-there-is-to-know, but basics
like, can some simple query constructs reliably cause an intended type to
be reported?  I assume if the query is "SELECT ?" then the reported argtype
will be unknown. If changed to, say, "SELECT ?::foo", will that report
a type of foo for the parameter, or continue to report the parameter
as untyped because it isn't needed to type the cast expression?
(In XQuery they made up distinct constructs "cast as" and "treat as"
to leave no such conundrum.)

-Chap

Chapman Flack <chap@anastigmatix.net> writes:
> Where should I look to learn more about the capabilities of the type
> inference done in planning?

It's not terribly well documented outside the source code, I fear.

> Not everything-there-is-to-know, but basics
> like, can some simple query constructs reliably cause an intended type to
> be reported?  I assume if the query is "SELECT ?" then the reported argtype
> will be unknown. If changed to, say, "SELECT ?::foo", will that report
> a type of foo for the parameter, or continue to report the parameter
> as untyped because it isn't needed to type the cast expression?

That will result in reporting the parameter as having type foo, cf
variable_coerce_param_hook().  Experimenting with this sort of stuff
isn't hard, eg in psql:

regression=# prepare p1 as select $1;
PREPARE
regression=# prepare p2 as select $1::bigint;
PREPARE
regression=# select name, parameter_types from pg_prepared_statements ;
 name | parameter_types 
------+-----------------
 p1   | {text}
 p2   | {bigint}
(2 rows)

(Before v10, p1 would have failed PREPARE for lack of a determinate
type for the parameter, which I think corresponds to sending back
UNKNOWN in the wire-protocol-prepare case.)

            regards, tom lane

On 05/14/18 13:46, Chapman Flack wrote:
> On 05/14/18 11:29, Tom Lane wrote:
>> Chapman Flack <chap@anastigmatix.net> writes:
>>> The longer version of $subject is: how would one go about, in the
>>> backend using SPI (or SPI and maybe other server APIs as needed),
>>> obtaining the same inferred parameter information that a front-end
>>> client can get with the Describe (statement variant) extended-query
>>> message?
>>
>> If you're talking about the plan's input parameters, don't SPI_getargcount
>> and SPI_getargtypeid do what you need?
> 
> Huh ... let me go read those again. Sure sound promising. Right there on

I'm not sure I'm out of the woods yet. The extended-query-protocol
documentation clearly advertises that you can send param oids of zero,
or send fewer of them than the actual number of params, and get the
unspecified ones inferred. And exec_parse_message() pointedly calls
parse_analyze_varparams(), with a comment saying "Note that the originally
specified parameter set is not required to be complete, so we have to use
parse_analyze_varparams()."

The SPI_prepare docs don't contain any similar advertisement of what to do
if you want parameter types inferred ... and the code doesn't appear to
call parse_analyze_varparams, at least never before PG 9.0.

In 9.0, there's SPI_prepare_params, which seems promising; it accepts
an arbitrary ParserSetupHook "to control the parsing of external parameter
references." But its documentation doesn't suggest what to use as the
ParserSetupHook to say "please just do the same stuff you would do if
I were a client sending a Parse message with unspecified parameter types!"

Perhaps I just need something like

struct varparinfo { Oid *paramTypes, int numParams } vpi = {palloc(0), 0};

static void inferringSetupHook(struct ParseState *pstate, void *arg)
{
    struct varparinfo *vpi = (struct varparinfo *)arg;
    parse_variable_parameters(pstate, &vpi->paramTypes, &vpi->numParams);
}

SPI_prepare_params("SELECT $1", inferringSetupHook, &vpi, 0);

?

So, does this mean this really couldn't have been done in SPI earlier
than 9.0, and that old pre-9.0 comment from Thomas was completely accurate?

Were there no other PLs needing to do that before? Is SQL/JRT the only
spec for a PL that presupposes a prepare-describe-bind-execute model
where 'describe' tells you the inferred parameter types? I would have
assumed that was more common.

-Chap

On 05/24/2018 02:30 AM, Chapman Flack wrote:
> In 9.0, there's SPI_prepare_params, which seems promising; it accepts
> an arbitrary ParserSetupHook "to control the parsing of external parameter
> references." But its documentation doesn't suggest what to use as the
> ParserSetupHook to say "please just do the same stuff you would do if
> I were a client sending a Parse message with unspecified parameter types!"
> 
> Perhaps I just need something like
> 
> struct varparinfo { Oid *paramTypes, int numParams } vpi = {palloc(0), 0};
> 
> static void inferringSetupHook(struct ParseState *pstate, void *arg)
> {
>     struct varparinfo *vpi = (struct varparinfo *)arg;
>     parse_variable_parameters(pstate, &vpi->paramTypes, &vpi->numParams);
> }
> 
> SPI_prepare_params("SELECT $1", inferringSetupHook, &vpi, 0);

Am I on the right track here? Is what I'm looking to do something
that became possible in SPI in 9.0 and wasn't before, or did I overlook
a way it could have been done pre-9.0 ?

Thanks,
-Chap

Chapman Flack <chap@anastigmatix.net> writes:
> Am I on the right track here? Is what I'm looking to do something
> that became possible in SPI in 9.0 and wasn't before, or did I overlook
> a way it could have been done pre-9.0 ?

Offhand I don't believe SPI exposed a way to do that before 9bedd128d.
Does it matter?  Pre-9.0 releases are long out of support by now,
which means they're full of known data-loss hazards and security bugs.

            regards, tom lane

On 05/25/2018 06:01 PM, Tom Lane wrote:

> Offhand I don't believe SPI exposed a way to do that before 9bedd128d.
> Does it matter?  Pre-9.0 releases are long out of support by now,
> which means they're full of known data-loss hazards and security bugs.

Well, my exploration arose from discovering something in PL/Java's
implementation that surprised me, but it dated from pre-9.0 times,
so seems to have been doing the best that was possible then. Didn't
want to start changing it before understanding why it was that way.

As for bringing it along to the modern API, am I on the right track
with writing a ParserSetupHook that's just a thin wrapper around
parse_variable_parameters ?

-Chap

Chapman Flack <chap@anastigmatix.net> writes:
> As for bringing it along to the modern API, am I on the right track
> with writing a ParserSetupHook that's just a thin wrapper around
> parse_variable_parameters ?

Seems reasonable from here.

            regards, tom lane

>>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:

 > Chapman Flack <chap@anastigmatix.net> writes:
 >> As for bringing it along to the modern API, am I on the right track
 >> with writing a ParserSetupHook that's just a thin wrapper around
 >> parse_variable_parameters ?

 Tom> Seems reasonable from here.

That's what I did in pllua-ng. The tricky bit was in arranging to also
call check_variable_parameters; I considered skipping that part, but
that seemed like it could be potentially problematic.

https://github.com/pllua/pllua-ng/blob/master/src/spi.c#L266

-- 
Andrew (irc:RhodiumToad)

On 05/25/18 20:07, Andrew Gierth wrote:
>  >> with writing a ParserSetupHook that's just a thin wrapper around
>  >> parse_variable_parameters ?
> 
> That's what I did in pllua-ng. The tricky bit was in arranging to also
> call check_variable_parameters; I considered skipping that part, but
> that seemed like it could be potentially problematic.
> 
> https://github.com/pllua/pllua-ng/blob/master/src/spi.c#L266
>
>/*
> * GAH. To do parameter type checking properly, we have to install our
> * own global post-parse hook transiently.
> */
>...
>PG_TRY();
>{
>    pllua_spi_prev_parse_hook = post_parse_analyze_hook;
>    post_parse_analyze_hook = pllua_spi_prepare_checkparam_hook;
>...
>PG_CATCH();
>{
>    post_parse_analyze_hook = pllua_spi_prev_parse_hook;
>    --pllua_spi_prepare_recursion;
>    PG_RE_THROW();
>...

Gah, indeed. Thanks for the heads up. I would never have guessed it'd
be that fiddly.

-Chap

Chapman Flack <chap@anastigmatix.net> writes:
> On 05/25/18 20:07, Andrew Gierth wrote:
>> /*
>> * GAH. To do parameter type checking properly, we have to install our
>> * own global post-parse hook transiently.
>> */
>> ...
>> PG_TRY();
>> {
>> pllua_spi_prev_parse_hook = post_parse_analyze_hook;
>> post_parse_analyze_hook = pllua_spi_prepare_checkparam_hook;
>> ...
>> PG_CATCH();
>> {
>> post_parse_analyze_hook = pllua_spi_prev_parse_hook;
>> --pllua_spi_prepare_recursion;
>> PG_RE_THROW();
>> ...

> Gah, indeed. Thanks for the heads up. I would never have guessed it'd
> be that fiddly.

Yikes.  That seems pretty unsafe :-(

Obviously, I missed a bet by not folding check_variable_parameters
into the pstate hook mechanism.  It's a bit late to do anything
about that for v11, but I'd favor trying to improve the situation
in v12.

            regards, tom lane

>>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:

 >>> /*
 >>> * GAH. To do parameter type checking properly, we have to install our
 >>> * own global post-parse hook transiently.
 >>> */

 >> Gah, indeed. Thanks for the heads up. I would never have guessed it'd
 >> be that fiddly.

 Tom> Yikes.  That seems pretty unsafe :-(

I put in a recursion check out of paranoia, but even after considerable
thought wasn't able to figure out any scenario that would actually break
it. If it's actually unsafe I'd really like to know about it :-)

 Tom> Obviously, I missed a bet by not folding check_variable_parameters
 Tom> into the pstate hook mechanism. It's a bit late to do anything
 Tom> about that for v11, but I'd favor trying to improve the situation
 Tom> in v12.

Yeah. Another issue I ran into is that if you use SPI_prepare_params,
then you have to use SPI_execute_plan_with_paramlist, it's not possible
to use SPI_execute_plan (or SPI_execute_snapshot) instead because
plan->nargs was set to 0 by the prepare and never filled in with the
actual parameter count.

-- 
Andrew (irc:RhodiumToad)

On 05/25/18 21:16, Andrew Gierth wrote:
>>>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:
>  Tom> about that for v11, but I'd favor trying to improve the situation
>  Tom> in v12.
> 
> Yeah. Another issue I ran into is that if you use SPI_prepare_params,
> then you have to use SPI_execute_plan_with_paramlist, it's not possible
> to use SPI_execute_plan (or SPI_execute_snapshot) instead because
> plan->nargs was set to 0 by the prepare and never filled in with the
> actual parameter count.

Now *that* sounds arguably bug-like ...could *it*, at least, be a candidate
for back-patching?

If it's currently not possible to use SPI_execute_plan/SPI_execute_snapshot
after SPI_prepare_params, then there can't be anything currently doing so,
that a patch could conceivably disrupt, can there?

-Chap

Andrew Gierth <andrew@tao11.riddles.org.uk> writes:
> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:
>  Tom> Yikes.  That seems pretty unsafe :-(

> I put in a recursion check out of paranoia, but even after considerable
> thought wasn't able to figure out any scenario that would actually break
> it. If it's actually unsafe I'd really like to know about it :-)

The worrisome thing is the possibility that some other extension tries
to add to (or otherwise change) the post_parse_analyze_hook list while
you have it in a temporary state.  I can't think of a really likely
scenario for that, because I don't think parse analysis would ever cause
loading of a shared library that wasn't loaded already ... but just to
state that assumption is to expose how non-future-proof it is.

Really our hook mechanism only supports adding hooks, not removing them.

>  Tom> Obviously, I missed a bet by not folding check_variable_parameters
>  Tom> into the pstate hook mechanism. It's a bit late to do anything
>  Tom> about that for v11, but I'd favor trying to improve the situation
>  Tom> in v12.

> Yeah. Another issue I ran into is that if you use SPI_prepare_params,
> then you have to use SPI_execute_plan_with_paramlist, it's not possible
> to use SPI_execute_plan (or SPI_execute_snapshot) instead because
> plan->nargs was set to 0 by the prepare and never filled in with the
> actual parameter count.

I'm not following why that's such a problem?  The whole point of
SPI_prepare_params and friends is that the actual number and types
of the parameters is hidden behind the parse hooks and ParamListInfo
--- and, indeed, could change from one execution to the next.
SPI_execute_plan only makes sense with a predetermined, fixed
parameter list.

            regards, tom lane

On 05/26/18 10:03, Tom Lane wrote:
> Really our hook mechanism only supports adding hooks, not removing them.

I suppose the pllua_spi_prepare_checkparam_hook could be linked in once
and for all, and turned on and off just where the code now hooks and
unhooks it, and just forward to the next hook when it's off.

>> Yeah. Another issue I ran into is that if you use SPI_prepare_params,
>> then you have to use SPI_execute_plan_with_paramlist, it's not possible
>> to use SPI_execute_plan (or SPI_execute_snapshot) instead because
>> plan->nargs was set to 0 by the prepare and never filled in with the
>> actual parameter count.
> 
> I'm not following why that's such a problem?  The whole point of
> SPI_prepare_params and friends is that the actual number and types
> of the parameters is hidden behind the parse hooks and ParamListInfo
> --- and, indeed, could change from one execution to the next.
> SPI_execute_plan only makes sense with a predetermined, fixed
> parameter list.

Well, when you're implementing a PL, you're faced with this task of
mapping/coercing parameters from a PL type system that invariably
(invariably? Yes, I think, unless your PL's name has "sql" in it)
differs from SQL's, and that's a pretty ill-defined task if your PL
runtime is handed a query to prepare in the form of a string, and
then handed some parameter values in the PL's type system, and can't
find out what SQL types they could appropriately be mapped/coerced to
for the query to be valid.

In the JDBC API, you pass a query string to prepareStatement(), and
then on what you get back you can call getParameterMetaData() and
learn what SQL thinks the types of the parameters will have to be.
That isn't really expected to change; the requirement isn't necessarily
to support some dizzying all-dynamic-all-the-time usage pattern, it's
just to be able to get the information, and SPI_prepare_params seems
the only way to get it.

-Chap

>>>>> "Chapman" == Chapman Flack <chap@anastigmatix.net> writes:

 >> Really our hook mechanism only supports adding hooks, not removing
 >> them.

 Chapman> I suppose the pllua_spi_prepare_checkparam_hook could be
 Chapman> linked in once and for all, and turned on and off just where
 Chapman> the code now hooks and unhooks it, and just forward to the
 Chapman> next hook when it's off.

Yeah, or have it detect whether the ParseState it's being called for is
ours by some other means.

 >> I'm not following why that's such a problem?  The whole point of
 >> SPI_prepare_params and friends is that the actual number and types
 >> of the parameters is hidden behind the parse hooks and ParamListInfo
 >> --- and, indeed, could change from one execution to the next.

So while looking at the hook issue, I found another can of worms.

What a protocol-level Parse does is to call parse-analysis via
parse_analyze_varparams, which calls parse_variable_parameters _without_
making it a parser setup hook (either there or in CompleteCachedPlan).

This has the effect of casting the parameter types in stone on the first
parse, as the client expects; a subsequent revalidate of the statement
will use pg_analyze_and_rewrite, which takes a fixed parameter list.

However if you call parse_variable_parameters from a hook passed to
SPI_prepare_params, then you're asking for it to be called again on
revalidations, which means that the parameters might change (even if
just changing types, I think you'd need a more complex set of hooks than
parse_variable_parameters uses to change the number of parameters too).

So what I'm thinking now is that the way to go, if one wants to imitate
the client-side protocol behavior closely, would be to have a setup hook
that calls parse_variable_parameters the first time, and then
parse_fixed_parameters on subsequent calls for revalidation.

-- 
Andrew (irc:RhodiumToad)

On 05/26/18 15:22, Andrew Gierth wrote:
> So what I'm thinking now is that the way to go, if one wants to imitate
> the client-side protocol behavior closely, would be to have a setup hook
> that calls parse_variable_parameters the first time, and then
> parse_fixed_parameters on subsequent calls for revalidation.

What circumstances would call for revalidation, and what would it be
able to accomplish, under that design?

I'm kind of trying to think out what the semantics could be in PL/Java,
should they be changed from what they are now (which is just to tell you
all params are varchar until you have supplied values for them, and then
tell you what types you supplied, in case you'd forgotten).

It's interesting that with parse_variable_parameters, it is possible
to supply some types explicitly, while leaving others to be inferred.
In one plausible implementation, that could be what would happen in
PL/Java if you called prepareStatement(), then called setter methods
on a few of the parameters, then called getParameterMetaData(). And
I suppose there could be cases where the explicitly supplied types for
some parameters would affect the types that get inferred for others.

On one hand, that sounds like it could be a bit confusing, and on the
other, sounds like it might be useful sometimes and I should just embrace
my inner Schrödinger and say yeah, that's how it works, you 'collapse'
the type assignments to whatever fits best at whatever moment you call
getParameterMetaData().

From a glance at the (network client) pgjdbc code, that seems to be
what you'd expect to happen there, too; it gets sent to the backend
for "describe only" at the moment getParameterMetaData is called.

What I can find of the JDBC spec seems informal and breezy about this
stuff to an unsettling degree....

-Chap

>>>>> "Chapman" == Chapman Flack <chap@anastigmatix.net> writes:

 >> So what I'm thinking now is that the way to go, if one wants to
 >> imitate the client-side protocol behavior closely, would be to have
 >> a setup hook that calls parse_variable_parameters the first time,
 >> and then parse_fixed_parameters on subsequent calls for
 >> revalidation.

 Chapman> What circumstances would call for revalidation, and what would
 Chapman> it be able to accomplish, under that design?

Any change that could invalidate the cached plan, such as altering any
of the tables referenced by it.

But thinking about it more (and actually trying it out in pllua-ng),
it's not as bad as I thought because parse_variable_parameters on
revalidation (at least the way I do it) will still be using the same
parameter types array as before, which will have the types of all
actually used parameters already filled in, and so those won't be
changed.

I think in the absence of a columnref hook, that means that the number
and type of parameters found when using parse_variable_parameters can't
change after the first time, so there's probably no need to get too
fancy in the hook.

-- 
Andrew (irc:RhodiumToad)