#!/bin/bash

# scale
scale=2000
iterations=3

# test specifications
ntests=1
#testname[ntests]="two short fields, no change"
#testinit[ntests]="
#create table updatetest (id int4, data1 text) with (fillfactor=50);
#-- initialize
#insert into updatetest select a*$scale + b, 'foo' from generate_series(1, 5000) a, generate_series(1, $scale) b;
#"
# testupdate[ntests]="update updatetest set data1 = data1;"
# let "ntests++"

#testname[ntests]="two short fields, one changed"
#testinit[ntests]="
#create table updatetest (id int4, data1 text) with (fillfactor=50);
#-- initialize
#insert into updatetest select a*$scale + b, 'foo' from generate_series(1, 5000) a, generate_series(1, $scale) b;
#"
#testupdate[ntests]="update updatetest set data1 = 'bar';"
#let "ntests++"

#testname[ntests]="two short fields, both changed"
#testinit[ntests]="
#create table updatetest (id int4, data1 text) with (fillfactor=50);
#-- initialize
#insert into updatetest select a*$scale + b, 'foo' from generate_series(1, 5000) a, generate_series(1, $scale) b;
#"
#testupdate[ntests]="update updatetest set id = -id, data1 = 'bar';"
#let "ntests++"

testname[ntests]="one short and one long field, no change"
testinit[ntests]="
create table updatetest (id int4, data1 text) with (fillfactor=50);
-- initialize
insert into updatetest select a*$scale + b, repeat('f', 200) from generate_series(1, 1000) a, generate_series(1, $scale) b;
"
testupdate[ntests]="update updatetest set data1 = data1;"
let "ntests++"

#testname[ntests]="ten tiny fields, all changed"
#testinit[ntests]="
#create table updatetest (id int4, b1 bool, b2 bool, b3 bool, b4 bool, b5 bool, b6 bool, b7 bool, b8 bool, b9 bool, b10 bool) with (fillfactor=50);
#-- initialize
#insert into updatetest select a*$scale + b, 't', 't', 't', 't', 't', 't', 't', 't', 't', 't'
#from generate_series(1, 5000) a, generate_series(1, $scale) b;
#"
#testupdate[ntests]="update updatetest set id = -id, b1 = 'f', b2 = 'f', b3 = 'f', b4 = 'f', b5 = 'f', b6 = 'f', b7 = 'f', b8 = 'f', b9 = 'f', b10 = 'f';"
#let "ntests++"

