test_update_inh.sh

test_time=60
dropdb --if-exists test_update
createdb test_update
for proto in "simple" "prepared"; do
for ncols in 10 20 40; do
for nparts in 64 128 256 1024; do
	echo "$ncols columns $nparts partitions"
	echo "select 'drop table if exists test_$ncols"_"$nparts, foo; create unlogged table foo (b int primary key, a int); create table test_$ncols"_"$nparts (a int primary key, b int default null, c int default 0) partition by hash (a);' union all select 'alter table test_$ncols"_"$nparts add column f' || i || ' text default ''xxxxx'';' from generate_series(3, 40) i;" > /tmp/test_table.sql
	echo "\gexec" >> /tmp/test_table.sql
	echo "select 'create unlogged table test_$ncols"_"$nparts' || i || ' partition of test_$ncols"_"$nparts for values with (modulus $nparts, remainder ' || i || ');' from generate_series(0, $nparts-1) i;" >> /tmp/test_table.sql
	echo "\gexec" >> /tmp/test_table.sql
	psql test_update -f /tmp/test_table.sql > /dev/null 2>&1
	psql test_update -c "insert into foo (b) select generate_series(1, 1000000)" > /dev/null 2>&1

	echo "\set a random(1, 1000000)" > /tmp/nojoin.sql
	echo "update test_$ncols"_"$nparts t set b = :a where a = :a;" >> /tmp/nojoin.sql
	echo "\set a random(1, 1000000)" > /tmp/join.sql
	echo "update test_$ncols"_"$nparts t set b = foo.b from foo where t.a = foo.a and foo.b = :a;" >> /tmp/join.sql
	for test_file in nojoin; do
		echo $test_file $proto
		for i in 1 2 3; do
			psql test_update -c \
			"truncate test_$ncols\"_\"$nparts; \
			 insert into test_$ncols\"_\"$nparts (a) select generate_series(1, 1000000)" > /dev/null 2>&1;
			pgbench test_update -n -T $test_time -M$proto -f /tmp/"$test_file".sql | grep tps
		done
	done
	echo ""
done
done
done