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
Attachments:
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.com
PostgreSQL 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