copytests-run.sh

#!/bin/bash
#
# Test script. To use, first create the required test tables and data files
# by running copytests-setup.sql. Then run this script, and pipe the output
# to psql:
#
# cat copytests-setup.sql | psql postgres
# ./copytests-run.sh | psql postgres
#

cat <<EOF

-- a helper function to convert the xlog position returned by
-- pg_current_xlog_insert_location() to a bigint
CREATE OR REPLACE FUNCTION xlog_to_int(xlogpos varchar) RETURNS bigint AS \$\$
DECLARE
   xlogid_txt text;
   xrecoff_txt text;
   xlogid bigint;
   xrecoff bigint;
BEGIN
   xlogid_txt := split_part(xlogpos, '/', 1);
   xrecoff_txt := split_part(xlogpos, '/', 2);
   EXECUTE 'SELECT x''' || xlogid_txt || '00000000''::bigint' INTO xlogid;
   EXECUTE 'SELECT x''' || xrecoff_txt || '''::bigint' INTO xrecoff;
   RETURN xlogid + xrecoff ;
END; \$\$ LANGUAGE 'plpgsql' IMMUTABLE STRICT;

drop table if exists results;
CREATE TABLE results (testname text,
  begintime timestamp, endtime timestamp,
  beginxlog text, endxlog text);

EOF

testnames[1]="narrow"
testnames[2]="mid"
testnames[3]="wide"

# Test the non-logged cases first, 5 times each
for ((n=1; n <= 3; n++))
do
  testname=${testnames[$n]};

  # Repeat each test 5 times
  for ((i=1; i <= 5; i++))
  do
    cat <<EOF

CHECKPOINT;
BEGIN;
TRUNCATE ${testname}table;
INSERT INTO results (testname, begintime, beginxlog) VALUES ('$testname-unlogged', clock_timestamp(), pg_current_xlog_insert_location());
COPY ${testname}table FROM '/tmp/${testname}table';
UPDATE results set endtime = clock_timestamp(), endxlog = pg_current_xlog_insert_location() WHERE endtime IS NULL;
COMMIT;

EOF
  done
done

# Then the same, with TRUNCATE in separate transaction so that the COPY is
# WAL-logged
for ((n=1; n <= 3; n++))
do
  testname=${testnames[$n]};

  # Repeat each test 5 times
  for ((i=1; i <= 5; i++))
  do
    cat <<EOF

CHECKPOINT;
TRUNCATE ${testname}table;
BEGIN;
INSERT INTO results (testname, begintime, beginxlog) VALUES ('$testname-logged', clock_timestamp(), pg_current_xlog_insert_location());
COPY ${testname}table FROM '/tmp/${testname}table';
UPDATE results set endtime = clock_timestamp(), endxlog = pg_current_xlog_insert_location() WHERE endtime IS NULL;
COMMIT;

EOF
  done
done

# Display results
cat <<EOF

SELECT testname,
       MIN(endtime - begintime) AS mintime,
       AVG(endtime - begintime) AS avgtime,
       MAX(endtime - begintime) AS maxtime,
       MIN(xlog_to_int(endxlog) - xlog_to_int(beginxlog)) AS minxlog,
       ROUND(AVG(xlog_to_int(endxlog) - xlog_to_int(beginxlog))) AS avgxlog,
       MAX(xlog_to_int(endxlog) - xlog_to_int(beginxlog)) AS maxxlog
FROM results GROUP BY testname ORDER BY testname;

EOF