Re: Suspending SELECTs

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