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

Re: Suspending SELECTs

From: Alessandro Baretta <a(dot)baretta(at)barettadeit(dot)com>
To: josh(at)agliodbs(dot)com
Cc: pgsql-performance(at)postgresql(dot)org, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Subject: Re: Suspending SELECTs
Date: 2006-01-18 09:14:49
Message-ID: 43CE0709.4000104@barettadeit.com (view raw or flat)
Thread:
Lists: pgsql-performance
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/>

In response to

Responses

pgsql-performance by date

Next:From: JDate: 2006-01-18 14:06:05
Subject: Re: Multiple Order By Criteria
Previous:From: MarcosDate: 2006-01-18 09:10:30
Subject: Simple Question of Performance ILIKE or Lower

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