precheck_testcase.sql

-- Run this test case with the !so->scanBehind gating condition removed from
-- _bt_readpage's precheck code, like so:

/*

diff --git a/src/backend/access/nbtree/nbtsearch.c b/src/backend/access/nbtree/nbtsearch.c
index be201652c..40787311d 100644
--- a/src/backend/access/nbtree/nbtsearch.c
+++ b/src/backend/access/nbtree/nbtsearch.c
@@ -1651,7 +1651,7 @@ _bt_readpage(IndexScanDesc scan, ScanDirection dir, OffsetNumber offnum,
-    if (!firstPage && !so->scanBehind && minoff < maxoff)
+    if (!firstPage && minoff < maxoff)
     {
         ItemId      iid;
         IndexTuple  itup;

*/

-- Running this script when the patch has been built with these modifcations
-- will lead to an assertion failure:
-- TRAP: failed Assert("res == _bt_check_compare(dir, so, tuple, tupnatts, tupdesc, arrayKeys, false, false, &dcontinuescan, &dikey)"), File: "../source/src/backend/access/nbtree/nbtutils.c", Line: 3488, PID: 430843
--
-- (Plus non-assert builds will get the wrong answer, due to _bt_readpage
-- returning a row that doesn't really satisfy the scan's qual)

-- Setup
drop table if exists precheck_testcase;
set enable_bitmapscan to on;
set enable_indexonlyscan to off;
set enable_indexscan to off;

-- Index has to have very wide tuples, to make it easy to defeat suffix
-- truncation:
create unlogged table precheck_testcase(a int4, b int4, c int4, filler text);
alter table plain_precheck_testcase set (autovacuum_enabled=off);
alter table precheck_testcase alter column filler set storage plain;
create index plain_precheck_testcase on precheck_testcase(a, b, c, filler);

-- Bulk insert:
insert into precheck_testcase
select
  42,
  i % 5,
  i,
  repeat(chr(ascii('0')), 2500)
from
  generate_series(1, 20) i;

-- Now delete a much smaller tuple (no filler) that's going to be the one the
-- query returns from end of first page (page at blkno 1):
insert into precheck_testcase select 41, -1, -1;
-- Delete other tuples in blkno 1:
delete from precheck_testcase
where a = 42
  and b = 0
  and c < 15;
-- Make sure the deleted tuples are gone from index:
vacuum verbose precheck_testcase;

select a, b, c
from precheck_testcase
where
  a = 42
  and b in (1, 2, 6, 7)
  and c in (7, 17);