>
> On Tuesday 25 February 2003 09:28, Christoph Haller wrote:
> > > On Mon, Feb 24, 2003 at 07:53:05PM +0000, Darko Prenosil wrote:
> > > >     I need two answers I did not find in documentation :
> > > > How can I get exact number of rows in DECLARED CURSOR ?
> > > > OK, I can FETCH until NULL, but this does not fits my needs !
> >
> > You may want to use FETCH ALL, otherwise what or your needs in
detail?
> >
> If I use FETCH ALL all, all the data will be sent to client, then why
to use
> CURSOR at all ? I need to reduce network trafic on slow connections !
>
I cannot see how you are going to reduce network traffic by knowing in
advance
how many rows will be returned.
Anyway, you may MOVE until 0 instead of FETCH, or use the COUNT()
function on the query to learn about the number of rows to be returned.

Regards, Christoph



Tue, 25 Feb 2003 14:04:50 +0100
Christoph Haller <ch@rodos.fzk.de>
Re: I cant find it or I'm just lazy ?
On Tue, Feb 25, 2003 at 02:04:50PM +0100, Christoph Haller wrote:
>
> Anyway, you may MOVE until 0 instead of FETCH, or use the COUNT()
> function on the query to learn about the number of rows to be returned.

Hmm...  Wouldn't the reliability of a count() depend on the isolation
level?

OTOH the problem with MOVE ALL is that not all cursors support backward
scrolling, apparently, and there is no clear documentation (or even
diagnostics!) to determine whether they do.


Jeroen


Tue, 25 Feb 2003 16:13:09 +0100
"Jeroen T. Vermeulen" <jtv@xs4all.nl>
Re: I cant find it or I'm just lazy ?
Jeroen T. Vermeulen wrote:
> On Tue, Feb 25, 2003 at 02:04:50PM +0100, Christoph Haller wrote:
> >
> > Anyway, you may MOVE until 0 instead of FETCH, or use the COUNT()
> > function on the query to learn about the number of rows to be returned.
> 
> Hmm...  Wouldn't the reliability of a count() depend on the isolation
> level?
> 
> OTOH the problem with MOVE ALL is that not all cursors support backward
> scrolling, apparently, and there is no clear documentation (or even
> diagnostics!) to determine whether they do.

7.4 does document MOVE ALL as going to the end of the cursor.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Thu, 6 Mar 2003 14:08:34 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: I cant find it or I'm just lazy ?
On Thu, Mar 06, 2003 at 02:08:34PM -0500, Bruce Momjian wrote:
> > 
> > OTOH the problem with MOVE ALL is that not all cursors support backward
> > scrolling, apparently, and there is no clear documentation (or even
> > diagnostics!) to determine whether they do.
> 
> 7.4 does document MOVE ALL as going to the end of the cursor.
 
Yes, but to do anything interesting *after* that......


Jeroen


Thu, 6 Mar 2003 20:37:01 +0100
"Jeroen T. Vermeulen" <jtv@xs4all.nl>
Re: I cant find it or I'm just lazy ?
Jeroen T. Vermeulen wrote:
> On Thu, Mar 06, 2003 at 02:08:34PM -0500, Bruce Momjian wrote:
> > > 
> > > OTOH the problem with MOVE ALL is that not all cursors support backward
> > > scrolling, apparently, and there is no clear documentation (or even
> > > diagnostics!) to determine whether they do.
> > 
> > 7.4 does document MOVE ALL as going to the end of the cursor.
>  
> Yes, but to do anything interesting *after* that......

Oh, I see now.  Yep.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Thu, 6 Mar 2003 14:39:07 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: I cant find it or I'm just lazy ?
On Thursday 06 March 2003 19:08, Bruce Momjian wrote:
> Jeroen T. Vermeulen wrote:
> > On Tue, Feb 25, 2003 at 02:04:50PM +0100, Christoph Haller wrote:
> > > Anyway, you may MOVE until 0 instead of FETCH, or use the COUNT()
> > > function on the query to learn about the number of rows to be returned.
> >
> > Hmm...  Wouldn't the reliability of a count() depend on the isolation
> > level?
> >
> > OTOH the problem with MOVE ALL is that not all cursors support backward
> > scrolling, apparently, and there is no clear documentation (or even
> > diagnostics!) to determine whether they do.
>
> 7.4 does document MOVE ALL as going to the end of the cursor.

Great, Bruce is back ! 
I drop the idea to use cursors for recordset buffering, and I'm using temp
tables. MOVE ALL can solve my first problem, but It can't solve the other
one: How to know if there is transaction in progress ? The final facts were:

	For cursor:
		Fast, and less memory (concerning that only query plan is stored on server).
	Against cursor:
		I can't determine if transaction is already in progress, so I do not 
		know can I COMMIT on cursor close. (Maybe some other of my recordset 
		controls started transactions before)

	For table:
		I do not need transaction
	Against table:
		More memory, and slower positioning in the buffer(using LIMIT and OFFSET)

OK it is slower, but it works ! 

