Re: the parsing of parameters

I'm working on a revised patch for PREPARE/EXECUTE. The basic code
has been written (although I've been delayed due to the workload at
school). I'm now trying to add support for preparing queries with
parameters, but it is failing at an early stage of the game:

nconway=> prepare q1 as select 1;
PREPARE
nconway=> prepare q2 as select $1;
ERROR: Parameter '$1' is out of range

(You'll see the same parse error with simply "select $1;")

The shortened version of the grammar I'm using is:

PrepareStmt: PREPARE name AS OptimizableStmt

What modifications need to be made to allow these kinds of
parametized queries?

BTW, is this a legacy from postquel? (from include/nodes/primnodes.h)

--------------
* Param
* paramkind - specifies the kind of parameter. The possible values
* for this field are specified in "params.h", and they are:
*
* PARAM_NAMED: The parameter has a name, i.e. something
* like `$.salary' or `$.foobar'.
--------------

Specifically, the "something like ..." stuff.

Thanks in advance,

Neil

--
Neil Conway <neilconway(at)rogers(dot)com>
PGP Key ID: DB3C29FC

Neil Conway <nconway(at)klamath(dot)dyndns(dot)org> writes:
> nconway=> prepare q2 as select $1;
> ERROR: Parameter '$1' is out of range

> (You'll see the same parse error with simply "select $1;")

You need to tell the parser the number of parameters to expect and their
datatypes. This is what the last two arguments to parser() are all
about. Look at _SPI_prepare for an example (I think plpgsql uses that).
Also, the plpgsql code for parameterized cursors might be a helpful
reference.

The actual syntax of PREPARE probably has to be something like

PREPARE queryname(parameter type list) FROM query

else you'll not have any way to get the type info.

> BTW, is this a legacy from postquel? (from include/nodes/primnodes.h)

I don't believe anything is using named parameters presently. PARAM_NEW
and PARAM_OLD also seem to be leftovers from an old implementation of
rules.

regards, tom lane

Tom Lane wrote:
> Neil Conway <nconway(at)klamath(dot)dyndns(dot)org> writes:
> > nconway=> prepare q2 as select $1;
> > ERROR: Parameter '$1' is out of range
>
> > (You'll see the same parse error with simply "select $1;")
>
> You need to tell the parser the number of parameters to expect and their
> datatypes. This is what the last two arguments to parser() are all
> about. Look at _SPI_prepare for an example (I think plpgsql uses that).
> Also, the plpgsql code for parameterized cursors might be a helpful
> reference.
>
> The actual syntax of PREPARE probably has to be something like
>
> PREPARE queryname(parameter type list) FROM query
>
> else you'll not have any way to get the type info.
>
> > BTW, is this a legacy from postquel? (from include/nodes/primnodes.h)
>
> I don't believe anything is using named parameters presently. PARAM_NEW
> and PARAM_OLD also seem to be leftovers from an old implementation of
> rules.

I have a little patch that actually allows SPI_prepare() to
use UNKNOWN_OID in the passed in parameter type array and
put's the choosen datatypes Oid back into there.

The parser treats those parameters like single quoted
literals of unknown type and chooses what would be the most
useful datatype here.

Any objections?

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #

Jan Wieck <janwieck(at)yahoo(dot)com> writes:
> I have a little patch that actually allows SPI_prepare() to
> use UNKNOWN_OID in the passed in parameter type array and
> put's the choosen datatypes Oid back into there.

> The parser treats those parameters like single quoted
> literals of unknown type and chooses what would be the most
> useful datatype here.

> Any objections?

For this particular application, at least, I do not see the value ...
in fact this seems more likely to break stuff than help. If the
application does not know what the datatypes are supposed to be,
how is it going to call the prepared statement?

You could possibly get away with that for a textual interface ("always
pass quoted literals"), but it would surely destroy any chance of having
a binary protocol for passing parameters to prepared statements.

Offhand I'm having a hard time visualizing why you'd want this at
the SPI_prepare level, either ... what's the application?

regards, tom lane

Tom Lane wrote:
> Jan Wieck <janwieck(at)yahoo(dot)com> writes:
> > I have a little patch that actually allows SPI_prepare() to
> > use UNKNOWN_OID in the passed in parameter type array and
> > put's the choosen datatypes Oid back into there.
>
> > The parser treats those parameters like single quoted
> > literals of unknown type and chooses what would be the most
> > useful datatype here.
>
> > Any objections?
>
> For this particular application, at least, I do not see the value ...
> in fact this seems more likely to break stuff than help. If the
> application does not know what the datatypes are supposed to be,
> how is it going to call the prepared statement?

Right now using UNKNOWN_OID in that place leads to a parse
error, what makes me feel absolutely comfortable that there
will be nobody using it today. So what kind of "break" are
you talking about?

>
> You could possibly get away with that for a textual interface ("always
> pass quoted literals"), but it would surely destroy any chance of having
> a binary protocol for passing parameters to prepared statements.

Right. And BTW, how do you propose that the client
application passes the values in binary form anyway? Are you
going to maintain that process for backwards compatibility
when we change the internal representation of stuff (like we
want to for numeric) or who? And what about byte ordering?
User defined types?

I think the backend is the only one who can convert into it's
personal, binary format. Wouldn't anything else lead to
security holes?

>
> Offhand I'm having a hard time visualizing why you'd want this at
> the SPI_prepare level, either ... what's the application?

It propagates up to the SPI level. In fact it is down in the
parser/analyzer.

There are DB interfaces that allow a generic application to
get a description of the result set (column names, types)
even before telling the data types of all parameters.

Our ODBC driver for example has it's own more or less
complete SQL parser to deal with that case! I don't see THAT
implementation very superior compared to the ability to ask
the DB server for a guess. I thought that this PREPARE
statement will be used by such interfaces in the future, no?

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #

Jan Wieck <janwieck(at)yahoo(dot)com> writes:
> Tom Lane wrote:
>> For this particular application, at least, I do not see the value ...
>> in fact this seems more likely to break stuff than help. If the
>> application does not know what the datatypes are supposed to be,
>> how is it going to call the prepared statement?

> Right now using UNKNOWN_OID in that place leads to a parse
> error, what makes me feel absolutely comfortable that there
> will be nobody using it today. So what kind of "break" are
> you talking about?

What I mean is that I don't see how an application is going to use
PREPARE/EXECUTE without knowing the data types of the values it
has to send for EXECUTE. Inside SPI you could maybe do it, since
the calling code can examine the modified argtype array, but there
is no such back-communication channel for PREPARE. This holds
for both textual and binary kinds of EXECUTE: how do you know what
you are supposed to send?

>> You could possibly get away with that for a textual interface ("always
>> pass quoted literals"), but it would surely destroy any chance of having
>> a binary protocol for passing parameters to prepared statements.

> Right. And BTW, how do you propose that the client
> application passes the values in binary form anyway?

Same way as binary cursors work today, with the same ensuing platform
and version dependencies. Maybe someday we'll improve on that, but
that's a different project from supporting PREPARE/EXECUTE.

> I think the backend is the only one who can convert into it's
> personal, binary format. Wouldn't anything else lead to
> security holes?

Good point; might need to restrict the operation to superusers.

> There are DB interfaces that allow a generic application to
> get a description of the result set (column names, types)
> even before telling the data types of all parameters.

> Our ODBC driver for example has it's own more or less
> complete SQL parser to deal with that case! I don't see THAT
> implementation very superior compared to the ability to ask
> the DB server for a guess. I thought that this PREPARE
> statement will be used by such interfaces in the future, no?

Hmm. So your vision of PREPARE would allow the backend to reply
with a list of parameter types. How would you envision that working
exactly?

regards, tom lane

On Fri, May 10, 2002 at 11:17:39AM -0400, Tom Lane wrote:
> Jan Wieck <janwieck(at)yahoo(dot)com> writes:
> > Tom Lane wrote:
> >> For this particular application, at least, I do not see the value ...
> >> in fact this seems more likely to break stuff than help. If the
> >> application does not know what the datatypes are supposed to be,
> >> how is it going to call the prepared statement?
>
> > Right now using UNKNOWN_OID in that place leads to a parse
> > error, what makes me feel absolutely comfortable that there
> > will be nobody using it today. So what kind of "break" are
> > you talking about?
>
> What I mean is that I don't see how an application is going to use
> PREPARE/EXECUTE without knowing the data types of the values it
> has to send for EXECUTE. Inside SPI you could maybe do it, since
> the calling code can examine the modified argtype array, but there
> is no such back-communication channel for PREPARE. This holds
> for both textual and binary kinds of EXECUTE: how do you know what
> you are supposed to send?

In my original PREPARE/EXECUTE patch (it works in 7.1):

PREPARE name AS select * from tab where data=$1 USING text;
EXECUTE name USING 'nice text data';

IMHO is possible think about

EXECUTE name USING 'nice text'::text;

or other cast methods.

Karel

--
Karel Zak <zakkr(at)zf(dot)jcu(dot)cz>
http://home.zf.jcu.cz/~zakkr/

C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz

Tom Lane wrote:
> Jan Wieck <janwieck(at)yahoo(dot)com> writes:
> > There are DB interfaces that allow a generic application to
> > get a description of the result set (column names, types)
> > even before telling the data types of all parameters.
>
> > Our ODBC driver for example has it's own more or less
> > complete SQL parser to deal with that case! I don't see THAT
> > implementation very superior compared to the ability to ask
> > the DB server for a guess. I thought that this PREPARE
> > statement will be used by such interfaces in the future, no?
>
> Hmm. So your vision of PREPARE would allow the backend to reply
> with a list of parameter types. How would you envision that working
> exactly?

I guess there's some sort of statement identifier you use to
refer to something you've prepared. Wouldn't a function call
returning a list of names or type oid's be sufficient?

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #

Jan Wieck <janwieck(at)yahoo(dot)com> writes:
>> Hmm. So your vision of PREPARE would allow the backend to reply
>> with a list of parameter types. How would you envision that working
>> exactly?

> I guess there's some sort of statement identifier you use to
> refer to something you've prepared. Wouldn't a function call
> returning a list of names or type oid's be sufficient?

I was thinking of having the type names returned unconditionally,
perhaps like a SELECT result (compare the new behavior of EXPLAIN).
But if we assume that this won't be a commonly used feature, maybe
a separate inquiry operation is better.

regards, tom lane

Tom Lane wrote:
> Jan Wieck <janwieck(at)yahoo(dot)com> writes:
> >> Hmm. So your vision of PREPARE would allow the backend to reply
> >> with a list of parameter types. How would you envision that working
> >> exactly?
>
> > I guess there's some sort of statement identifier you use to
> > refer to something you've prepared. Wouldn't a function call
> > returning a list of names or type oid's be sufficient?
>
> I was thinking of having the type names returned unconditionally,
> perhaps like a SELECT result (compare the new behavior of EXPLAIN).
> But if we assume that this won't be a commonly used feature, maybe
> a separate inquiry operation is better.

I wouldn't mind. One way or the other is okay with me.

Reminds me though of another feature we should have on the
TODO. INSERT/UPDATE/DELETE ... RETURNING ...

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #

Jan Wieck wrote:
> Tom Lane wrote:
> > Jan Wieck <janwieck(at)yahoo(dot)com> writes:
> > >> Hmm. So your vision of PREPARE would allow the backend to reply
> > >> with a list of parameter types. How would you envision that working
> > >> exactly?
> >
> > > I guess there's some sort of statement identifier you use to
> > > refer to something you've prepared. Wouldn't a function call
> > > returning a list of names or type oid's be sufficient?
> >
> > I was thinking of having the type names returned unconditionally,
> > perhaps like a SELECT result (compare the new behavior of EXPLAIN).
> > But if we assume that this won't be a commonly used feature, maybe
> > a separate inquiry operation is better.
>
> I wouldn't mind. One way or the other is okay with me.
>
> Reminds me though of another feature we should have on the
> TODO. INSERT/UPDATE/DELETE ... RETURNING ...

TODO already has:

o Allow INSERT/UPDATE ... RETURNING new.col or old.col; handle
RULE cases (Philip)

Do we need DELETE too?

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026