Thread: Why does "fetch last from cur" always cause error 55000 for non-scrollable cursor?
Why does "fetch last from cur" always cause error 55000 for non-scrollable cursor?
From
Bryn Llewellyn
Date:
I’m using Version 15.2. I did this test:
-- Test One
start transaction;
declare cur no scroll cursor without hold for
select g.val as k, g.val*100 as v
from generate_series(1, 10) as g(val)
order by g.val;
fetch first from cur;
fetch next from cur;
fetch first from cur;
rollback;
declare cur no scroll cursor without hold for
select g.val as k, g.val*100 as v
from generate_series(1, 10) as g(val)
order by g.val;
fetch first from cur;
fetch next from cur;
fetch first from cur;
rollback;
The first "fetch first" succeeds—presumably (given that the current position for a freshly-declared cursor is before the first row of the result set) because scrolling forwards takes you to the first row. And "fetch next" succeeds too. But the next "fetch first" fails with error 55000 ("cursor can only scan forward"). This makes perfect sense. It's not the spelling of the statement that matters; rather, it's the requested effect, given the cursor's current position.
I can imagine, therefore, that "fetch last" might fail if the last row has already been fetched (for example with "fetch all") and you've fallen off the end of the result set.
But why does "fetch last" fail here:
-- Test Two
start transaction;
declare cur no scroll cursor without hold for
select g.val as k, g.val*100 as v
from generate_series(1, 10) as g(val)
order by g.val;
fetch first from cur;
fetch last from cur;
rollback;
declare cur no scroll cursor without hold for
select g.val as k, g.val*100 as v
from generate_series(1, 10) as g(val)
order by g.val;
fetch first from cur;
fetch last from cur;
rollback;
It needs only a forward scroll to get to the last row from this starting point.
I tried this as a sanity check:
-- Test Three
start transaction;
declare cur no scroll cursor without hold for
select g.val as k, g.val*100 as v
from generate_series(1, 10) as g(val)
order by g.val;
fetch first from cur;
fetch absolute 10 from cur;
declare cur no scroll cursor without hold for
select g.val as k, g.val*100 as v
from generate_series(1, 10) as g(val)
order by g.val;
fetch first from cur;
fetch absolute 10 from cur;
fetch next from cur;
rollback;
rollback;
No errors. (But "fetch next" gets an empty result, as expected.) In other words, I can fetch the last row, when my current position is before it, by addressing it absolutely. But I can't do this by spelling it "last".
Am I missing something? Or might this be a bug?
Finally, I tried the same tests in PG 11.19. There, in Test One, the second "fetch first" succeeds (and gets the right result). But it has to scroll backwards to do this. I'm guessing that the Version 11 behavior was regarded as a bug—and was fixed. When did the behavior change here?
However, Test Two and Test Three behave the same in Version 11 as in Version 15. So my question about "fetch last" applies there too.
Re: Why does "fetch last from cur" always cause error 55000 for non-scrollable cursor?
From
Tom Lane
Date:
Bryn Llewellyn <bryn@yugabyte.com> writes: > But why does "fetch last" fail here: > -- Test Two > start transaction; > declare cur no scroll cursor without hold for > select g.val as k, g.val*100 as v > from generate_series(1, 10) as g(val) > order by g.val; > fetch first from cur; > fetch last from cur; > rollback; FETCH LAST is implemented as "run forward to the end, then back up one". You could imagine adding more infrastructure to allow doing it without backup, but it'd be complicated (not least because the cursor's ending position would be subtly wrong). > Finally, I tried the same tests in PG 11.19. There, in Test One, the second "fetch first" succeeds (and gets the rightresult). But it has to scroll backwards to do this. I'm guessing that the Version 11 behavior was regarded as a bug—andwas fixed. When did the behavior change here? Probably here: https://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=c1b7a6c27 regards, tom lane
Re: Why does "fetch last from cur" always cause error 55000 for non-scrollable cursor?
From
Bryn Llewellyn
Date:
tgl@sss.pgh.pa.us wrote:bryn@yugabyte.com wrote:But why does "fetch last" fail here:-- Test Two
start transaction;
declare cur no scroll cursor without hold for
select g.val as k, g.val*100 as v
from generate_series(1, 10) as g(val)
order by g.val;fetch first from cur;
fetch last from cur;
rollback;
FETCH LAST is implemented as "run forward to the end, then back up one". You could imagine adding more infrastructure to allow doing it without backup, but it'd be complicated (not least because the cursor's ending position would be subtly wrong).Finally, I tried the same tests in PG 11.19. There, in Test One, the second "fetch first" succeeds (and gets the right result). But it has to scroll backwards to do this. I'm guessing that the Version 11 behavior was regarded as a bug—and was fixed. When did the behavior change here?
Probably here:
git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=c1b7a6c27
Thanks for the quick response, Tom. "Could be done—but not cost-effective effort" works for me.