Thread: 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 |
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
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