In-Reply-To: <60mzqsqpku.fsf@dba2.int.libertyrms.com>
References: 
 <DCD4903594CCF34CAFE200BDF3448D8D5B291A@tshuscodenmbx01.ERF.THOMSON.COM>
	<20050517170636.GC12405@phlogiston.dyndns.org>
	<428A2E81.2060501@pobox.com>
	<60mzqsqpku.fsf@dba2.int.libertyrms.com>
Mime-Version: 1.0 (Apple Message framework v622)
Content-Type: text/plain; charset=US-ASCII; format=flowed
Message-Id: <bd64363b8e45406a72b43db31af55e76@mail.nih.gov>
Content-Transfer-Encoding: 7bit
Cc: pgsql-sql@postgresql.org
From: Sean Davis <sdavis2@mail.nih.gov>
Subject: Re: Does Postgresql have a similar pseudo-column "ROWNUM" as
Date: Wed, 18 May 2005 16:20:26 -0400
To: Chris Browne <cbbrowne@acm.org>


On May 18, 2005, at 3:52 PM, Chris Browne wrote:

> alainm@pobox.com (Alain) writes:
>> Andrew Sullivan escreveu:
>>> On Thu, May 12, 2005 at 01:07:00PM -0600, Dennis.Jiang@thomson.com 
>>> wrote:
>>>
>>>> Does Postgresql have a similar pseudo-column "ROWNUM" as Oracle? If
>>>> so, we can write the following query:
>>> No.  What is the purpose of your query?  You could use ORDER BY and
>>> LIMIT..OFFSET to do what you want. I think.
>>
>> The problem is probably speed. I have done a lot of tests, and when
>> OFFSET gets to a few thousands on a multimega-recs database, it gets
>> very very slow... Is there any other to work around that?
>
> The other way to do this would involve creating a cursor against the
> table, and using suitable FETCHes to grab the portions that you
> needed.
>
> In practice, this has seemed to be the relevant answer to what the
> application developer actually wanted.
>
> The common "use case" where I see it is in a web application where
> they discover that there are 800K records, and the user only wants a
> screenful at a time.
>
> Establishing a cursor, and having the web app jump around on it, seems
> to be the right answer.  (Whether it's reasonably implementable by the
> developers may be another question, but that's allowed to be a
> separate question ;-).)

In a web app, I doubt that cursors can be useful because of the 
stateless nature of web interaction.  I'd love to hear otherwise, 
but....

Sean