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;
