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@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 | 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@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
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@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
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