Re: [PATCHES] A bug in gist code with fetch/move - Mailing list pgsql-bugs

Your patch has been added to the PostgreSQL unapplied patches list at:

    http://candle.pha.pa.us/cgi-bin/pgpatches

I will try to apply it within the next 48 hours.

---------------------------------------------------------------------------


Dmitry Tkach wrote:
> ============================================================================
>                         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
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026