Revert "Enable parallel SELECT for "INSERT INTO ... SELECT ..."."

To allow inserts in parallel-mode this feature has to ensure that all the
constraints, triggers, etc. are parallel-safe for the partition hierarchy
which is costly and we need to find a better way to do that. Additionally,
we could have used existing cached information in some cases like indexes,
domains, etc. to determine the parallel-safety.

List of commits reverted, in reverse chronological order:

ed62d3737c Doc: Update description for parallel insert reloption.
c8f78b6161 Add a new GUC and a reloption to enable inserts in parallel-mode.
c5be48f092 Improve FK trigger parallel-safety check added by 05c8482f7f.
e2cda3c20a Fix use of relcache TriggerDesc field introduced by commit 05c8482f7f.
e4e87a32cc Fix valgrind issue in commit 05c8482f7f.
05c8482f7f Enable parallel SELECT for "INSERT INTO ... SELECT ...".

Discussion: https://postgr.es/m/E1lMiB9-0001c3-SY@gemulon.postgresql.org

5 files changed, 1 insertions, 963 deletions

diff --git a/src/test/regress/expected/insert_parallel.out b/src/test/regress/expected/insert_parallel.out
deleted file mode 100644
index 164668e3191..00000000000
--- a/src/test/regress/expected/insert_parallel.out
+++ /dev/null
@@ -1,586 +0,0 @@
---
--- PARALLEL
---
---
--- START: setup some tables and data needed by the tests.
---
--- Setup - index expressions test
--- For testing purposes, we'll mark this function as parallel-unsafe
-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_restricted(f text, l text) returns text as $$
-    begin
-        return f || l;
-    end;
-$$ language plpgsql immutable parallel restricted;
-create table names(index int, first_name text, last_name text);
-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 names4(index int, first_name text, last_name text);
-create index names4_fullname_idx on names4 (fullname_parallel_restricted(first_name, last_name));
-insert into names values
-	(1, 'albert', 'einstein'),
-	(2, 'niels', 'bohr'),
-	(3, 'erwin', 'schrodinger'),
-	(4, 'leonhard', 'euler'),
-	(5, 'stephen', 'hawking'),
-	(6, 'isaac', 'newton'),
-	(7, 'alan', 'turing'),
-	(8, 'richard', 'feynman');
--- Setup - 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 table test_data(a int);
-insert into test_data select * from generate_series(1,10);
---
--- END: setup some tables and data needed by the tests.
---
-begin;
--- encourage use of parallel plans
-set parallel_setup_cost=0;
-set parallel_tuple_cost=0;
-set min_parallel_table_scan_size=0;
-set max_parallel_workers_per_gather=4;
-create table para_insert_p1 (
-	unique1		int4	PRIMARY KEY,
-	stringu1	name
-) with (parallel_insert_enabled = off);
-create table para_insert_f1 (
-	unique1		int4	REFERENCES para_insert_p1(unique1),
-	stringu1	name
-);
---
--- Disable guc option enable_parallel_insert
---
-set enable_parallel_insert = off;
--- Test INSERT with underlying query when enable_parallel_insert=off and reloption.parallel_insert_enabled=off.
--- (should create plan with serial INSERT + SELECT)
---
-explain(costs off) insert into para_insert_p1 select unique1, stringu1 from tenk1;
-        QUERY PLAN        
---------------------------
- Insert on para_insert_p1
-   ->  Seq Scan on tenk1
-(2 rows)
-
---
--- Reset guc option enable_parallel_insert
---
-reset enable_parallel_insert;
---
--- Test INSERT with underlying query when enable_parallel_insert=on and reloption.parallel_insert_enabled=off.
--- (should create plan with serial INSERT + SELECT)
---
-explain(costs off) insert into para_insert_p1 select unique1, stringu1 from tenk1;
-        QUERY PLAN        
---------------------------
- Insert on para_insert_p1
-   ->  Seq Scan on tenk1
-(2 rows)
-
---
--- Enable reloption parallel_insert_enabled
---
-alter table para_insert_p1 set (parallel_insert_enabled = on);
---
--- Test INSERT with underlying query.
--- (should create plan with parallel SELECT, Gather parent node)
---
-explain (costs off) insert into para_insert_p1 select unique1, stringu1 from tenk1;
-               QUERY PLAN               
-----------------------------------------
- Insert on para_insert_p1
-   ->  Gather
-         Workers Planned: 4
-         ->  Parallel Seq Scan on tenk1
-(4 rows)
-
-insert into para_insert_p1 select unique1, stringu1 from tenk1;
--- select some values to verify that the parallel insert worked
-select count(*), sum(unique1) from para_insert_p1;
- count |   sum    
--------+----------
- 10000 | 49995000
-(1 row)
-
--- verify that the same transaction has been used by all parallel workers
-select count(*) from (select distinct cmin,xmin from para_insert_p1) as dt;
- count 
--------
-     1
-(1 row)
-
---
--- Test INSERT with ordered underlying query.
--- (should create plan with parallel SELECT, GatherMerge parent node)
---
-truncate para_insert_p1 cascade;
-NOTICE:  truncate cascades to table "para_insert_f1"
-explain (costs off) insert into para_insert_p1 select unique1, stringu1 from tenk1 order by unique1;
-                  QUERY PLAN                  
-----------------------------------------------
- Insert on para_insert_p1
-   ->  Gather Merge
-         Workers Planned: 4
-         ->  Sort
-               Sort Key: tenk1.unique1
-               ->  Parallel Seq Scan on tenk1
-(6 rows)
-
-insert into para_insert_p1 select unique1, stringu1 from tenk1 order by unique1;
--- select some values to verify that the parallel insert worked
-select count(*), sum(unique1) from para_insert_p1;
- count |   sum    
--------+----------
- 10000 | 49995000
-(1 row)
-
--- verify that the same transaction has been used by all parallel workers
-select count(*) from (select distinct cmin,xmin from para_insert_p1) as dt;
- count 
--------
-     1
-(1 row)
-
---
--- Test INSERT with RETURNING clause.
--- (should create plan with parallel SELECT, Gather parent node)
---
-create table test_data1(like test_data);
-explain (costs off) insert into test_data1 select * from test_data where a = 10 returning a as data;
-                 QUERY PLAN                 
---------------------------------------------
- Insert on test_data1
-   ->  Gather
-         Workers Planned: 3
-         ->  Parallel Seq Scan on test_data
-               Filter: (a = 10)
-(5 rows)
-
-insert into test_data1 select * from test_data where a = 10 returning a as data;
- data 
-------
-   10
-(1 row)
-
---
--- Test INSERT into a table with a foreign key.
--- (Insert into a table with a foreign key is parallel-restricted,
---  as doing this in a parallel worker would create a new commandId
---  and within a worker this is not currently supported)
---
-explain (costs off) insert into para_insert_f1 select unique1, stringu1 from tenk1;
-               QUERY PLAN               
-----------------------------------------
- Insert on para_insert_f1
-   ->  Gather
-         Workers Planned: 4
-         ->  Parallel Seq Scan on tenk1
-(4 rows)
-
-insert into para_insert_f1 select unique1, stringu1 from tenk1;
--- select some values to verify that the insert worked
-select count(*), sum(unique1) from para_insert_f1;
- count |   sum    
--------+----------
- 10000 | 49995000
-(1 row)
-
---
--- Test INSERT with ON CONFLICT ... DO UPDATE ...
--- (should not create a parallel plan)
---
-create table test_conflict_table(id serial primary key, somedata int);
-explain (costs off) insert into test_conflict_table(id, somedata) select a, a from test_data;
-                 QUERY PLAN                 
---------------------------------------------
- Insert on test_conflict_table
-   ->  Gather
-         Workers Planned: 3
-         ->  Parallel Seq Scan on test_data
-(4 rows)
-
-insert into test_conflict_table(id, somedata) select a, a from test_data;
-explain (costs off) insert into test_conflict_table(id, somedata) select a, a from test_data ON CONFLICT(id) DO UPDATE SET somedata = EXCLUDED.somedata + 1;
-                      QUERY PLAN                      
-------------------------------------------------------
- Insert on test_conflict_table
-   Conflict Resolution: UPDATE
-   Conflict Arbiter Indexes: test_conflict_table_pkey
-   ->  Seq Scan on test_data
-(4 rows)
-
---
--- Test INSERT with parallel-unsafe index expression
--- (should not create a parallel plan)
---
-explain (costs off) insert into names2 select * from names;
-       QUERY PLAN        
--------------------------
- Insert on names2
-   ->  Seq Scan on names
-(2 rows)
-
---
--- Test INSERT with parallel-restricted index expression
--- (should create a parallel plan)
---
-explain (costs off) insert into names4 select * from names;
-               QUERY PLAN               
-----------------------------------------
- Insert on names4
-   ->  Gather
-         Workers Planned: 3
-         ->  Parallel Seq Scan on names
-(4 rows)
-
---
--- Test INSERT with underlying query - and RETURNING (no projection)
--- (should create a parallel plan; parallel SELECT)
---
-create table names5 (like names);
-explain (costs off) insert into names5 select * from names returning *;
-               QUERY PLAN               
-----------------------------------------
- Insert on names5
-   ->  Gather
-         Workers Planned: 3
-         ->  Parallel Seq Scan on names
-(4 rows)
-
---
--- Test INSERT with underlying ordered query - and RETURNING (no projection)
--- (should create a parallel plan; parallel SELECT)
---
-create table names6 (like names);
-explain (costs off) insert into names6 select * from names order by last_name returning *;
-                  QUERY PLAN                  
-----------------------------------------------
- Insert on names6
-   ->  Gather Merge
-         Workers Planned: 3
-         ->  Sort
-               Sort Key: names.last_name
-               ->  Parallel Seq Scan on names
-(6 rows)
-
-insert into names6 select * from names order by last_name returning *;
- index | first_name |  last_name  
--------+------------+-------------
-     2 | niels      | bohr
-     1 | albert     | einstein
-     4 | leonhard   | euler
-     8 | richard    | feynman
-     5 | stephen    | hawking
-     6 | isaac      | newton
-     3 | erwin      | schrodinger
-     7 | alan       | turing
-(8 rows)
-
---
--- Test INSERT with underlying ordered query - and RETURNING (with projection)
--- (should create a parallel plan; parallel SELECT)
---
-create table names7 (like names);
-explain (costs off) insert into names7 select * from names order by last_name returning last_name || ', ' || first_name as last_name_then_first_name;
-                  QUERY PLAN                  
-----------------------------------------------
- Insert on names7
-   ->  Gather Merge
-         Workers Planned: 3
-         ->  Sort
-               Sort Key: names.last_name
-               ->  Parallel Seq Scan on names
-(6 rows)
-
-insert into names7 select * from names order by last_name returning last_name || ', ' || first_name as last_name_then_first_name;
- last_name_then_first_name 
----------------------------
- bohr, niels
- einstein, albert
- euler, leonhard
- feynman, richard
- hawking, stephen
- newton, isaac
- schrodinger, erwin
- turing, alan
-(8 rows)
-
---
--- Test INSERT into temporary table with underlying query.
--- (Insert into a temp table is parallel-restricted;
--- should create a parallel plan; parallel SELECT)
---
-create temporary table temp_names (like names);
-explain (costs off) insert into temp_names select * from names;
-               QUERY PLAN               
-----------------------------------------
- Insert on temp_names
-   ->  Gather
-         Workers Planned: 3
-         ->  Parallel Seq Scan on names
-(4 rows)
-
-insert into temp_names select * from names;
---
--- Test INSERT with column defaults
---
---
---
--- Parallel unsafe column default, should not use a parallel plan
---
-explain (costs off) insert into testdef(a,c,d) select a,a*4,a*8 from test_data;
-         QUERY PLAN          
------------------------------
- Insert on testdef
-   ->  Seq Scan on test_data
-(2 rows)
-
---
--- Parallel restricted column default, should use parallel SELECT
---
-explain (costs off) insert into testdef(a,b,d) select a,a*2,a*8 from test_data;
-                 QUERY PLAN                 
---------------------------------------------
- Insert on testdef
-   ->  Gather
-         Workers Planned: 3
-         ->  Parallel Seq Scan on test_data
-(4 rows)
-
-insert into testdef(a,b,d) select a,a*2,a*8 from test_data;
-select * from testdef order by a;
- a  | b  | c  | d  
-----+----+----+----
-  1 |  2 | 10 |  8
-  2 |  4 | 10 | 16
-  3 |  6 | 10 | 24
-  4 |  8 | 10 | 32
-  5 | 10 | 10 | 40
-  6 | 12 | 10 | 48
-  7 | 14 | 10 | 56
-  8 | 16 | 10 | 64
-  9 | 18 | 10 | 72
- 10 | 20 | 10 | 80
-(10 rows)
-
-truncate testdef;
---
--- Parallel restricted and unsafe column defaults, should not use a parallel plan
---
-explain (costs off) insert into testdef(a,d) select a,a*8 from test_data;
-         QUERY PLAN          
------------------------------
- Insert on testdef
-   ->  Seq Scan on test_data
-(2 rows)
-
---
--- Test INSERT into partition with underlying query.
---
-create table parttable1 (a int, b name) partition by range (a) with (parallel_insert_enabled=off);
-create table parttable1_1 partition of parttable1 for values from (0) to (5000);
-create table parttable1_2 partition of parttable1 for values from (5000) to (10000);
---
--- Test INSERT into partition when reloption.parallel_insert_enabled=off
--- (should not create a parallel plan)
---
-explain (costs off) insert into parttable1 select unique1,stringu1 from tenk1;
-       QUERY PLAN        
--------------------------
- Insert on parttable1
-   ->  Seq Scan on tenk1
-(2 rows)
-
---
--- Enable reloption parallel_insert_enabled
---
-alter table parttable1 set (parallel_insert_enabled = on);
---
--- Test INSERT into partition when reloption.parallel_insert_enabled=on
--- (should create a parallel plan)
---
-explain (costs off) insert into parttable1 select unique1,stringu1 from tenk1;
-               QUERY PLAN               
-----------------------------------------
- Insert on parttable1
-   ->  Gather
-         Workers Planned: 4
-         ->  Parallel Seq Scan on tenk1
-(4 rows)
-
-insert into parttable1 select unique1,stringu1 from tenk1;
-select count(*) from parttable1_1;
- count 
--------
-  5000
-(1 row)
-
-select count(*) from parttable1_2;
- count 
--------
-  5000
-(1 row)
-
---
--- Test INSERT into table with parallel-unsafe check constraint
--- (should not create a parallel plan)
---
-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 (costs off) insert into table_check_b(a,b,c) select unique1, unique2, stringu1 from tenk1;
-       QUERY PLAN        
--------------------------
- Insert on table_check_b
-   ->  Seq Scan on tenk1
-(2 rows)
-
---
--- Test INSERT into table with parallel-safe after stmt-level triggers
--- (should create a parallel SELECT plan; triggers should fire)
---
-create table names_with_safe_trigger (like names);
-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_after_trigger_safe after insert on names_with_safe_trigger
-    for each statement execute procedure insert_after_trigger_safe();
-explain (costs off) insert into names_with_safe_trigger select * from names;
-               QUERY PLAN               
-----------------------------------------
- Insert on names_with_safe_trigger
-   ->  Gather
-         Workers Planned: 3
-         ->  Parallel Seq Scan on names
-(4 rows)
-
-insert into names_with_safe_trigger select * from names;
-NOTICE:  hello from insert_after_trigger_safe
---
--- Test INSERT into table with parallel-unsafe after stmt-level triggers
--- (should not create a parallel plan; triggers should fire)
---
-create table names_with_unsafe_trigger (like names);
-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_after_trigger_unsafe after insert on names_with_unsafe_trigger
-    for each statement execute procedure insert_after_trigger_unsafe();
-explain (costs off) insert into names_with_unsafe_trigger select * from names;
-             QUERY PLAN              
--------------------------------------
- Insert on names_with_unsafe_trigger
-   ->  Seq Scan on names
-(2 rows)
-
-insert into names_with_unsafe_trigger select * from names;
-NOTICE:  hello from insert_after_trigger_unsafe
---
--- Test INSERT into partition with parallel-unsafe trigger
--- (should not create a parallel plan)
---
-create table part_unsafe_trigger (a int4, b name) partition by range (a);
-create table part_unsafe_trigger_1 partition of part_unsafe_trigger for values from (0) to (5000);
-create table part_unsafe_trigger_2 partition of part_unsafe_trigger for values from (5000) to (10000);
-create trigger part_insert_after_trigger_unsafe after insert on part_unsafe_trigger_1
-    for each statement execute procedure insert_after_trigger_unsafe();
-explain (costs off) insert into part_unsafe_trigger select unique1, stringu1 from tenk1;
-          QUERY PLAN           
--------------------------------
- Insert on part_unsafe_trigger
-   ->  Seq Scan on tenk1
-(2 rows)
-
---
--- Test that parallel-safety-related changes to partitions are detected and
--- plan cache invalidation is working correctly.
---
-create table rp (a int) partition by range (a);
-create table rp1 partition of rp for values from (minvalue) to (0);
-create table rp2 partition of rp for values from (0) to (maxvalue);
-create table foo (a) as select unique1 from tenk1;
-prepare q as insert into rp select * from foo where a%2 = 0;
--- should create a parallel plan
-explain (costs off) execute q;
-              QUERY PLAN              
---------------------------------------
- Insert on rp
-   ->  Gather
-         Workers Planned: 4
-         ->  Parallel Seq Scan on foo
-               Filter: ((a % 2) = 0)
-(5 rows)
-
-create or replace function make_table_bar () returns trigger language
-plpgsql as $$ begin create table bar(); return null; end; $$ parallel unsafe;
-create trigger ai_rp2 after insert on rp2 for each row execute
-function make_table_bar();
--- should create a non-parallel plan
-explain (costs off) execute q;
-          QUERY PLAN           
--------------------------------
- Insert on rp
-   ->  Seq Scan on foo
-         Filter: ((a % 2) = 0)
-(3 rows)
-
---
--- Test INSERT into table having a DOMAIN column with a CHECK constraint
---
-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 domain inotnull_u int
-  check (sql_is_distinct_from_u(value, null));
-create table dom_table_u (x inotnull_u, y int);
--- Test INSERT into table having a DOMAIN column with parallel-unsafe CHECK constraint
-explain (costs off) insert into dom_table_u select unique1, unique2 from tenk1;
-       QUERY PLAN        
--------------------------
- Insert on dom_table_u
-   ->  Seq Scan on tenk1
-(2 rows)
-
-rollback;
---
--- Clean up anything not created in the transaction
---
-drop table names;
-drop index names2_fullname_idx;
-drop table names2;
-drop index names4_fullname_idx;
-drop table names4;
-drop table testdef;
-drop table test_data;
-drop function bdefault_unsafe;
-drop function cdefault_restricted;
-drop function ddefault_safe;
-drop function fullname_parallel_unsafe;
-drop function fullname_parallel_restricted;
diff --git a/src/test/regress/expected/sysviews.out b/src/test/regress/expected/sysviews.out
index a62bf5dc923..6d048e309cb 100644
--- a/src/test/regress/expected/sysviews.out
+++ b/src/test/regress/expected/sysviews.out
@@ -107,14 +107,13 @@ select name, setting from pg_settings where name like 'enable%';
  enable_nestloop                | on
  enable_parallel_append         | on
  enable_parallel_hash           | on
