create extension if not exists amcheck;
create extension if not exists pageinspect;

drop table if exists my_table;
drop type if exists my_type;

-- define an enum ordered a, b, c, d, but do it in a funky way that results in
-- odd OIDs that we can later mess around with
create type my_type as enum ('d');
alter type my_type add value 'c' before 'd';
alter type my_type add value 'b' before 'c';
alter type my_type add value 'a' before 'b';

create table my_table (my_column my_type, counter serial);
create index my_index on my_table(my_column);

create or replace function pretty_print_btree_recurse(pageno int,
                                                      height int,
                                                      level int)
returns void language plpgsql as
$$
declare
  v_spaces text := rpad('', (height - level) * 2, ' ');
  v_ctid text;
  v_subpageno int;
  v_key_hex text;
  v_key_oid oid;
  v_key_label text;
  v_page_description text;
  v_item int;
begin
  if height = level then
    v_page_description := 'root';
  elsif level = 0 then
    v_page_description := 'leaf';
  else
    v_page_description := 'intl';
  end if;
  for v_ctid, v_key_hex, v_item in
  select ctid::text, data, itemoffset
  from bt_page_items('my_index', pageno)
  loop
    if v_ctid like '(4294967295,%' then
      continue;
    end if;
    v_subpageno := (v_ctid::point)[0];
    v_key_oid := ('x' || substring(v_key_hex, 1, 2))::bit(8)::int +
                 (('x' || substring(v_key_hex, 4, 2))::bit(8)::int << 8);
    v_key_label := coalesce((select enumlabel from pg_enum where oid = v_key_oid::oid), '-inf');
    if level = 0 then
      raise info '% % page % item %: [%] -> heap tuple', v_spaces, v_page_description, pageno, v_item - 1, v_key_label;
    else
      raise info '% % page % item %: [%] -> page %', v_spaces, v_page_description, pageno, v_item, v_key_label, v_subpageno;
    end if;
    if level > 0 then
      perform pretty_print_btree_recurse(v_subpageno, height, level - 1);
    end if;
  end loop;
end;
$$;

create or replace function pretty_print_btree() returns void language plpgsql as
$$
declare
  v_root int;
  v_height int;
begin
  select root, level into v_root, v_height from bt_metap('my_index');
  if v_root = 0 then
    raise info 'empty btree';
  else
    perform pretty_print_btree_recurse(v_root, v_height, v_height);
  end if;
end;
$$;

truncate my_table;
insert into my_table (my_column) select 'a' from generate_series(1, 64000);
insert into my_table (my_column) select 'c' from generate_series(1, 64000);
insert into my_table (my_column) select 'b' from generate_series(1, 64000);
insert into my_table (my_column) select 'd' from generate_series(1, 64000);

delete from my_table where my_column = 'a' and counter > (select min(counter) from my_table where my_column = 'a');

delete from my_table where my_column = 'b' and counter > (select min(counter) from my_table where my_column = 'b');

delete from my_table where my_column = 'c' and counter > (select min(counter) from my_table where my_column = 'c');

delete from my_table where my_column = 'd' and counter > (select min(counter) from my_table where my_column = 'd');

vacuum my_table;

-- now we have a btree that looks like this:
--     Btree   =        [a|c]
--                      /   \
--                     /     \
--                    /       \
--                  [a]-------[c]
--                   |         |
--                   |         |
--                  [b]-------[d]

select pretty_print_btree();


set seq_page_cost to 100;
set enable_bitmapscan to false;

-- we can find b by index scan
select * from my_table where my_column = 'b';

-- now mess with the sort order of the enum, as a cheap hack way to simulate
-- a collation order change affecting a text field.  this switches the order
-- of 'b' and 'c' around in the enum

update pg_enum set enumsortorder = 999 where enumtypid = 'my_type'::regtype and enumlabel = 'c';
update pg_enum set enumsortorder = 0 where enumtypid = 'my_type'::regtype and enumlabel = 'b';
update pg_enum set enumsortorder = -1 where enumtypid = 'my_type'::regtype and enumlabel = 'c';

-- log out and back in again to nuke syscache


-- now we can't find 'b' by index scan anymore!
set seq_page_cost to 100;
set enable_bitmapscan to false;

select * from my_table where my_column = 'b';

-- let's see what amcheck says about that

select bt_index_check('my_index');
ERROR:  high key invariant violated for index "my_index"