testname[ntests]="hundred tiny fields, all changed"
testinit[ntests]="
create table updatetest (id int4,
b1 bool, b2 bool, b3 bool, b4 bool, b5 bool, b6 bool, b7 bool, b8 bool, b9 bool, b10 bool,
b11 bool, b12 bool, b13 bool, b14 bool, b15 bool, b16 bool, b17 bool, b18 bool, b19 bool, b20 bool,
b21 bool, b22 bool, b23 bool, b24 bool, b25 bool, b26 bool, b27 bool, b28 bool, b29 bool, b30 bool,
b31 bool, b32 bool, b33 bool, b34 bool, b35 bool, b36 bool, b37 bool, b38 bool, b39 bool, b40 bool,
b41 bool, b42 bool, b43 bool, b44 bool, b45 bool, b46 bool, b47 bool, b48 bool, b49 bool, b50 bool,
b51 bool, b52 bool, b53 bool, b54 bool, b55 bool, b56 bool, b57 bool, b58 bool, b59 bool, b60 bool,
b61 bool, b62 bool, b63 bool, b64 bool, b65 bool, b66 bool, b67 bool, b68 bool, b69 bool, b70 bool,
b71 bool, b72 bool, b73 bool, b74 bool, b75 bool, b76 bool, b77 bool, b78 bool, b79 bool, b80 bool,
b81 bool, b82 bool, b83 bool, b84 bool, b85 bool, b86 bool, b87 bool, b88 bool, b89 bool, b90 bool,
b91 bool, b92 bool, b93 bool, b94 bool, b95 bool, b96 bool, b97 bool, b98 bool, b99 bool, b100 bool
) with (fillfactor=50);
-- initialize
insert into updatetest select a*$scale + b,
't', 't', 't', 't', 't', 't', 't', 't', 't', 't',  't', 't', 't', 't', 't', 't', 't', 't', 't', 't',
't', 't', 't', 't', 't', 't', 't', 't', 't', 't',  't', 't', 't', 't', 't', 't', 't', 't', 't', 't',
't', 't', 't', 't', 't', 't', 't', 't', 't', 't',  't', 't', 't', 't', 't', 't', 't', 't', 't', 't',
't', 't', 't', 't', 't', 't', 't', 't', 't', 't',  't', 't', 't', 't', 't', 't', 't', 't', 't', 't',
't', 't', 't', 't', 't', 't', 't', 't', 't', 't',  't', 't', 't', 't', 't', 't', 't', 't', 't', 't'
from generate_series(1, 1000) a, generate_series(1, $scale) b;"
testupdate[ntests]="update updatetest set id = -id,
b1 = 'f', b2 = 'f', b3 = 'f', b4 = 'f', b5 = 'f', b6 = 'f', b7 = 'f', b8 = 'f', b9 = 'f', b10 = 'f',
b11 = 'f', b12 = 'f', b13 = 'f', b14 = 'f', b15 = 'f', b16 = 'f', b17 = 'f', b18 = 'f', b19 = 'f', b20 = 'f',
b21 = 'f', b22 = 'f', b23 = 'f', b24 = 'f', b25 = 'f', b26 = 'f', b27 = 'f', b28 = 'f', b29 = 'f', b30 = 'f',
b31 = 'f', b32 = 'f', b33 = 'f', b34 = 'f', b35 = 'f', b36 = 'f', b37 = 'f', b38 = 'f', b39 = 'f', b40 = 'f',
b41 = 'f', b42 = 'f', b43 = 'f', b44 = 'f', b45 = 'f', b46 = 'f', b47 = 'f', b48 = 'f', b49 = 'f', b50 = 'f',
b51 = 'f', b52 = 'f', b53 = 'f', b54 = 'f', b55 = 'f', b56 = 'f', b57 = 'f', b58 = 'f', b59 = 'f', b60 = 'f',
b61 = 'f', b62 = 'f', b63 = 'f', b64 = 'f', b65 = 'f', b66 = 'f', b67 = 'f', b68 = 'f', b69 = 'f', b70 = 'f',
b71 = 'f', b72 = 'f', b73 = 'f', b74 = 'f', b75 = 'f', b76 = 'f', b77 = 'f', b78 = 'f', b79 = 'f', b80 = 'f',
b81 = 'f', b82 = 'f', b83 = 'f', b84 = 'f', b85 = 'f', b86 = 'f', b87 = 'f', b88 = 'f', b89 = 'f', b90 = 'f',
b91 = 'f', b92 = 'f', b93 = 'f', b94 = 'f', b95 = 'f', b96 = 'f', b97 = 'f', b98 = 'f', b99 = 'f', b100 = 'f';"
let "ntests++"

