\getenv abs_builddir PG_ABS_BUILDDIR
\set temp_tablespace :abs_builddir '/testtablespace_temp_tablespace'
\set dddefault_tablepace :abs_builddir '/testtablespace_default_tablespace'
create tablespace some_temp_tablespace location :'temp_tablespace';
create tablespace some_default_tablespace location :'dddefault_tablepace';
-- Given I've set up GUCS for how to use tablespaces
set temp_tablespaces to some_temp_tablespace;
set default_tablespace to 'some_default_tablespace';
-- When I create a temporary table
create temporary table some_table_in_temp_tablespace (a int);
-- Then the temp_tablespaces settings should be used
select count(1) from pg_class inner join pg_tablespace ON pg_class.reltablespace = pg_tablespace.oid where spcname = 'some_temp_tablespace' AND relname = 'some_table_in_temp_tablespace';
 count 
-------
     1
(1 row)

-- And the temp_tablespaces should be used on the segments too
select count(1) from gp_dist_random('pg_class') segment_pg_class inner join pg_tablespace on pg_tablespace.oid = segment_pg_class.reltablespace where relname = 'some_table_in_temp_tablespace' and spcname = 'some_temp_tablespace';
 count 
-------
     3
(1 row)

-- When I create a non-temporary table, the default_tablespace should be chosen
create table some_non_temp_table (a int);
select count(1) from pg_class inner join pg_tablespace ON pg_class.reltablespace = pg_tablespace.oid where spcname = 'some_default_tablespace' AND relname = 'some_non_temp_table';
 count 
-------
     1
(1 row)

-- And the default_tablespace should be used on the segments too
select count(1) from gp_dist_random('pg_class') segment_pg_class inner join pg_tablespace on pg_tablespace.oid = segment_pg_class.reltablespace where relname = 'some_non_temp_table' and spcname = 'some_default_tablespace';
 count 
-------
     3
(1 row)

drop table some_table_in_temp_tablespace;
drop table some_non_temp_table;
drop tablespace some_temp_tablespace;
drop tablespace some_default_tablespace;
reset default_tablespace;
reset temp_tablespaces;
-- When the GUC temp_tablespaces is set, one of the temp tablespaces is used instead of the default tablespace.
-- create several tablespaces and use them as temp tablespaces
-- all QD/QEs in one session should have the same temp tablespace
\set mytempsp0 :abs_builddir '/testtablespace_mytempsp0'
\set mytempsp1 :abs_builddir '/testtablespace_mytempsp1'
\set mytempsp2 :abs_builddir '/testtablespace_mytempsp2'
\set mytempsp3 :abs_builddir '/testtablespace_mytempsp3'
\set mytempsp4 :abs_builddir '/testtablespace_mytempsp4'
create tablespace mytempsp0 location :'mytempsp0';
create tablespace mytempsp1 location :'mytempsp1';
create tablespace mytempsp2 location :'mytempsp2';
create tablespace mytempsp3 location :'mytempsp3';
create tablespace mytempsp4 location :'mytempsp4';
CREATE TABLE tts_foo (i int, j int) distributed by(i);
insert into tts_foo select i, i from generate_series(1,80000)i;
ANALYZE tts_foo;
set gp_cte_sharing=on;
-- CBDB_PARALLEL_FIXME: since we disabled Share Input Scan in parallel mode, sisc_xslice_temp_files
-- will never be triggered. We need set max_parallel_workers_per_gather to 0 in this case.
set max_parallel_workers_per_gather = 0;
-- CASE 1: when temp_tablespaces is set, hashagg and share-input-scan
-- should honor the GUC and creates temp files under the specified tablespaces.
-- temp_tablespaces will synchronized to all segments
set temp_tablespaces=mytempsp0,mytempsp1,mytempsp2,mytempsp3,mytempsp4;
set statement_mem='2MB';
select gp_inject_fault('sisc_xslice_temp_files', 'skip', dbid)
  from gp_segment_configuration where role='p' and content>=0;
 gp_inject_fault 
-----------------
 Success:
 Success:
 Success:
(3 rows)

CREATE TEMP TABLE tts_bar as
WITH a1 as (select * from tts_foo),
     a2 as (select * from tts_foo)
    SELECT a1.i xx
       FROM a1
         INNER JOIN a2 ON a2.i = a1.i
         UNION ALL
         SELECT count(a1.i)
           FROM a1
             INNER JOIN a2 ON a2.i = a1.i
distributed by(xx);
NOTICE:  sisc_xslice: Use temp tablespace  (seg0 slice1 172.17.0.2:7002 pid=2944771)
NOTICE:  sisc_xslice: Use temp tablespace  (seg1 slice1 172.17.0.2:7003 pid=2944772)
NOTICE:  sisc_xslice: Use temp tablespace  (seg2 slice1 172.17.0.2:7004 pid=2944773)
-- Make sure the following fault injector is triggered.
select gp_wait_until_triggered_fault('sisc_xslice_temp_files', 1, dbid)
  from gp_segment_configuration where role='p' and content>=0;
 gp_wait_until_triggered_fault 
-------------------------------
 Success:
 Success:
 Success:
(3 rows)

