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

Re: Suspending SELECTs

From: Alessandro Baretta <a(dot)baretta(at)barettadeit(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Suspending SELECTs
Date: 2006-01-17 19:56:00
Message-ID: 43CD4BD0.9090807@barettadeit.com (view raw or flat)
Thread:
Lists: pgsql-performance
Tom Lane wrote:
> Alessandro Baretta <a(dot)baretta(at)barettadeit(dot)com> writes:
> 
>>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,
> 
> 
> This is a pipe dream, I'm afraid, as the state of a cursor does not
> consist exclusively of bits that can be sent somewhere else and then
> retrieved.  There are also locks to worry about, as well as the open
> transaction itself, and these must stay alive inside the DBMS because
> they affect the behavior of other transactions.  As an example, once
> the cursor's originating transaction closes, there is nothing to stop
> other transactions from modifying or removing rows it would have read.

I understand most of these issues, and expected this kind of reply. Please, 
allow me to insist that we reason on this problem and try to find a solution. My 
reason for doing so is that the future software industry is likely to see more 
and more web applications retrieving data from virtually endless databases, and 
in such contexts, it is sensible to ask the final client--the web client--to 
store the "cursor state", because web interaction is intrinsically asynchronous, 
and you cannot count on users logging out when they're done, releasing resources 
allocated to them. Think of Google.

Let me propose a possible solution strategy for the problem of "client-side 
cursors".
* Let us admit the limitation that a "client-side cursor" can only be declared 
in a transaction where no inserts, updates or deletes are allowed, so that such 
a transaction is virtually non-existent to other transactions. This allows the 
backend to close the transaction and release locks as soon as the cursor is 
declared.
* When the cursor state is pushed back to the backend, no new transaction is 
instantiated, but the XID of the original transaction is reused. In the MVCC 
system, this allows us to achieve a perfectly consistent view of the database at 
the instant the original transaction started, unless a VACUUM command has been 
executed in the meantime, in which case I would lose track of tuples which would 
have been live in the context of the original transaction, but have been updated 
or deleted and later vacuumed; however, this does not bother me at all.

Is this not a viable solution?

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/>

In response to

Responses

pgsql-performance by date

Next:From: Yantao ShiDate: 2006-01-17 20:00:37
Subject: wildcard search performance with "like"
Previous:From: Tom LaneDate: 2006-01-17 19:32:52
Subject: Re: Autovacuum / full vacuum

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