testname[ntests]="hundred tiny fields, half changed"
testinit[ntests]="
create table updatetest (id int4,
b1 bool, b2 bool, b3 bool, b4 bool, b5 bool, b6 bool, b7 bool, b8 bool, b9 bool, b10 bool,
b11 bool, b12 bool, b13 bool, b14 bool, b15 bool, b16 bool, b17 bool, b18 bool, b19 bool, b20 bool,
b21 bool, b22 bool, b23 bool, b24 bool, b25 bool, b26 bool, b27 bool, b28 bool, b29 bool, b30 bool,
b31 bool, b32 bool, b33 bool, b34 bool, b35 bool, b36 bool, b37 bool, b38 bool, b39 bool, b40 bool,
b41 bool, b42 bool, b43 bool, b44 bool, b45 bool, b46 bool, b47 bool, b48 bool, b49 bool, b50 bool,
b51 bool, b52 bool, b53 bool, b54 bool, b55 bool, b56 bool, b57 bool, b58 bool, b59 bool, b60 bool,
b61 bool, b62 bool, b63 bool, b64 bool, b65 bool, b66 bool, b67 bool, b68 bool, b69 bool, b70 bool,
b71 bool, b72 bool, b73 bool, b74 bool, b75 bool, b76 bool, b77 bool, b78 bool, b79 bool, b80 bool,
b81 bool, b82 bool, b83 bool, b84 bool, b85 bool, b86 bool, b87 bool, b88 bool, b89 bool, b90 bool,
b91 bool, b92 bool, b93 bool, b94 bool, b95 bool, b96 bool, b97 bool, b98 bool, b99 bool, b100 bool
) with (fillfactor=50);
-- initialize
insert into updatetest select a*$scale + b,
't', 't', 't', 't', 't', 't', 't', 't', 't', 't',  't', 't', 't', 't', 't', 't', 't', 't', 't', 't',
't', 't', 't', 't', 't', 't', 't', 't', 't', 't',  't', 't', 't', 't', 't', 't', 't', 't', 't', 't',
't', 't', 't', 't', 't', 't', 't', 't', 't', 't',  't', 't', 't', 't', 't', 't', 't', 't', 't', 't',
't', 't', 't', 't', 't', 't', 't', 't', 't', 't',  't', 't', 't', 't', 't', 't', 't', 't', 't', 't',
't', 't', 't', 't', 't', 't', 't', 't', 't', 't',  't', 't', 't', 't', 't', 't', 't', 't', 't', 't'
from generate_series(1, 1000) a, generate_series(1, $scale) b;"
testupdate[ntests]="update updatetest set id = -id,
b1 = 'f', b2 = 't', b3 = 'f', b4 = 't', b5 = 'f', b6 = 't', b7 = 'f', b8 = 't', b9 = 'f', b10 = 't',
b11 = 'f', b12 = 't', b13 = 'f', b14 = 't', b15 = 'f', b16 = 't', b17 = 'f', b18 = 't', b19 = 'f', b20 = 't',
b21 = 'f', b22 = 't', b23 = 'f', b24 = 't', b25 = 'f', b26 = 't', b27 = 'f', b28 = 't', b29 = 'f', b30 = 't',
b31 = 'f', b32 = 't', b33 = 'f', b34 = 't', b35 = 'f', b36 = 't', b37 = 'f', b38 = 't', b39 = 'f', b40 = 't',
b41 = 'f', b42 = 't', b43 = 'f', b44 = 't', b45 = 'f', b46 = 't', b47 = 'f', b48 = 't', b49 = 'f', b50 = 't',
b51 = 'f', b52 = 't', b53 = 'f', b54 = 't', b55 = 'f', b56 = 't', b57 = 'f', b58 = 't', b59 = 'f', b60 = 't',
b61 = 'f', b62 = 't', b63 = 'f', b64 = 't', b65 = 'f', b66 = 't', b67 = 'f', b68 = 't', b69 = 'f', b70 = 't',
b71 = 'f', b72 = 't', b73 = 'f', b74 = 't', b75 = 'f', b76 = 't', b77 = 'f', b78 = 't', b79 = 'f', b80 = 't',
b81 = 'f', b82 = 't', b83 = 'f', b84 = 't', b85 = 'f', b86 = 't', b87 = 'f', b88 = 't', b89 = 'f', b90 = 't',
b91 = 'f', b92 = 't', b93 = 'f', b94 = 't', b95 = 'f', b96 = 't', b97 = 'f', b98 = 't', b99 = 'f', b100 = 't';"
let "ntests++"

