Message-ID: <43CB71AC.20203@barettadeit.com>
Date: Mon, 16 Jan 2006 11:13:00 +0100
From: Alessandro Baretta <a.baretta@barettadeit.com>
User-Agent: Debian Thunderbird 1.0.7 (X11/20051017)
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Suspending SELECTs 
Content-Type: text/plain; charset=ISO-8859-15; format=flowed
Content-Transfer-Encoding: 7bit

I am aware that what I am dreaming of is already available through cursors, but 
in a web application, cursors are bad boys, and should be avoided. What I would 
like to be able to do is to plan a query and run the plan to retreive a limited 
number of rows as well as the executor's state. This way, the burden of 
maintaining the cursor "on hold", between activations of the web resource which 
uses it, is transferred from the DBMS to the web application server, and, most 
importantly, the responsibility for garbage-collecting stale cursors is 
implicitely delegated to the garbage-collector of active user sessions. Without 
this mechanism, we are left with two equally unpleasant solutions: first, any 
time a user instantiates a new session, a new cursor would have to be declared 
for all relevant queries, and an ad-hoc garbage collection daemon would have to 
be written to periodically scan the database for stale cursors to be closed; 
otherwise, instead of using cursors, the web application could resort to 
OFFSET-LIMIT queries--no garbage collection issues but pathetic performance and 
server-load.

Do we have any way out?

Alex

-- 
*********************************************************************
http://www.barettadeit.com/
Baretta DE&IT
A division of Baretta SRL

tel. +39 02 370 111 55
fax. +39 02 370 111 54

Our technology:

The Application System/Xcaml (AS/Xcaml)
<http://www.asxcaml.org/>

The FreerP Project
<http://www.freerp.org/>