PREPARE statement (was Speedups) - Mailing list pgsql-hackers

David Hartwig <daveh@insightdist.com>

> Not to pile on, but, I have a great interest in this subject.   We do a
> lot of work using off-the-shelf ODBC tools.  And, we have observed that
> these tools use PREPARE for two purposes.
>
> One is to speed up iterative queries which join data from different
> databases.   You seem to be addressing this issue.
>
> The other reason PREPARE is used is to retrieve a description of a
> query's projection (target/result) with out actually running the
> query.   Currently, ODBC drivers must simulate the prepare statement by
> submitting the full query and discard the data just to get the result
> description.
>
I did ask the PostODBC team (about a year ago, before Julie took over
the maintenance) to change this so that it opens a _cursor_ and get just
enough rows to determine the types and lengths (you can determine the
types by getting just one row, but you also can get the real length of
varchar fields by getting enough rows of a _binary_ cursor to get each
varchar field to be non-null. At that time (and maybe even now) the
behaviour was to open an ASCII cursor and to get the whole recordset and
find the longest field ;), this got mostly wrong results and messed up
Delphi in a big way.)

I have since stopped using ODBC (and have never had a setup to develop
odbc drivers), but if this change is not yet there, it can be used as a
quick, client-side-only, workaround.

Of course the real soultion would be changing the front-end protocol to
be somewhat compatible with ISO-ANSI SQL CLI/ODBC and to use prepared
statements at the protocol level (as I understand the SPI already does
it?). I would also recommend taking notice of X-Window protocol when
designing the new DB protocol.

And it would be a really good idea tyo have some design effort put into
the specifing the new protocol before starting to implement it.

At the very least the core postgresql developers, JDBC and ODBC
developers should be involved in defining the new protocol.

The current protocol seems not designed but just evolved from some
protocol that has started as telnet-to-port-5432 and added various parts
(like connect options and binary cursors) later - nice for initial
debugging but a real pita to implement fast clients.

My ideal protocol would be one that merges ISO-ANSI SQL CLI
functionality with X-Window like protocol. That would also be easily
extensible for any other be-fe communication like user-defined functions
sending their info to frontends using their own packet types  or even
asking for info from them. Or having special higher priority packets for
sending signals to backend that would by-pass others in the send queue
(this is not an Xproto feature, but much needed anyhow.)

> Obviously this slows response time greatly when the query
> is a large data set.   So if you haven't considered returning the the
> results description, please do.
>
> Thank Very Much
>
In my opinion the first thing to change is the protocol as it has to be
changed anyhow when implementing types longer than 8k.

It would be nice to give a list of requested headers to backend when
establishing the connection and later just get these (so that when you
don't wand/need some bookkeeping info you dont get it, and when you want
loads of debugging info you can request it also.

Currently you can't even ignore the response packets you dont want
easily, because you have still to parse them in order to know when they
are over. A clean protocol design would just allow you to ignore the
responses you don't understand. (Isn't this also one principle of OO?).

Hannu Krosing