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.
---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
