Skip site navigation (1) Skip section navigation (2)

Suspending SELECTs

From: Alessandro Baretta <a(dot)baretta(at)barettadeit(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Suspending SELECTs
Date: 2006-01-16 10:13:00
Message-ID: 43CB71AC.20203@barettadeit.com (view raw or flat)
Thread:
Lists: pgsql-performance
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/>

Responses

pgsql-performance by date

Next:From: Michael RiessDate: 2006-01-16 14:36:53
Subject: Materialized Views
Previous:From: Luke LonerganDate: 2006-01-15 17:21:00
Subject: Re: SAN/NAS options

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group