src/test/isolation2/input/workfile_mgr_test.source

CREATE OR REPLACE FUNCTION gp_workfile_mgr_test_on_master(testname text, numfiles int)
RETURNS setof bool
LANGUAGE C VOLATILE EXECUTE ON COORDINATOR AS '@abs_builddir@/isolation2_regress@DLSUFFIX@', 'gp_workfile_mgr_test_harness';

CREATE OR REPLACE FUNCTION gp_workfile_mgr_test_on_segments(testname text, numfiles int)
RETURNS setof bool
LANGUAGE C VOLATILE EXECUTE ON ALL SEGMENTS AS '@abs_builddir@/isolation2_regress@DLSUFFIX@', 'gp_workfile_mgr_test_harness';

CREATE FUNCTION gp_workfile_mgr_test(testname text, numfiles int)
RETURNS SETOF BOOL
AS
$$
	SELECT C.* FROM gp_workfile_mgr_test_on_master($1, $2) as C
	UNION ALL
	SELECT C.* FROM gp_workfile_mgr_test_on_segments($1, $2) as C
$$
LANGUAGE SQL;

CREATE OR REPLACE FUNCTION gp_workfile_mgr_create_workset(worksetname text, interXact bool, holdPin bool, closeFile bool)
RETURNS setof void 
LANGUAGE C VOLATILE EXECUTE ON ALL SEGMENTS AS '@abs_builddir@/isolation2_regress@DLSUFFIX@', 'gp_workfile_mgr_create_workset';

CREATE OR REPLACE FUNCTION gp_workfile_mgr_create_empty_workset(worksetname text)
RETURNS setof void 
LANGUAGE C VOLATILE EXECUTE ON ALL SEGMENTS AS '@abs_builddir@/isolation2_regress@DLSUFFIX@', 'gp_workfile_mgr_create_workset';

CREATE FUNCTION gp_workfile_mgr_cache_entries()
RETURNS TABLE(segid int4, prefix text, size int8, operation text, slice int4, sessionid int4, commandid int4, numfiles int4)
AS '$libdir/gp_workfile_mgr', 'gp_workfile_mgr_cache_entries'
LANGUAGE C VOLATILE EXECUTE ON ALL SEGMENTS;

-- Wait for at the most 1 min for backends to remove transient
-- workfile sets as part of exit processing and then report long lived
-- workfile sets.
create or replace function report_workfile_entries()
returns table(segid int4, prefix text, size int8, operation text, slice int4, numfiles int4) as $$
declare
    iterations int; /* in func */
    cnt int; /* in func */
begin
    iterations := 120; /* wait at the most 1 min */
    select count(*) into cnt from gp_workfile_mgr_cache_entries() w
    where w.prefix not like 'long_live_workset%'; /* in func */

    while (iterations > 0) and (cnt > 0)
    loop
        select count(*) into cnt from gp_workfile_mgr_cache_entries() w
        where w.prefix not like 'long_live_workset%'; /* in func */
        perform pg_sleep(0.5); /* sleep for half a second */
	iterations := iterations - 1; /* in func */
    end loop; /* in func */
    return query select w.segid, w.prefix, w.size, w.operation, w.slice, w.numfiles
                 from gp_workfile_mgr_cache_entries() w; /* in func */
end; /* in func */
$$
language plpgsql volatile execute on all segments;

-- start_ignore
!\retcode gpconfig -c gp_workfile_max_entries -v 32 --skipvalidation;
!\retcode gpstop -ari;
-- end_ignore

-- setup for workfile made in temp tablespace test
! mkdir -p '@testtablespace@/workfile_mgr';
1: DROP TABLESPACE IF EXISTS work_file_test_ts;
1: CREATE TABLESPACE work_file_test_ts LOCATION '@testtablespace@/workfile_mgr';

1: select gp_workfile_mgr_test('atomic_test', 0);

-- test will fail when the workset exceeds gp_workfile_max_entries, the workset will be released at the end of transaction.
1: select gp_workfile_mgr_test('workfile_fill_sharedcache', 0);
1: select segid, count(*) from gp_workfile_mgr_cache_entries() group by segid order by segid;

1: select gp_workfile_mgr_test('workfile_create_and_set_cleanup', 2000);
1: select gp_workfile_mgr_test('workfile_create_and_individual_cleanup', 2000);
1: select gp_workfile_mgr_test('workfile_made_in_temp_tablespace', 2000);
1: select gp_workfile_mgr_test('workfile_create_and_individual_cleanup_with_pinned_workfile_set', 2000);

1: DROP TABLESPACE work_file_test_ts;

-- start_ignore
!\retcode gpconfig -r gp_workfile_max_entries --skipvalidation;
!\retcode gpstop -ari;
-- end_ignore

-- test workset cleanup
2: begin;
2: select gp_workfile_mgr_create_workset('short_live_workset', false, false, false);
2: select gp_workfile_mgr_create_empty_workset('long_live_workset');
2: select segid, count(*) from gp_workfile_mgr_cache_entries() group by segid order by segid;

3: select gp_workfile_mgr_create_workset('inter_xact_workset', true, false, false);

-- transaction commit will cleanup the pinned workfile_set.
4: begin;
4: select gp_workfile_mgr_create_workset('commit_tnx_workset', false, true, false);
4: select gp_workfile_mgr_create_workset('commit_tnx_workset_empty', false, true, true);
4: select segid, prefix, size, operation, slice, numfiles from gp_workfile_mgr_cache_entries() order by (segid, prefix);
4: end;
4: select segid, prefix, size, operation, slice, numfiles from gp_workfile_mgr_cache_entries() order by (segid, prefix);

-- transaction abort will cleanup the workset.
4: begin;
4: select gp_workfile_mgr_create_workset('abort_tnx_workset', false, false, false);
4: select gp_workfile_mgr_create_workset('abort_tnx_workset_pinned', false, true, false);
4: select segid, prefix, size, operation, slice, numfiles from gp_workfile_mgr_cache_entries() order by (segid, prefix);
4: abort;
4: select segid, prefix, size, operation, slice, numfiles from gp_workfile_mgr_cache_entries() order by (segid, prefix);

-- for workset lives across transaction, e.g. with hold cursor, proc exit will cleanup the workset
3q:

-- The "q:" step does not wait for the backend process to exit.  So
-- wait at the most 1 min so that only the long lived sessions are
-- reported.  If we don't wait thte test sometimes fails because
-- "inter_xact_workset" entries show up in the output of
-- gp_workfile_mgr_cache_entries().
4: select * from report_workfile_entries();