Re: Roadmap for FE/BE protocol redesign

Tom Lane wrote:
> Barry Lind <blind@xythos.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
ofthis 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