src/test/regress/sql/segspace.sql

--
-- Tests the spill files disk space accounting mechanism
--
-- CBDB_PARALLEL_FIXME: it's hard to make fault_injection work with prallel processes.
set enable_parallel = false;

-- check segspace before test
reset statement_mem;
select max(bytes) as max, min(bytes) as min from gp_toolkit.gp_workfile_mgr_used_diskspace;

--- create and populate the table

DROP TABLE IF EXISTS segspace_test_hj_skew;
CREATE TABLE segspace_test_hj_skew (i1 int, i2 int, i3 int, i4 int, i5 int, i6 int, i7 int, i8 int) DISTRIBUTED BY (i1);

set gp_autostats_mode = none;

-- many values with i1 = 1
INSERT INTO segspace_test_hj_skew SELECT 1,i,i,i,i,i,i,i FROM 
	(select generate_series(1, nsegments * 50000) as i from 
	(select count(*) as nsegments from gp_segment_configuration where role='p' and content >= 0) foo) bar;
-- some nicely distributed values
INSERT INTO segspace_test_hj_skew SELECT i,i,i,i,i,i,i,i FROM 
	(select generate_series(1, nsegments * 100000) as i from 
	(select count(*) as nsegments from gp_segment_configuration where role='p' and content >= 0) foo) bar;

ANALYZE segspace_test_hj_skew;

--
--  Testing that query cancelation during spilling updates the accounting
--

------------ Interrupting SELECT query that spills -------------------

-- enable the fault injector
select gp_inject_fault('exec_hashjoin_new_batch', 'reset', 2);
select gp_inject_fault('exec_hashjoin_new_batch', 'interrupt', 2);

set statement_mem=2048;
set gp_autostats_mode = none;

begin;
SELECT t1.* FROM segspace_test_hj_skew AS t1, segspace_test_hj_skew AS t2 WHERE t1.i1=t2.i2;
rollback;

-- 
-- GPDB parallel once got errors like: 
-- could not read from shared tuplestore temporary file: read only 0 of 8 bytes from file.
-- Enable parallel here to test it.
-- 
begin;
set local enable_parallel = true;
set local optimizer=off;
set local min_parallel_table_scan_size=0;
set local min_parallel_index_scan_size = 0;
set local force_parallel_mode=1;
EXPLAIN(COSTS OFF) SELECT t1.* FROM segspace_test_hj_skew AS t1, segspace_test_hj_skew AS t2 WHERE t1.i1=t2.i2;
SELECT count(t1.*) FROM segspace_test_hj_skew AS t1, segspace_test_hj_skew AS t2 WHERE t1.i1=t2.i2;
rollback;

select gp_inject_fault('exec_hashjoin_new_batch', 'status', 2);

-- check used segspace after test
select max(bytes) as max, min(bytes) as min from gp_toolkit.gp_workfile_mgr_used_diskspace;

-- Run the test without fault injection
begin;
-- Doing select count so output file doesn't have 75000 rows.
select count(*) from 
(SELECT t1.* FROM segspace_test_hj_skew AS t1, segspace_test_hj_skew AS t2 WHERE t1.i1=t2.i2) temp;
rollback;

-- check used segspace after test
reset statement_mem;
select max(bytes) as max, min(bytes) as min from gp_toolkit.gp_workfile_mgr_used_diskspace;


------------ Interrupting INSERT INTO query that spills -------------------

drop table if exists segspace_t1_created;
create table segspace_t1_created (i1 int, i2 int, i3 int, i4 int, i5 int, i6 int, i7 int, i8 int) DISTRIBUTED BY (i1);

set statement_mem=2048;
set gp_autostats_mode = none;

-- enable the fault injector
select gp_inject_fault('exec_hashjoin_new_batch', 'reset', 2);
select gp_inject_fault('exec_hashjoin_new_batch', 'interrupt', 2);

begin;

insert into segspace_t1_created
SELECT t1.* FROM segspace_test_hj_skew AS t1, segspace_test_hj_skew AS t2 WHERE t1.i1=t2.i2;

rollback;

select gp_inject_fault('exec_hashjoin_new_batch', 'status', 2);

-- check used segspace after test
select max(bytes) as max, min(bytes) as min from gp_toolkit.gp_workfile_mgr_used_diskspace;

-- Run the test without fault injection
begin;

insert into segspace_t1_created
SELECT t1.* FROM segspace_test_hj_skew AS t1, segspace_test_hj_skew AS t2 WHERE t1.i1=t2.i2;

