#!/usr/bin/env bash
#
# test fillfactor with pgbench
#

# scale 10 is too small (~ 150 MB), try 100/1000?
SIZE=100
TIME=200
TSIZE=30
RUNS=36
NCLIENTS=2
PORT=20000
# BENCH="-N" # simple update
BENCH="-f bench.sql" # larger & simpler updates

function err()
{
  local status=$1
  shift
  echo "$@" >&2
  exit $status
}

# check that all commands are available
for cmd in initdb createdb pg_ctl psql pgbench ; do
  type $cmd || echo "command $cmd not found!"
done

for FILLFACTOR in 95 100 ; do
  name="test-$FILLFACTOR.$$"
  echo "# $name: $(date)"
  DATA=./$name.data
  LOG=./$name.log
  TEST=./$name.out
  # run tests
  initdb $DATA || exit 2
  {
	echo "# FILLFACTOR = $FILLFACTOR"
	echo "port = $PORT"
	echo "log_autovacuum_min_duration = 100" # ms
	echo "log_checkpoints = on"
    echo "shared_preload_libraries = 'pg_stat_statements'"
  } >> $DATA/postgresql.conf
  pg_ctl -D $DATA -l $LOG start || err 2 "pg_ctl start"
  sleep 5
  createdb -h localhost -p $PORT test || err 3 "createdb"
  pgbench -h localhost -p $PORT -i -s $SIZE -F ${FILLFACTOR:-100} test || \
	err 4 "pgbench -i"
  {
    if [ "$TSIZE" ] ; then
      # change tuple size if required
      for t in accounts tellers branches history ; do
  	    # we do not want the "filler" column compressed:
	    # choose PLAIN or EXTERNAL to avoid compression
	  echo "ALTER TABLE pgbench_$t" \
	      "DROP COLUMN filler," \
	      "ADD COLUMN filler CHAR($TSIZE) NOT NULL DEFAULT ''," \
	      "ALTER COLUMN filler SET STORAGE EXTERNAL;"
	  done
    fi
    cat <<EOF
\x
SHOW ALL;
\timing on
VACUUM FULL;
\timing off
-- checks
\d+ pgbench_accounts
SELECT COUNT(*) AS "# accounts" FROM pgbench_accounts;
SELECT pg_size_pretty(pg_table_size('pgbench_accounts')) AS "accounts size";
SELECT pg_size_pretty(pg_database_size('test')) AS "database size";
CREATE EXTENSION pg_stat_statements;
-- such a function must exist somewhere?
CREATE OR REPLACE FUNCTION log(s TEXT) RETURNS BOOLEAN
VOLATILE STRICT AS \$\$
BEGIN
  RAISE WARNING '%', s;
  RETURN TRUE;
END;
\$\$ LANGUAGE plpgsql
EOF
} | psql -h localhost -p $PORT test >> $TEST 2>&1
  s=0
  while [[ $s -lt $RUNS ]] ; do
    let s++
    du -sh $DATA
    # show non default configuration parameters
    grep '^ *[a-z]' $DATA/postgresql.conf
    # BEGIN
    psql -h localhost -p $PORT test <<EOF
SELECT pg_stat_statements_reset();
SELECT pg_stat_reset_shared('bgwriter');
SELECT log('BEGIN $name $s');
EOF
    pgbench -h localhost -p $PORT -M prepared $BENCH -s $SIZE -T $TIME -c $NCLIENTS -P 1 test
	echo -n "# checkpoints: "
	grep "checkpoints " $LOG | wc -l
    psql -h localhost -p $PORT test <<EOF
\x
\timing on
-- hmmm, force a checkpoint before next round, to restart anew
CHECKPOINT;
-- N3: check that updates were performed
SELECT COUNT(*) AS "# accounts updated"
FROM pgbench_accounts
WHERE filler <> ''::CHAR(30);
SELECT COUNT(*) AS "# accounts" FROM pgbench_accounts;
\timing off
-- show sizes after run
SELECT pg_size_pretty(pg_table_size('pgbench_accounts')) AS "accounts size";
SELECT pg_size_pretty(pg_database_size('test')) AS "database size";
-- show summary of operations
SELECT * FROM pg_stat_statements
WHERE query ~* '^(SELECT|INSERT|UPDATE|DELETE).* pgbench_';
SELECT * FROM pg_stat_bgwriter;
SELECT log('END $name');
EOF
  done >> $TEST 2>&1
  # wait & cleanup
  sleep 10
  pg_ctl -D $DATA stop || err 5 "pg_ctl stop"
  # cleanup tmp instance
  rm -rf $DATA
done >> tests.out 2>&1