--big source data size(10G)
create table x(
unique1  int, 
billion  int, 
tenthous int, 
thousand int, 
stringu1 name);

insert into x select generate_series(1,100000000),generate_series(1,1000000),floor(random()*(10001-1)+1),floor(random()*(1001-1)+1),upper(substring(md5(random()::varchar),2,6));

create index x_unique1 on x USING btree(unique1 int4_ops);

create index x_unique2_tenthous ON x (billion, tenthous);

--target table
create table para_insert_p1 (
        unique1         int4    PRIMARY KEY,
        stringu1        name
);

create table para_insert_f1 (
        unique1         int4    REFERENCES para_insert_p1(unique1),
        stringu1        name
);

--column defaults
create table test_data(a int);
insert into test_data select * from generate_series(1,5000000);

--append plan
CREATE TABLE a_star(class char,a int4);
CREATE TABLE b_star (
	b 			text
) INHERITS (a_star);

CREATE TABLE c_star (
	c 			name
) INHERITS (a_star);

CREATE TABLE d_star (
	d 			float8
) INHERITS (b_star, c_star);

CREATE TABLE e_star (
	e 			int2
) INHERITS (c_star);

CREATE TABLE f_star (
	f 			polygon
) INHERITS (e_star);

INSERT INTO a_star (class, a) VALUES ('a', generate_series(28,1000000));
INSERT INTO a_star (class) VALUES ('a');
INSERT INTO b_star (class, a, b) VALUES ('b', 3, 'mumble'::text);
INSERT INTO b_star (class, a) VALUES ('b', 4);
INSERT INTO b_star (class, b) VALUES ('b', 'bumble'::text);
INSERT INTO b_star (class) VALUES ('b');
INSERT INTO c_star (class, a, c) VALUES ('c', 5, 'hi mom'::name);
INSERT INTO c_star (class, a) VALUES ('c', 6);
INSERT INTO c_star (class, c) VALUES ('c', 'hi paul'::name);
INSERT INTO c_star (class) VALUES ('c');
INSERT INTO d_star (class, a, b, c, d)
   VALUES ('d', 7, 'grumble'::text, 'hi sunita'::name, '0.0'::float8);
INSERT INTO d_star (class, a, b, c)
   VALUES ('d', 8, 'stumble'::text, 'hi koko'::name);
INSERT INTO d_star (class, a, b, d)
   VALUES ('d', 9, 'rumble'::text, '1.1'::float8);
INSERT INTO d_star (class, a, c, d)
   VALUES ('d', 10, 'hi kristin'::name, '10.01'::float8);
INSERT INTO d_star (class, b, c, d)
   VALUES ('d', 'crumble'::text, 'hi boris'::name, '100.001'::float8);
INSERT INTO d_star (class, a, b)
   VALUES ('d', 11, 'fumble'::text);
INSERT INTO d_star (class, a, c)
   VALUES ('d', 12, 'hi avi'::name);
INSERT INTO d_star (class, a, d)
   VALUES ('d', 13, '1000.0001'::float8);
INSERT INTO d_star (class, b, c)
   VALUES ('d', 'tumble'::text, 'hi andrew'::name);
INSERT INTO d_star (class, b, d)
   VALUES ('d', 'humble'::text, '10000.00001'::float8);
INSERT INTO d_star (class, c, d)
   VALUES ('d', 'hi ginger'::name, '100000.000001'::float8);
INSERT INTO d_star (class, a) VALUES ('d', 14);
INSERT INTO d_star (class, b) VALUES ('d', 'jumble'::text);
INSERT INTO d_star (class, c) VALUES ('d', 'hi jolly'::name);
INSERT INTO d_star (class, d) VALUES ('d', '1000000.0000001'::float8);
INSERT INTO d_star (class) VALUES ('d');
INSERT INTO e_star (class, a, c, e)
   VALUES ('e', 15, 'hi carol'::name, '-1'::int2);
INSERT INTO e_star (class, a, c)
   VALUES ('e', 16, 'hi bob'::name);
INSERT INTO e_star (class, a, e)
   VALUES ('e', 17, '-2'::int2);
INSERT INTO e_star (class, c, e)
   VALUES ('e', 'hi michelle'::name, '-3'::int2);
INSERT INTO e_star (class, a)
   VALUES ('e', 18);
