Skip site navigation (1) Skip section navigation (2)

Re: Bug(?) with cursors using aggregate functions.

From: Dennis Gearon <gearond(at)cvc(dot)net>
To: Benjamin Scherrey <scherrey(at)proteus-tech(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>,PostgreSQL General <pgsql-general(at)postgresql(dot)org>
Subject: Re: Bug(?) with cursors using aggregate functions.
Date: 2003-04-29 15:04:47
Message-ID: 3EAE948F.9000500@cvc.net (view raw or flat)
Thread:
Lists: pgsql-general
If you are using PHP, the ADODB library will do caching of results on the server file system and allow you to 'cursor' through it. Of course, if you need requerying, you'd have to trigger that and pay the time penalty.

Benjamin Scherrey wrote:
> 	Thanx for the response, Tom, which was, unfortunately, pretty close to what I feared. I'm 
> glad to hear that a fix is pending but I am concerned about the performance issue. Some of the 
> queries that my form will be browsing are in the tens of thousands of results. This is actually the 
> reason why I use cursors so I can just fetch one screen full of results but bounce back and forth in 
> the result set to get where I want. Saving copies of what actually gets fetched will be fine, but 
> saving copies of anything that I actually scroll by would quickly be prohibitive Presently I guess I 
> could do a fetch all and get the same result. A much preferable solution would be the ability to 
> determine absolute position of the query and even pay the performance cost of re-querying at some 
> points. I imagine that this it outside the SQL standard but I'm willing to take that penalty to get 
> around a complex query limitation. I haven't tried it yet but I presume a view built from a complex 
> query will give me the same problem?
> 
> 	thanx & later,
> 
> 		Ben Scherrey
> 
> 4/28/2003 11:51:51 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> 
> 
>>Benjamin Scherrey <scherrey(at)proteus-tech(dot)com> writes:
>>
>>>I've been developing a web-based selection browser using cursors and
>>>discovered a very frustrating little feature as I try to MOVE
>>>FORWARD/BACKWARD through my selection.
>>
>>You can't run a nontrivial query plan (anything more than a seqscan or
>>indexscan) backwards with any reliability.  There are fixes for this in
>>CVS tip, but not in any released version :-(.  It should also be noted
>>that the fix consists of saving-aside copies of all rows emitted by the
>>underlying query, so if you are talking about a large result set you
>>might not like the performance...
>>
>>			regards, tom lane
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
> http://archives.postgresql.org
> 


In response to

Responses

pgsql-general by date

Next:From: Tom LaneDate: 2003-04-29 15:10:29
Subject: Re: Error: No one parent tuple was found
Previous:From: Tom LaneDate: 2003-04-29 14:22:15
Subject: Re: Slow Queries with OR's?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group