Dmitry Tkach (dmitry@openratings.com) reports a bug with a severity of 2
The lower the number the more severe it is.
Short Description
move works incorrectly on cursors using GiST indexes
Long Description
If you declare a cursor for a query, that's using a gist index, then
fetch a few rows from it, and then move it backwards the same number of
rows, and fetch again, the output starts with the second row, not the first one as expected.
I am using btree_gist from contrib/ below as an example of a gist implementation, but note that the problem is not
specificto btree_gist - I ran into it with another (custom) implementation, and then used btree_gist to verify it
wasn'tsomething I did wrong with my implementation, and I got the same problem. So, it looks like the problem is with
thegist itself, not with any particular extension.
Sample Code
\i contrib/btree_gist/btree_gist.sql
create table test (x int);
insert into x values (1);
insert into x values (2);
insert into x values (3);
begin;
declare test_cursor for select * from x where x > 1;
fetch 1 from test_cursor;
x
---
2
(1 row)
move -1 in test_cursor;
MOVE 0
fetch 1 from test_cursor;
x
---
2
(1 row)
commit;
-- Works as expected so far... Now - THE PROBLEM:
create index test_idx on test using gist (x gist_int4_ops);
set enable_seqscan = false;
begin;
declare test_cursor for select * from x where x > 1;
fetch 1 from test_cursor;
x
---
2
(1 row)
move -1 in test_cursor;
MOVE 0
fetch 1 from test_cursor;
x
---
3 <---- HERE IT IS: it is supposed to return 2!!!
move -1 in test_cursor;
MOVE 1
fetch 1 from test_cursor;
x
---
3 <---- Now it works - the problem is only with refetching the FIRST
Note, that with a regular (btree) index it works ok, as it does with the sequentual scan...
No file was uploaded with this report