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 protocol is 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.