X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 597AA9DC865 for ; Wed, 18 Jan 2006 05:14:37 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 97785-08 for ; Wed, 18 Jan 2006 05:14:37 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mail.barettadeit.com (unknown [213.255.109.130]) by postgresql.org (Postfix) with ESMTP id 5BDEF9DC833 for ; Wed, 18 Jan 2006 05:14:34 -0400 (AST) Received: from [10.1.0.20] (unknown [10.1.0.20]) by mail.barettadeit.com (Postfix) with ESMTP id 8623C96E1; Wed, 18 Jan 2006 10:19:03 +0100 (CET) Message-ID: <43CE0709.4000104@barettadeit.com> Date: Wed, 18 Jan 2006 10:14:49 +0100 From: Alessandro Baretta User-Agent: Debian Thunderbird 1.0.7 (X11/20051017) X-Accept-Language: en-us, en MIME-Version: 1.0 To: josh@agliodbs.com Cc: pgsql-performance@postgresql.org, Tom Lane Subject: Re: Suspending SELECTs References: <43CB71AC.20203@barettadeit.com> <43CD4BD0.9090807@barettadeit.com> <200601171459.02398.josh@agliodbs.com> <200601171518.07342.josh@agliodbs.com> In-Reply-To: <200601171518.07342.josh@agliodbs.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.12 required=5 tests=[AWL=0.120] X-Spam-Score: 0.12 X-Spam-Level: X-Archive-Number: 200601/289 X-Sequence-Number: 16767 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) The FreerP Project