Re: Roadmap for FE/BE protocol redesign - Mailing list pgsql-hackers

Tom Lane wrote:
> Barry Lind <blind@xythos.com> writes:
>>The describe request is generally only 
>>done once even though you may do multiple fetchs (unlike todays protocol 
>>which includes the describe information on every fetch, even if you are 
>>fetching one row at a time).
> 
> 
> I'm less than excited about changing that, because it breaks clients
> that don't want to remember past RowDescriptions (libpq being the
> front-line victim), and it guarantees loss-of-synchronization failures
> anytime the client misassociates rowdescription with query.  In exchange
> for that, we get what exactly?  Fetching one row at a time is
> *guaranteed* to be inefficient.  The correct response if that bothers
> you is to fetch multiple rows at a time, not to make a less robust
> protocol.

I don't think that protocol support for cursors should change the 
behavior of executing all seven stages by default. A "FETCH ..." 
commmand would get processed like any other (e.g. "SELECT ...") and 
metadata is sent back, too (which corresponds to decribe stage IIRC).

New programs have the option to use the backwards compatible high level 
access via PQexec(c,"FETCH FROM X") which does all seven steps at once, 
or use the new low level way e.g. PQexec_new(c,"SELECT ...", 
query_parameter_descriptor, what_to_do (*), 
lines_to_return_without_cursor_overhead) which should return at most the 
specified lines and (if needed) a cursor descriptor (most likely an int) 
for subsequent PQfetch and PQclose calls.

I really like the idea of PGresult as an argument (cursor descriptor) 
for PQfetch (instead of an int) because it may even copy the metadata to 
the new PGresult, or perhaps replace the values in the original PGresult 
(if we decide to go this way). [proposed signature: PGresult 
*PQfetch(PGresult*result_of_the_select, how_many_lines, 
perhaps_even_offset/position)]

Additional there should be a PQclose and perhaps a PQprocess(PGresult *, 
things_to_do (*)) if we want to be able to separate every step.

If you know you are never interested in metadata, you can omit the 
describe flag at all. [null indication and type specification is of 
course always needed to access the actual data]
   Christof

*) open, parse, describe, bind, execute, fetch, close

PS: If we decide to omit the lines_to_return_without_cursor_overhead 
optimization, the new architecture would still be a big win for *DBC.

This optimization can not get a GUC variable instead of a protocol 
parameter since this would break clients: should they specify 
fetch+close to enable it? If yes, there's no easy way to implement the 
old behavior (all seven stages, no limit on returned lines). If no, the 
client cannot specify to omit the fetch without changing it (limit 0).

PPS: Query parameter passing is another topic, but I tend to propose a 
PGresult variant for specifying them (of course each with its type).