twophase_bench.bash

#!/bin/bash

NUM_SESSIONS=100
NUM_TABLES=300
RUNTIME=30

# Create utility function for creating multiple tables and
# be able to lock them.
psql <<EOF
CREATE OR REPLACE FUNCTION create_tables(num_tables int)
  RETURNS VOID AS
  \$func\$
  BEGIN
  FOR i IN 1..num_tables LOOP
    EXECUTE format('
      CREATE TABLE IF NOT EXISTS %I (id int)', 't_' || i);
  END LOOP;
END
\$func\$ LANGUAGE plpgsql;

-- Lock many tables at once
CREATE OR REPLACE FUNCTION lock_tables(num_tables int)
  RETURNS VOID AS
  \$func\$
  BEGIN
  FOR i IN 1..num_tables LOOP
    EXECUTE format('LOCK t_' || i || ' IN SHARE MODE');
  END LOOP;
END
\$func\$ LANGUAGE plpgsql;
SELECT create_tables($NUM_TABLES);
EOF

# Create set of pgbench scripts
for i in `seq 1 $NUM_SESSIONS`; do
	cat <<EOF > bench_script_$i.sql
BEGIN;
SELECT lock_tables($NUM_TABLES);
PREPARE TRANSACTION 't_$i';
COMMIT PREPARED 't_$i';
EOF
done

# And do the runs with their results in dedicated log files
for i in `seq 1 $NUM_SESSIONS`; do
	pgbench -T $RUNTIME -n -f bench_script_$i.sql > bench_log_$i.txt &
done