A bug in gist code with fetch/move

============================================================================
                        POSTGRESQL BUG REPORT TEMPLATE
============================================================================


Your name               :       Dima Tkach
Your email address      :       dmitry@openratings.com


System Configuration
---------------------
  Architecture (example: Intel Pentium)         : pentium

  Operating System (example: Linux 2.0.26 ELF)  : Linux  2.2.12-20

  PostgreSQL version (example: PostgreSQL-7.2.1): PostgreSQL-7.2.1

  Compiler used (example:  gcc 2.95.2)          : egcs-2.91.57


Please enter a FULL description of your problem:
------------------------------------------------

Move/fetch doesn't work correctly with cursors for queries that use GiST
indexes:
If you declare such a cursor, fetch a few rows, move it back the same number
of rows and fetch again, the output will start with the SECOND row, not the
first one.
Similarly, if you 'fetch all' from the cursor, and then 'fetch -1', you will
get the one BEFORE the last row, not the last one.

The problem is specific to GiST - it does not occur with btree or seq scan
(I did not test rtrees though) - but it is NOT related to any particular
extension. I ran into it with my own (custom) extension, and then reproduced
the same problem using btree_gist from contrib...

Please describe a way to repeat the problem.   Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------
This example uses btree_gist implementation from contrib, but the same problem
will occur with any GiST extension, as far as I know:

-- START OF EXAMPLE (run it in psql)

\i contrib/btree_gist.sql
create table test (x int);
insert into x values (1);
insert into x values (2);
create index test_idx on test using gist (x gist_int4_ops);
set enable_seqscan = false;

begin;
declare test_cursor cursor for select * from test where x > 0;
fetch 1 from test_cursor;
move -1 in test_cursor;

-- PROBLEM HERE:
-- The following command will return 2, although the correct output is 1

fetch 1 from test_cursor;

-- Or (it is actually, the same problem):
declare test_cursor cursor for select * from test where x > 0;
fetch all from test_cursor;

-- PROBLEM: This returns 1, but it should be 2

fetch -1 from test_cursor;

commit;

---------------------------------------------------------------------
-- AND HERE IS THE CORRECT BEHAVIOR:
---------------------------------------------------------------------
drop index test_idx;
begin;
declare test_cursor cursor for select * from test where x > 0;
fetch 1 from test_cursor;
move -1 in test_cursor;
fetch 1 from test_cursor; -- Returns 1 (CORRECT)
declare test_cursor cursor for select * from test where x > 0;
fetch all from test_cursor;
fetch -1 from test_cursor; -- Returns 2 (ALSO CORRECT)
commit;


-- END OF EXAMPLE




--------------------------------------------------------------------
If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------

I compared the gist code to btree, and it seems to me that the patch below
should do the job (I did compile it and verified that it fixes the problem):


*** gistget.c   Wed Apr  3 16:31:08 2002
--- gistget.c.old       Wed Apr  3 16:29:08 2002
***************
*** 164,173 ****

                        ReleaseBuffer(b);
                        if (so->s_stack == (GISTSTACK *) NULL)
-                       {
-                               ItemPointerSetInvalid (&(s->currentItemData));
                                return (RetrieveIndexResult) NULL;
-                       }

                        stk = so->s_stack;
                        b = ReadBuffer(s->relation, stk->gs_blk);
--- 164,170 ----



I hope, it helps...

Dima