Re: Incorrect cursor behaviour with gist index

Teodor Sigaev <teodor(at)sigaev(dot)ru> writes:
> I'm back, sorry for a long absence.
> About this bug: http://archives.postgresql.org/pgsql-bugs/2008-09/msg00086.php

> Unfortunately, GiST index doesn't work with change direction of scan. I.e. it
> can't move forward then move backward and this behaviour was from the beginning.

> I think it's fixable, although GiST doesn't store on page left links (only right
> links) and doesn't store parent page pointer. That's needed because matched
> entries in GiST isn't stored consecutively unlike to BTree. So, price for it
> will be storing in memory or stack all numbers of visited pages in index even
> they will not be used. In worst case it's 2^32 of GISTSearchStack. Right now it
> occupies 24-32 bytes depending on sizeof(void*), and it will be needed to add
> one more pointer to make double-linked stack. So, about 32Mb. Is it acceptable?

> Nevertheless, it doesn't solve problem with concurrent page splitting what can
> cause different order between forward and backward scan in one cursor.

Seems like a lotta work for a partial solution :-(. Probably the path
of least resistance is to teach the planner that only some index AMs can
do backwards scan. That would result in a Materialize buffer getting
placed in front of the query if the user demanded scroll capability,
but it would cost nothing in the more typical case where backwards scan
isn't needed.

It should be sufficient to specify this in pg_am, right? Or could the
opclass or indexkey details affect it?

BTW, can GIN do backwards scan?

regards, tom lane