Обсуждение: Scroll cursor oddity...

Поиск
Список
Период
Сортировка

Scroll cursor oddity...

От
Mike Aubury
Дата:
Does anyone know what the "correct" behaviour for a scroll cursor should be 
when you've scrolled past the end ?

If you take this SQL for example : 

  create temp table sometab ( a integer);  insert into sometab values(1);  insert into sometab values(2);  insert into
sometabvalues(3);  begin work;
 
  declare c1 scroll cursor for select * from sometab;  fetch next from c1;  fetch next from c1;  fetch next from c1;
fetchnext from c1;  fetch prior from c1;  fetch prior from c1;  fetch prior from c1;
 




The first 4 fetches work as expected and return 1,2,3, and the 4th fetch 
returns no rows as its at the end of the list...

** But ** - when I do the fetch prior, I would have expected it to go back to 
the '2' row, not the '3' row...

ie - under postgresql it appears we've scrolled *past* the last row and need 
an additional fetch to get back to our last row..



For reference - heres what I get as output : 


CREATE TABLE
INSERT 32429 1
INSERT 32430 1
INSERT 32431 1
BEGIN
DECLARE CURSORa
---1
(1 row)
a
---2
(1 row)
a
---3
(1 row)
a
---
(0 rows)
a
---3
(1 row)
a
---2
(1 row)
a
---1
(1 row)






TIA
-- 
Mike Aubury

Aubit Computing Ltd is registered in England and Wales, Number: 3112827
Registered Address : Clayton House,59 Piccadilly,Manchester,M1 2AQ




Re: Scroll cursor oddity...

От
Tom Lane
Дата:
Mike Aubury <mike.aubury@aubit.com> writes:
> ie - under postgresql it appears we've scrolled *past* the last row and need 
> an additional fetch to get back to our last row..

Why do you find that surprising?  It seems to me to be symmetrical with
the case at the beginning of the table --- the cursor is initially
positioned before the first row.  Why shouldn't there be a corresponding
state where it's positioned after the last row?
        regards, tom lane


Re: Scroll cursor oddity...

От
Gregory Stark
Дата:
"Tom Lane" <tgl@sss.pgh.pa.us> writes:

> Mike Aubury <mike.aubury@aubit.com> writes:
>> ie - under postgresql it appears we've scrolled *past* the last row and need 
>> an additional fetch to get back to our last row..
>
> Why do you find that surprising?  It seems to me to be symmetrical with
> the case at the beginning of the table --- the cursor is initially
> positioned before the first row.  Why shouldn't there be a corresponding
> state where it's positioned after the last row?

What's implied by that but perhaps not clear is that it's easier to think of
cursors as being *between* rows rather than *on* rows. I'm not sure the
standard entirely adopts that model however. 

--  Gregory Stark EnterpriseDB          http://www.enterprisedb.com Ask me about EnterpriseDB's PostGIS support!


Re: Scroll cursor oddity...

От
Tom Lane
Дата:
Gregory Stark <stark@enterprisedb.com> writes:
> What's implied by that but perhaps not clear is that it's easier to think of
> cursors as being *between* rows rather than *on* rows. I'm not sure the
> standard entirely adopts that model however. 

That's an interesting way of thinking about it, but I think it fails
when you consider UPDATE/DELETE WHERE CURRENT OF.
        regards, tom lane