Re: idea to have driver return immediately after a query

On Fri, Mar 25, 2011 at 6:33 AM, Dave Cramer <pg(at)fastcrypt(dot)com> wrote:
> On Thu, Mar 24, 2011 at 9:09 PM, Oliver Jowett <oliver(at)opencloud(dot)com> wrote:
>> On 25 March 2011 13:40, Dave Cramer <pg(at)fastcrypt(dot)com> wrote:
>>> It was suggested by Robert Haas that it would be possible to return
>>> from a query immediately instead of reading the entire result set.
>>> Instead of reading it we just let the O/S buffer the results until we
>>> get around to reading it. Before I go to the trouble of prototyping
>>> this can anyone see a reason why this wouldn't work ?
>>
>> What happens if the app then wants to run another query before reading
>> the resultset? One common case is going to be run query - inspect
>> resultset metadata - driver has to run internal queries to return that
>> metadata. I'm a little worried about error handling too.
>>
>> For queries in a transaction, it might make sense to implement this
>> via portals, much as done for fetchsize (i.e. always ask for only 1
>> row initially, and read that immediately; then reading the resultset
>> beyond the first row triggers a fetch of the rest of the resultset as
>> if you had set a large fetchsize). Then you don't have to worry about
>> tying up the connection with an unread resultset. Though this means
>> you have to use a named statement and lose the unnamed statement
>> planning tweaks; and you will have to wait for the query to produce at
>> least one row before returning.
>>
>> There are various things that the wire protocol / backend could do
>> better here - a portal equivalent to DECLARE CURSOR WITH HOLD, and
>> some way to say "defer planning on this named statement until Bind
>> please", would both be useful.
>>
>> Oliver
>
> I've included Robert on this email as he intimated that he if protocol
> changes were made he would be interested in implementing them.

I was more interesting in helping review, but I do think it might be
about time to consider a protocol version bump, if we can gather
together in one bundle all the needs that aren't satisfied by the
current version.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company