Re: Suspending SELECTs

Josh Berkus wrote:
> People:
>
> To follow up further, what Alessandro is talking about is known as a
> "keyset cursor". Sybase and SQL Server used to support them; I beleive
> that they were strictly read-only and had weird issues with record
> visibility.

I would like to thank everyone for sharing their ideas with me. I democratically
accept the idea that my middleware will have to support the functionality I
would have liked to delegate to PostgreSQL. If I have to implement anything of
this sort--just like Tom--I don't want to spend time on a solution lacking
generality or imposing unacceptable resource requirements under high load. The
keyset-cursor idea is probably the best bet--and BTW, let me specifically thank
Josh for mentioning them.

What I could do relatively easily is instantiate a thread to iteratively scan a
traditional cursor N rows at a time, retrieving only record keys, and finally
send them to the query-cache-manager. The application thread would then scan
through the cursor results by fetching the rows associated to a given "page" of
keys. I would have to keep the full cursor keyset in the application server's
session state, but, hopefully, this is not nearly as bad as storing the entire
recordset.

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