INSERT INTO e_star (class, c)
   VALUES ('e', 'hi elisa'::name);
INSERT INTO e_star (class, e)
   VALUES ('e', '-4'::int2);
INSERT INTO f_star (class, a, c, e, f)
   VALUES ('f', 19, 'hi claire'::name, '-5'::int2, '(1,3),(2,4)'::polygon);
INSERT INTO f_star (class, a, c, e)
   VALUES ('f', 20, 'hi mike'::name, '-6'::int2);
INSERT INTO f_star (class, a, c, f)
   VALUES ('f', 21, 'hi marcel'::name, '(11,44),(22,55),(33,66)'::polygon);
INSERT INTO f_star (class, a, e, f)
   VALUES ('f', 22, '-7'::int2, '(111,555),(222,666),(333,777),(444,888)'::polygon);
INSERT INTO f_star (class, c, e, f)
   VALUES ('f', 'hi keith'::name, '-8'::int2,
	   '(1111,3333),(2222,4444)'::polygon);
INSERT INTO f_star (class, a, c)
   VALUES ('f', 24, 'hi marc'::name);
INSERT INTO f_star (class, a, e)
   VALUES ('f', 25, '-9'::int2);
INSERT INTO f_star (class, a, f)
   VALUES ('f', 26, '(11111,33333),(22222,44444)'::polygon);
INSERT INTO f_star (class, c, e)
   VALUES ('f', 'hi allison'::name, '-10'::int2);
INSERT INTO f_star (class, c, f)
   VALUES ('f', 'hi jeff'::name,
           '(111111,333333),(222222,444444)'::polygon);
INSERT INTO f_star (class, e, f)
   VALUES ('f', '-11'::int2, '(1111111,3333333),(2222222,4444444)'::polygon);
INSERT INTO f_star (class, a) VALUES ('f', 27);
INSERT INTO f_star (class, c) VALUES ('f', 'hi carl'::name);
INSERT INTO f_star (class, e) VALUES ('f', '-12'::int2);
INSERT INTO f_star (class, f)
   VALUES ('f', '(11111111,33333333),(22222222,44444444)'::polygon);
INSERT INTO f_star (class) VALUES ('f');

ALTER TABLE a_star* RENAME COLUMN a TO aa;

-- index expression, parallel safety check on index
create or replace function fullname_parallel_unsafe(f text, l text) returns text as $$
    begin
        return f || l;
    end;
$$ language plpgsql immutable parallel unsafe;

create or replace function fullname_parallel_safe(f text, l text) returns text as $$
    begin
        return f || l;
    end;
$$ language plpgsql immutable parallel safe;

create table names(index int, first_name text, last_name text);
insert into names values(generate_series(1,5000000),upper(substring(md5(random()::varchar),2,5)),upper(substring(md5(random()::varchar),2,5)));

create table names2(index int, first_name text, last_name text);
create index names2_fullname_idx on names2 (fullname_parallel_unsafe(first_name, last_name));
create table names3(index int, first_name text, last_name text);
create index names3_fullname_idx on names3 (fullname_parallel_safe(first_name, last_name));

-- column default tests

create or replace function bdefault_unsafe ()
returns int language plpgsql parallel unsafe as $$
begin
	RETURN 5;
end $$;

create or replace function cdefault_restricted ()
returns int language plpgsql parallel restricted as $$
begin
	RETURN 10;
end $$;

create or replace function ddefault_safe ()
returns int language plpgsql parallel safe as $$
begin
	RETURN 20;
end $$;

create table testdef(a int, b int default bdefault_unsafe(), c int default cdefault_restricted(), d int default ddefault_safe());
create index testdef_a_index on testdef(a);

-- toast data prepare
create table insert_toast_table_data (index int4, data text);
insert into insert_toast_table_data select i, rpad('T', 16384, 'ABCDEFGH') from generate_series(1,500000) as i;

-- foreign table Note:first, if postgres_fdw is not installed, should make,make install postgres_fdw under path ./contrib/postgres_fdw/
CREATE EXTENSION postgres_fdw;
create database foreign_db;
\c foreign_db
create table foreign_names(index int, first_name text, last_name text);
\c postgres
CREATE SERVER foreign_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (dbname 'foreign_db');
CREATE USER MAPPING FOR postgres SERVER foreign_server OPTIONS (user 'postgres');


------------------begin test-------------------
analyze;