testname[ntests]="hundred tiny fields, half nulled"
testinit[ntests]="
create table updatetest (id int4,
b1 bool, b2 bool, b3 bool, b4 bool, b5 bool, b6 bool, b7 bool, b8 bool, b9 bool, b10 bool,
b11 bool, b12 bool, b13 bool, b14 bool, b15 bool, b16 bool, b17 bool, b18 bool, b19 bool, b20 bool,
b21 bool, b22 bool, b23 bool, b24 bool, b25 bool, b26 bool, b27 bool, b28 bool, b29 bool, b30 bool,
b31 bool, b32 bool, b33 bool, b34 bool, b35 bool, b36 bool, b37 bool, b38 bool, b39 bool, b40 bool,
b41 bool, b42 bool, b43 bool, b44 bool, b45 bool, b46 bool, b47 bool, b48 bool, b49 bool, b50 bool,
b51 bool, b52 bool, b53 bool, b54 bool, b55 bool, b56 bool, b57 bool, b58 bool, b59 bool, b60 bool,
b61 bool, b62 bool, b63 bool, b64 bool, b65 bool, b66 bool, b67 bool, b68 bool, b69 bool, b70 bool,
b71 bool, b72 bool, b73 bool, b74 bool, b75 bool, b76 bool, b77 bool, b78 bool, b79 bool, b80 bool,
b81 bool, b82 bool, b83 bool, b84 bool, b85 bool, b86 bool, b87 bool, b88 bool, b89 bool, b90 bool,
b91 bool, b92 bool, b93 bool, b94 bool, b95 bool, b96 bool, b97 bool, b98 bool, b99 bool, b100 bool
) with (fillfactor=50);
-- initialize
insert into updatetest select a*$scale + b,
't', 't', 't', 't', 't', 't', 't', 't', 't', 't',  't', 't', 't', 't', 't', 't', 't', 't', 't', 't',
't', 't', 't', 't', 't', 't', 't', 't', 't', 't',  't', 't', 't', 't', 't', 't', 't', 't', 't', 't',
't', 't', 't', 't', 't', 't', 't', 't', 't', 't',  't', 't', 't', 't', 't', 't', 't', 't', 't', 't',
't', 't', 't', 't', 't', 't', 't', 't', 't', 't',  't', 't', 't', 't', 't', 't', 't', 't', 't', 't',
't', 't', 't', 't', 't', 't', 't', 't', 't', 't',  't', 't', 't', 't', 't', 't', 't', 't', 't', 't'
from generate_series(1, 1000) a, generate_series(1, $scale) b;"
testupdate[ntests]="update updatetest set id = -id,
b1 = NULL, b2 = 't', b3 = NULL, b4 = 't', b5 = NULL, b6 = 't', b7 = NULL, b8 = 't', b9 = NULL, b10 = 't',
b11 = NULL, b12 = 't', b13 = NULL, b14 = 't', b15 = NULL, b16 = 't', b17 = NULL, b18 = 't', b19 = NULL, b20 = 't',
b21 = NULL, b22 = 't', b23 = NULL, b24 = 't', b25 = NULL, b26 = 't', b27 = NULL, b28 = 't', b29 = NULL, b30 = 't',
b31 = NULL, b32 = 't', b33 = NULL, b34 = 't', b35 = NULL, b36 = 't', b37 = NULL, b38 = 't', b39 = NULL, b40 = 't',
b41 = NULL, b42 = 't', b43 = NULL, b44 = 't', b45 = NULL, b46 = 't', b47 = NULL, b48 = 't', b49 = NULL, b50 = 't',
b51 = NULL, b52 = 't', b53 = NULL, b54 = 't', b55 = NULL, b56 = 't', b57 = NULL, b58 = 't', b59 = NULL, b60 = 't',
b61 = NULL, b62 = 't', b63 = NULL, b64 = 't', b65 = NULL, b66 = 't', b67 = NULL, b68 = 't', b69 = NULL, b70 = 't',
b71 = NULL, b72 = 't', b73 = NULL, b74 = 't', b75 = NULL, b76 = 't', b77 = NULL, b78 = 't', b79 = NULL, b80 = 't',
b81 = NULL, b82 = 't', b83 = NULL, b84 = 't', b85 = NULL, b86 = 't', b87 = NULL, b88 = 't', b89 = NULL, b90 = 't',
b91 = NULL, b92 = 't', b93 = NULL, b94 = 't', b95 = NULL, b96 = 't', b97 = NULL, b98 = 't', b99 = NULL, b100 = 't';"
let "ntests++"

