From: August Zajonc <augustz@augustz.com>
Subject: Re: Suspending SELECTs
Date: Sun, 22 Jan 2006 11:04:29 -0500
Organization: Hub.Org Networking Services
Lines: 39
Message-ID: <43D3AD0D.9050201@augustz.com>
References: <43CB71AC.20203@barettadeit.com>
 <43CD4BD0.9090807@barettadeit.com>
	<200601171459.02398.josh@agliodbs.com>
	<200601171518.07342.josh@agliodbs.com>
	<43CE0709.4000104@barettadeit.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
To: Alessandro Baretta <a.baretta@barettadeit.com>
User-Agent: Thunderbird 1.5 (Windows/20051201)
In-Reply-To: <43CE0709.4000104@barettadeit.com>
To: pgsql-performance@postgresql.org

Alessandro Baretta wrote:
> 
> 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
> 
> 
> 

Alessandro,

I've very much enjoyed reading your thoughts and the problem your facing
and everyone's responses.

Since you control the middle layer, could you not use a cookie to keep a
cursor open on the middle layer and tie into it on subsequent queries?

If you are concerned with too many connections open, you could timeout
the sessions quickly and recreate the cursor if someone came back. If
they waited 5 minutes to make the next query, certainly they could wait
a few extra seconds to offset and reacquire a cursor?

The hitlist idea was also a nice one if the size of the data returned is
not overwhelming and does not need to track the underlying db at all
(ie, no refresh).

Mark had a number of good general suggestions though, and I'd like to
echo the materialized views as an option that I could see a lot of uses
for (and have worked around in the past with SELECT INTO's and like).

Interesting stuff.

- August