Re: PREPARE and parameter types (Re: [INTERFACES] DBD::PostgreSQL)

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
> Neil Conway <neilc(at)samurai(dot)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(at)samurai(dot)com> || PGP Key ID: DB3C29FC