I must say one more thing I noticed experimenting with cursors:
	Let's say that we have cursor with 10 rows, if we MOVE 11 rows, cursor 
become unusable, because even if we after that MOVE -5, no row can be 
fetched. I do not think that this is bug, but at last notice should be raised
with warning that we missed the cursors size. I even find the code that is
working with cursor, and tried to figure out how to fix this, but it is too
much for me. Sorry !

Regards !


Fri, 7 Mar 2003 14:54:04 +0000
Darko Prenosil <darko.prenosil@finteh.hr>
Re: I cant find it or I'm just lazy ?

I just tested the MOVE -5 in a simple case, and it worked:

	test=> begin;
	BEGIN
	test=> declare xx cursor for select * from pg_class;
	DECLARE CURSOR
	test=> move 99999999 from xx;
	MOVE 157
	test=> move -5 from xx;
	MOVE 5
	test=> fetch 1 from xx;
	   relname    | relnamespace | reltype | relowner | relam | relfilenode | relpages | reltuples | reltoastrelid | reltoastidxid | relhasindex | relisshared | relkind | relnatts | relchecks | reltriggers | relukeys | relfkeys | relrefs | relhasoids | relhaspkey | relhasrules | relhassubclass |    relacl
	--------------+--------------+---------+----------+-------+-------------+----------+-----------+---------------+---------------+-------------+-------------+---------+----------+-----------+-------------+----------+----------+---------+------------+------------+-------------+----------------+---------------
	 pg_namespace |           11 |   16595 |        1 |     0 |       16594 |        1 |         5 |             0 |             0 | t           | f           | r       |        3 |         0 |           0 |        0 |        0 |       0 | t          | f          | f           | f              | {=r/postgres}
	(1 row)

What I think you are seeing are that certain cursors can't go backwards.
However, I don't know the details.  Anyone?

---------------------------------------------------------------------------

Darko Prenosil wrote:
> On Thursday 06 March 2003 19:08, Bruce Momjian wrote:
> > Jeroen T. Vermeulen wrote:
> > > On Tue, Feb 25, 2003 at 02:04:50PM +0100, Christoph Haller wrote:
> > > > Anyway, you may MOVE until 0 instead of FETCH, or use the COUNT()
> > > > function on the query to learn about the number of rows to be returned.
> > >
> > > Hmm...  Wouldn't the reliability of a count() depend on the isolation
> > > level?
> > >
> > > OTOH the problem with MOVE ALL is that not all cursors support backward
> > > scrolling, apparently, and there is no clear documentation (or even
> > > diagnostics!) to determine whether they do.
> >
> > 7.4 does document MOVE ALL as going to the end of the cursor.
> 
> Great, Bruce is back ! 
> I drop the idea to use cursors for recordset buffering, and I'm using temp
> tables. MOVE ALL can solve my first problem, but It can't solve the other
> one: How to know if there is transaction in progress ? The final facts were:
> 
> 	For cursor:
> 		Fast, and less memory (concerning that only query plan is stored on server).
> 	Against cursor:
> 		I can't determine if transaction is already in progress, so I do not 
> 		know can I COMMIT on cursor close. (Maybe some other of my recordset 
> 		controls started transactions before)
> 
> 	For table:
> 		I do not need transaction
> 	Against table:
> 		More memory, and slower positioning in the buffer(using LIMIT and OFFSET)
> 
> OK it is slower, but it works ! 
> 
> I must say one more thing I noticed experimenting with cursors:
> 	Let's say that we have cursor with 10 rows, if we MOVE 11 rows, cursor 
> become unusable, because even if we after that MOVE -5, no row can be 
> fetched. I do not think that this is bug, but at last notice should be raised
> with warning that we missed the cursors size. I even find the code that is
> working with cursor, and tried to figure out how to fix this, but it is too
> much for me. Sorry !
> 
> Regards !
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
> http://www.postgresql.org/docs/faqs/FAQ.html
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Fri, 7 Mar 2003 14:28:59 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: I cant find it or I'm just lazy ?
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> What I think you are seeing are that certain cursors can't go backwards.

Lots of the more complex plan node types don't correctly implement
backwards fetch.  I've looked briefly at fixing that, but it looks like
it'd be a major pain in the rear for some cases, such as Agg nodes.

A stopgap I've been considering is to add code that knows which plan node
types can go backwards.  Then, if the cursor logic needs to go backwards
on a plan type that doesn't support it, it could instead rewind to start
(all plan types seem to support Rescan) and step forwards the correct
number of rows.  This could be horribly inefficient but at least it
would work.

A less inefficient solution would be to stick a Materialize node atop
the plan, but the trouble is that would be a huge penalty for the common
cases where no backwards scan is actually ever done.  Maybe we could
have the cursor logic insert the Materialize node on-the-fly when the
first backwards motion command is received.  Also, we could implement
the SQL keyword "SCROLL" and say that you have to specify SCROLL if you
don't want this extra work to occur (with SCROLL, we could insert
Materialize if needed before starting).

			regards, tom lane

Fri, 07 Mar 2003 15:03:03 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: I cant find it or I'm just lazy ? 