Re: [PATCHES] A bug in gist code with fetch/move

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(at)openratings(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026