Thread: 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
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
This patch is being put on hold until Oleg can review it. Thanks. It
will remain in the queue so we don't lose it.
---------------------------------------------------------------------------
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
This patch no longer appears to be required. I think someone has fixed
it already. Thanks.
---------------------------------------------------------------------------
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