Re: Roadmap for FE/BE protocol redesign

Barry Lind wrote:
> 3) Protocol level support for CURSORs.  It would be nice if cursor 
> support was done at the protocol level and not as a SQL command.

I want to second this proposal. Currently I avoid using cursors in my 
programs since
a) they need much more logic and _string_concatenation_ to be handled 
transparently by a library (prepend the query with DECLARE X CURSOR 
FOR), then (FETCH n FROM X), then (CLOSE X). That's inefficient.
b) I have a really bad feeling to have the backend parse (FETCH FROM X) 
every time I ask for a (single) row
c) I hate that the backend retransmits column names etc. for every fetch 
I issue. This information is mostly unneeded but the backend cannot know 
better

Of course these issues can be addressed by using FETCH n (n>10) but this  kludge is only needed because the FETCH
protocolis so inefficient. 
 
Think about the amount of bytes transferred for "select 2000 lines of 
integers" with and without declare/fetch/close. Imagine a result set of 
1 to 20000 integers given back (depending on parameters) for an 
interactive program (e.g. browsing a customer list by initials). Prefer 
a cursor (much more constant overhead even for single results) or all in 
one (and wait longer for a first result)?

I'd love to tell the backend to give a "descriptor" for this query back 
and use it efficiently to get data and/or metadata (see ODBC, JDBC, 
sqlda or dynamic sql). Perhaps it's most efficient to ask for N initial 
results (which are instantly returned).
   Christof (who implemented dynamic sql for ecpg)

PS: perhaps this protocol infrastructure is also well suited to return 
large bytea values (<M bytes : return inline, > return a descriptor). 
[Also proposed by Barry Lind.]

PPS: I'm perfectly fine with returning attrelid/attnum. Then the client 
can control how many effort is spent for determining only the asked for 
metadata.