rollback;

-- check used segspace after test
reset statement_mem;
select max(bytes) as max, min(bytes) as min from gp_toolkit.gp_workfile_mgr_used_diskspace;

--start_ignore
drop table if exists segspace_t1_created;
--end_ignore

------------ Interrupting CREATE TABLE AS query that spills -------------------

drop table if exists segspace_t1_created;
set statement_mem=2048;
set gp_autostats_mode = none;

-- enable the fault injector
select gp_inject_fault('exec_hashjoin_new_batch', 'reset', 2);
select gp_inject_fault('exec_hashjoin_new_batch', 'interrupt', 2);

begin;

create table segspace_t1_created AS
SELECT t1.* FROM segspace_test_hj_skew AS t1, segspace_test_hj_skew AS t2 WHERE t1.i1=t2.i2;

rollback;

select gp_inject_fault('exec_hashjoin_new_batch', 'status', 2);

-- check used segspace after test
select max(bytes) as max, min(bytes) as min from gp_toolkit.gp_workfile_mgr_used_diskspace;

-- Run the test without fault injection
begin;

create table segspace_t1_created AS
SELECT t1.* FROM segspace_test_hj_skew AS t1, segspace_test_hj_skew AS t2 WHERE t1.i1=t2.i2;

rollback;

-- check used segspace after test
reset statement_mem;
select max(bytes) as max, min(bytes) as min from gp_toolkit.gp_workfile_mgr_used_diskspace;


------------ workfile_limit_per_segment leak check during ERROR on UPDATE with CTE and MK_SORT -------------------

drop table if exists testsisc;
drop table if exists foo;

create table testsisc (i1 int, i2 int, i3 int, i4 int);
insert into testsisc select i, i % 1000, i % 100000, i % 75 from
	(select generate_series(1, nsegments * 100000) as i from
	(select count(*) as nsegments from gp_segment_configuration where role='p' and content >= 0) foo) bar;

create table foo (i int, j int);

set statement_mem=1024; -- 1mb for 3 segment to get leak.
set gp_resqueue_print_operator_memory_limits=on;
set gp_cte_sharing=on;

-- enable the fault injector
select gp_inject_fault('workfile_write_failure', 'reset', 2);
select gp_inject_fault('workfile_write_failure', 'error', 2);

-- LEAK in UPDATE: update with sisc xslice sort
update foo set j=m.cc1 from (
  with ctesisc as
    (select * from testsisc order by i2)
  select t1.i1 as cc1, t1.i2 as cc2
  from ctesisc as t1, ctesisc as t2
  where t1.i1 = t2.i2 ) as m;

select gp_inject_fault('exec_hashjoin_new_batch', 'status', 2);

select max(bytes) as max, min(bytes) as min from gp_toolkit.gp_workfile_mgr_used_diskspace;

-- Run the test without fault injection
-- LEAK in UPDATE: update with sisc xslice sort
update foo set j=m.cc1 from (
  with ctesisc as
    (select * from testsisc order by i2)
  select t1.i1 as cc1, t1.i2 as cc2
  from ctesisc as t1, ctesisc as t2
  where t1.i1 = t2.i2 ) as m;
  
select max(bytes) as max, min(bytes) as min from gp_toolkit.gp_workfile_mgr_used_diskspace;

reset statement_mem;
reset gp_resqueue_print_operator_memory_limits;
reset gp_cte_sharing;

------------ workfile_limit_per_segment leak check during ERROR on DELETE with APPEND-ONLY table -------------------

drop table if exists testsisc;
drop table if exists foo;

create table testsisc (i1 int, i2 int, i3 int, i4 int) WITH (appendonly=true, compresstype=zlib) ;
insert into testsisc select i, i % 1000, i % 100000, i % 75 from
    (select generate_series(1, nsegments * 100000) as i from
	(select count(*) as nsegments from gp_segment_configuration where role='p' and content >= 0) foo) bar;

create table foo (i int, j int) WITH (appendonly=true, compresstype=zlib);
insert into foo select i, i % 1000 from
    (select generate_series(1, nsegments * 100000) as i from
	(select count(*) as nsegments from gp_segment_configuration where role='p' and content >= 0) foo) bar;


set statement_mem=1024; -- 1mb for 3 segment to get leak.

-- enable the fault injector
select gp_inject_fault('workfile_write_failure', 'reset', 2);
select gp_inject_fault('workfile_write_failure', 'error', 2);

