From: Andrew Gierth <andrew@tao11.riddles.org.uk>
To: pgsql-hackers@lists.postgresql.org
Cc: kes-kes@yandex.ru
Subject: mark/restore failures on unsorted merge joins
Message-ID: <87o8jn50be.fsf@news-spur.riddles.org.uk>
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/26.3 (berkeley-unix)
Date: Mon, 23 Nov 2020 19:48:29 +0000
MIME-Version: 1.0
Content-Type: text/plain
Precedence: bulk

From a report by user "kes" on irc, I constructed this test case:

create table marktst (id integer, val numrange, exclude using gist (val with &&));
insert into marktst select i, numrange(i, i+1, '[)') from generate_series(1,1000) i;
vacuum marktst;
analyze marktst;

select * from (select val from marktst where val && numrange(10,11)) s1 full join (values (1)) v(a) on true;
ERROR:  function ammarkpos is not defined for index marktst_val_excl

for which the query plan is:
                                         QUERY PLAN                                         
--------------------------------------------------------------------------------------------
 Merge Full Join  (cost=0.14..4.21 rows=2 width=20)
   ->  Result  (cost=0.00..0.01 rows=1 width=4)
   ->  Index Only Scan using marktst_val_excl on marktst  (cost=0.14..4.18 rows=2 width=16)
         Index Cond: (val && '[10,11)'::numrange)
(4 rows)

The problem is that the planner calls ExecSupportsMarkRestore to find
out whether a Materialize node is needed, and that function looks no
further than the Path type of T_Index[Only]Path in order to return true,
even though in this case it's a GiST index which does not support
mark/restore.

(Usually this can't be a problem because the merge join would need
sorted input, thus the index scan would be a btree; but a merge join
that doesn't actually have any sort keys could take unsorted input from
any index type.)

Going forward, this looks like IndexOptInfo needs another am* boolean
field, but that's probably not appropriate for the back branches; maybe
as a workaround, ExecSupportsMarkRestore should just check for btree?

-- 
Andrew (irc:RhodiumToad)