testname[ntests]="nine short and one long field, thirty percent change"
testinit[ntests]="
create table updatetest (id1 int, id2 int, id3 int, id4 int, id5 int, id6 int, id7 int, id8 int, id9 int, data1 text) with (fillfactor=50);
-- initialize
insert into updatetest select a*$scale + b, 102, 103, 104, 105, 106, 107, 108, 109, random_text(80) from generate_series(1, 1000) a, generate_series(1, $scale) b;
"
testupdate[ntests]="update updatetest set id2=-id2, id3=-id3, id4=-id4, id5=-id5, id6=-id6, id7=-id7, id8=-id8, id9=-id9;"
let "ntests++"

testname[ntests]="ten long fields, all changed"
testinit[ntests]="
create table updatetest (id int4,
 data1 text, data2 text, data3 text, data4 text, data5 text, data6 text, data7 text, data8 text, data9 text, data10 text) with (fillfactor=50);
-- initialize
insert into updatetest select a*$scale + b, 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa','aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa','aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa','aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa','aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa','aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa','aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa','aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa','aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa','aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' from generate_series(1, 1000) a, generate_series(1, $scale) b;
"

testupdate[ntests]="update updatetest set id = -id, data1 = 'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb', data2 = 'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb', data3 = 'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb', data4 = 'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb', data5 = 'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb', data6 = 'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb', data7 = 'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb', data8 = 'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb', data9 = 'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb', data10 = 'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb';
"
let "ntests++"

psql -d postgres <<EOF
drop table if exists testresults;
create table testresults (testname text, patchname text, wal_generated numeric, duration numeric);

drop table if exists timer;
create table timer (startepoch numeric, startwal text);

create or replace function starttest() returns void as \$\$
insert into timer values (extract (epoch from clock_timestamp()), pg_current_xlog_location());
\$\$ language sql;

create or replace function finishtest(testname text, patchname text) returns void as \$\$
insert into testresults
select \$1, \$2,
       pg_xlog_location_diff(pg_current_xlog_location(), startwal),
       extract(epoch from clock_timestamp()) - startepoch
from timer;
\$\$ language sql;

Drop Function If Exists random_text(INTEGER);

CREATE OR REPLACE FUNCTION random_text(length INTEGER)
RETURNS TEXT
LANGUAGE SQL
AS \$\$ select string_agg(chr(32+(random()*96)::int), '') from generate_series(1,length); \$\$;
EOF

function inittest
{
    i=$1
    psql -d postgres -c "set client_min_messages='error'; drop table if exists updatetest;" > /dev/null
    psql -d postgres -c "${testinit[i]}"
    psql -d postgres -c "vacuum freeze updatetest"
    psql -d postgres -c "truncate timer"
    psql -d postgres -c "checkpoint"
}

function runtest
{
    i=$1
    updatesql="select starttest(); begin; ${testupdate[i]}; rollback;"

    echo "Running test $i / ${#testname[*]}: ${testname[i]}"
    for ((iter=1; iter <= $iterations; iter=iter+1))
    do
        # with patch
        inittest $i
	psql -d postgres <<EOF
$updatesql
select finishtest('${testname[i]}', '');
EOF
    done
}

for ((i=1; i <= "${#testname[*]}"; i=i+1)); do
    runtest $i
done
#runtest 8

psql -d postgres -c "select testname, wal_generated, duration from testresults"