Re: Suspending SELECTs - Mailing list pgsql-performance

From Mark Kirkwood
Subject Re: Suspending SELECTs
Date
Msg-id 43CDD77E.7080902@paradise.net.nz
Whole thread Raw
In response to Re: Suspending SELECTs  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-performance
Tom Lane wrote:
> Mark Kirkwood <markir@paradise.net.nz> 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

pgsql-performance by date:

Previous
From: "Ahmad Fajar"
Date:
Subject: Re: Multiple Order By Criteria
Next
From: Alessandro Baretta
Date:
Subject: Re: Suspending SELECTs