empty_temp_tables_test.psql

\set USER postgres
\set TEST_SIZE 10000
\set ON_ERROR_STOP
\timing on

/*
 * We are running tests with autovacuum turned off, to reduce variablity of test
 * results. So perform a manual vacuum and a checkpoint at the beginning of
 * every test.
 *
 * Other settings:
 *   checkpoint_segments = 300
 *   log_checkpoints = on
 *   
 */
vacuum full;
checkpoint;

\echo Creating function

create or replace function CreateTempTables(
								prefix varchar,
								deleteOnCOmmit boolean,
								numTables int,
								withARow boolean)
	returns void as $$
	declare
		str text;
	begin
		for i in 1..numTables loop
			str := 'create temporary table '
					|| prefix ||  '_' || i || '(a int)'
					|| case
						when deleteOnCOmmit then
							'on commit delete rows'
						else ''
						end;
			-- raise notice '%', str;
			execute str;
			if (withARow) then
				str := 'insert into ' || prefix ||  '_' || i || ' values(1)';
				-- raise notice '%', str;
				execute str;
			end if;
		end loop;
	end;
$$ language plpgsql;

\c - :USER

-- empty transaction to compare time taken to begin different transactions.
begin transaction;
commit transaction;

\c - :USER

begin transaction;

\echo Calling function to create :TEST_SIZE normal, empty, temp tables
select CreateTempTables('normal_empty', false, :TEST_SIZE, false);

commit transaction;

begin transaction;
commit transaction;

\c - :USER

begin transaction;

\echo Calling function to create :TEST_SIZE on-commit-delete-rows, empty, temp tables
select CreateTempTables('ocdr_empty', true, :TEST_SIZE, false);

commit transaction;

begin transaction;
commit transaction;

\c - :USER

begin transaction;

\echo Calling function to create :TEST_SIZE on-commit-delete-rows, 1 row, temp tables
select CreateTempTables('ocdr_onerow', true, :TEST_SIZE, true);

commit transaction;

begin transaction;
commit transaction;

/*

? Why do we get frequent checkpoint warnings even though these are all temp tables
  which are not supposed to be WAL logged?!
A: I saw VACUUM run by autovacuum daemons in pg_stat_activity, which were
   apparentlytrying to vacuum pg_class and pg_depends tables.

*/