Re: Roadmap for FE/BE protocol redesign

Tom Lane wrote:
> Barry Lind <blind(at)xythos(dot)com> writes:
>
>>>AFAICS the only context where this could make sense is binary
>>>transmission of parameters for a previously-prepared statement. We do
>>>have all the pieces for that on the roadmap.
>>>
>>
>>Actually it is the select of binary data that I was refering to. Are
>>you suggesting that the over the wire format for bytea in a query result
>>will be binary (instead of the ascii encoded text format as it currently
>>exists)?
>
>
> See binary cursors ...

Generally that is not an option. It either requires users to code to
postgresql specific sql syntax, or requires the driver to do it
magically for them. The later runs into all the issues that I raised on
cursor support.

In general the jdbc driver is expected to execute arbitrary sql
statements any application might want to send it. The driver is
handicaped because it doesn't know really anything about that sql
statement (other than it is a select vs an update or delete).
Specifically it doesn't know what tables or columns that SQL will access
or how many rows a select will return. All of this knowledge is in
the backend, and short of implementing a full sql parser in java this
knowledge will never exist in the front end. Many of the things I put
on my wish list for the protocol stem from this.

Where there are two ways to do something (use cursors or not, use
prepared statements or not, use binary cursors or not) the driver either
needs to a) choose one way and always use it, b) infer from the sql
statement which way will be better, or c) require the user to tell us.
The problem with a) is that it may not always be the correct choice.
The problem with b) is that generally this isn't possible and the
problem with c) is it requires that the user write code that isn't
portable across different databases.

I would like to simply do a) in all cases. But that means that one of
the two options should always (or almost always) be the best choice. So
in the case of "use cursors or not", it would be nice if using cursors
added little or no overhead such that it could always be used. In the
case of "use prepared statements vs not", it would be nice if prepared
statements added little or no overhead so that they could always be
used. And finally in the case of "use binary or regular cursors" it
would be nice if binary cursors could always be used.

The Oracle SQLNet protocol supports most of this. Though it has been a
few years since I worked with it, the oracle protocol has many of the
features I am looking for (and perhaps the reason I am looking for them,
is that I have seen them used there before). Essentially the Oracle
protocol lets you do the following operations: open, parse, describe,
bind, execute, fetch, close. A request from the client to the server
specifies what operations it wants to perform on a sql statement. So a
client could request to do all seven operations (which is essentially
what the current postgres protocol does today). Or it could issue an
open,parse call which essentially is that same thing as the PREPARE sql
statement, followed by a describe,bind,execute,fetch which is similar to
an EXECUTE and FETCH sql statement and finally a close which is similar
to a CLOSE and DEALLOCATE sql. The describe request is generally only
done once even though you may do multiple fetchs (unlike todays protocol
which includes the describe information on every fetch, even if you are
fetching one row at a time). The oracle approach gives the client
complete flexibility to do a lot, without requiring that the client
start parsing sql statements and doing things like appending on DECLARE
CURSOR, or FETCH in order to reformate the applications sql statement
into the postgresql sql way of doing this.

--Barry