Thread: Missing results from scroll cursor in PostgreSQL 8.3.3?

Hi there,

Following up a report on the PostGIS bugtracker (also submitted to
pgsql-bugs here:
http://archives.postgresql.org/pgsql-bugs/2008-09/msg00086.php), I'm
wondering if there is a bug in the way that GiST indexes interact with
scroll cursors.

I've managed to reproduce the bug using btree_gist rather than PostGIS
and have attached a test case for review. The key point is that if a
GiST index is used to pull results from a scroll cursor then "FETCH
ABSOLUTE X" fails to return any rows. I'm wondering if it could be
related to the fact the GiST indexes are not ordered? Perhaps the only
thing that is wrong is that a suitable ERROR message is missing?


ATB,

Mark.

--
Mark Cave-Ayland
Sirius Corporation - The Open Source Experts
http://www.siriusit.co.uk
T: +44 870 608 0063
--
-- Initial data
--

CREATE TABLE ctest (
    id int8,
    name varchar
);

INSERT INTO ctest (id, name) SELECT id, 'Test' || id FROM generate_series(1, 1000) AS id;

CREATE INDEX ctest_id_idx ON ctest(id);


--
-- Return absolute cursor records using sequential scan & index
--

BEGIN;

SET enable_seqscan = 't';

DECLARE CUR SCROLL CURSOR FOR SELECT * FROM ctest WHERE id >= 990;
FETCH ABSOLUTE -1 IN CUR;
FETCH ABSOLUTE 3 IN CUR;
CLOSE CUR;

SET enable_seqscan = 'f';

DECLARE CUR SCROLL CURSOR FOR SELECT * FROM ctest WHERE id >= 990;
FETCH ABSOLUTE -1 IN CUR;
FETCH ABSOLUTE 3 IN CUR;
CLOSE CUR;

COMMIT;


--
-- Rebuild with btree_gist
--

DROP INDEX ctest_id_idx;
CREATE INDEX ctest_id_gist_idx ON ctest USING gist(id gist_int8_ops);


--
-- Now try again... but this time no results are returned using GiST index scan?
--

BEGIN;

SET enable_seqscan = 't';

DECLARE CUR SCROLL CURSOR FOR SELECT * FROM ctest WHERE id >= 990::bigint;
FETCH ABSOLUTE -1 IN CUR;
FETCH ABSOLUTE 3 IN CUR;
CLOSE CUR;

SET enable_seqscan = 'f';

DECLARE CUR SCROLL CURSOR FOR SELECT * FROM ctest WHERE id >= 990::bigint;
FETCH ABSOLUTE -1 IN CUR;
FETCH ABSOLUTE 3 IN CUR;
CLOSE CUR;

COMMIT;

Mark Cave-Ayland <mark.cave-ayland@siriusit.co.uk> writes:
> Following up a report on the PostGIS bugtracker (also submitted to 
> pgsql-bugs here: 
> http://archives.postgresql.org/pgsql-bugs/2008-09/msg00086.php), I'm 
> wondering if there is a bug in the way that GiST indexes interact with 
> scroll cursors.

I remember Teodor remarking that there's some problem with fetching
backwards in a GIST indexscan, but I don't know the details --- in
particular, no idea whether it's fixable or we need to put in something
to prevent trying it.  The latter would be a bit of a PITA since right
now indexscans are assumed to support backwards scan regardless of
index type.
        regards, tom lane

On Thu, 2008-09-25 at 12:27 -0400, Tom Lane wrote:
> Mark Cave-Ayland <mark.cave-ayland@siriusit.co.uk> writes:
> > Following up a report on the PostGIS bugtracker (also submitted to 
> > pgsql-bugs here: 
> > http://archives.postgresql.org/pgsql-bugs/2008-09/msg00086.php), I'm 
> > wondering if there is a bug in the way that GiST indexes interact with 
> > scroll cursors.
> 
> I remember Teodor remarking that there's some problem with fetching
> backwards in a GIST indexscan, but I don't know the details --- in
> particular, no idea whether it's fixable or we need to put in something
> to prevent trying it.  The latter would be a bit of a PITA since right
> now indexscans are assumed to support backwards scan regardless of
> index type.

Does ABSOLUTE 3 do a backward scan? Hope not. Just rewind and forward
scan. Presumably the bug is present when you don't do ABSOLUTE -1 first?

Seems like GIST should be able to fake a backwards scan if needed.

-- Simon Riggs           www.2ndQuadrant.comPostgreSQL Training, Services and Support

Simon Riggs <simon@2ndQuadrant.com> writes:
> Seems like GIST should be able to fake a backwards scan if needed.

Well, it tries --- there is logic in there that pays attention to the
scan direction.  Like I say, I don't know the extent of the difficulty.

If we were to decide that it's unfixable, the right thing would be to
have the planner stick a materialize node on top, rather than invent
a specialized kluge for GIST.
        regards, tom lane