#!/bin/sh

# Insert tuples so that B-tree has three level (root-intermediate-leaf).
# We should insert as few tuples as possible to prevent checkpoint.
# If checkpoint does not run, we can insert more tuples.
# The following tuple number is for IA32 RHEL4 (update 4).
# Please refer to postgresql.conf parameters for the problem restriction.

#num=137450
num=27249
psql<<EOF
drop table if exists test;
create table test (
	name text
);
create index id_test on test (name);

-- Insert just before the B-tree level increases.
insert into test
select 'PostgreSQL-' || to_char(id, 'FM0000000000')
  from generate_series($num, 1, -1) as id;
select * from bt_metap('id_test');

-- Restrict the redo to tree-level increase.
checkpoint;

-- The next Insert increases B-tree tree level
insert into test values('PostgreSQL-0000000000');
select * from bt_metap('id_test');
select * from bt_page_items('id_test', 3) where itemoffset < 10;
EOF

# Before the crash, all the tuples are visible.
psql -a <<EOF
--  Visible tuples (name = HIKEY)
explain select * from test where name = 'PostgreSQL-0000000147';
select * from test where name = 'PostgreSQL-0000000147';

-- Still visible tuples (name = HIKEY)
explain select * from test where name = 'PostgreSQL-0000000146';
select * from test where name = 'PostgreSQL-0000000146';

-- They are vislble when no indexes are used
explain select * from test where name like '%PostgreSQL-0000000146';
select * from test where name like '%PostgreSQL-0000000146';
EOF

# Simulate the crash by killing all the background.
# Then perform the crash recovery
killall -9 postgres
echo "###########################################################"
echo "# Server crashed"
echo "###########################################################"
pg_ctl start -w

# Dump the brock where HIKEY exists.
psql<<EOF
select * from bt_metap('id_test');
select * from bt_page_items('id_test', 3) where itemoffset < 10;
EOF

# After the recovery, some tuples become invisible.
psql -a <<EOF
-- Visible tuples (name = HIKEY)
explain select * from test where name = 'PostgreSQL-0000000147';
select * from test where name = 'PostgreSQL-0000000147';

-- They become invisible! (name < HIKEY)
explain select * from test where name = 'PostgreSQL-0000000146';
select * from test where name = 'PostgreSQL-0000000146';

-- Without index, they're still visible.
explain select * from test where name like '%PostgreSQL-0000000146';
select * from test where name like '%PostgreSQL-0000000146';
EOF