From: Craig Ringer <ringerc@ringerc.id.au>
To: sthomas@optionshouse.com
Cc: Gregg Jaskiewicz <gryzman@gmail.com>
Cc: Misa Simic <misa.simic@gmail.com>
Cc: Hermann Matthes <hermann.matthes@web.de>
Cc: pgsql-performance@postgresql.org
Subject: Re: Paged Query
Date: Tue, 10 Jul 2012 07:48:28 +0800
Message-ID: <4FFB6DCC.8090903@ringerc.id.au> (raw)
In-Reply-To: <4FFADB02.7010300@optionshouse.com>
References: <4FF43638.5080301@web.de>
<CAH3i69=B7VPj0rkbuxv9TLEF6V0DjB2xzcEUkf8U7BYULCOBug@mail.gmail.com>
<CAJY59_icde+87mo65fJT8v9GdOTFemQFMNg5mxy52GCgjVe85w@mail.gmail.com>
<4FFAC85E.4010607@ringerc.id.au>
<4FFADB02.7010300@optionshouse.com>
On 07/09/2012 09:22 PM, Shaun Thomas wrote:
> On 07/09/2012 07:02 AM, Craig Ringer wrote:
>
>> Do do cursors.
>
> Did you mean "Do not use cursors" here?
>
Oops. "So do cursors".
>> Then the user goes away on a week's holiday and leaves their PC at
>> your "next" button.
>
> This exactly. Cursors have limited functionality that isn't directly
> disruptive to the database in general. At the very least, the
> transaction ID reservation necessary to preserve a cursor long-term
> can wreak havoc on your transaction ID wraparound if you have a fairly
> busy database. I can't think of a single situation where either client
> caching or LIMIT/OFFSET can't supplant it with better risk levels and
> costs.
>
My ideal is a cursor with timeout.
If I could use a cursor but know that the DB would automatically expire
the cursor and any associated resources after a certain inactivity
period (_not_ total life, inactivity) that'd be great. Or, for that
matter, a cursor the DB could expire when it began to get in the way.
I'm surprised more of the numerous tools that use LIMIT and OFFSET don't
instead use cursors that they hold for a short time, then drop if
there's no further activity and re-create next time there's interaction
from the user. ORMs that tend to use big joins would particularly
benefit from doing this.
I suspect the reason is that many tools - esp ORMs, web frameworks, etc
- try to be portable between DBs, and cursors are a high-quirk-density
area in SQL RDBMSs, not to mention unsupported by some DBs. Pity, though.
There's nothing wrong with using a cursor so long as you don't hang onto
it over user think-time without also setting a timeout of some kind to
destroy it in the background.
--
Craig Ringer
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: ringerc@ringerc.id.au, sthomas@optionshouse.com, gryzman@gmail.com, misa.simic@gmail.com, hermann.matthes@web.de
Subject: Re: Paged Query
In-Reply-To: <4FFB6DCC.8090903@ringerc.id.au>
* 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