gist-vacuum-test.sh

#!/bin/bash
#
# If you run this test script, with Test-left-jumps-2.patch applied, you should see
# lines like this in the log:
#
# RESCAN 154->3 TRIGGERED BY FollowRight
#
# and
# 
# RESCAN 154->16 TRIGGERED BY NSN
#
# and
#
# LOG:  fixing incomplete split in index "test_accounts_balance_idx", block 154

# Initialize test table
#
# The table has 1000000 accounts, initialized to 0 balance. There's a GiST index on balance.
psql <<EOF
DROP TABLE IF EXISTS test_accounts;
CREATE TABLE test_accounts (aid serial, balance int);
INSERT INTO test_accounts (balance) SELECT 0 FROM generate_series(1, 10000 + 1) g;
CREATE INDEX ON test_accounts USING gist (balance);
EOF

# Run the test for a while
#
# The test script picks two accounts at random. It increases the balance on one of them,
# and decreases the balance on the other by the same amount. The sum of all balances
# should stay 0.
cat > /tmp/gist-test-update.sql <<EOF
\set scale 10000

\set account1 random(1, :scale / 2)
\set delta random(1, 100)

begin;
update test_accounts SET balance = balance + :delta where aid = :account1;
update test_accounts SET balance = balance - :delta where aid = :account1 + 1;
commit;
EOF
cat > /tmp/gist-test-vacuum.sql <<EOF
set vacuum_cost_limit=30;
set vacuum_cost_delay=5;
VACUUM test_accounts;
EOF

# Script to verify the balances. The sum of negative and positive balances should be zero.
cat > /tmp/gist-verify.sql <<EOF
SET enable_seqscan=off;
SELECT sum(balance) FROM test_accounts WHERE balance < 0
UNION ALL
SELECT sum(balance) FROM test_accounts WHERE balance > 0;
EOF

pgbench -n -f /tmp/gist-test-update.sql@500 -f /tmp/gist-test-vacuum.sql@1 -T 10 -c4 -j4
psql -f /tmp/gist-verify.sql