User-Agent: Microsoft-Entourage/11.2.5.060620
Date: Fri, 15 Sep 2006 11:01:19 -0600
Subject: Re: Progress of asynchronous queries
From: Keary Suska <hierophant@pcisys.net>
To: Postgres-Interfaces <pgsql-interfaces@postgresql.org>
Message-ID: <C130387F.5144%hierophant@pcisys.net>
Thread-Topic: [INTERFACES] Progress of asynchronous queries
Thread-Index: AcbY6JA0zvFbtETbEduQuQADk83m0A==
In-Reply-To: <25525.203.121.164.162.1158315505.squirrel@webmail.xs4all.nl>
Mime-version: 1.0
Content-type: text/plain;
	charset="US-ASCII"
Content-transfer-encoding: 7bit

on 9/15/06 4:18 AM, jtv@xs4all.nl purportedly said:

>> I couldn't find anything in the libpq interfaces that identifies the
>> current progress of an
>> asynchronous query as a percentage of the estimated total query time. So,
>> unless I missed
>> something, I would like to file this as a feature request.
>> 
>> The idea behind this, of course, is to show a progress bar in the user
>> interface of the client
>> application as soon as the query takes longer than say 1 second.
> 
> One way to do this would be to use a cursor, and only receive batches of
> (say) a hundred or a thousand rows at a time.  In that case you might not
> even want to bother with asynchronous execution at all.
> 
> It may take some extra work for your program to handle its data in chunks.
>  Then again, for many programs it's not necessary to hold all of a result
> set in memory at the same time.  In that case you may see reduced memory
> usage if you request one chunk of data through a cursor, process it, then
> request the next batch.
> 
> Doing that will take longer overall but give you faster initial response
> time.  If you want to keep the overall time low, and you do a lot of
> processing per batch, you may still want to request your batches
> asynchronously just so your program can process one batch of data while
> the server is retrieving the next.

Using a cursor would only be useful in cases where the data transfer is the
main bottleneck. This may work in situations with fairly straightforward
queries and exceedingly large datasets, but will not work at all with
complex queries that return small datasets. I.e., where the bottleneck is
the query planning/execution phase.

I will add my vote to this feature request. After all, MySQL and MSSql can
do this. I don't know about Oracle, but I would be surprised if it didn't.

Best,

Keary Suska
Esoteritech, Inc.
"Demystifying technology for your home or business"