- enable_parallel_insert         | on
  enable_partition_pruning       | on
  enable_partitionwise_aggregate | off
  enable_partitionwise_join      | off
  enable_seqscan                 | on
  enable_sort                    | on
  enable_tidscan                 | on
-(19 rows)
+(18 rows)
 
 -- Test that the pg_timezone_names and pg_timezone_abbrevs views are
 -- more-or-less working.  We can't test their contents in any great detail
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 70c38309d76..b3605db88c1 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -90,7 +90,6 @@ test: rules psql psql_crosstab amutils stats_ext collate.linux.utf8
 # run by itself so it can run parallel workers
 test: select_parallel
 test: write_parallel
-test: insert_parallel
 
 # no relation related tests can be put in this group
 test: publication subscription
diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule
index d81d04136ce..15ec7548e3a 100644
--- a/src/test/regress/serial_schedule
+++ b/src/test/regress/serial_schedule
@@ -148,7 +148,6 @@ test: stats_ext
 test: collate.linux.utf8
 test: select_parallel
 test: write_parallel
-test: insert_parallel
 test: publication
 test: subscription
 test: select_views
diff --git a/src/test/regress/sql/insert_parallel.sql b/src/test/regress/sql/insert_parallel.sql
deleted file mode 100644
index 171d8e5b84e..00000000000
--- a/src/test/regress/sql/insert_parallel.sql
+++ /dev/null
@@ -1,373 +0,0 @@
---
--- PARALLEL
---
-
---
--- START: setup some tables and data needed by the tests.
---
-
--- Setup - index expressions test
-
--- For testing purposes, we'll mark this function as parallel-unsafe
-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_restricted(f text, l text) returns text as $$
-    begin
-        return f || l;
-    end;
-$$ language plpgsql immutable parallel restricted;
-
-create table names(index int, first_name text, last_name text);
-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 names4(index int, first_name text, last_name text);
-create index names4_fullname_idx on names4 (fullname_parallel_restricted(first_name, last_name));
-
-insert into names values
-	(1, 'albert', 'einstein'),
-	(2, 'niels', 'bohr'),
-	(3, 'erwin', 'schrodinger'),
-	(4, 'leonhard', 'euler'),
-	(5, 'stephen', 'hawking'),
-	(6, 'isaac', 'newton'),
-	(7, 'alan', 'turing'),
-	(8, 'richard', 'feynman');
-
--- Setup - 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 table test_data(a int);
-insert into test_data select * from generate_series(1,10);
-
---
--- END: setup some tables and data needed by the tests.
---
-
-begin;
-
--- encourage use of parallel plans
-set parallel_setup_cost=0;
-set parallel_tuple_cost=0;
-set min_parallel_table_scan_size=0;
-set max_parallel_workers_per_gather=4;
-
-create table para_insert_p1 (
-	unique1		int4	PRIMARY KEY,
-	stringu1	name
-) with (parallel_insert_enabled = off);
-
-create table para_insert_f1 (
-	unique1		int4	REFERENCES para_insert_p1(unique1),
-	stringu1	name
-);
-
---
--- Disable guc option enable_parallel_insert
---
-set enable_parallel_insert = off;
-
--- Test INSERT with underlying query when enable_parallel_insert=off and reloption.parallel_insert_enabled=off.
--- (should create plan with serial INSERT + SELECT)
---
-explain(costs off) insert into para_insert_p1 select unique1, stringu1 from tenk1;
-
---
--- Reset guc option enable_parallel_insert
---
-reset enable_parallel_insert;
-
---
--- Test INSERT with underlying query when enable_parallel_insert=on and reloption.parallel_insert_enabled=off.
--- (should create plan with serial INSERT + SELECT)
---
-explain(costs off) insert into para_insert_p1 select unique1, stringu1 from tenk1;
-
---
--- Enable reloption parallel_insert_enabled
---
-alter table para_insert_p1 set (parallel_insert_enabled = on);
-
---
--- Test INSERT with underlying query.
--- (should create plan with parallel SELECT, Gather parent node)
---
-explain (costs off) insert into para_insert_p1 select unique1, stringu1 from tenk1;
-insert into para_insert_p1 select unique1, stringu1 from tenk1;
--- select some values to verify that the parallel insert worked
-select count(*), sum(unique1) from para_insert_p1;
--- verify that the same transaction has been used by all parallel workers
-select count(*) from (select distinct cmin,xmin from para_insert_p1) as dt;
-
---
--- Test INSERT with ordered underlying query.
--- (should create plan with parallel SELECT, GatherMerge parent node)
---
-truncate para_insert_p1 cascade;
-explain (costs off) insert into para_insert_p1 select unique1, stringu1 from tenk1 order by unique1;
-insert into para_insert_p1 select unique1, stringu1 from tenk1 order by unique1;
--- select some values to verify that the parallel insert worked
-select count(*), sum(unique1) from para_insert_p1;
--- verify that the same transaction has been used by all parallel workers
-select count(*) from (select distinct cmin,xmin from para_insert_p1) as dt;
-
---
--- Test INSERT with RETURNING clause.
--- (should create plan with parallel SELECT, Gather parent node)
---
-create table test_data1(like test_data);
-explain (costs off) insert into test_data1 select * from test_data where a = 10 returning a as data;
-insert into test_data1 select * from test_data where a = 10 returning a as data;
-
---
--- Test INSERT into a table with a foreign key.
--- (Insert into a table with a foreign key is parallel-restricted,
---  as doing this in a parallel worker would create a new commandId
---  and within a worker this is not currently supported)
---
-explain (costs off) insert into para_insert_f1 select unique1, stringu1 from tenk1;
-insert into para_insert_f1 select unique1, stringu1 from tenk1;
--- select some values to verify that the insert worked
-select count(*), sum(unique1) from para_insert_f1;
-
---
--- Test INSERT with ON CONFLICT ... DO UPDATE ...
--- (should not create a parallel plan)
---
-create table test_conflict_table(id serial primary key, somedata int);
-explain (costs off) insert into test_conflict_table(id, somedata) select a, a from test_data;
-insert into test_conflict_table(id, somedata) select a, a from test_data;
-explain (costs off) insert into test_conflict_table(id, somedata) select a, a from test_data ON CONFLICT(id) DO UPDATE SET somedata = EXCLUDED.somedata + 1;
-
-
---
--- Test INSERT with parallel-unsafe index expression
--- (should not create a parallel plan)
---
-explain (costs off) insert into names2 select * from names;
-
---
--- Test INSERT with parallel-restricted index expression
--- (should create a parallel plan)
---
-explain (costs off) insert into names4 select * from names;
-
---
--- Test INSERT with underlying query - and RETURNING (no projection)
--- (should create a parallel plan; parallel SELECT)
---
-create table names5 (like names);
-explain (costs off) insert into names5 select * from names returning *;
-
---
--- Test INSERT with underlying ordered query - and RETURNING (no projection)
--- (should create a parallel plan; parallel SELECT)
---
-create table names6 (like names);
-explain (costs off) insert into names6 select * from names order by last_name returning *;
-insert into names6 select * from names order by last_name returning *;
-
---
--- Test INSERT with underlying ordered query - and RETURNING (with projection)
--- (should create a parallel plan; parallel SELECT)
---
-create table names7 (like names);
-explain (costs off) insert into names7 select * from names order by last_name returning last_name || ', ' || first_name as last_name_then_first_name;
-insert into names7 select * from names order by last_name returning last_name || ', ' || first_name as last_name_then_first_name;
-
-
---
--- Test INSERT into temporary table with underlying query.
--- (Insert into a temp table is parallel-restricted;
--- should create a parallel plan; parallel SELECT)
---
-create temporary table temp_names (like names);
-explain (costs off) insert into temp_names select * from names;
-insert into temp_names select * from names;
-
---
--- Test INSERT with column defaults
---
---
-
---
--- Parallel unsafe column default, should not use a parallel plan
---
-explain (costs off) insert into testdef(a,c,d) select a,a*4,a*8 from test_data;
-
---
--- Parallel restricted column default, should use parallel SELECT
---
-explain (costs off) insert into testdef(a,b,d) select a,a*2,a*8 from test_data;
-insert into testdef(a,b,d) select a,a*2,a*8 from test_data;
-select * from testdef order by a;
-truncate testdef;
-
---
--- Parallel restricted and unsafe column defaults, should not use a parallel plan
---
-explain (costs off) insert into testdef(a,d) select a,a*8 from test_data;
-
---
--- Test INSERT into partition with underlying query.
---
-create table parttable1 (a int, b name) partition by range (a) with (parallel_insert_enabled=off);
-create table parttable1_1 partition of parttable1 for values from (0) to (5000);
-create table parttable1_2 partition of parttable1 for values from (5000) to (10000);
-
---
--- Test INSERT into partition when reloption.parallel_insert_enabled=off
--- (should not create a parallel plan)
---
-explain (costs off) insert into parttable1 select unique1,stringu1 from tenk1;
-
---
--- Enable reloption parallel_insert_enabled
---
-alter table parttable1 set (parallel_insert_enabled = on);
-
---
--- Test INSERT into partition when reloption.parallel_insert_enabled=on
--- (should create a parallel plan)
---
-explain (costs off) insert into parttable1 select unique1,stringu1 from tenk1;
-insert into parttable1 select unique1,stringu1 from tenk1;
-select count(*) from parttable1_1;
-select count(*) from parttable1_2;
-
---
--- Test INSERT into table with parallel-unsafe check constraint
--- (should not create a parallel plan)
---
-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 (costs off) insert into table_check_b(a,b,c) select unique1, unique2, stringu1 from tenk1;
-
---
--- Test INSERT into table with parallel-safe after stmt-level triggers
--- (should create a parallel SELECT plan; triggers should fire)
---
-create table names_with_safe_trigger (like names);
-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_after_trigger_safe after insert on names_with_safe_trigger
-    for each statement execute procedure insert_after_trigger_safe();
-explain (costs off) insert into names_with_safe_trigger select * from names;
-insert into names_with_safe_trigger select * from names;
-
---
--- Test INSERT into table with parallel-unsafe after stmt-level triggers
--- (should not create a parallel plan; triggers should fire)
---
-create table names_with_unsafe_trigger (like names);
-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_after_trigger_unsafe after insert on names_with_unsafe_trigger
-    for each statement execute procedure insert_after_trigger_unsafe();
-explain (costs off) insert into names_with_unsafe_trigger select * from names;
-insert into names_with_unsafe_trigger select * from names;
-
---
--- Test INSERT into partition with parallel-unsafe trigger
--- (should not create a parallel plan)
---
-
-create table part_unsafe_trigger (a int4, b name) partition by range (a);
-create table part_unsafe_trigger_1 partition of part_unsafe_trigger for values from (0) to (5000);
-create table part_unsafe_trigger_2 partition of part_unsafe_trigger for values from (5000) to (10000);
-create trigger part_insert_after_trigger_unsafe after insert on part_unsafe_trigger_1
-    for each statement execute procedure insert_after_trigger_unsafe();
-
-explain (costs off) insert into part_unsafe_trigger select unique1, stringu1 from tenk1;
-
---
--- Test that parallel-safety-related changes to partitions are detected and
--- plan cache invalidation is working correctly.
---
-
-create table rp (a int) partition by range (a);
-create table rp1 partition of rp for values from (minvalue) to (0);
-create table rp2 partition of rp for values from (0) to (maxvalue);
-create table foo (a) as select unique1 from tenk1;
-prepare q as insert into rp select * from foo where a%2 = 0;
--- should create a parallel plan
-explain (costs off) execute q;
-
-create or replace function make_table_bar () returns trigger language
-plpgsql as $$ begin create table bar(); return null; end; $$ parallel unsafe;
-create trigger ai_rp2 after insert on rp2 for each row execute
-function make_table_bar();
--- should create a non-parallel plan
-explain (costs off) execute q;
-
---
--- Test INSERT into table having a DOMAIN column with a CHECK constraint
---
-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 domain inotnull_u int
-  check (sql_is_distinct_from_u(value, null));
-
-create table dom_table_u (x inotnull_u, y int);
-
-
--- Test INSERT into table having a DOMAIN column with parallel-unsafe CHECK constraint
-explain (costs off) insert into dom_table_u select unique1, unique2 from tenk1;
-
-
-rollback;
-
---
--- Clean up anything not created in the transaction
---
-
-drop table names;
-drop index names2_fullname_idx;
-drop table names2;
-drop index names4_fullname_idx;
-drop table names4;
-drop table testdef;
-drop table test_data;
-
-drop function bdefault_unsafe;
-drop function cdefault_restricted;
-drop function ddefault_safe;
-drop function fullname_parallel_unsafe;
-drop function fullname_parallel_restricted;