Dmitry Tkach ([EMAIL PROTECTED]) 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 specific to btree_gist - I ran into it with another
(custom) implementation, and then used btree_gist to verify it wasn't something I did
wrong with my implementation, and I got the same problem. So, it looks like the
problem is with the gist 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
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
