From: August Zajonc <augustz@augustz.com>
To: Alessandro Baretta <a.baretta@barettadeit.com>
Subject: Re: Suspending SELECTs
Date: Sun, 22 Jan 2006 11:04:29 -0500
Message-ID: <43D3AD0D.9050201@augustz.com> (raw)
In-Reply-To: <43CE0709.4000104@barettadeit.com>
References: <43CB71AC.20203@barettadeit.com>
<43CD4BD0.9090807@barettadeit.com>
<200601171459.02398.josh@agliodbs.com>
<200601171518.07342.josh@agliodbs.com>
<43CE0709.4000104@barettadeit.com>
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
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: augustz@augustz.com, a.baretta@barettadeit.com
Subject: Re: Suspending SELECTs
In-Reply-To: <43D3AD0D.9050201@augustz.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