-- 1. Test INSERT with underlying query. parallel INSERT+SELECT, parallel seq scan
truncate para_insert_p1 cascade;
explain analyze verbose insert into para_insert_p1 select unique1, stringu1 from x where unique1%10=0;

-- 2. Test INSERT into a table with a foreign key. Insert into a table with a foreign key is parallel-restricted. only parallel SELECT
explain analyze verbose insert into para_insert_f1 select unique1, stringu1 from x where unique1%10=0;

-- 3. Test INSERT with ON CONFLICT ... DO UPDATE ... , non parallel.
create table test_conflict_table(id serial primary key, somedata int);
insert into test_conflict_table(id, somedata) select a, a from test_data;
explain analyze verbose insert into test_conflict_table(id, somedata) select a, a from test_data ON CONFLICT(id) DO UPDATE SET somedata = EXCLUDED.somedata + 1;

-- 4. Test INSERT with underlying query, leader participation disabled, parallel NSERT+SELECT. index only scan
truncate para_insert_p1 cascade;
set parallel_leader_participation = off;
explain analyze verbose insert into para_insert_p1 select billion,tenthous from x where billion%10=0;

-- 5. Test INSERT with underlying query, leader participation disabled and no workers available. parallel NSERT+SELECT.
set max_parallel_workers=0;
truncate para_insert_p1 cascade;
explain analyze verbose insert into para_insert_p1 select unique1, stringu1 from x where unique1%10=0;

-- 6. Test INSERT with parallelized aggregate, only parallel select
truncate para_insert_p1 cascade;
reset parallel_leader_participation;
reset max_parallel_workers;
create table x_avg_data(count int, avg_unique1 int, avg_stringu1_len int);
explain analyze  verbose insert into x_avg_data select count(*), avg(unique1), generate_series(1, count(*))  from x;

-- 7.Test INSERT with parallel bitmap heap scan, target table with constraint.
create table para_insert_p2 (
        unique1         int4,
        stringu1        name
);
explain analyze verbose insert into para_insert_p2 select unique1,billion from x where billion<800000 or (tenthous%2=0 and unique1>99990000);

-- 8. Test INSERT with parallel append
create table a_star_data(aa int , b int );
explain analyze verbose insert into a_star_data select aa from a_star where aa is not null and aa%5=0;

-- 9. Test INSERT with parallel index scan
truncate para_insert_p1 cascade;
explain analyze verbose insert into para_insert_p1 select unique1, stringu1 from x where unique1 < 7500000 and tenthous=9000;

-- 10. Test INSERT with parallel-safe index expression
truncate names3;
explain analyze verbose insert into names3 select * from names where index%10=0;

-- 11. Test INSERT with parallel-unsafe index expressions, non parallel
truncate names2;
explain analyze verbose insert into names2 select * from names where index%10=0;

-- 12. Test INSERT with underlying query - and RETURNING (no projection)
create table names4 (like names);
explain analyze verbose insert into names4 select * from names where index%10=0 returning *;

-- 13. Test INSERT with underlying ordered query - and RETURNING (no projection)
create table names5 (like names);
explain analyze verbose insert into names5 select * from names where index%10=0 order by last_name returning *;

-- 14. Test INSERT with underlying ordered query - and RETURNING (with projection)
create table names6 (like names);
explain analyze verbose insert into names6 select * from names where index%10=0 order by last_name returning last_name || ', ' || first_name as last_name_then_first_name;

-- 15. Test INSERT into temporary table with underlying query.
create temporary table temp_names (like names);
explain analyze verbose insert into temp_names select * from names where index%10=0;

---------------- Begin Test INSERT with column defaults-------------------------------------
-- a: no default
-- b: unsafe default
-- c: restricted default
-- d: safe default

-- 16. No column defaults, should use parallel INSERT+SELECT
truncate testdef;
explain analyze verbose insert into testdef(a,b,c,d) select a,a*2,a*4,a*8 from test_data where a%5=0;

-- 17. Parallel unsafe column default, should not use a parallel plan
truncate testdef;
explain analyze verbose insert into testdef(a,c,d) select a,a*4,a*8 from test_data where a%5=0;

-- 18. Parallel restricted column default, should use INSERT + parallel SELECT
truncate testdef;
explain analyze verbose insert into testdef(a,b,d) select a,a*2,a*8 from test_data where a%5=0;

-- 19. Parallel safe column default, should use parallel INSERT+SELECT
truncate testdef;
explain analyze verbose insert into testdef(a,b,c) select a,a*2,a*4 from test_data where a%5=0;

-- 20. Parallel restricted and unsafe column defaults, should not use a parallel plan
truncate testdef;
explain analyze verbose insert into testdef(a,d) select a,a*8 from test_data where a%5=0;

-------------------------- Begin Test INSERT into partition-------------------------------------
-- 21. Test INSERT into partition with underlying query. should use parallel INSERT+SELECT
create table parttable1 (a int, b name) partition by range (a);
create index parttable1_index_a on parttable1(a);
create table parttable1_1 partition of parttable1 for values from (1) to (50000001);
create table parttable1_2 partition of parttable1 for values from (50000001) to (100000001);

explain analyze verbose insert into parttable1 select unique1,stringu1 from x where unique1%100=0;

-- 22. Test INSERT into partition with parallel-unsafe partition key expression, non parallel
drop function if exists my_int4_sort cascade;
create function my_int4_sort(int4,int4) returns int language sql
  as $$ select case when $1 = $2 then 0 when $1 > $2 then 1 else -1 end; $$;

create operator class test_int4_ops for type int4 using btree as
  operator 1 < (int4,int4), operator 2 <= (int4,int4),
  operator 3 = (int4,int4), operator 4 >= (int4,int4),
  operator 5 > (int4,int4), function 1 my_int4_sort(int4,int4);
  
create table partkey_unsafe_key_expr_t (a int4, b name) partition by range (a test_int4_ops);
create table partkey_unsafe_key_expr_t_1 partition of partkey_unsafe_key_expr_t for values from (1) to (50000001);
create table partkey_unsafe_key_expr_t_2 partition of partkey_unsafe_key_expr_t for values from (50000001) to (100000001);

explain analyze verbose insert into partkey_unsafe_key_expr_t select unique1,stringu1 from x where unique1%100=0;

-- 23. Test INSERT into table with parallel-safe check constraint, should create a parallel plan
drop function if exists check_a cascade;
create or replace function check_a(a int4) returns boolean as $$
    begin
        return (a >= 1 and a <= 100000000);
    end;
$$ language plpgsql parallel safe;

create table table_check_a(a int4 check (check_a(a)), b name);
explain analyze verbose insert into table_check_a select unique1,stringu1 from x where unique1%100=0;

-- 24. Test INSERT into table with parallel-unsafe check constraint, non parallel
drop function if exists check_b_unsafe cascade;
create or replace function check_b_unsafe(b name) returns boolean as $$
    begin
        return (b <> 'XXXXXX');
    end;
$$ language plpgsql parallel unsafe;

create table table_check_b(a int4, b name check (check_b_unsafe(b)), c name);
explain analyze verbose insert into table_check_b(a,b,c) select unique1,stringu1,stringu1 from x where unique1%100=0;

-- 25. Test INSERT into table with before+after parallel-safe stmt-level triggers,  should create a parallel plan
create table names_with_safe_trigger (like names);
drop function if exists insert_before_trigger_safe,insert_after_trigger_safe cascade;
create or replace function insert_before_trigger_safe() returns trigger as $$
    begin
        raise notice 'hello from insert_before_trigger_safe';
		return new;
    end;
$$ language plpgsql parallel safe;
create or replace function insert_after_trigger_safe() returns trigger as $$
    begin
        raise notice 'hello from insert_after_trigger_safe';
		return new;
    end;
$$ language plpgsql parallel safe;
create trigger insert_before_trigger_safe before insert on names_with_safe_trigger
    for each statement execute procedure insert_before_trigger_safe();
create trigger insert_after_trigger_safe after insert on names_with_safe_trigger
    for each statement execute procedure insert_after_trigger_safe();

explain analyze verbose insert into names_with_safe_trigger select * from names where index%5=0;

-- 26. Test INSERT into table with before+after parallel-unsafe stmt-level triggers,non parallel
create table names_with_unsafe_trigger (like names);
drop function if exists insert_before_trigger_unsafe,insert_after_trigger_unsafe cascade;
create or replace function insert_before_trigger_unsafe() returns trigger as $$
    begin
        raise notice 'hello from insert_before_trigger_unsafe';
		return new;
    end;