-- LEAK in DELETE with APPEND ONLY tables
delete from testsisc using (
  select *
  from foo
) src  where testsisc.i1 = src.i;

select gp_inject_fault('exec_hashjoin_new_batch', 'status', 2);

select max(bytes) as max, min(bytes) as min from gp_toolkit.gp_workfile_mgr_used_diskspace;

-- Run the test without fault injection
-- LEAK in DELETE with APPEND ONLY tables
delete from testsisc using (
  select *
  from foo
) src  where testsisc.i1 = src.i;

reset statement_mem;
select max(bytes) as max, min(bytes) as min from gp_toolkit.gp_workfile_mgr_used_diskspace;

------------ workfile_limit_per_segment leak check during UPDATE of SORT -------------------

drop table if exists testsort;
create table testsort (i1 int, i2 int, i3 int, i4 int);
insert into testsort select i, i % 1000, i % 100000, i % 75 from generate_series(0,1000000) i;
analyze testsort;

set statement_mem="1MB";

drop table if exists foo;
create table foo (c int, d int);
insert into foo values (1, 1);

-- enable the fault injector
select gp_inject_fault('workfile_write_failure', 'reset', 2);
select gp_inject_fault('workfile_write_failure', 'error', 2);

-- expect to see leak if we hit error
update foo set d = i1 from (select i1,i2 from testsort order by i2) x;

-- check counter leak
select max(bytes) as max, min(bytes) as min from gp_toolkit.gp_workfile_mgr_used_diskspace;

select gp_inject_fault('exec_hashjoin_new_batch', 'status', 2);

-- Run the test without fault injection
-- expect to see leak if we hit error
-- start_ignore
update foo set d = i1 from (select i1,i2 from testsort order by i2) x;
-- end_ignore

-- check counter leak
select max(bytes) as max, min(bytes) as min from gp_toolkit.gp_workfile_mgr_used_diskspace;

drop table testsort;

------------ workfile_limit_per_segment leak check during UPDATE of SHARE_SORT_XSLICE -------------------

drop table if exists testsisc;
create table testsisc (i1 int, i2 int, i3 int, i4 int);
insert into testsisc select i, i % 1000, i % 100000, i % 75 from generate_series(0,1000000) i;

set statement_mem="2MB";
set gp_cte_sharing=on;

drop table if exists foo;
create table foo (c int, d int);
-- expect to see leak if we hit error

-- enable the fault injector
select gp_inject_fault('workfile_write_failure', 'reset', 2);
select gp_inject_fault('workfile_write_failure', 'error', 2);

update foo set d = i1 from (with ctesisc as (select * from testsisc order by i2)
select * from
(select count(*) from ctesisc) x(a), ctesisc
where x.a = ctesisc.i1) y;

select gp_inject_fault('exec_hashjoin_new_batch', 'status', 2);

-- check counter leak
select max(bytes) as max, min(bytes) as min from gp_toolkit.gp_workfile_mgr_used_diskspace;

-- Run the test without fault injection
update foo set d = i1 from (with ctesisc as (select * from testsisc order by i2)
select * from
(select count(*) from ctesisc) x(a), ctesisc
where x.a = ctesisc.i1) y;

-- check counter leak
select max(bytes) as max, min(bytes) as min from gp_toolkit.gp_workfile_mgr_used_diskspace;

-- Check if we delete workfile_set at each subtransaction.
-- This test doesn't depend on the guc `gp_workfile_limit_per_segment` like rest
-- start_ignore
drop external table if exists exttest;
create external web table exttest (x int)
execute E'echo 1; echo 2; echo 3; echo bogus; echo 5'
on master
format 'text';
-- end_ignore

create or replace function workset_cleanup_test()
returns boolean as
$func$
declare                                          
  sub_work_set_cnt  integer;
  top_work_set_cnt 	integer;
begin
  select count(*) into top_work_set_cnt from gp_toolkit.gp_workfile_entries;
   for i in 1..2 loop
     begin
       select * from exttest a, exttest b;
     exception when others then
       raise notice 'caught exception: %', sqlerrm;
     end;
     select count(*) into sub_work_set_cnt from gp_toolkit.gp_workfile_entries;
   end loop;
  return top_work_set_cnt = sub_work_set_cnt;
end;
$func$ language plpgsql;

select workset_cleanup_test();

drop table segspace_test_hj_skew;

reset enable_parallel;

-- don't disturb other processes.
select cleanupAllGangs();