Thread: PREPARE and parameter types (Re: [INTERFACES] DBD::PostgreSQL)
David Wheeler <david@wheeler.net> writes:
> On Monday, November 18, 2002, at 08:19 AM, Tom Lane wrote:
>> There are various ways to retrieve the datatypes of the columns of a
>> table, but I'm not sure how that helps you to determine the parameter
>> types for an arbitrary SQL command to be prepared. Are you assuming
>> a specific structure of the command you want to prepare?
> Ouch, good point. I don't want to go there. It's a shame, really, but
> in light of this requirement, I don't see how PostgreSQL prepared
> statements can be supported by the DBI. Pity; I was really looking
> forward to the performance boost.
Thinking about this, it occurs to me that there's no good reason why
we couldn't allow parameter symbols ($n) to be considered type UNKNOWN
initially. The type interpretation algorithms would then treat them
just like quoted literal constants. After parsing finishes, PREPARE
could scan the tree to see what type each symbol had been cast to.
(You'd have to raise an error if multiple appearances of the same symbol
had been cast to different types, but that'd be an uncommon case.)
This form of PREPARE would presumably need some way of reporting back
the types it had determined for the symbols; anyone have a feeling for
the appropriate API for that?
regards, tom lane
On Monday, November 18, 2002, at 08:58 AM, Tom Lane wrote:
> Thinking about this, it occurs to me that there's no good reason why
> we couldn't allow parameter symbols ($n) to be considered type UNKNOWN
> initially. The type interpretation algorithms would then treat them
> just like quoted literal constants. After parsing finishes, PREPARE
> could scan the tree to see what type each symbol had been cast to.
> (You'd have to raise an error if multiple appearances of the same
> symbol
> had been cast to different types, but that'd be an uncommon case.)
>
> This form of PREPARE would presumably need some way of reporting back
> the types it had determined for the symbols; anyone have a feeling for
> the appropriate API for that?
If I'm understanding you correctly this approach would make it much
easier on dynamic drivers such as DBI and JDBC. Ideally, in DBI, I'd be
able to do something like this:
PREPARE my_stmt AS SELECT foo, bar FROM bat WHERE foo = $1 AND bar = $2;
EXECUTE my_stmt('foo_val', 'bar_val');
It would be the responsibility of the PostgreSQL PREPARE parser to
handle the data typing of $1 and $2, and the responsibility of the DBI
client to pass in data of the appropriate type.
Is this along the lines of what you're thinking, Tom?
Regards,
David
--
David Wheeler AIM: dwTheory
david@wheeler.net ICQ: 15726394
http://david.wheeler.net/ Yahoo!: dew7e Jabber:
Theory@jabber.org
Tom Lane <tgl@sss.pgh.pa.us> writes: > Thinking about this, it occurs to me that there's no good reason why > we couldn't allow parameter symbols ($n) to be considered type UNKNOWN > initially. Good idea. > This form of PREPARE would presumably need some way of reporting back > the types it had determined for the symbols; anyone have a feeling for > the appropriate API for that? Why would this be needed? Couldn't we rely on the client programmer to know that '$n is of type foo', and then pass the appropriately-typed data to EXECUTE? If we *do* need an API for this, ISTM that by adding protocol-level support for PREPARE/EXECUTE, this shouldn't be too difficult to do (and analogous to the way we pass back type information for SELECT results). It would also allow us to side-step the parser for EXECUTE parameters, which was something that a few people had requested earlier. Cheers, Neil -- Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC
Neil Conway <neilc@samurai.com> writes:
> Tom Lane <tgl@sss.pgh.pa.us> writes:
>> This form of PREPARE would presumably need some way of reporting back
>> the types it had determined for the symbols; anyone have a feeling for
>> the appropriate API for that?
> Why would this be needed? Couldn't we rely on the client programmer to
> know that '$n is of type foo', and then pass the appropriately-typed
> data to EXECUTE?
I don't think so. You may as well ask why we waste bandwidth on passing
back info about the column names and types of a SELECT result ---
shouldn't the client know that already? There are lots of middleware
layers that don't know it, or at least don't want to expend a lot of
code on trying to deduce it.
> If we *do* need an API for this, ISTM that by adding protocol-level
> support for PREPARE/EXECUTE, this shouldn't be too difficult to do
> (and analogous to the way we pass back type information for SELECT
> results).
I'm not sure what you mean by protocol-level support, but the one idea
I had was to return a table (equivalent to a SELECT result) listing
the parameter types. This would not break libpq, for example, so
arguably it's not a protocol change. But if you think the recent
changes in how EXPLAIN returns its results are a protocol change, then
yeah it's a protocol change ...
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> writes: > Neil Conway <neilc@samurai.com> writes: > > Why would this be needed? Couldn't we rely on the client programmer to > > know that '$n is of type foo', and then pass the appropriately-typed > > data to EXECUTE? > > I don't think so. You may as well ask why we waste bandwidth on passing > back info about the column names and types of a SELECT result --- > shouldn't the client know that already? There are lots of middleware > layers that don't know it, or at least don't want to expend a lot of > code on trying to deduce it. Fair enough -- although there's a major difference between the meta-data stored about tables (which are permanent database objects and are typically complex), and prepared statements (which (at present) are only stored for the duration of the current connection, and are relatively simple: many statements will not have more than a couple params). Arguably, the difference is enough to make it nonessential that we provide client programmers with that information. > > If we *do* need an API for this, ISTM that by adding protocol-level > > support for PREPARE/EXECUTE, this shouldn't be too difficult to do > > (and analogous to the way we pass back type information for SELECT > > results). > > I'm not sure what you mean by protocol-level support I was thinking something along the lines of making prepared statements actually part of the protocol itself -- i.e. have a client message for 'PrepareStatement', a server message for 'StatementDescriptor', a client message for 'ExecuteStatement', a server message for 'ExecuteResults', and so on. The message that returns the statement descriptor would provide the necessary typo info, which the client's language interface can make available to them in a convenient fashion. As I mentioned, this would also allow EXECUTE parameters to bypass the parser, which a couple people have remarked is slow when inputting megabytes of data in a query string. > the one idea I had was to return a table (equivalent to a SELECT > result) listing the parameter types. This would not break libpq, > for example, so arguably it's not a protocol change. Hmmm... that would work, although it strikes me as being a bit messy to use tables to return data intended solely for machine use. As far as changing the protocol, I think there's justification for doing that in 7.4 anyway -- so ISTM that either solution would work pretty well. If anyone would prefer one or the other of these APIs, please speak up... Cheers, Neil -- Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC
Neil Conway <neilc@samurai.com> writes:
> Fair enough -- although there's a major difference between the
> meta-data stored about tables (which are permanent database objects
> and are typically complex), and prepared statements (which (at
> present) are only stored for the duration of the current connection,
> and are relatively simple: many statements will not have more than a
> couple params). Arguably, the difference is enough to make it
> nonessential that we provide client programmers with that information.
I forgot to point out this: if the client programmer could conveniently
provide that info, we'd not be having this discussion, because he could
just as well include the datatypes in the PREPARE command to meet our
existing syntax. The fact that we are getting complaints about the
syntax is sufficient evidence that it's not always reasonable to expect
client-side code to know the datatypes. (I think this comes mainly from
the fact that client-side code is not monolithic but tends to consist
of multiple layers. Some of those layers may be expected to know
a-priori what datatypes a query involves, but others will not know.)
regards, tom lane