$$ language plpgsql parallel unsafe;
create or replace function insert_after_trigger_unsafe() returns trigger as $$
    begin
        raise notice 'hello from insert_after_trigger_unsafe';
		return new;
    end;
$$ language plpgsql parallel unsafe;
create trigger insert_before_trigger_unsafe before insert on names_with_unsafe_trigger
    for each statement execute procedure insert_before_trigger_unsafe();
create trigger insert_after_trigger_unsafe after insert on names_with_unsafe_trigger
    for each statement execute procedure insert_after_trigger_unsafe();

explain analyze verbose insert into names_with_unsafe_trigger select * from names where index%5=0;

-- 27. Test INSERT into table with before+after parallel-restricted stmt-level trigger
-- (should create a parallel plan with INSERT + parallel SELECT;
--  stmt-level before+after triggers should fire)
create table names_with_restricted_trigger (like names);
drop function if exists insert_before_trigger_restricted,insert_after_trigger_restricted cascade;
create or replace function insert_before_trigger_restricted() returns trigger as $$
    begin
        raise notice 'hello from insert_before_trigger_restricted';
		return new;
    end;
$$ language plpgsql parallel restricted;
create or replace function insert_after_trigger_restricted() returns trigger as $$
    begin
        raise notice 'hello from insert_after_trigger_restricted';
		return new;
    end;
$$ language plpgsql parallel restricted;
create trigger insert_before_trigger_restricted before insert on names_with_restricted_trigger
    for each statement execute procedure insert_before_trigger_restricted();
create trigger insert_after_trigger_restricted after insert on names_with_restricted_trigger
    for each statement execute procedure insert_after_trigger_restricted();

explain analyze verbose insert into names_with_restricted_trigger select * from names where index%5=0;

-- 28. Test INSERT into table with TOAST column
create table insert_toast_table(like insert_toast_table_data);
explain analyze verbose insert into insert_toast_table select index, data from insert_toast_table_data where index%2=0;

-- 29. Test INSERT into table having a DOMAIN column with parallel-unsafe CHECK constraint, non parallel
drop function if exists sql_is_distinct_from_u,sql_is_distinct_from_r,sql_is_distinct_from_s cascade;
create function sql_is_distinct_from_u(anyelement, anyelement)
returns boolean language sql parallel unsafe
as 'select $1 is distinct from $2 limit 1';

create or replace function sql_is_distinct_from_r(a anyelement, b anyelement) returns boolean as $$
    begin
        return (a <> b);
    end;
$$ language plpgsql parallel restricted;

create or replace function sql_is_distinct_from_s(a anyelement, b anyelement) returns boolean as $$
    begin
        return (a <> b);
    end;
$$ language plpgsql parallel safe;

drop domain if exists inotnull_u,inotnull_r,inotnull_s cascade;
create domain inotnull_u int
  check (sql_is_distinct_from_u(value, null));

create domain inotnull_r int
  check (sql_is_distinct_from_r(value, null));

create domain inotnull_s int
  check (sql_is_distinct_from_s(value, null));

create table dom_table_u (x inotnull_u, y int);
create table dom_table_r (x inotnull_r, y int);
create table dom_table_s (x inotnull_s, y int);

explain analyze verbose insert into dom_table_u select unique1, billion from x where unique1%100=0;

-- 30. Test INSERT into table having a DOMAIN column with parallel-restricted CHECK constraint, parallel select

explain analyze verbose insert into dom_table_r select unique1, billion from x where unique1%100=0;

-- 31. Test INSERT into table having a DOMAIN column with parallel-safe CHECK constraint NOTE: Currently max_parallel_hazard() regards CoerceToDomain as parallel-restricted
explain analyze verbose insert into dom_table_s select unique1, billion from x where unique1%100=0;

-- 32. Test INSERT with ordered underlying query.(should create plan with INSERT + parallel SELECT, GatherMerge parent node)
truncate para_insert_p1 cascade;
explain analyze verbose insert into para_insert_p1 select unique1, stringu1 from x where unique1%100=0 order by unique1;

-- 33. Test INSERT into foreign table with underlying query.
\c foreign_db
truncate foreign_names;
\c postgres
drop foreign table if exists foreign_names;
create foreign table foreign_names(index int, first_name text, last_name text) SERVER foreign_server OPTIONS (table_name 'foreign_names');
explain analyze verbose insert into foreign_names select * from names where index%10=0;