From: Alessandro Baretta <a.baretta@barettadeit.com>
To: josh@agliodbs.com
Cc: pgsql-performance@postgresql.org, Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: Suspending SELECTs
Date: Wed, 18 Jan 2006 10:14:49 +0100
Message-ID: <43CE0709.4000104@barettadeit.com> (raw)
In-Reply-To: <200601171518.07342.josh@agliodbs.com>
References: <43CB71AC.20203@barettadeit.com>
<43CD4BD0.9090807@barettadeit.com>
<200601171459.02398.josh@agliodbs.com>
<200601171518.07342.josh@agliodbs.com>
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/;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: a.baretta@barettadeit.com, josh@agliodbs.com, tgl@sss.pgh.pa.us
Subject: Re: Suspending SELECTs
In-Reply-To: <43CE0709.4000104@barettadeit.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox