Thread: More on cursors in 7.3

More on cursors in 7.3

From
"Jeroen T. Vermeulen"
Date:
Looking at my problem with changed cursor behaviour in 7.3 again, I
noticed something interesting: a cursor in 7.3 apparently does not let 
you scroll back to its first row at all!  Neither a "move backward all"
or a "move -n" where n is equal to or greater than the cursor's current 
position, will let you fetch any more rows from the cursor.  Scrolling 
back to the second row does work though.

I can work around this in libpqxx, without too much trouble, but I'm 
not sure I should have to.


Jeroen



Re: More on cursors in 7.3

From
Tom Lane
Date:
"Jeroen T. Vermeulen" <jtv@xs4all.nl> writes:
> Looking at my problem with changed cursor behaviour in 7.3 again, I
> noticed something interesting: a cursor in 7.3 apparently does not let 
> you scroll back to its first row at all!

Oh?

regression=# begin;
BEGIN
regression=# declare c cursor for select * from int8_tbl;
DECLARE CURSOR
regression=# fetch all from c;       q1        |        q2
------------------+-------------------             123 |               456             123 |
45678901234567894567890123456789|               1234567890123456789 |  45678901234567894567890123456789 |
-4567890123456789
(5 rows)

regression=# move backward all in c;
MOVE 5
regression=# fetch all from c;       q1        |        q2
------------------+-------------------             123 |               456             123 |
45678901234567894567890123456789|               1234567890123456789 |  45678901234567894567890123456789 |
-4567890123456789
(5 rows)

regression=#


I believe it is true though that backing up a cursor only works for
certain plan types (seqscan, indexscan, sort, maybe a couple others).
That has always been true --- 7.3 is no better nor worse than prior
releases.
        regards, tom lane


Re: More on cursors in 7.3

From
"Jeroen T. Vermeulen"
Date:
On Sun, Dec 08, 2002 at 04:28:38PM -0500, Tom Lane wrote:
> 
> I believe it is true though that backing up a cursor only works for
> certain plan types (seqscan, indexscan, sort, maybe a couple others).
> That has always been true --- 7.3 is no better nor worse than prior
> releases.

Ah, I didn't know that.  I guess the plan for "select * from pg_tables"
must have changed in 7.3 then.

Is any of this described in the docs somewhere?


Jeroen



Re: More on cursors in 7.3

From
Tom Lane
Date:
"Jeroen T. Vermeulen" <jtv@xs4all.nl> writes:
> Ah, I didn't know that.  I guess the plan for "select * from pg_tables"
> must have changed in 7.3 then.

[looks...]  Yeah, there's a join to pg_namespace in there now.

> Is any of this described in the docs somewhere?

Fraid not.
        regards, tom lane


Re: More on cursors in 7.3

From
"Jeroen T. Vermeulen"
Date:
On Sun, Dec 08, 2002 at 05:09:09PM -0500, Tom Lane wrote:
> 
> > Is any of this described in the docs somewhere?
> 
> Fraid not.

Damn & blast.  I was rather counting on cursors that could back up for
my nifty CachedResult class (which acts more or less like a normal result 
set but transparently fetches rows on demand).

Now if I understood a bit more of what's going on here, at least I could
document it...


Jeroen



Re: More on cursors in 7.3

From
Tom Lane
Date:
"Jeroen T. Vermeulen" <jtv@xs4all.nl> writes:
> Now if I understood a bit more of what's going on here, at least I could
> document it...

Well, you could dig through backend/executor/node*.c and see which of
the node types pay attention to es_direction.  To a first approximation
it looks like these do:

Functionscan
Append
Indexscan
Mergejoin
Limit
Material
Subqueryscan
Seqscan
Sort
Tidscan

although I have not thought about which other upper plan nodes might be
okay (ie, they're safe if their input nodes are).  Also, a Material or
Sort node will hide any direction-unsafety in its input.
        regards, tom lane


Re: More on cursors in 7.3

From
"Jeroen T. Vermeulen"
Date:
On Sun, Dec 08, 2002 at 05:28:22PM -0500, Tom Lane wrote:
> 
> Well, you could dig through backend/executor/node*.c and see which of
> the node types pay attention to es_direction.  To a first approximation
> it looks like these do:
I'll be honest with you: I don't know much about the internals and this
is pure Greek to me...  And I never was much good at Greek in school.


> although I have not thought about which other upper plan nodes might be
> okay (ie, they're safe if their input nodes are).  Also, a Material or
> Sort node will hide any direction-unsafety in its input.

More Greek, I'm afraid.  :-(


Jeroen