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 03A329DC9AC for ; Wed, 18 Jan 2006 01:52:06 -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 44279-01 for ; Wed, 18 Jan 2006 01:52:05 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from linda-1.paradise.net.nz (bm-1a.paradise.net.nz [203.96.152.180]) by postgresql.org (Postfix) with ESMTP id B30AF9DC9A5 for ; Wed, 18 Jan 2006 01:52:03 -0400 (AST) Received: from smtp-2.paradise.net.nz (tclsnelb1-src-1.paradise.net.nz [203.96.152.172]) by linda-1.paradise.net.nz (Paradise.net.nz) with ESMTP id <0IT9007CTXMQ0W@linda-1.paradise.net.nz> for pgsql-performance@postgresql.org; Wed, 18 Jan 2006 18:52:03 +1300 (NZDT) Received: from [192.168.1.11] (218-101-28-158.dsl.clear.net.nz [218.101.28.158]) by smtp-2.paradise.net.nz (Postfix) with ESMTP id 414EF1261726; Wed, 18 Jan 2006 18:52:02 +1300 (NZDT) Date: Wed, 18 Jan 2006 18:51:58 +1300 From: Mark Kirkwood Subject: Re: Suspending SELECTs In-reply-to: <4245.1137556941@sss.pgh.pa.us> To: Tom Lane Cc: mark@mark.mielke.cc, Alessandro Baretta , pgsql-performance@postgresql.org Message-id: <43CDD77E.7080902@paradise.net.nz> MIME-version: 1.0 Content-type: text/plain; format=flowed; charset=ISO-8859-1 Content-transfer-encoding: 7bit X-Accept-Language: en-us, en User-Agent: Mozilla Thunderbird 1.0.6 (X11/20051106) References: <43CB71AC.20203@barettadeit.com> <9766.1137433907@sss.pgh.pa.us> <43CD4BD0.9090807@barettadeit.com> <20060117211259.GA13926@mark.mielke.cc> <43CD8B45.4090907@paradise.net.nz> <4245.1137556941@sss.pgh.pa.us> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.265 required=5 tests=[AWL=0.265] X-Spam-Score: 0.265 X-Spam-Level: X-Archive-Number: 200601/286 X-Sequence-Number: 16764 Tom Lane wrote: > Mark Kirkwood writes: > >>SELECT ... FROM table WHERE ... ORDER BY id LIMIT 20; > > >>Suppose this displays records for id 10000 -> 10020. >>When the user hits next, and page saves id=10020 in the session state >>and executes: > > >>SELECT ... FROM table WHERE ... AND id > 10020 ORDER BY id LIMIT 20; > > >>Clearly you have to be a little careful about whether to use '>' or '>=' >>depending on whether 'id' is unique or not (to continue using '>' in the >>non unique case, you can just save and use all the members of the >>primary key too). > > > This is actually fairly painful to get right for a multi-column key > at the moment. It'll be much easier once I finish up the > SQL-spec-row-comparison project. Right, I think it was actually an Oracle 7.3 based web app (err... showing age here...) that I used this technique on. Cheers Mark