More on cursors in 7.3

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

"Jeroen T. Vermeulen" <jtv(at)xs4all(dot)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 | 4567890123456789
4567890123456789 | 123
4567890123456789 | 4567890123456789
4567890123456789 | -4567890123456789
(5 rows)

regression=# move backward all in c;
MOVE 5
regression=# fetch all from c;
q1 | q2
------------------+-------------------
123 | 456
123 | 4567890123456789
4567890123456789 | 123
4567890123456789 | 4567890123456789
4567890123456789 | -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

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

"Jeroen T. Vermeulen" <jtv(at)xs4all(dot)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

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

"Jeroen T. Vermeulen" <jtv(at)xs4all(dot)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

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