select gp_inject_fault('sisc_xslice_temp_files', 'reset', dbid)
  from gp_segment_configuration where role='p' and content>=0;
 gp_inject_fault 
-----------------
 Success:
 Success:
 Success:
(3 rows)

-- test for hash agg
set statement_mem='1MB';
select gp_inject_fault('hashagg_spill_temp_files', 'skip', dbid)
  from gp_segment_configuration where role='p' and content>=0;
 gp_inject_fault 
-----------------
 Success:
 Success:
 Success:
(3 rows)

create temp table tts_hashagg as
select * from tts_foo group by i, j
distributed by(i);
NOTICE:  hashagg: Use temp tablespace  (seg1 slice1 172.17.0.2:7003 pid=2944795)
NOTICE:  hashagg: Use temp tablespace  (seg2 slice1 172.17.0.2:7004 pid=2944796)
NOTICE:  hashagg: Use temp tablespace  (seg0 slice1 172.17.0.2:7002 pid=2944794)
-- hashagg should spill on the temp tablespaces specified by temp_tablespaces
-- Make sure the following fault injector is triggered.
select gp_wait_until_triggered_fault('hashagg_spill_temp_files', 1, dbid)
  from gp_segment_configuration where role='p' and content>=0;
 gp_wait_until_triggered_fault 
-------------------------------
 Success:
 Success:
 Success:
(3 rows)

select gp_inject_fault('hashagg_spill_temp_files', 'reset', dbid)
  from gp_segment_configuration where role='p' and content>=0;
 gp_inject_fault 
-----------------
 Success:
 Success:
 Success:
(3 rows)

-- CASE 2: when temp_tablespaces is not set, hashagg and share-input-scan
-- should create temp files under the default tablespaces.
drop table tts_bar, tts_hashagg;
set temp_tablespaces='';
set statement_mem='2MB';
-- The following CTAS query should generate share input scan cross slices.
select gp_inject_fault('sisc_xslice_temp_files', 'skip', dbid)
  from gp_segment_configuration where role='p' and content>=0;
 gp_inject_fault 
-----------------
 Success:
 Success:
 Success:
(3 rows)

CREATE TEMP TABLE tts_bar as
WITH a1 as (select * from tts_foo),
     a2 as (select * from tts_foo)
    SELECT a1.i xx
       FROM a1
         INNER JOIN a2 ON a2.i = a1.i
         UNION ALL
         SELECT count(a1.i)
           FROM a1
             INNER JOIN a2 ON a2.i = a1.i
distributed by(xx);
NOTICE:  sisc_xslice: Use default tablespace  (seg0 slice1 172.17.0.2:7002 pid=2945819)
NOTICE:  sisc_xslice: Use default tablespace  (seg1 slice1 172.17.0.2:7003 pid=2945820)
NOTICE:  sisc_xslice: Use default tablespace  (seg2 slice1 172.17.0.2:7004 pid=2945821)
-- Make sure the following fault injector is triggered.
select gp_wait_until_triggered_fault('sisc_xslice_temp_files', 1, dbid)
  from gp_segment_configuration where role='p' and content>=0;
 gp_wait_until_triggered_fault 
-------------------------------
 Success:
 Success:
 Success:
(3 rows)

select gp_inject_fault('sisc_xslice_temp_files', 'reset', dbid)
  from gp_segment_configuration where role='p' and content>=0;
 gp_inject_fault 
-----------------
 Success:
 Success:
 Success:
(3 rows)

-- test for hash agg
set statement_mem='1MB';
select gp_inject_fault('hashagg_spill_temp_files', 'skip', dbid)
  from gp_segment_configuration where role='p' and content>=0;
 gp_inject_fault 
-----------------
 Success:
 Success:
 Success:
(3 rows)

create temp table tts_hashagg as
select * from tts_foo group by i, j
distributed by(i);
NOTICE:  hashagg: Use default tablespace  (seg0 172.17.0.2:7002 pid=2945760)
NOTICE:  hashagg: Use default tablespace  (seg1 172.17.0.2:7003 pid=2945761)
NOTICE:  hashagg: Use default tablespace  (seg2 172.17.0.2:7004 pid=2945762)
-- hashagg should spill on the default tablespaces.
-- Make sure the following fault injector is triggered.
select gp_wait_until_triggered_fault('hashagg_spill_temp_files', 1, dbid)
  from gp_segment_configuration where role='p' and content>=0;
 gp_wait_until_triggered_fault 
-------------------------------
 Success:
 Success:
 Success:
(3 rows)

select gp_inject_fault('hashagg_spill_temp_files', 'reset', dbid)
  from gp_segment_configuration where role='p' and content>=0;
 gp_inject_fault 
-----------------
 Success:
 Success:
 Success:
(3 rows)

-- CBDB_PARALLEL_FIXME: we need to reset max_parallel_workers_per_gather since we changed it.
reset max_parallel_workers_per_gather;
drop table tts_foo, tts_bar, tts_hashagg;
drop tablespace mytempsp0;
drop tablespace mytempsp1;
drop tablespace mytempsp2;
drop tablespace mytempsp3;
drop tablespace mytempsp4;