Message-ID: <3E70A669.2040905@petig-baender.de>
Date: Thu, 13 Mar 2003 16:40:25 +0100
From: Christof Petig <christof@petig-baender.de>
Organization: Adolf Petig GmbH & Co. KG
User-Agent: Mozilla/5.0 (X11; U; Linux ppc; en-US; rv:1.4a) Gecko/20030305
MIME-Version: 1.0
To: Barry Lind <blind@xythos.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-hackers@postgresql.org
Subject: Re: Roadmap for FE/BE protocol redesign
References: <19041.1047322279@sss.pgh.pa.us> <3E6EEA9B.3050201@xythos.com>
In-Reply-To: <3E6EEA9B.3050201@xythos.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

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.