Determine parallel-safety of partition relations for Inserts

From d3602c6fda71dcbb24f2df8edfebc9e2e98df52e Mon Sep 17 00:00:00 2001
From: houzj <houzj.fnst@cn.fujitsu.com>
Date: Fri, 29 Jan 2021 13:58:29 +0800
Subject: [PATCH] reloption parallel_dml test and doc

Test and document for reloption parallel_dml

---
 doc/src/sgml/ref/alter_table.sgml             |  2 +-
 doc/src/sgml/ref/create_table.sgml            | 22 +++++++
 src/test/regress/expected/insert_parallel.out | 92 ++++++++++++++++-----------
 src/test/regress/sql/insert_parallel.sql      | 87 ++++++++++++++-----------
 4 files changed, 128 insertions(+), 75 deletions(-)

diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index c25ef5a..e649f85 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -722,7 +722,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
      <para>
       <literal>SHARE UPDATE EXCLUSIVE</literal> lock will be taken for
       fillfactor, toast and autovacuum storage parameters, as well as the
-      planner parameter <varname>parallel_workers</varname>.
+      planner parameter <varname>parallel_workers</varname> and <varname>parallel_dml</varname>.
      </para>
     </listitem>
    </varlistentry>
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 569f4c9..c1f343e 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -1408,6 +1408,28 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
     </listitem>
    </varlistentry>
 
+   <varlistentry id="reloption-parallel-dml" xreflabel="parallel_dml">
+    <term><literal>parallel_dml</literal> (<type>boolean</type>)
+     <indexterm>
+     <primary><varname>parallel_dml</varname> storage parameter</primary>
+    </indexterm>
+    </term>
+    <listitem>
+     <para>
+      Enables or disables the parallel table-modification on a particular table.
+      if set it to true, planner will check parallel safety of the target table
+      to determine if it’s safe to execute parallel. (Note: If target is
+      partitioned table, it will check all its partitions and indexes, then
+      the check overhead can become prohibitively high if the number of
+      partitions is large, Especially when the parallelism is not chosen in
+      the end.) To avoid the overhead, the default is <literal>false</literal>.
+      Parallel_dml will not work if <xref linkend="guc-enable-parallel-dml"/>
+      is not set. If target table is parent table(partitioned), only the option
+      of the parent table works, the option values of its child will be ignored.
+     </para>
+    </listitem>
+   </varlistentry>
+
    <varlistentry id="reloption-autovacuum-enabled" xreflabel="autovacuum_enabled">
     <term><literal>autovacuum_enabled</literal>, <literal>toast.autovacuum_enabled</literal> (<type>boolean</type>)
     <indexterm>
diff --git a/src/test/regress/expected/insert_parallel.out b/src/test/regress/expected/insert_parallel.out
index f98d1ae..6954c3c 100644
--- a/src/test/regress/expected/insert_parallel.out
+++ b/src/test/regress/expected/insert_parallel.out
@@ -21,12 +21,12 @@ create or replace function fullname_parallel_restricted(f text, l text) returns
         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 table names(index int, first_name text, last_name text) with (parallel_dml = on);
+create table names2(index int, first_name text, last_name text) with (parallel_dml = on);
 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 table names3(index int, first_name text, last_name text) with (parallel_dml = on);
 create index names3_fullname_idx on names3 (fullname_parallel_safe(first_name, last_name));
-create table names4(index int, first_name text, last_name text);
+create table names4(index int, first_name text, last_name text) with (parallel_dml = on);
 create index names4_fullname_idx on names4 (fullname_parallel_restricted(first_name, last_name));
 insert into names values
 	(1, 'albert', 'einstein'),
@@ -53,8 +53,8 @@ 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);
+create table testdef(a int, b int default bdefault_unsafe(), c int default cdefault_restricted(), d int default ddefault_safe()) with (parallel_dml = on);
+create table test_data(a int) with (parallel_dml = on);
 insert into test_data select * from generate_series(1,10);
 --
 -- END: setup some tables and data needed by the tests.
@@ -74,9 +74,9 @@ create table para_insert_p1 (
 create table para_insert_f1 (
 	unique1		int4	REFERENCES para_insert_p1(unique1),
 	stringu1	name
-);
+) with (parallel_dml = on);
 --
--- Test INSERT with underlying query when enable_parallel_dml=off.
+-- Test INSERT with underlying query when enable_parallel_dml=off and reloption.parallel_dml=off.
 -- (should create plan with serial INSERT + SELECT)
 --
 explain(costs off) insert into para_insert_p1 select unique1, stringu1 from tenk1;
@@ -88,10 +88,28 @@ explain(costs off) insert into para_insert_p1 select unique1, stringu1 from tenk
 
 insert into para_insert_p1 select unique1, stringu1 from tenk1;
 --
--- Enable parallel dml
+-- Enable guc option enable_parallel_dml
 --
 set enable_parallel_dml = on;
 --
+-- Test INSERT with underlying query when enable_parallel_dml=on and reloption.parallel_dml=off.
+-- (should create plan with serial INSERT + SELECT)
+--
+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;
+        QUERY PLAN        
+--------------------------
+ Insert on para_insert_p1
+   ->  Seq Scan on tenk1
+(2 rows)
+
+insert into para_insert_p1 select unique1, stringu1 from tenk1;
+--
+-- Enable reloption parallel_dml
+--
+alter table para_insert_p1 set (parallel_dml = on);
+--
 -- Test INSERT with underlying query.
 -- (should create plan with parallel INSERT+SELECT, Gather parent node)
 --
@@ -305,9 +323,9 @@ reset max_parallel_workers;
 -- Test INSERT with ON CONFLICT ... DO UPDATE ...
 -- (should not create a parallel plan)
 --
-create table test_data2(like test_data);
+create table test_data2(like test_data) with (parallel_dml = on);
 insert into test_data2 select i from generate_series(1,10000) i;
-create table test_conflict_table(id serial primary key, somedata int);
+create table test_conflict_table(id serial primary key, somedata int) with (parallel_dml = on);
 explain (costs off) insert into test_conflict_table(id, somedata) select a, a from test_data;
                  QUERY PLAN                 
 --------------------------------------------
@@ -331,7 +349,7 @@ insert into test_conflict_table(id, somedata) select a, a from test_data ON CONF
 --
 -- Test INSERT with parallelized aggregate
 --
-create table tenk1_avg_data(count int, avg_unique1 int, avg_stringu1_len int);
+create table tenk1_avg_data(count int, avg_unique1 int, avg_stringu1_len int) with (parallel_dml = on);
 explain (costs off) insert into tenk1_avg_data select count(*), avg(unique1), avg(length(stringu1)) from tenk1;
                         QUERY PLAN                        
 ----------------------------------------------------------
@@ -392,7 +410,7 @@ reset enable_indexscan;
 --
 -- Test INSERT with parallel append
 --
-create table a_star_data(aa int);
+create table a_star_data(aa int) with (parallel_dml = on);
 explain (costs off) insert into a_star_data select aa from a_star where aa > 10;
                        QUERY PLAN                       
 --------------------------------------------------------
@@ -586,7 +604,7 @@ select * from names4 order by fullname_parallel_restricted(first_name, last_name
 -- Test INSERT with underlying query - and RETURNING (no projection)
 -- (should create a parallel plan; parallel INSERT+SELECT)
 --
-create table names5 (like names);
+create table names5 (like names) with (parallel_dml = on);
 explain (costs off) insert into names5 select * from names returning *;
                QUERY PLAN               
 ----------------------------------------
@@ -600,7 +618,7 @@ 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; INSERT + parallel SELECT)
 --
-create table names6 (like names);
+create table names6 (like names) with (parallel_dml = on);
 explain (costs off) insert into names6 select * from names order by last_name returning *;
                   QUERY PLAN                  
 ----------------------------------------------
@@ -629,7 +647,7 @@ 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; INSERT + parallel SELECT)
 --
-create table names7 (like names);
+create table names7 (like names) with (parallel_dml = on);
 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                  
 ----------------------------------------------
@@ -658,7 +676,7 @@ insert into names7 select * from names order by last_name returning last_name ||
 -- Test INSERT into temporary table with underlying query.
 -- (should not use a parallel plan)
 --
-create temporary table temp_names (like names);
+create temporary table temp_names (like names) with (parallel_dml = on);
 explain (costs off) insert into temp_names select * from names;
                QUERY PLAN               
 ----------------------------------------
@@ -822,9 +840,9 @@ truncate testdef;
 --
 -- Test INSERT into partition with underlying query.
 --
-create table parttable1 (a int, b name) partition by range (a);
-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);
+create table parttable1 (a int, b name) partition by range (a) with (parallel_dml = on);
+create table parttable1_1 partition of parttable1 for values from (0) to (5000) with (parallel_dml = on);
+create table parttable1_2 partition of parttable1 for values from (5000) to (10000) with (parallel_dml = on);
 explain (costs off) insert into parttable1 select unique1,stringu1 from tenk1;
                QUERY PLAN               
 ----------------------------------------
@@ -857,9 +875,9 @@ 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_supp_fn_t (a int4, b name) partition by range (a test_int4_ops);
-create table partkey_unsafe_key_supp_fn_t_1 partition of partkey_unsafe_key_supp_fn_t for values from (0) to (5000);
-create table partkey_unsafe_key_supp_fn_t_2 partition of partkey_unsafe_key_supp_fn_t for values from (5000) to (10000);
+create table partkey_unsafe_key_supp_fn_t (a int4, b name) partition by range (a test_int4_ops) with (parallel_dml = on);
+create table partkey_unsafe_key_supp_fn_t_1 partition of partkey_unsafe_key_supp_fn_t for values from (0) to (5000) with (parallel_dml = on);
+create table partkey_unsafe_key_supp_fn_t_2 partition of partkey_unsafe_key_supp_fn_t for values from (5000) to (10000) with (parallel_dml = on);
 explain (costs off) insert into partkey_unsafe_key_supp_fn_t select unique1, stringu1 from tenk1;
                QUERY PLAN               
 ----------------------------------------
@@ -871,7 +889,7 @@ explain (costs off) insert into partkey_unsafe_key_supp_fn_t select unique1, str
 -- Test INSERT into partition with parallel-unsafe partition key expression
 -- (should not create a parallel plan)
 --
-create table partkey_unsafe_key_expr_t (a int4, b name) partition by range ((fullname_parallel_unsafe('',a::varchar)));
+create table partkey_unsafe_key_expr_t (a int4, b name) partition by range ((fullname_parallel_unsafe('',a::varchar))) with (parallel_dml = on);
 explain (costs off) insert into partkey_unsafe_key_expr_t select unique1, stringu1 from tenk1;
              QUERY PLAN              
 -------------------------------------
@@ -888,7 +906,7 @@ create or replace function check_a(a int4) returns boolean as $$
         return (a >= 0 and a <= 9999);
     end;
 $$ language plpgsql parallel safe;
-create table table_check_a(a int4 check (check_a(a)), b name);
+create table table_check_a(a int4 check (check_a(a)), b name) with (parallel_dml = on);
 explain (costs off) insert into table_check_a select unique1, stringu1 from tenk1;
                QUERY PLAN               
 ----------------------------------------
@@ -914,7 +932,7 @@ create or replace function check_b_unsafe(b name) returns boolean as $$
         return (b <> 'XXXXXX');
     end;
 $$ language plpgsql parallel unsafe;
-create table table_check_b(a int4, b name check (check_b_unsafe(b)), c name);
+create table table_check_b(a int4, b name check (check_b_unsafe(b)), c name) with (parallel_dml = on);
 explain (costs off) insert into table_check_b(a,b,c) select unique1, unique2, stringu1 from tenk1;
        QUERY PLAN        
 -------------------------
@@ -934,7 +952,7 @@ select count(*), sum(a) from table_check_b;
 -- (should create a parallel INSERT+SELECT plan;
 --  stmt-level before+after triggers should fire)
 --
-create table names_with_safe_trigger (like names);
+create table names_with_safe_trigger (like names) with (parallel_dml = on);
 create or replace function insert_before_trigger_safe() returns trigger as $$
     begin
         raise notice 'hello from insert_before_trigger_safe';
@@ -968,7 +986,7 @@ NOTICE:  hello from insert_after_trigger_safe
 -- (should not create a parallel plan;
 --  stmt-level before+after triggers should fire)
 --
-create table names_with_unsafe_trigger (like names);
+create table names_with_unsafe_trigger (like names) with (parallel_dml = on);
 create or replace function insert_before_trigger_unsafe() returns trigger as $$
     begin
         raise notice 'hello from insert_before_trigger_unsafe';
@@ -1000,7 +1018,7 @@ NOTICE:  hello from insert_after_trigger_unsafe
 -- (should create a parallel plan with INSERT + parallel SELECT;
 --  stmt-level before+after triggers should fire)
 --
-create table names_with_restricted_trigger (like names);
+create table names_with_restricted_trigger (like names) with (parallel_dml = on);
 create or replace function insert_before_trigger_restricted() returns trigger as $$
     begin
         raise notice 'hello from insert_before_trigger_restricted';
@@ -1033,9 +1051,9 @@ NOTICE:  hello from insert_after_trigger_restricted
 -- 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 table part_unsafe_trigger (a int4, b name) partition by range (a) with (parallel_dml = on);
+create table part_unsafe_trigger_1 partition of part_unsafe_trigger for values from (0) to (5000) with (parallel_dml = on);
+create table part_unsafe_trigger_2 partition of part_unsafe_trigger for values from (5000) to (10000) with (parallel_dml = on);
 create trigger insert_before_trigger_unsafe before insert on part_unsafe_trigger_1
     for each statement execute procedure insert_before_trigger_unsafe();
 explain (costs off) insert into part_unsafe_trigger select unique1, stringu1 from tenk1;
@@ -1048,8 +1066,8 @@ explain (costs off) insert into part_unsafe_trigger select unique1, stringu1 fro
 --
 -- Test INSERT into table with TOAST column
 --
-create table insert_toast_table(index int4, data text);
-create table insert_toast_table_data (like insert_toast_table);
+create table insert_toast_table(index int4, data text) with (parallel_dml = on);
+create table insert_toast_table_data (like insert_toast_table) with (parallel_dml = on);
 insert into insert_toast_table_data select i, rpad('T', 16384, 'ABCDEFGH') from generate_series(1,20) as i;
 explain (costs off) insert into insert_toast_table select index, data from insert_toast_table_data;
                         QUERY PLAN                        
@@ -1090,9 +1108,9 @@ 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);
+create table dom_table_u (x inotnull_u, y int) with (parallel_dml = on);
+create table dom_table_r (x inotnull_r, y int) with (parallel_dml = on);
+create table dom_table_s (x inotnull_s, y int) with (parallel_dml = on);
 -- 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        
diff --git a/src/test/regress/sql/insert_parallel.sql b/src/test/regress/sql/insert_parallel.sql
index 5317313..2f7b92a 100644
--- a/src/test/regress/sql/insert_parallel.sql
+++ b/src/test/regress/sql/insert_parallel.sql
@@ -27,12 +27,12 @@ create or replace function fullname_parallel_restricted(f text, l text) returns
     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 table names(index int, first_name text, last_name text) with (parallel_dml = on);
+create table names2(index int, first_name text, last_name text) with (parallel_dml = on);
 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 table names3(index int, first_name text, last_name text) with (parallel_dml = on);
 create index names3_fullname_idx on names3 (fullname_parallel_safe(first_name, last_name));
-create table names4(index int, first_name text, last_name text);
+create table names4(index int, first_name text, last_name text) with (parallel_dml = on);
 create index names4_fullname_idx on names4 (fullname_parallel_restricted(first_name, last_name));
 
 insert into names values
@@ -65,9 +65,9 @@ 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 testdef(a int, b int default bdefault_unsafe(), c int default cdefault_restricted(), d int default ddefault_safe()) with (parallel_dml = on);
 
-create table test_data(a int);
+create table test_data(a int) with (parallel_dml = on);
 insert into test_data select * from generate_series(1,10);
 
 --
@@ -92,21 +92,34 @@ create table para_insert_p1 (
 create table para_insert_f1 (
 	unique1		int4	REFERENCES para_insert_p1(unique1),
 	stringu1	name
-);
+) with (parallel_dml = on);
 
 --
--- Test INSERT with underlying query when enable_parallel_dml=off.
+-- Test INSERT with underlying query when enable_parallel_dml=off and reloption.parallel_dml=off.
 -- (should create plan with serial INSERT + SELECT)
 --
 explain(costs off) insert into para_insert_p1 select unique1, stringu1 from tenk1;
 insert into para_insert_p1 select unique1, stringu1 from tenk1;
 
 --
--- Enable parallel dml
+-- Enable guc option enable_parallel_dml
 --
 set enable_parallel_dml = on;
 
 --
+-- Test INSERT with underlying query when enable_parallel_dml=on and reloption.parallel_dml=off.
+-- (should create plan with serial INSERT + SELECT)
+--
+truncate para_insert_p1 cascade;
+explain(costs off) insert into para_insert_p1 select unique1, stringu1 from tenk1;
+insert into para_insert_p1 select unique1, stringu1 from tenk1;
+
+--
+-- Enable reloption parallel_dml
+--
+alter table para_insert_p1 set (parallel_dml = on);
+
+--
 -- Test INSERT with underlying query.
 -- (should create plan with parallel INSERT+SELECT, Gather parent node)
 --
@@ -171,9 +184,9 @@ reset max_parallel_workers;
 -- Test INSERT with ON CONFLICT ... DO UPDATE ...
 -- (should not create a parallel plan)
 --
-create table test_data2(like test_data);
+create table test_data2(like test_data) with (parallel_dml = on);
 insert into test_data2 select i from generate_series(1,10000) i;
-create table test_conflict_table(id serial primary key, somedata int);
+create table test_conflict_table(id serial primary key, somedata int) with (parallel_dml = on);
 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;
@@ -182,7 +195,7 @@ insert into test_conflict_table(id, somedata) select a, a from test_data ON CONF
 --
 -- Test INSERT with parallelized aggregate
 --
-create table tenk1_avg_data(count int, avg_unique1 int, avg_stringu1_len int);
+create table tenk1_avg_data(count int, avg_unique1 int, avg_stringu1_len int) with (parallel_dml = on);
 explain (costs off) insert into tenk1_avg_data select count(*), avg(unique1), avg(length(stringu1)) from tenk1;
 insert into tenk1_avg_data select count(*), avg(unique1), avg(length(stringu1)) from tenk1;
 select * from tenk1_avg_data;
@@ -203,7 +216,7 @@ reset enable_indexscan;
 --
 -- Test INSERT with parallel append
 --
-create table a_star_data(aa int);
+create table a_star_data(aa int) with (parallel_dml = on);
 explain (costs off) insert into a_star_data select aa from a_star where aa > 10;
 insert into a_star_data select aa from a_star where aa > 10;
 select count(aa), sum(aa) from a_star_data;
@@ -264,14 +277,14 @@ select * from names4 order by fullname_parallel_restricted(first_name, last_name
 -- Test INSERT with underlying query - and RETURNING (no projection)
 -- (should create a parallel plan; parallel INSERT+SELECT)
 --
-create table names5 (like names);
+create table names5 (like names) with (parallel_dml = on);
 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; INSERT + parallel SELECT)
 --
-create table names6 (like names);
+create table names6 (like names) with (parallel_dml = on);
 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 *;
 
@@ -279,7 +292,7 @@ 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; INSERT + parallel SELECT)
 --
-create table names7 (like names);
+create table names7 (like names) with (parallel_dml = on);
 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;
 
@@ -287,7 +300,7 @@ insert into names7 select * from names order by last_name returning last_name ||
 -- Test INSERT into temporary table with underlying query.
 -- (should not use a parallel plan)
 --
-create temporary table temp_names (like names);
+create temporary table temp_names (like names) with (parallel_dml = on);
 explain (costs off) insert into temp_names select * from names;
 insert into temp_names select * from names;
 
@@ -344,9 +357,9 @@ truncate testdef;
 --
 -- Test INSERT into partition with underlying query.
 --
-create table parttable1 (a int, b name) partition by range (a);
-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);
+create table parttable1 (a int, b name) partition by range (a) with (parallel_dml = on);
+create table parttable1_1 partition of parttable1 for values from (0) to (5000) with (parallel_dml = on);
+create table parttable1_2 partition of parttable1 for values from (5000) to (10000) with (parallel_dml = on);
 
 explain (costs off) insert into parttable1 select unique1,stringu1 from tenk1;
 insert into parttable1 select unique1,stringu1 from tenk1;
@@ -365,9 +378,9 @@ create operator class test_int4_ops for type int4 using btree as
   operator 3 = (int4,int4), operator 4 >= (int4,int4),
   operator 5 > (int4,int4), function 1 my_int4_sort(int4,int4);
 
-create table partkey_unsafe_key_supp_fn_t (a int4, b name) partition by range (a test_int4_ops);
-create table partkey_unsafe_key_supp_fn_t_1 partition of partkey_unsafe_key_supp_fn_t for values from (0) to (5000);
-create table partkey_unsafe_key_supp_fn_t_2 partition of partkey_unsafe_key_supp_fn_t for values from (5000) to (10000);
+create table partkey_unsafe_key_supp_fn_t (a int4, b name) partition by range (a test_int4_ops) with (parallel_dml = on);
+create table partkey_unsafe_key_supp_fn_t_1 partition of partkey_unsafe_key_supp_fn_t for values from (0) to (5000) with (parallel_dml = on);
+create table partkey_unsafe_key_supp_fn_t_2 partition of partkey_unsafe_key_supp_fn_t for values from (5000) to (10000) with (parallel_dml = on);
 
 explain (costs off) insert into partkey_unsafe_key_supp_fn_t select unique1, stringu1 from tenk1;
 
@@ -375,7 +388,7 @@ explain (costs off) insert into partkey_unsafe_key_supp_fn_t select unique1, str
 -- Test INSERT into partition with parallel-unsafe partition key expression
 -- (should not create a parallel plan)
 --
-create table partkey_unsafe_key_expr_t (a int4, b name) partition by range ((fullname_parallel_unsafe('',a::varchar)));
+create table partkey_unsafe_key_expr_t (a int4, b name) partition by range ((fullname_parallel_unsafe('',a::varchar))) with (parallel_dml = on);
 explain (costs off) insert into partkey_unsafe_key_expr_t select unique1, stringu1 from tenk1;
 
 --
@@ -388,7 +401,7 @@ create or replace function check_a(a int4) returns boolean as $$
     end;
 $$ language plpgsql parallel safe;
 
-create table table_check_a(a int4 check (check_a(a)), b name);
+create table table_check_a(a int4 check (check_a(a)), b name) with (parallel_dml = on);
 explain (costs off) insert into table_check_a select unique1, stringu1 from tenk1;
 insert into table_check_a select unique1, stringu1 from tenk1;
 select count(*), sum(a) from table_check_a;
@@ -403,7 +416,7 @@ create or replace function check_b_unsafe(b name) returns boolean as $$
     end;
 $$ language plpgsql parallel unsafe;
 
-create table table_check_b(a int4, b name check (check_b_unsafe(b)), c name);
+create table table_check_b(a int4, b name check (check_b_unsafe(b)), c name) with (parallel_dml = on);
 explain (costs off) insert into table_check_b(a,b,c) select unique1, unique2, stringu1 from tenk1;
 insert into table_check_b(a,b,c) select unique1, stringu1, stringu2 from tenk1;
 select count(*), sum(a) from table_check_b;
@@ -413,7 +426,7 @@ select count(*), sum(a) from table_check_b;
 -- (should create a parallel INSERT+SELECT plan;
 --  stmt-level before+after triggers should fire)
 --
-create table names_with_safe_trigger (like names);
+create table names_with_safe_trigger (like names) with (parallel_dml = on);
 create or replace function insert_before_trigger_safe() returns trigger as $$
     begin
         raise notice 'hello from insert_before_trigger_safe';
@@ -438,7 +451,7 @@ insert into names_with_safe_trigger select * from names;
 -- (should not create a parallel plan;
 --  stmt-level before+after triggers should fire)
 --
-create table names_with_unsafe_trigger (like names);
+create table names_with_unsafe_trigger (like names) with (parallel_dml = on);
 create or replace function insert_before_trigger_unsafe() returns trigger as $$
     begin
         raise notice 'hello from insert_before_trigger_unsafe';
@@ -463,7 +476,7 @@ insert into names_with_unsafe_trigger select * from names;
 -- (should create a parallel plan with INSERT + parallel SELECT;
 --  stmt-level before+after triggers should fire)
 --
-create table names_with_restricted_trigger (like names);
+create table names_with_restricted_trigger (like names) with (parallel_dml = on);
 create or replace function insert_before_trigger_restricted() returns trigger as $$
     begin
         raise notice 'hello from insert_before_trigger_restricted';
@@ -488,9 +501,9 @@ insert into names_with_restricted_trigger select * from names;
 -- (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 table part_unsafe_trigger (a int4, b name) partition by range (a) with (parallel_dml = on);
+create table part_unsafe_trigger_1 partition of part_unsafe_trigger for values from (0) to (5000) with (parallel_dml = on);
+create table part_unsafe_trigger_2 partition of part_unsafe_trigger for values from (5000) to (10000) with (parallel_dml = on);
 create trigger insert_before_trigger_unsafe before insert on part_unsafe_trigger_1
     for each statement execute procedure insert_before_trigger_unsafe();
 
@@ -499,8 +512,8 @@ explain (costs off) insert into part_unsafe_trigger select unique1, stringu1 fro
 --
 -- Test INSERT into table with TOAST column
 --
-create table insert_toast_table(index int4, data text);
-create table insert_toast_table_data (like insert_toast_table);
+create table insert_toast_table(index int4, data text) with (parallel_dml = on);
+create table insert_toast_table_data (like insert_toast_table) with (parallel_dml = on);
 insert into insert_toast_table_data select i, rpad('T', 16384, 'ABCDEFGH') from generate_series(1,20) as i;
 explain (costs off) insert into insert_toast_table select index, data from insert_toast_table_data;
 insert into insert_toast_table select index, data from insert_toast_table_data;
@@ -535,9 +548,9 @@ create domain inotnull_r int
 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);
+create table dom_table_u (x inotnull_u, y int) with (parallel_dml = on);
+create table dom_table_r (x inotnull_r, y int) with (parallel_dml = on);
+create table dom_table_s (x inotnull_s, y int) with (parallel_dml = on);
 
 
 -- Test INSERT into table having a DOMAIN column with parallel-unsafe CHECK constraint
-- 
2.7.2.windows.1

From c23a9f029043e54fc7117e870f5e829c1d13adaa Mon Sep 17 00:00:00 2001
From: houzj <houzj.fnst@cn.fujitsu.com>
Date: Fri, 29 Jan 2021 10:30:01 +0800
Subject: [PATCH 1/2] guc option enable_parallel_dml src

add new guc option enable_parallel_dml(boolean)

The current implementation of parallel INSERT SELECT incurs non-negligible
overhead for parallel-safety check even when the parallelism is not chosen
in the end. To solve this, add enable_parallel_dml option let user decide whether
to use parallelizing DML.

The default is false.

---
 src/backend/optimizer/path/costsize.c         |  2 ++
 src/backend/optimizer/plan/planner.c          |  3 ++-
 src/backend/utils/misc/guc.c                  | 11 +++++++++++
 src/backend/utils/misc/postgresql.conf.sample |  1 +
 src/include/optimizer/cost.h                  |  1 +
 5 files changed, 17 insertions(+), 1 deletion(-)

diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c
index f7c13be..778f71b 100644
--- a/src/backend/optimizer/path/costsize.c
+++ b/src/backend/optimizer/path/costsize.c
@@ -129,6 +129,8 @@ Cost		disable_cost = 1.0e10;
 
 int			max_parallel_workers_per_gather = 2;
 
+bool		enable_parallel_dml = false;
+
 bool		enable_seqscan = true;
 bool		enable_indexscan = true;
 bool		enable_indexonlyscan = true;
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index 92f75f3..f6ac972 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -339,7 +339,8 @@ standard_planner(Query *parse, const char *query_string, int cursorOptions,
 	if ((cursorOptions & CURSOR_OPT_PARALLEL_OK) != 0 &&
 		IsUnderPostmaster &&
 		(parse->commandType == CMD_SELECT ||
-		 IsModifySupportedInParallelMode(parse->commandType)) &&
+		(enable_parallel_dml &&
+		IsModifySupportedInParallelMode(parse->commandType))) &&
 		!parse->hasModifyingCTE &&
 		max_parallel_workers_per_gather > 0 &&
 		!IsParallelWorker())
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index 17579ee..aaa788e 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -2048,6 +2048,17 @@ static struct config_bool ConfigureNamesBool[] =
 		NULL, NULL, NULL
 	},
 
+	{
+		{"enable_parallel_dml", PGC_USERSET, QUERY_TUNING_METHOD,
+			gettext_noop("Enables the planner's use of parallel plans for table-modification command."),
+			NULL,
+			GUC_EXPLAIN
+		},
+		&enable_parallel_dml,
+		false,
+		NULL, NULL, NULL
+	},
+
 	/* End-of-list marker */
 	{
 		{NULL, 0, 0, NULL, NULL}, NULL, false, NULL, NULL, NULL
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index 8930a94..8d897ba 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -370,6 +370,7 @@
 #enable_partitionwise_aggregate = off
 #enable_parallel_hash = on
 #enable_partition_pruning = on
+#enable_parallel_dml = off
 
 # - Planner Cost Constants -
 
diff --git a/src/include/optimizer/cost.h b/src/include/optimizer/cost.h
index 9f15fcb..4af0beb 100644
--- a/src/include/optimizer/cost.h
+++ b/src/include/optimizer/cost.h
@@ -47,6 +47,7 @@ typedef enum
 /* parameter variables and flags (see also optimizer.h) */
 extern PGDLLIMPORT Cost disable_cost;
 extern PGDLLIMPORT int max_parallel_workers_per_gather;
+extern PGDLLIMPORT bool enable_parallel_dml;
 extern PGDLLIMPORT bool enable_seqscan;
 extern PGDLLIMPORT bool enable_indexscan;
 extern PGDLLIMPORT bool enable_indexonlyscan;
-- 
2.7.2.windows.1

From eb4ea2423d3ab63fd87767afcfe7560c7e2e39ad Mon Sep 17 00:00:00 2001
From: houzj <houzj.fnst@cn.fujitsu.com>
Date: Fri, 29 Jan 2021 10:30:18 +0800
Subject: [PATCH 2/2] guc option enable_parallel_dml doc and test

Test and document for enable_parallel_dml

---
 doc/src/sgml/config.sgml                      | 19 +++++++++++++++++++
 src/test/regress/expected/insert_parallel.out | 18 ++++++++++++++++++
 src/test/regress/expected/sysviews.out        |  3 ++-
 src/test/regress/sql/insert_parallel.sql      | 12 ++++++++++++
 4 files changed, 51 insertions(+), 1 deletion(-)

diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 82864bb..c895999 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -5024,6 +5024,25 @@ ANY <replaceable class="parameter">num_sync</replaceable> ( <replaceable class="
       </listitem>
      </varlistentry>
 
+     <varlistentry id="guc-enable-parallel-dml" xreflabel="enable_parallel_dml">
+      <term><varname>enable_parallel_dml</varname> (<type>boolean</type>)
+      <indexterm>
+       <primary><varname>enable_parallel_dml</varname> configuration parameter</primary>
+      </indexterm>
+      </term>
+      <listitem>
+       <para>
+        Enables or disables the parallel mode for table-modification command.
+        if set it to on, planner will check parallel safety of the target table
+        to determine if it’s safe to execute parallel. (Note: If target is
+        partitioned table, it will check all its partitions and indexes, then
+        the check overhead can become prohibitively high if the number of
+        partitions is large, Especially when the parallelism is not chosen in
+        the end.) To avoid the overhead, The default is <literal>off</literal>.
+       </para>
+      </listitem>
+     </varlistentry>
+
      </variablelist>
      </sect2>
      <sect2 id="runtime-config-query-constants">
diff --git a/src/test/regress/expected/insert_parallel.out b/src/test/regress/expected/insert_parallel.out
index 3b922a2..f98d1ae 100644
--- a/src/test/regress/expected/insert_parallel.out
+++ b/src/test/regress/expected/insert_parallel.out
@@ -76,9 +76,27 @@ create table para_insert_f1 (
 	stringu1	name
 );
 --
+-- Test INSERT with underlying query when enable_parallel_dml=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)
+
+insert into para_insert_p1 select unique1, stringu1 from tenk1;
+--
+-- Enable parallel dml
+--
+set enable_parallel_dml = on;
+--
 -- Test INSERT with underlying query.
 -- (should create plan with parallel INSERT+SELECT, Gather 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;
                QUERY PLAN               
 ----------------------------------------
diff --git a/src/test/regress/expected/sysviews.out b/src/test/regress/expected/sysviews.out
index 81bdacf..3f11216 100644
--- a/src/test/regress/expected/sysviews.out
+++ b/src/test/regress/expected/sysviews.out
@@ -99,6 +99,7 @@ select name, setting from pg_settings where name like 'enable%';
  enable_mergejoin               | on
  enable_nestloop                | on
  enable_parallel_append         | on
+ enable_parallel_dml            | off
  enable_parallel_hash           | on
  enable_partition_pruning       | on
  enable_partitionwise_aggregate | off
@@ -106,7 +107,7 @@ select name, setting from pg_settings where name like 'enable%';
  enable_seqscan                 | on
  enable_sort                    | on
  enable_tidscan                 | on
-(18 rows)
+(19 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/sql/insert_parallel.sql b/src/test/regress/sql/insert_parallel.sql
index 34a191f..5317313 100644
--- a/src/test/regress/sql/insert_parallel.sql
+++ b/src/test/regress/sql/insert_parallel.sql
@@ -94,11 +94,23 @@ create table para_insert_f1 (
 	stringu1	name
 );
 
+--
+-- Test INSERT with underlying query when enable_parallel_dml=off.
+-- (should create plan with serial INSERT + SELECT)
+--
+explain(costs off) insert into para_insert_p1 select unique1, stringu1 from tenk1;
+insert into para_insert_p1 select unique1, stringu1 from tenk1;
+
+--
+-- Enable parallel dml
+--
+set enable_parallel_dml = on;
 
 --
 -- Test INSERT with underlying query.
 -- (should create plan with parallel INSERT+SELECT, Gather parent node)
 --
+truncate para_insert_p1 cascade;
 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
-- 
2.7.2.windows.1

From dc0577899642da7847e25673bac2ab3b526bdb62 Mon Sep 17 00:00:00 2001
From: houzj <houzj.fnst@cn.fujitsu.com>
Date: Fri, 29 Jan 2021 13:19:41 +0800
Subject: [PATCH 1/2] reloption parallel_dml src

add new tableoption parallel_dml

In addition to guc option, user may want to use parallel 
dml for some specific tables.So add new tableoption 
parallel_dml, let user decide whether to use parallel 
DML for specific table.

The default is false.

---
 src/backend/access/common/reloptions.c | 25 +++++++++++++++++++++----
 src/backend/optimizer/util/clauses.c   | 26 ++++++++++++++++++++++++++
 src/bin/psql/tab-complete.c            |  1 +
 src/include/utils/rel.h                | 10 ++++++++++
 4 files changed, 58 insertions(+), 4 deletions(-)

diff --git a/src/backend/access/common/reloptions.c b/src/backend/access/common/reloptions.c
index c687d3e..f30f2fa 100644
--- a/src/backend/access/common/reloptions.c
+++ b/src/backend/access/common/reloptions.c
@@ -168,6 +168,15 @@ static relopt_bool boolRelOpts[] =
 		},
 		true
 	},
+	{
+		{
+			"parallel_dml",
+			"Enables \"parallel dml\" feature for this table",
+			RELOPT_KIND_HEAP | RELOPT_KIND_PARTITIONED,
+			ShareUpdateExclusiveLock
+		},
+		false
+	},
 	/* list terminator */
 	{{NULL}}
 };
@@ -1859,7 +1868,9 @@ default_reloptions(Datum reloptions, bool validate, relopt_kind kind)
 		{"vacuum_index_cleanup", RELOPT_TYPE_BOOL,
 		offsetof(StdRdOptions, vacuum_index_cleanup)},
 		{"vacuum_truncate", RELOPT_TYPE_BOOL,
-		offsetof(StdRdOptions, vacuum_truncate)}
+		offsetof(StdRdOptions, vacuum_truncate)},
+		{"parallel_dml", RELOPT_TYPE_BOOL,
+		offsetof(StdRdOptions, parallel_dml)}
 	};
 
 	return (bytea *) build_reloptions(reloptions, validate, kind,
@@ -1961,13 +1972,19 @@ build_local_reloptions(local_relopts *relopts, Datum options, bool validate)
 bytea *
 partitioned_table_reloptions(Datum reloptions, bool validate)
 {
+	static const relopt_parse_elt tab[] = {
+		{"parallel_dml", RELOPT_TYPE_BOOL,
+		offsetof(StdRdOptions, parallel_dml)}
+	};
+
 	/*
-	 * There are no options for partitioned tables yet, but this is able to do
-	 * some validation.
+	 * The only option for partitioned tables works for heap too,
+	 * so we temporarily store it in the same struct as heap.
 	 */
 	return (bytea *) build_reloptions(reloptions, validate,
 									  RELOPT_KIND_PARTITIONED,
-									  0, NULL, 0);
+									  sizeof(StdRdOptions),
+									  tab, lengthof(tab));
 }
 
 /*
diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index 02a7e05..9d4b100 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -570,6 +570,32 @@ max_parallel_hazard(Query *parse)
 	context.max_hazard = PROPARALLEL_SAFE;
 	context.max_interesting = PROPARALLEL_UNSAFE;
 	context.safe_param_ids = NIL;
+
+	if (IsModifySupportedInParallelMode(parse->commandType))
+	{
+		Relation		rel;
+		RangeTblEntry  *rte;
+
+		rte = rt_fetch(parse->resultRelation, parse->rtable);
+		rel = table_open(rte->relid, NoLock);
+
+		/*
+		 * Check if parallel_dml is enabled for the target table,
+		 * if not, skip the safety checks and return PARALLEL_UNSAFE.
+		 *
+		 * (Note: if target table is partitioned, we just check the
+		 * option of parent table and ignore the option values for its child)
+		 */
+		if(!RelationGetParallelDML(rel, false))
+		{
+			table_close(rel, NoLock);
+			return PROPARALLEL_UNSAFE;
+		}
+
+		table_close(rel, NoLock);
+	}
+
+
 	(void) max_parallel_hazard_walker((Node *) parse, &context);
 
 	/*
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 17f7265..38f5b1e 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1110,6 +1110,7 @@ static const char *const table_storage_parameters[] = {
 	"autovacuum_vacuum_threshold",
 	"fillfactor",
 	"log_autovacuum_min_duration",
+	"parallel_dml",
 	"parallel_workers",
 	"toast.autovacuum_enabled",
 	"toast.autovacuum_freeze_max_age",
diff --git a/src/include/utils/rel.h b/src/include/utils/rel.h
index 2a41a00..9dc1ab8 100644
--- a/src/include/utils/rel.h
+++ b/src/include/utils/rel.h
@@ -307,6 +307,7 @@ typedef struct StdRdOptions
 	int			parallel_workers;	/* max number of parallel workers */
 	bool		vacuum_index_cleanup;	/* enables index vacuuming and cleanup */
 	bool		vacuum_truncate;	/* enables vacuum to truncate a relation */
+	bool		parallel_dml;	/* enable parallel table-modification */
 } StdRdOptions;
 
 #define HEAP_MIN_FILLFACTOR			10
@@ -362,6 +363,15 @@ typedef struct StdRdOptions
 	((relation)->rd_options ? \
 	 ((StdRdOptions *) (relation)->rd_options)->parallel_workers : (defaultpw))
 
+/*
+ * RelationGetParallelDML
+ *		Returns the relation's parallel_dml reloption setting.
+ *		Note multiple eval of argument!
+ */
+#define RelationGetParallelDML(relation, defaultpd) \
+	((relation)->rd_options ? \
+	 ((StdRdOptions *) (relation)->rd_options)->parallel_dml : (defaultpd))
+
 /* ViewOptions->check_option values */
 typedef enum ViewOptCheckOption
 {
-- 
2.7.2.windows.1

From 6b09e2082eb8b2ff8ebc53ddfcdf1bd110660214 Mon Sep 17 00:00:00 2001
From: houzj <houzj.fnst@cn.fujitsu.com>
Date: Mon, 1 Feb 2021 11:24:22 +0800
Subject: [PATCH 1/2] reloption parallel_dml_enabled src

add new tableoption parallel_dml_enabled

In addition to guc option, user may want to use parallel 
dml for some specific tables.So add new tableoption 
parallel_dml_enabled, let user decide whether to use parallel 
DML for specific table.

The default is true.
---
 src/backend/access/common/reloptions.c | 25 +++++++++++--
 src/backend/optimizer/plan/planner.c   |  3 +-
 src/backend/optimizer/util/clauses.c   | 68 ++++++++++++++++++++++++++++------
 src/bin/psql/tab-complete.c            |  1 +
 src/include/optimizer/clauses.h        |  1 +
 src/include/utils/rel.h                | 10 +++++
 6 files changed, 91 insertions(+), 17 deletions(-)

diff --git a/src/backend/access/common/reloptions.c b/src/backend/access/common/reloptions.c
index c687d3e..3fb3ceb 100644
--- a/src/backend/access/common/reloptions.c
+++ b/src/backend/access/common/reloptions.c
@@ -168,6 +168,15 @@ static relopt_bool boolRelOpts[] =
 		},
 		true
 	},
+	{
+		{
+			"parallel_dml_enabled",
+			"Enables \"parallel dml\" feature for this table",
+			RELOPT_KIND_HEAP | RELOPT_KIND_PARTITIONED,
+			ShareUpdateExclusiveLock
+		},
+		true
+	},
 	/* list terminator */
 	{{NULL}}
 };
@@ -1859,7 +1868,9 @@ default_reloptions(Datum reloptions, bool validate, relopt_kind kind)
 		{"vacuum_index_cleanup", RELOPT_TYPE_BOOL,
 		offsetof(StdRdOptions, vacuum_index_cleanup)},
 		{"vacuum_truncate", RELOPT_TYPE_BOOL,
-		offsetof(StdRdOptions, vacuum_truncate)}
+		offsetof(StdRdOptions, vacuum_truncate)},
+		{"parallel_dml_enabled", RELOPT_TYPE_BOOL,
+		offsetof(StdRdOptions, parallel_dml_enabled)}
 	};
 
 	return (bytea *) build_reloptions(reloptions, validate, kind,
@@ -1961,13 +1972,19 @@ build_local_reloptions(local_relopts *relopts, Datum options, bool validate)
 bytea *
 partitioned_table_reloptions(Datum reloptions, bool validate)
 {
+	static const relopt_parse_elt tab[] = {
+		{"parallel_dml_enabled", RELOPT_TYPE_BOOL,
+		offsetof(StdRdOptions, parallel_dml_enabled)}
+	};
+
 	/*
-	 * There are no options for partitioned tables yet, but this is able to do
-	 * some validation.
+	 * The only option for partitioned tables works for heap too,
+	 * so we temporarily store it in the same struct as heap.
 	 */
 	return (bytea *) build_reloptions(reloptions, validate,
 									  RELOPT_KIND_PARTITIONED,
-									  0, NULL, 0);
+									  sizeof(StdRdOptions),
+									  tab, lengthof(tab));
 }
 
 /*
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index f6ac972..cac11a9 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -339,8 +339,7 @@ standard_planner(Query *parse, const char *query_string, int cursorOptions,
 	if ((cursorOptions & CURSOR_OPT_PARALLEL_OK) != 0 &&
 		IsUnderPostmaster &&
 		(parse->commandType == CMD_SELECT ||
-		(enable_parallel_dml &&
-		IsModifySupportedInParallelMode(parse->commandType))) &&
+		is_parallel_possible_for_modify(parse)) &&
 		!parse->hasModifyingCTE &&
 		max_parallel_workers_per_gather > 0 &&
 		!IsParallelWorker())
diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index 02a7e05..8a119a2 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -1169,23 +1169,32 @@ rel_max_parallel_hazard_for_modify(Relation rel,
 }
 
 /*
- * max_parallel_hazard_for_modify
+ * is_parallel_possible_for_modify
  *
- * Determines the worst parallel-mode hazard level for the specified
- * table-modification statement, based on the statement attributes and
- * target table. An initial max parallel hazard level may optionally be
- * supplied. The search returns the earliest in the following list:
- * PROPARALLEL_UNSAFE, PROPARALLEL_RESTRICTED, PROPARALLEL_SAFE
+ * Check if current table-modification statement is able to use
+ * parallel mode. The following case are currently not able to
+ * use parallel mode :
+ *
+ *  1) enable_parallel_dml is off
+ *  2) UPDATE or DELETE command
+ *  3) INSERT...ON CONFLICT...DO UPDATE
+ *  4) INSERT without SELECT part
+ *  5) the reloption parallel_dml_enabled is not set for the target table
+ *
+ * (Note: we do not do parallel-safety check here, we do only the
+ * advance check to see whether it's necessary to do futher
+ * safety check)
  */
-char
-max_parallel_hazard_for_modify(Query *parse, char initial_max_parallel_hazard)
+bool is_parallel_possible_for_modify(Query *parse)
 {
 	RangeTblEntry *rte;
 	ListCell *lc;
 	bool hasSubQueryOnRelation;
-	max_parallel_hazard_context context;
 	Relation rel;
 
+	if (!enable_parallel_dml ||
+		!IsModifySupportedInParallelMode(parse->commandType))
+		return false;
 
 	/*
 	 * UPDATE is not currently supported in parallel-mode, so prohibit
@@ -1197,7 +1206,7 @@ max_parallel_hazard_for_modify(Query *parse, char initial_max_parallel_hazard)
 	 * combo-cid and it needs to be propagated to the workers.
 	 */
 	if (parse->onConflict != NULL && parse->onConflict->action == ONCONFLICT_UPDATE)
-		return PROPARALLEL_UNSAFE;
+		return false;
 
 	/*
 	 * If there is no underlying query on a relation, a parallel
@@ -1226,7 +1235,44 @@ max_parallel_hazard_for_modify(Query *parse, char initial_max_parallel_hazard)
 		}
 	}
 	if (!hasSubQueryOnRelation)
-		return PROPARALLEL_UNSAFE;
+		return false;
+
+	rte = rt_fetch(parse->resultRelation, parse->rtable);
+	rel = table_open(rte->relid, NoLock);
+
+	/*
+	 * Check if parallel_dml_enabled is enabled for the target table,
+	 * if not, skip the safety checks and return PARALLEL_UNSAFE.
+	 *
+	 * (Note: if target table is partitioned, we just check the
+	 * option of parent table and ignore the option values for its child)
+	 */
+	if (!RelationGetParallelDML(rel, true))
+	{
+		table_close(rel, NoLock);
+		return false;
+	}
+
+	table_close(rel, NoLock);
+
+	return true;
+}
+
+/*
+ * max_parallel_hazard_for_modify
+ *
+ * Determines the worst parallel-mode hazard level for the specified
+ * table-modification statement, based on the statement attributes and
+ * target table. An initial max parallel hazard level may optionally be
+ * supplied. The search returns the earliest in the following list:
+ * PROPARALLEL_UNSAFE, PROPARALLEL_RESTRICTED, PROPARALLEL_SAFE
+ */
+char
+max_parallel_hazard_for_modify(Query *parse, char initial_max_parallel_hazard)
+{
+	RangeTblEntry *rte;
+	max_parallel_hazard_context context;
+	Relation rel;
 
 	/*
 	 * Setup the context used in finding the max parallel-mode hazard.
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 17f7265..89b5fe5 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1110,6 +1110,7 @@ static const char *const table_storage_parameters[] = {
 	"autovacuum_vacuum_threshold",
 	"fillfactor",
 	"log_autovacuum_min_duration",
+	"parallel_dml_enabled",
 	"parallel_workers",
 	"toast.autovacuum_enabled",
 	"toast.autovacuum_freeze_max_age",
diff --git a/src/include/optimizer/clauses.h b/src/include/optimizer/clauses.h
index 3cdddbf..ccbc35a 100644
--- a/src/include/optimizer/clauses.h
+++ b/src/include/optimizer/clauses.h
@@ -53,5 +53,6 @@ extern void CommuteOpExpr(OpExpr *clause);
 extern Query *inline_set_returning_function(PlannerInfo *root,
 											RangeTblEntry *rte);
 extern char max_parallel_hazard_for_modify(Query *parse, char initial_max_parallel_hazard);
+extern bool is_parallel_possible_for_modify(Query *parse);
 
 #endif							/* CLAUSES_H */
diff --git a/src/include/utils/rel.h b/src/include/utils/rel.h
index 2a41a00..fd1f409 100644
--- a/src/include/utils/rel.h
+++ b/src/include/utils/rel.h
@@ -307,6 +307,7 @@ typedef struct StdRdOptions
 	int			parallel_workers;	/* max number of parallel workers */
 	bool		vacuum_index_cleanup;	/* enables index vacuuming and cleanup */
 	bool		vacuum_truncate;	/* enables vacuum to truncate a relation */
+	bool		parallel_dml_enabled;	/* enable parallel table-modification */
 } StdRdOptions;
 
 #define HEAP_MIN_FILLFACTOR			10
@@ -362,6 +363,15 @@ typedef struct StdRdOptions
 	((relation)->rd_options ? \
 	 ((StdRdOptions *) (relation)->rd_options)->parallel_workers : (defaultpw))
 
+/*
+ * RelationGetParallelDML
+ *		Returns the relation's parallel_dml_enabled reloption setting.
+ *		Note multiple eval of argument!
+ */
+#define RelationGetParallelDML(relation, defaultpd) \
+	((relation)->rd_options ? \
+	 ((StdRdOptions *) (relation)->rd_options)->parallel_dml_enabled : (defaultpd))
+
 /* ViewOptions->check_option values */
 typedef enum ViewOptCheckOption
 {
-- 
2.7.2.windows.1

From 15729ba70e900450f60286721c00b5a209c8f7f1 Mon Sep 17 00:00:00 2001
From: houzj <houzj.fnst@cn.fujitsu.com>
Date: Mon, 1 Feb 2021 11:24:43 +0800
Subject: [PATCH 2/2] reloption parallel_dml_enabled test and doc

Test and document for reloption parallel_dml_enabled

---
 doc/src/sgml/ref/alter_table.sgml             |  2 +-
 doc/src/sgml/ref/create_table.sgml            | 22 ++++++++++++++++++++++
 src/test/regress/expected/insert_parallel.out | 24 +++++++++++++++++++++---
 src/test/regress/sql/insert_parallel.sql      | 19 ++++++++++++++++---
 4 files changed, 60 insertions(+), 7 deletions(-)

diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index c25ef5a..ecb0470 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -722,7 +722,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
      <para>
       <literal>SHARE UPDATE EXCLUSIVE</literal> lock will be taken for
       fillfactor, toast and autovacuum storage parameters, as well as the
-      planner parameter <varname>parallel_workers</varname>.
+      planner parameter <varname>parallel_workers</varname> and <varname>parallel_dml_enabled</varname>.
      </para>
     </listitem>
    </varlistentry>
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 569f4c9..4380184 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -1408,6 +1408,28 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
     </listitem>
    </varlistentry>
 
+   <varlistentry id="reloption-parallel-dml-enabled" xreflabel="parallel_dml_enabled">
+    <term><literal>parallel_dml_enabled</literal> (<type>boolean</type>)
+     <indexterm>
+     <primary><varname>parallel_dml_enabled</varname> storage parameter</primary>
+    </indexterm>
+    </term>
+    <listitem>
+     <para>
+      Enables or disables the parallel table-modification on a particular table.
+      if set it to true, planner will check parallel safety of the target table
+      to determine if it’s safe to execute parallel. (Note: If target is
+      partitioned table, it will check all its partitions and indexes, then
+      the check overhead can become prohibitively high if the number of
+      partitions is large, Especially when the parallelism is not chosen in
+      the end.) To avoid the overhead, the default is <literal>false</literal>.
+      Parallel_dml_enabled will not work if <xref linkend="guc-enable-parallel-dml"/>
+      is not set. If target table is parent table(partitioned), only the option
+      of the parent table works, the option values of its child will be ignored.
+     </para>
+    </listitem>
+   </varlistentry>
+
    <varlistentry id="reloption-autovacuum-enabled" xreflabel="autovacuum_enabled">
     <term><literal>autovacuum_enabled</literal>, <literal>toast.autovacuum_enabled</literal> (<type>boolean</type>)
     <indexterm>
diff --git a/src/test/regress/expected/insert_parallel.out b/src/test/regress/expected/insert_parallel.out
index f98d1ae..641a5cc 100644
--- a/src/test/regress/expected/insert_parallel.out
+++ b/src/test/regress/expected/insert_parallel.out
@@ -70,13 +70,13 @@ set max_parallel_workers_per_gather=4;
 create table para_insert_p1 (
 	unique1		int4	PRIMARY KEY,
 	stringu1	name
-);
+) with (parallel_dml_enabled = off);
 create table para_insert_f1 (
 	unique1		int4	REFERENCES para_insert_p1(unique1),
 	stringu1	name
 );
 --
--- Test INSERT with underlying query when enable_parallel_dml=off.
+-- Test INSERT with underlying query when enable_parallel_dml=off and reloption.parallel_dml_enabled=off.
 -- (should create plan with serial INSERT + SELECT)
 --
 explain(costs off) insert into para_insert_p1 select unique1, stringu1 from tenk1;
@@ -88,10 +88,28 @@ explain(costs off) insert into para_insert_p1 select unique1, stringu1 from tenk
 
 insert into para_insert_p1 select unique1, stringu1 from tenk1;
 --
--- Enable parallel dml
+-- Enable guc option enable_parallel_dml
 --
 set enable_parallel_dml = on;
 --
+-- Test INSERT with underlying query when enable_parallel_dml=on and reloption.parallel_dml_enabled=off.
+-- (should create plan with serial INSERT + SELECT)
+--
+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;
+        QUERY PLAN        
+--------------------------
+ Insert on para_insert_p1
+   ->  Seq Scan on tenk1
+(2 rows)
+
+insert into para_insert_p1 select unique1, stringu1 from tenk1;
+--
+-- Enable reloption parallel_dml_enabled
+--
+alter table para_insert_p1 set (parallel_dml_enabled = on);
+--
 -- Test INSERT with underlying query.
 -- (should create plan with parallel INSERT+SELECT, Gather parent node)
 --
diff --git a/src/test/regress/sql/insert_parallel.sql b/src/test/regress/sql/insert_parallel.sql
index 5317313..4c6b352 100644
--- a/src/test/regress/sql/insert_parallel.sql
+++ b/src/test/regress/sql/insert_parallel.sql
@@ -87,7 +87,7 @@ set max_parallel_workers_per_gather=4;
 create table para_insert_p1 (
 	unique1		int4	PRIMARY KEY,
 	stringu1	name
-);
+) with (parallel_dml_enabled = off);
 
 create table para_insert_f1 (
 	unique1		int4	REFERENCES para_insert_p1(unique1),
@@ -95,18 +95,31 @@ create table para_insert_f1 (
 );
 
 --
--- Test INSERT with underlying query when enable_parallel_dml=off.
+-- Test INSERT with underlying query when enable_parallel_dml=off and reloption.parallel_dml_enabled=off.
 -- (should create plan with serial INSERT + SELECT)
 --
 explain(costs off) insert into para_insert_p1 select unique1, stringu1 from tenk1;
 insert into para_insert_p1 select unique1, stringu1 from tenk1;
 
 --
--- Enable parallel dml
+-- Enable guc option enable_parallel_dml
 --
 set enable_parallel_dml = on;
 
 --
+-- Test INSERT with underlying query when enable_parallel_dml=on and reloption.parallel_dml_enabled=off.
+-- (should create plan with serial INSERT + SELECT)
+--
+truncate para_insert_p1 cascade;
+explain(costs off) insert into para_insert_p1 select unique1, stringu1 from tenk1;
+insert into para_insert_p1 select unique1, stringu1 from tenk1;
+
+--
+-- Enable reloption parallel_dml_enabled
+--
+alter table para_insert_p1 set (parallel_dml_enabled = on);
+
+--
 -- Test INSERT with underlying query.
 -- (should create plan with parallel INSERT+SELECT, Gather parent node)
 --
-- 
2.7.2.windows.1

From c23a9f029043e54fc7117e870f5e829c1d13adaa Mon Sep 17 00:00:00 2001
From: houzj <houzj.fnst@cn.fujitsu.com>
Date: Fri, 29 Jan 2021 10:30:01 +0800
Subject: [PATCH 1/2] guc option enable_parallel_dml src

add new guc option enable_parallel_dml(boolean)

The current implementation of parallel INSERT SELECT incurs non-negligible
overhead for parallel-safety check even when the parallelism is not chosen
in the end. To solve this, add enable_parallel_dml option let user decide whether
to use parallelizing DML.

The default is false.

---
 src/backend/optimizer/path/costsize.c         |  2 ++
 src/backend/optimizer/plan/planner.c          |  3 ++-
 src/backend/utils/misc/guc.c                  | 11 +++++++++++
 src/backend/utils/misc/postgresql.conf.sample |  1 +
 src/include/optimizer/cost.h                  |  1 +
 5 files changed, 17 insertions(+), 1 deletion(-)

diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c
index f7c13be..778f71b 100644
--- a/src/backend/optimizer/path/costsize.c
+++ b/src/backend/optimizer/path/costsize.c
@@ -129,6 +129,8 @@ Cost		disable_cost = 1.0e10;
 
 int			max_parallel_workers_per_gather = 2;
 
+bool		enable_parallel_dml = false;
+
 bool		enable_seqscan = true;
 bool		enable_indexscan = true;
 bool		enable_indexonlyscan = true;
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index 92f75f3..f6ac972 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -339,7 +339,8 @@ standard_planner(Query *parse, const char *query_string, int cursorOptions,
 	if ((cursorOptions & CURSOR_OPT_PARALLEL_OK) != 0 &&
 		IsUnderPostmaster &&
 		(parse->commandType == CMD_SELECT ||
-		 IsModifySupportedInParallelMode(parse->commandType)) &&
+		(enable_parallel_dml &&
+		IsModifySupportedInParallelMode(parse->commandType))) &&
 		!parse->hasModifyingCTE &&
 		max_parallel_workers_per_gather > 0 &&
 		!IsParallelWorker())
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index 17579ee..aaa788e 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -2048,6 +2048,17 @@ static struct config_bool ConfigureNamesBool[] =
 		NULL, NULL, NULL
 	},
 
+	{
+		{"enable_parallel_dml", PGC_USERSET, QUERY_TUNING_METHOD,
+			gettext_noop("Enables the planner's use of parallel plans for table-modification command."),
+			NULL,
+			GUC_EXPLAIN
+		},
+		&enable_parallel_dml,
+		false,
+		NULL, NULL, NULL
+	},
+
 	/* End-of-list marker */
 	{
 		{NULL, 0, 0, NULL, NULL}, NULL, false, NULL, NULL, NULL
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index 8930a94..8d897ba 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -370,6 +370,7 @@
 #enable_partitionwise_aggregate = off
 #enable_parallel_hash = on
 #enable_partition_pruning = on
+#enable_parallel_dml = off
 
 # - Planner Cost Constants -
 
diff --git a/src/include/optimizer/cost.h b/src/include/optimizer/cost.h
index 9f15fcb..4af0beb 100644
--- a/src/include/optimizer/cost.h
+++ b/src/include/optimizer/cost.h
@@ -47,6 +47,7 @@ typedef enum
 /* parameter variables and flags (see also optimizer.h) */
 extern PGDLLIMPORT Cost disable_cost;
 extern PGDLLIMPORT int max_parallel_workers_per_gather;
+extern PGDLLIMPORT bool enable_parallel_dml;
 extern PGDLLIMPORT bool enable_seqscan;
 extern PGDLLIMPORT bool enable_indexscan;
 extern PGDLLIMPORT bool enable_indexonlyscan;
-- 
2.7.2.windows.1

From eb4ea2423d3ab63fd87767afcfe7560c7e2e39ad Mon Sep 17 00:00:00 2001
From: houzj <houzj.fnst@cn.fujitsu.com>
Date: Fri, 29 Jan 2021 10:30:18 +0800
Subject: [PATCH 2/2] guc option enable_parallel_dml doc and test

Test and document for enable_parallel_dml

---
 doc/src/sgml/config.sgml                      | 19 +++++++++++++++++++
 src/test/regress/expected/insert_parallel.out | 18 ++++++++++++++++++
 src/test/regress/expected/sysviews.out        |  3 ++-
 src/test/regress/sql/insert_parallel.sql      | 12 ++++++++++++
 4 files changed, 51 insertions(+), 1 deletion(-)

diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 82864bb..c895999 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -5024,6 +5024,25 @@ ANY <replaceable class="parameter">num_sync</replaceable> ( <replaceable class="
       </listitem>
      </varlistentry>
 
+     <varlistentry id="guc-enable-parallel-dml" xreflabel="enable_parallel_dml">
+      <term><varname>enable_parallel_dml</varname> (<type>boolean</type>)
+      <indexterm>
+       <primary><varname>enable_parallel_dml</varname> configuration parameter</primary>
+      </indexterm>
+      </term>
+      <listitem>
+       <para>
+        Enables or disables the parallel mode for table-modification command.
+        if set it to on, planner will check parallel safety of the target table
+        to determine if it’s safe to execute parallel. (Note: If target is
+        partitioned table, it will check all its partitions and indexes, then
+        the check overhead can become prohibitively high if the number of
+        partitions is large, Especially when the parallelism is not chosen in
+        the end.) To avoid the overhead, The default is <literal>off</literal>.
+       </para>
+      </listitem>
+     </varlistentry>
+
      </variablelist>
      </sect2>
      <sect2 id="runtime-config-query-constants">
diff --git a/src/test/regress/expected/insert_parallel.out b/src/test/regress/expected/insert_parallel.out
index 3b922a2..f98d1ae 100644
--- a/src/test/regress/expected/insert_parallel.out
+++ b/src/test/regress/expected/insert_parallel.out
@@ -76,9 +76,27 @@ create table para_insert_f1 (
 	stringu1	name
 );
 --
+-- Test INSERT with underlying query when enable_parallel_dml=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)
+
+insert into para_insert_p1 select unique1, stringu1 from tenk1;
+--
+-- Enable parallel dml
+--
+set enable_parallel_dml = on;
+--
 -- Test INSERT with underlying query.
 -- (should create plan with parallel INSERT+SELECT, Gather 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;
                QUERY PLAN               
 ----------------------------------------
diff --git a/src/test/regress/expected/sysviews.out b/src/test/regress/expected/sysviews.out
index 81bdacf..3f11216 100644
--- a/src/test/regress/expected/sysviews.out
+++ b/src/test/regress/expected/sysviews.out
@@ -99,6 +99,7 @@ select name, setting from pg_settings where name like 'enable%';
  enable_mergejoin               | on
  enable_nestloop                | on
  enable_parallel_append         | on
+ enable_parallel_dml            | off
  enable_parallel_hash           | on
  enable_partition_pruning       | on
  enable_partitionwise_aggregate | off
@@ -106,7 +107,7 @@ select name, setting from pg_settings where name like 'enable%';
  enable_seqscan                 | on
  enable_sort                    | on
  enable_tidscan                 | on
-(18 rows)
+(19 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/sql/insert_parallel.sql b/src/test/regress/sql/insert_parallel.sql
index 34a191f..5317313 100644
--- a/src/test/regress/sql/insert_parallel.sql
+++ b/src/test/regress/sql/insert_parallel.sql
@@ -94,11 +94,23 @@ create table para_insert_f1 (
 	stringu1	name
 );
 
+--
+-- Test INSERT with underlying query when enable_parallel_dml=off.
+-- (should create plan with serial INSERT + SELECT)
+--
+explain(costs off) insert into para_insert_p1 select unique1, stringu1 from tenk1;
+insert into para_insert_p1 select unique1, stringu1 from tenk1;
+
+--
+-- Enable parallel dml
+--
+set enable_parallel_dml = on;
 
 --
 -- Test INSERT with underlying query.
 -- (should create plan with parallel INSERT+SELECT, Gather parent node)
 --
+truncate para_insert_p1 cascade;
 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
-- 
2.7.2.windows.1

From b34e392905c0ec4b1835f05d2af8b297390ce325 Mon Sep 17 00:00:00 2001
From: Greg Nancarrow <gregn4422@gmail.com>
Date: Tue, 2 Feb 2021 10:03:45 +1100
Subject: [PATCH v3 2/2] reloption parallel_dml_enabled test and doc

Test and documentation updates for reloption parallel_dml_enabled.
---
 doc/src/sgml/ref/alter_table.sgml             |  2 +-
 doc/src/sgml/ref/create_table.sgml            | 27 +++++++++++++++++++
 src/test/regress/expected/insert_parallel.out | 24 ++++++++++++++---
 src/test/regress/sql/insert_parallel.sql      | 19 ++++++++++---
 4 files changed, 65 insertions(+), 7 deletions(-)

diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index c25ef5abd6..ecb047021d 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -722,7 +722,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
      <para>
       <literal>SHARE UPDATE EXCLUSIVE</literal> lock will be taken for
       fillfactor, toast and autovacuum storage parameters, as well as the
-      planner parameter <varname>parallel_workers</varname>.
+      planner parameter <varname>parallel_workers</varname> and <varname>parallel_dml_enabled</varname>.
      </para>
     </listitem>
    </varlistentry>
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 569f4c9da7..420cbcfd7c 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -1408,6 +1408,33 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
     </listitem>
    </varlistentry>
 
+   <varlistentry id="reloption-parallel-dml-enabled" xreflabel="parallel_dml_enabled">
+    <term><literal>parallel_dml_enabled</literal> (<type>boolean</type>)
+     <indexterm>
+     <primary><varname>parallel_dml_enabled</varname> storage parameter</primary>
+    </indexterm>
+    </term>
+    <listitem>
+     <para>
+      Enables or disables the query planner's use of parallel DML for
+      this table. When enabled (and provided that
+      <xref linkend="guc-enable-parallel-dml"/> is also <literal>true</literal>),
+      the planner performs additional parallel-safety checks on the table's
+      attributes and indexes, in order to determine if it's safe to use a
+      parallel plan for table-modification. The default is
+      <literal>true</literal>.
+      In cases such as when the table has a large number of partitions, and
+      particularly also when that table uses a parallel-unsafe feature that
+      prevents parallelism, the overhead of these checks may become prohibitively
+      high. To address this potential overhead in these cases, this option may be
+      used to disable the use of parallel DML for this table.
+      Note that if the target table of the parallel DML is partitioned, the
+      <literal>parallel_dml_enabled</literal> option values of the partitions are
+      ignored.
+     </para>
+    </listitem>
+   </varlistentry>
+
    <varlistentry id="reloption-autovacuum-enabled" xreflabel="autovacuum_enabled">
     <term><literal>autovacuum_enabled</literal>, <literal>toast.autovacuum_enabled</literal> (<type>boolean</type>)
     <indexterm>
diff --git a/src/test/regress/expected/insert_parallel.out b/src/test/regress/expected/insert_parallel.out
index f98d1aec7f..641a5cc0ca 100644
--- a/src/test/regress/expected/insert_parallel.out
+++ b/src/test/regress/expected/insert_parallel.out
@@ -70,13 +70,13 @@ set max_parallel_workers_per_gather=4;
 create table para_insert_p1 (
 	unique1		int4	PRIMARY KEY,
 	stringu1	name
-);
+) with (parallel_dml_enabled = off);
 create table para_insert_f1 (
 	unique1		int4	REFERENCES para_insert_p1(unique1),
 	stringu1	name
 );
 --
--- Test INSERT with underlying query when enable_parallel_dml=off.
+-- Test INSERT with underlying query when enable_parallel_dml=off and reloption.parallel_dml_enabled=off.
 -- (should create plan with serial INSERT + SELECT)
 --
 explain(costs off) insert into para_insert_p1 select unique1, stringu1 from tenk1;
@@ -88,10 +88,28 @@ explain(costs off) insert into para_insert_p1 select unique1, stringu1 from tenk
 
 insert into para_insert_p1 select unique1, stringu1 from tenk1;
 --
--- Enable parallel dml
+-- Enable guc option enable_parallel_dml
 --
 set enable_parallel_dml = on;
 --
+-- Test INSERT with underlying query when enable_parallel_dml=on and reloption.parallel_dml_enabled=off.
+-- (should create plan with serial INSERT + SELECT)
+--
+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;
+        QUERY PLAN        
+--------------------------
+ Insert on para_insert_p1
+   ->  Seq Scan on tenk1
+(2 rows)
+
+insert into para_insert_p1 select unique1, stringu1 from tenk1;
+--
+-- Enable reloption parallel_dml_enabled
+--
+alter table para_insert_p1 set (parallel_dml_enabled = on);
+--
 -- Test INSERT with underlying query.
 -- (should create plan with parallel INSERT+SELECT, Gather parent node)
 --
diff --git a/src/test/regress/sql/insert_parallel.sql b/src/test/regress/sql/insert_parallel.sql
index 531731378e..4c6b352986 100644
--- a/src/test/regress/sql/insert_parallel.sql
+++ b/src/test/regress/sql/insert_parallel.sql
@@ -87,7 +87,7 @@ set max_parallel_workers_per_gather=4;
 create table para_insert_p1 (
 	unique1		int4	PRIMARY KEY,
 	stringu1	name
-);
+) with (parallel_dml_enabled = off);
 
 create table para_insert_f1 (
 	unique1		int4	REFERENCES para_insert_p1(unique1),
@@ -95,17 +95,30 @@ create table para_insert_f1 (
 );
 
 --
--- Test INSERT with underlying query when enable_parallel_dml=off.
+-- Test INSERT with underlying query when enable_parallel_dml=off and reloption.parallel_dml_enabled=off.
 -- (should create plan with serial INSERT + SELECT)
 --
 explain(costs off) insert into para_insert_p1 select unique1, stringu1 from tenk1;
 insert into para_insert_p1 select unique1, stringu1 from tenk1;
 
 --
--- Enable parallel dml
+-- Enable guc option enable_parallel_dml
 --
 set enable_parallel_dml = on;
 
+--
+-- Test INSERT with underlying query when enable_parallel_dml=on and reloption.parallel_dml_enabled=off.
+-- (should create plan with serial INSERT + SELECT)
+--
+truncate para_insert_p1 cascade;
+explain(costs off) insert into para_insert_p1 select unique1, stringu1 from tenk1;
+insert into para_insert_p1 select unique1, stringu1 from tenk1;
+
+--
+-- Enable reloption parallel_dml_enabled
+--
+alter table para_insert_p1 set (parallel_dml_enabled = on);
+
 --
 -- Test INSERT with underlying query.
 -- (should create plan with parallel INSERT+SELECT, Gather parent node)
-- 
2.27.0

From 76a811086b11eb2d3d0b1131bbc94f5619e95c63 Mon Sep 17 00:00:00 2001
From: Greg Nancarrow <gregn4422@gmail.com>
Date: Mon, 1 Feb 2021 14:54:26 +1100
Subject: [PATCH v3 1/2] Add new GUC option: enable_parallel_dml (boolean)

The current implementation of parallel "INSERT ... SELECT ..." may incur
non-negligible overhead in the additional parallel-safety checks that it
performs, even when, in the end, those checks determine that parallelism
can't be used. This is normally only ever a problem for large complex tables,
particularly in the case of when the target table has a large number of
partitions.

To address this potential isse, a new GUC option "enable_parallel_dml" is
added, to allow parallel DML to be enabled/disabled.

The default is false.
---
 src/backend/optimizer/path/costsize.c         |  2 ++
 src/backend/optimizer/plan/planner.c          |  3 ++-
 src/backend/utils/misc/guc.c                  | 11 +++++++++++
 src/backend/utils/misc/postgresql.conf.sample |  1 +
 src/include/optimizer/cost.h                  |  1 +
 5 files changed, 17 insertions(+), 1 deletion(-)

diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c
index f7c13bed7a..778f71b98a 100644
--- a/src/backend/optimizer/path/costsize.c
+++ b/src/backend/optimizer/path/costsize.c
@@ -129,6 +129,8 @@ Cost		disable_cost = 1.0e10;
 
 int			max_parallel_workers_per_gather = 2;
 
+bool		enable_parallel_dml = false;
+
 bool		enable_seqscan = true;
 bool		enable_indexscan = true;
 bool		enable_indexonlyscan = true;
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index 6efce22a1a..3fb108d120 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -339,7 +339,8 @@ standard_planner(Query *parse, const char *query_string, int cursorOptions,
 	if ((cursorOptions & CURSOR_OPT_PARALLEL_OK) != 0 &&
 		IsUnderPostmaster &&
 		(parse->commandType == CMD_SELECT ||
-		 IsModifySupportedInParallelMode(parse->commandType)) &&
+		(enable_parallel_dml &&
+		IsModifySupportedInParallelMode(parse->commandType))) &&
 		!parse->hasModifyingCTE &&
 		max_parallel_workers_per_gather > 0 &&
 		!IsParallelWorker())
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index eafdb1118e..6fdb991a26 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -2048,6 +2048,17 @@ static struct config_bool ConfigureNamesBool[] =
 		NULL, NULL, NULL
 	},
 
+	{
+		{"enable_parallel_dml", PGC_USERSET, QUERY_TUNING_METHOD,
+			gettext_noop("Enables the planner's use of parallel plans for table-modification commands."),
+			NULL,
+			GUC_EXPLAIN
+		},
+		&enable_parallel_dml,
+		false,
+		NULL, NULL, NULL
+	},
+
 	/* End-of-list marker */
 	{
 		{NULL, 0, 0, NULL, NULL}, NULL, false, NULL, NULL, NULL
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index bd57e917e1..1f699b0f2d 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -370,6 +370,7 @@
 #enable_partitionwise_aggregate = off
 #enable_parallel_hash = on
 #enable_partition_pruning = on
+#enable_parallel_dml = off
 
 # - Planner Cost Constants -
 
diff --git a/src/include/optimizer/cost.h b/src/include/optimizer/cost.h
index 9f15fcb240..4af0beb9c8 100644
--- a/src/include/optimizer/cost.h
+++ b/src/include/optimizer/cost.h
@@ -47,6 +47,7 @@ typedef enum
 /* parameter variables and flags (see also optimizer.h) */
 extern PGDLLIMPORT Cost disable_cost;
 extern PGDLLIMPORT int max_parallel_workers_per_gather;
+extern PGDLLIMPORT bool enable_parallel_dml;
 extern PGDLLIMPORT bool enable_seqscan;
 extern PGDLLIMPORT bool enable_indexscan;
 extern PGDLLIMPORT bool enable_indexonlyscan;
-- 
2.27.0

From 47aeb15eca62ec530231a6ddc63d5ab9fff1de76 Mon Sep 17 00:00:00 2001
From: Greg Nancarrow <gregn4422@gmail.com>
Date: Tue, 2 Feb 2021 10:30:35 +1100
Subject: [PATCH v3 2/2] Test and documentation updates for the GUC option
 "enable_parallel_dml".

---
 doc/src/sgml/config.sgml                      | 23 +++++++++++++++++++
 src/test/regress/expected/insert_parallel.out | 18 +++++++++++++++
 src/test/regress/expected/sysviews.out        |  3 ++-
 src/test/regress/sql/insert_parallel.sql      | 12 ++++++++++
 4 files changed, 55 insertions(+), 1 deletion(-)

diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index e17cdcc816..7102265dde 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -5026,6 +5026,29 @@ ANY <replaceable class="parameter">num_sync</replaceable> ( <replaceable class="
       </listitem>
      </varlistentry>
 
+     <varlistentry id="guc-enable-parallel-dml" xreflabel="enable_parallel_dml">
+      <term><varname>enable_parallel_dml</varname> (<type>boolean</type>)
+      <indexterm>
+       <primary><varname>enable_parallel_dml</varname> configuration parameter</primary>
+      </indexterm>
+      </term>
+      <listitem>
+       <para>
+        Enables or disables the query planner's use of parallel plans for
+        table-modification commands. The default is <literal>off</literal>.
+        When enabled, the planner performs additional parallel-safety checks
+        on the target table's attributes and indexes, in order to determine
+        if it's safe to use a parallel plan for table-modification. In cases
+        such as when the target table has a large number of partitions, and
+        particularly also when that table uses something parallel-unsafe that
+        prevents parallelism, the overhead of these checks may become
+        prohibitively high. To address this potential overhead in these cases,
+        this option may be used to disable the use of parallel plans for
+        table-modification.
+       </para>
+      </listitem>
+     </varlistentry>
+
      </variablelist>
      </sect2>
      <sect2 id="runtime-config-query-constants">
diff --git a/src/test/regress/expected/insert_parallel.out b/src/test/regress/expected/insert_parallel.out
index 3b922a2c3d..f98d1aec7f 100644
--- a/src/test/regress/expected/insert_parallel.out
+++ b/src/test/regress/expected/insert_parallel.out
@@ -76,9 +76,27 @@ create table para_insert_f1 (
 	stringu1	name
 );
 --
+-- Test INSERT with underlying query when enable_parallel_dml=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)
+
+insert into para_insert_p1 select unique1, stringu1 from tenk1;
+--
+-- Enable parallel dml
+--
+set enable_parallel_dml = on;
+--
 -- Test INSERT with underlying query.
 -- (should create plan with parallel INSERT+SELECT, Gather 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;
                QUERY PLAN               
 ----------------------------------------
diff --git a/src/test/regress/expected/sysviews.out b/src/test/regress/expected/sysviews.out
index 81bdacf59d..3f11216809 100644
--- a/src/test/regress/expected/sysviews.out
+++ b/src/test/regress/expected/sysviews.out
@@ -99,6 +99,7 @@ select name, setting from pg_settings where name like 'enable%';
  enable_mergejoin               | on
  enable_nestloop                | on
  enable_parallel_append         | on
+ enable_parallel_dml            | off
  enable_parallel_hash           | on
  enable_partition_pruning       | on
  enable_partitionwise_aggregate | off
@@ -106,7 +107,7 @@ select name, setting from pg_settings where name like 'enable%';
  enable_seqscan                 | on
  enable_sort                    | on
  enable_tidscan                 | on
-(18 rows)
+(19 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/sql/insert_parallel.sql b/src/test/regress/sql/insert_parallel.sql
index 34a191f7c5..531731378e 100644
--- a/src/test/regress/sql/insert_parallel.sql
+++ b/src/test/regress/sql/insert_parallel.sql
@@ -94,11 +94,23 @@ create table para_insert_f1 (
 	stringu1	name
 );
 
+--
+-- Test INSERT with underlying query when enable_parallel_dml=off.
+-- (should create plan with serial INSERT + SELECT)
+--
+explain(costs off) insert into para_insert_p1 select unique1, stringu1 from tenk1;
+insert into para_insert_p1 select unique1, stringu1 from tenk1;
+
+--
+-- Enable parallel dml
+--
+set enable_parallel_dml = on;
 
 --
 -- Test INSERT with underlying query.
 -- (should create plan with parallel INSERT+SELECT, Gather parent node)
 --
+truncate para_insert_p1 cascade;
 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
-- 
2.27.0

From 3afc62255af8b788d389ac166f20e26b06b3a1d6 Mon Sep 17 00:00:00 2001
From: Greg Nancarrow <gregn4422@gmail.com>
Date: Tue, 2 Feb 2021 00:07:24 +1100
Subject: [PATCH v3 1/2] reloption parallel_dml_enabled

Add new table option parallel_dml_enabled.

In addition to the GUC option, the user may want a mechanism for
specifying parallel dml with finer granularity, to specify the
use of parallel dml for specific tables.
The new table option parallel_dml_enabled allows this.

The default is true.
---
 src/backend/access/common/reloptions.c | 25 ++++++++--
 src/backend/optimizer/plan/planner.c   |  3 +-
 src/backend/optimizer/util/clauses.c   | 69 ++++++++++++++++++++++----
 src/bin/psql/tab-complete.c            |  1 +
 src/include/optimizer/clauses.h        |  1 +
 src/include/utils/rel.h                | 10 ++++
 6 files changed, 92 insertions(+), 17 deletions(-)

diff --git a/src/backend/access/common/reloptions.c b/src/backend/access/common/reloptions.c
index c687d3ee9e..3fb3cebc05 100644
--- a/src/backend/access/common/reloptions.c
+++ b/src/backend/access/common/reloptions.c
@@ -168,6 +168,15 @@ static relopt_bool boolRelOpts[] =
 		},
 		true
 	},
+	{
+		{
+			"parallel_dml_enabled",
+			"Enables \"parallel dml\" feature for this table",
+			RELOPT_KIND_HEAP | RELOPT_KIND_PARTITIONED,
+			ShareUpdateExclusiveLock
+		},
+		true
+	},
 	/* list terminator */
 	{{NULL}}
 };
@@ -1859,7 +1868,9 @@ default_reloptions(Datum reloptions, bool validate, relopt_kind kind)
 		{"vacuum_index_cleanup", RELOPT_TYPE_BOOL,
 		offsetof(StdRdOptions, vacuum_index_cleanup)},
 		{"vacuum_truncate", RELOPT_TYPE_BOOL,
-		offsetof(StdRdOptions, vacuum_truncate)}
+		offsetof(StdRdOptions, vacuum_truncate)},
+		{"parallel_dml_enabled", RELOPT_TYPE_BOOL,
+		offsetof(StdRdOptions, parallel_dml_enabled)}
 	};
 
 	return (bytea *) build_reloptions(reloptions, validate, kind,
@@ -1961,13 +1972,19 @@ build_local_reloptions(local_relopts *relopts, Datum options, bool validate)
 bytea *
 partitioned_table_reloptions(Datum reloptions, bool validate)
 {
+	static const relopt_parse_elt tab[] = {
+		{"parallel_dml_enabled", RELOPT_TYPE_BOOL,
+		offsetof(StdRdOptions, parallel_dml_enabled)}
+	};
+
 	/*
-	 * There are no options for partitioned tables yet, but this is able to do
-	 * some validation.
+	 * The only option for partitioned tables works for heap too,
+	 * so we temporarily store it in the same struct as heap.
 	 */
 	return (bytea *) build_reloptions(reloptions, validate,
 									  RELOPT_KIND_PARTITIONED,
-									  0, NULL, 0);
+									  sizeof(StdRdOptions),
+									  tab, lengthof(tab));
 }
 
 /*
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index 3fb108d120..fc6d5cc9d4 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -339,8 +339,7 @@ standard_planner(Query *parse, const char *query_string, int cursorOptions,
 	if ((cursorOptions & CURSOR_OPT_PARALLEL_OK) != 0 &&
 		IsUnderPostmaster &&
 		(parse->commandType == CMD_SELECT ||
-		(enable_parallel_dml &&
-		IsModifySupportedInParallelMode(parse->commandType))) &&
+		is_parallel_possible_for_modify(parse)) &&
 		!parse->hasModifyingCTE &&
 		max_parallel_workers_per_gather > 0 &&
 		!IsParallelWorker())
diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index a0c149bfa4..c078eccc11 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -1169,23 +1169,33 @@ rel_max_parallel_hazard_for_modify(Relation rel,
 }
 
 /*
- * max_parallel_hazard_for_modify
+ * is_parallel_possible_for_modify
  *
- * Determines the worst parallel-mode hazard level for the specified
- * table-modification statement, based on the statement attributes and
- * target table. An initial max parallel hazard level may optionally be
- * supplied. The search returns the earliest in the following list:
- * PROPARALLEL_UNSAFE, PROPARALLEL_RESTRICTED, PROPARALLEL_SAFE
+ * Check at a high-level if parallel mode is able to be used for the specified
+ * table-modification statement.
+ * It's not possible in the following cases:
+ *
+ *  1) enable_parallel_dml is off
+ *  2) UPDATE or DELETE command
+ *  3) INSERT...ON CONFLICT...DO UPDATE
+ *  4) INSERT without SELECT on a relation
+ *  5) the reloption parallel_dml_enabled is not set for the target table
+ *
+ * (Note: we don't do in-depth parallel-safety checks here, we do only the
+ * cheaper tests that can quickly exclude obvious cases for which
+ * parallelism isn't supported, to avoid having to do further parallel-safety
+ * checks for these)
  */
-char
-max_parallel_hazard_for_modify(Query *parse, char initial_max_parallel_hazard)
+bool is_parallel_possible_for_modify(Query *parse)
 {
 	RangeTblEntry *rte;
 	ListCell *lc;
 	bool hasSubQueryOnRelation;
-	max_parallel_hazard_context context;
 	Relation rel;
 
+	if (!enable_parallel_dml ||
+		!IsModifySupportedInParallelMode(parse->commandType))
+		return false;
 
 	/*
 	 * UPDATE is not currently supported in parallel-mode, so prohibit
@@ -1197,7 +1207,7 @@ max_parallel_hazard_for_modify(Query *parse, char initial_max_parallel_hazard)
 	 * combo-cid and it needs to be propagated to the workers.
 	 */
 	if (parse->onConflict != NULL && parse->onConflict->action == ONCONFLICT_UPDATE)
-		return PROPARALLEL_UNSAFE;
+		return false;
 
 	/*
 	 * If there is no underlying query on a relation, a parallel
@@ -1226,7 +1236,44 @@ max_parallel_hazard_for_modify(Query *parse, char initial_max_parallel_hazard)
 		}
 	}
 	if (!hasSubQueryOnRelation)
-		return PROPARALLEL_UNSAFE;
+		return false;
+
+	rte = rt_fetch(parse->resultRelation, parse->rtable);
+	rel = table_open(rte->relid, NoLock);
+
+	/*
+	 * Check if parallel_dml_enabled is enabled for the target table,
+	 * if not, skip the safety checks and return PARALLEL_UNSAFE.
+	 *
+	 * (Note: if the target table is partitioned, the parallel_dml_enabled
+	 * option setting of the partitions are ignored).
+	 */
+	if (!RelationGetParallelDML(rel, true))
+	{
+		table_close(rel, NoLock);
+		return false;
+	}
+
+	table_close(rel, NoLock);
+
+	return true;
+}
+
+/*
+ * max_parallel_hazard_for_modify
+ *
+ * Determines the worst parallel-mode hazard level for the specified
+ * table-modification statement, based on the statement attributes and
+ * target table. An initial max parallel hazard level may optionally be
+ * supplied. The search returns the earliest in the following list:
+ * PROPARALLEL_UNSAFE, PROPARALLEL_RESTRICTED, PROPARALLEL_SAFE
+ */
+char
+max_parallel_hazard_for_modify(Query *parse, char initial_max_parallel_hazard)
+{
+	RangeTblEntry *rte;
+	max_parallel_hazard_context context;
+	Relation rel;
 
 	/*
 	 * Setup the context used in finding the max parallel-mode hazard.
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 17f7265038..89b5fe5546 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1110,6 +1110,7 @@ static const char *const table_storage_parameters[] = {
 	"autovacuum_vacuum_threshold",
 	"fillfactor",
 	"log_autovacuum_min_duration",
+	"parallel_dml_enabled",
 	"parallel_workers",
 	"toast.autovacuum_enabled",
 	"toast.autovacuum_freeze_max_age",
diff --git a/src/include/optimizer/clauses.h b/src/include/optimizer/clauses.h
index 3cdddbfb35..ccbc35a82e 100644
--- a/src/include/optimizer/clauses.h
+++ b/src/include/optimizer/clauses.h
@@ -53,5 +53,6 @@ extern void CommuteOpExpr(OpExpr *clause);
 extern Query *inline_set_returning_function(PlannerInfo *root,
 											RangeTblEntry *rte);
 extern char max_parallel_hazard_for_modify(Query *parse, char initial_max_parallel_hazard);
+extern bool is_parallel_possible_for_modify(Query *parse);
 
 #endif							/* CLAUSES_H */
diff --git a/src/include/utils/rel.h b/src/include/utils/rel.h
index 2a41a00f29..fd1f409d1f 100644
--- a/src/include/utils/rel.h
+++ b/src/include/utils/rel.h
@@ -307,6 +307,7 @@ typedef struct StdRdOptions
 	int			parallel_workers;	/* max number of parallel workers */
 	bool		vacuum_index_cleanup;	/* enables index vacuuming and cleanup */
 	bool		vacuum_truncate;	/* enables vacuum to truncate a relation */
+	bool		parallel_dml_enabled;	/* enable parallel table-modification */
 } StdRdOptions;
 
 #define HEAP_MIN_FILLFACTOR			10
@@ -362,6 +363,15 @@ typedef struct StdRdOptions
 	((relation)->rd_options ? \
 	 ((StdRdOptions *) (relation)->rd_options)->parallel_workers : (defaultpw))
 
+/*
+ * RelationGetParallelDML
+ *		Returns the relation's parallel_dml_enabled reloption setting.
+ *		Note multiple eval of argument!
+ */
+#define RelationGetParallelDML(relation, defaultpd) \
+	((relation)->rd_options ? \
+	 ((StdRdOptions *) (relation)->rd_options)->parallel_dml_enabled : (defaultpd))
+
 /* ViewOptions->check_option values */
 typedef enum ViewOptCheckOption
 {
-- 
2.27.0

From 76a811086b11eb2d3d0b1131bbc94f5619e95c63 Mon Sep 17 00:00:00 2001
From: Greg Nancarrow <gregn4422@gmail.com>
Date: Mon, 1 Feb 2021 14:54:26 +1100
Subject: [PATCH v3 1/2] Add new GUC option: enable_parallel_dml (boolean)

The current implementation of parallel "INSERT ... SELECT ..." may incur
non-negligible overhead in the additional parallel-safety checks that it
performs, even when, in the end, those checks determine that parallelism
can't be used. This is normally only ever a problem for large complex tables,
particularly in the case of when the target table has a large number of
partitions.

To address this potential isse, a new GUC option "enable_parallel_dml" is
added, to allow parallel DML to be enabled/disabled.

The default is false.
---
 src/backend/optimizer/path/costsize.c         |  2 ++
 src/backend/optimizer/plan/planner.c          |  3 ++-
 src/backend/utils/misc/guc.c                  | 11 +++++++++++
 src/backend/utils/misc/postgresql.conf.sample |  1 +
 src/include/optimizer/cost.h                  |  1 +
 5 files changed, 17 insertions(+), 1 deletion(-)

diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c
index f7c13bed7a..778f71b98a 100644
--- a/src/backend/optimizer/path/costsize.c
+++ b/src/backend/optimizer/path/costsize.c
@@ -129,6 +129,8 @@ Cost		disable_cost = 1.0e10;
 
 int			max_parallel_workers_per_gather = 2;
 
+bool		enable_parallel_dml = false;
+
 bool		enable_seqscan = true;
 bool		enable_indexscan = true;
 bool		enable_indexonlyscan = true;
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index 6efce22a1a..3fb108d120 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -339,7 +339,8 @@ standard_planner(Query *parse, const char *query_string, int cursorOptions,
 	if ((cursorOptions & CURSOR_OPT_PARALLEL_OK) != 0 &&
 		IsUnderPostmaster &&
 		(parse->commandType == CMD_SELECT ||
-		 IsModifySupportedInParallelMode(parse->commandType)) &&
+		(enable_parallel_dml &&
+		IsModifySupportedInParallelMode(parse->commandType))) &&
 		!parse->hasModifyingCTE &&
 		max_parallel_workers_per_gather > 0 &&
 		!IsParallelWorker())
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index eafdb1118e..6fdb991a26 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -2048,6 +2048,17 @@ static struct config_bool ConfigureNamesBool[] =
 		NULL, NULL, NULL
 	},
 
+	{
+		{"enable_parallel_dml", PGC_USERSET, QUERY_TUNING_METHOD,
+			gettext_noop("Enables the planner's use of parallel plans for table-modification commands."),
+			NULL,
+			GUC_EXPLAIN
+		},
+		&enable_parallel_dml,
+		false,
+		NULL, NULL, NULL
+	},
+
 	/* End-of-list marker */
 	{
 		{NULL, 0, 0, NULL, NULL}, NULL, false, NULL, NULL, NULL
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index bd57e917e1..1f699b0f2d 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -370,6 +370,7 @@
 #enable_partitionwise_aggregate = off
 #enable_parallel_hash = on
 #enable_partition_pruning = on
+#enable_parallel_dml = off
 
 # - Planner Cost Constants -
 
diff --git a/src/include/optimizer/cost.h b/src/include/optimizer/cost.h
index 9f15fcb240..4af0beb9c8 100644
--- a/src/include/optimizer/cost.h
+++ b/src/include/optimizer/cost.h
@@ -47,6 +47,7 @@ typedef enum
 /* parameter variables and flags (see also optimizer.h) */
 extern PGDLLIMPORT Cost disable_cost;
 extern PGDLLIMPORT int max_parallel_workers_per_gather;
+extern PGDLLIMPORT bool enable_parallel_dml;
 extern PGDLLIMPORT bool enable_seqscan;
 extern PGDLLIMPORT bool enable_indexscan;
 extern PGDLLIMPORT bool enable_indexonlyscan;
-- 
2.27.0

From 47aeb15eca62ec530231a6ddc63d5ab9fff1de76 Mon Sep 17 00:00:00 2001
From: Greg Nancarrow <gregn4422@gmail.com>
Date: Tue, 2 Feb 2021 10:30:35 +1100
Subject: [PATCH v3 2/2] Test and documentation updates for the GUC option
 "enable_parallel_dml".

---
 doc/src/sgml/config.sgml                      | 23 +++++++++++++++++++
 src/test/regress/expected/insert_parallel.out | 18 +++++++++++++++
 src/test/regress/expected/sysviews.out        |  3 ++-
 src/test/regress/sql/insert_parallel.sql      | 12 ++++++++++
 4 files changed, 55 insertions(+), 1 deletion(-)

diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index e17cdcc816..7102265dde 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -5026,6 +5026,29 @@ ANY <replaceable class="parameter">num_sync</replaceable> ( <replaceable class="
       </listitem>
      </varlistentry>
 
+     <varlistentry id="guc-enable-parallel-dml" xreflabel="enable_parallel_dml">
+      <term><varname>enable_parallel_dml</varname> (<type>boolean</type>)
+      <indexterm>
+       <primary><varname>enable_parallel_dml</varname> configuration parameter</primary>
+      </indexterm>
+      </term>
+      <listitem>
+       <para>
+        Enables or disables the query planner's use of parallel plans for
+        table-modification commands. The default is <literal>off</literal>.
+        When enabled, the planner performs additional parallel-safety checks
+        on the target table's attributes and indexes, in order to determine
+        if it's safe to use a parallel plan for table-modification. In cases
+        such as when the target table has a large number of partitions, and
+        particularly also when that table uses something parallel-unsafe that
+        prevents parallelism, the overhead of these checks may become
+        prohibitively high. To address this potential overhead in these cases,
+        this option can be used to disable the use of parallel plans for
+        table-modification.
+       </para>
+      </listitem>
+     </varlistentry>
+
      </variablelist>
      </sect2>
      <sect2 id="runtime-config-query-constants">
diff --git a/src/test/regress/expected/insert_parallel.out b/src/test/regress/expected/insert_parallel.out
index 3b922a2c3d..f98d1aec7f 100644
--- a/src/test/regress/expected/insert_parallel.out
+++ b/src/test/regress/expected/insert_parallel.out
@@ -76,9 +76,27 @@ create table para_insert_f1 (
 	stringu1	name
 );
 --
+-- Test INSERT with underlying query when enable_parallel_dml=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)
+
+insert into para_insert_p1 select unique1, stringu1 from tenk1;
+--
+-- Enable parallel dml
+--
+set enable_parallel_dml = on;
+--
 -- Test INSERT with underlying query.
 -- (should create plan with parallel INSERT+SELECT, Gather 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;
                QUERY PLAN               
 ----------------------------------------
diff --git a/src/test/regress/expected/sysviews.out b/src/test/regress/expected/sysviews.out
index 81bdacf59d..3f11216809 100644
--- a/src/test/regress/expected/sysviews.out
+++ b/src/test/regress/expected/sysviews.out
@@ -99,6 +99,7 @@ select name, setting from pg_settings where name like 'enable%';
  enable_mergejoin               | on
  enable_nestloop                | on
  enable_parallel_append         | on
+ enable_parallel_dml            | off
  enable_parallel_hash           | on
  enable_partition_pruning       | on
  enable_partitionwise_aggregate | off
@@ -106,7 +107,7 @@ select name, setting from pg_settings where name like 'enable%';
  enable_seqscan                 | on
  enable_sort                    | on
  enable_tidscan                 | on
-(18 rows)
+(19 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/sql/insert_parallel.sql b/src/test/regress/sql/insert_parallel.sql
index 34a191f7c5..531731378e 100644
--- a/src/test/regress/sql/insert_parallel.sql
+++ b/src/test/regress/sql/insert_parallel.sql
@@ -94,11 +94,23 @@ create table para_insert_f1 (
 	stringu1	name
 );
 
+--
+-- Test INSERT with underlying query when enable_parallel_dml=off.
+-- (should create plan with serial INSERT + SELECT)
+--
+explain(costs off) insert into para_insert_p1 select unique1, stringu1 from tenk1;
+insert into para_insert_p1 select unique1, stringu1 from tenk1;
+
+--
+-- Enable parallel dml
+--
+set enable_parallel_dml = on;
 
 --
 -- Test INSERT with underlying query.
 -- (should create plan with parallel INSERT+SELECT, Gather parent node)
 --
+truncate para_insert_p1 cascade;
 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
-- 
2.27.0

From 2ea397a0c31f835a508770cd290e6e95ba149c86 Mon Sep 17 00:00:00 2001
From: houzj <houzj.fnst@cn.fujitsu.com>
Date: Tue, 2 Feb 2021 09:07:02 +0800
Subject: [PATCH] reloption parallel_dml_enabled

Add new table option parallel_dml_enabled.

In addition to the GUC option, the user may want a mechanism for
specifying parallel dml with finer granularity, to specify the
use of parallel dml for specific tables.
The new table option parallel_dml_enabled allows this.

The default is true.
---
 src/backend/access/common/reloptions.c | 25 +++++++++---
 src/backend/optimizer/plan/planner.c   |  3 +-
 src/backend/optimizer/util/clauses.c   | 70 ++++++++++++++++++++++++++++------
 src/bin/psql/tab-complete.c            |  1 +
 src/include/optimizer/clauses.h        |  1 +
 src/include/utils/rel.h                | 23 +++++++++++
 6 files changed, 104 insertions(+), 19 deletions(-)

diff --git a/src/backend/access/common/reloptions.c b/src/backend/access/common/reloptions.c
index c687d3e..938131a 100644
--- a/src/backend/access/common/reloptions.c
+++ b/src/backend/access/common/reloptions.c
@@ -168,6 +168,15 @@ static relopt_bool boolRelOpts[] =
 		},
 		true
 	},
+	{
+		{
+			"parallel_dml_enabled",
+			"Enables \"parallel dml\" feature for this table",
+			RELOPT_KIND_HEAP | RELOPT_KIND_PARTITIONED,
+			ShareUpdateExclusiveLock
+		},
+		true
+	},
 	/* list terminator */
 	{{NULL}}
 };
@@ -1859,7 +1868,9 @@ default_reloptions(Datum reloptions, bool validate, relopt_kind kind)
 		{"vacuum_index_cleanup", RELOPT_TYPE_BOOL,
 		offsetof(StdRdOptions, vacuum_index_cleanup)},
 		{"vacuum_truncate", RELOPT_TYPE_BOOL,
-		offsetof(StdRdOptions, vacuum_truncate)}
+		offsetof(StdRdOptions, vacuum_truncate)},
+		{"parallel_dml_enabled", RELOPT_TYPE_BOOL,
+		offsetof(StdRdOptions, parallel_dml_enabled)}
 	};
 
 	return (bytea *) build_reloptions(reloptions, validate, kind,
@@ -1961,13 +1972,15 @@ build_local_reloptions(local_relopts *relopts, Datum options, bool validate)
 bytea *
 partitioned_table_reloptions(Datum reloptions, bool validate)
 {
-	/*
-	 * There are no options for partitioned tables yet, but this is able to do
-	 * some validation.
-	 */
+	static const relopt_parse_elt tab[] = {
+		{"parallel_dml_enabled", RELOPT_TYPE_BOOL,
+		offsetof(PartitionedOptions, parallel_dml_enabled)}
+	};
+
 	return (bytea *) build_reloptions(reloptions, validate,
 									  RELOPT_KIND_PARTITIONED,
-									  0, NULL, 0);
+									  sizeof(PartitionedOptions),
+									  tab, lengthof(tab));
 }
 
 /*
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index f6ac972..cac11a9 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -339,8 +339,7 @@ standard_planner(Query *parse, const char *query_string, int cursorOptions,
 	if ((cursorOptions & CURSOR_OPT_PARALLEL_OK) != 0 &&
 		IsUnderPostmaster &&
 		(parse->commandType == CMD_SELECT ||
-		(enable_parallel_dml &&
-		IsModifySupportedInParallelMode(parse->commandType))) &&
+		is_parallel_possible_for_modify(parse)) &&
 		!parse->hasModifyingCTE &&
 		max_parallel_workers_per_gather > 0 &&
 		!IsParallelWorker())
diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index 02a7e05..30f6a54 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -1169,23 +1169,34 @@ rel_max_parallel_hazard_for_modify(Relation rel,
 }
 
 /*
- * max_parallel_hazard_for_modify
+ * is_parallel_possible_for_modify
  *
- * Determines the worst parallel-mode hazard level for the specified
- * table-modification statement, based on the statement attributes and
- * target table. An initial max parallel hazard level may optionally be
- * supplied. The search returns the earliest in the following list:
- * PROPARALLEL_UNSAFE, PROPARALLEL_RESTRICTED, PROPARALLEL_SAFE
+ * Check at a high-level if parallel mode is able to be used for the specified
+ * table-modification statement.
+ * It's not possible in the following cases:
+ *
+ *  1) enable_parallel_dml is off
+ *  2) UPDATE or DELETE command
+ *  3) INSERT...ON CONFLICT...DO UPDATE
+ *  4) INSERT without SELECT on a relation
+ *  5) the reloption parallel_dml_enabled is not set for the target table
+ *
+ * (Note: we don't do in-depth parallel-safety checks here, we do only the
+ * cheaper tests that can quickly exclude obvious cases for which
+ * parallelism isn't supported, to avoid having to do further parallel-safety
+ * checks for these)
  */
-char
-max_parallel_hazard_for_modify(Query *parse, char initial_max_parallel_hazard)
+bool
+is_parallel_possible_for_modify(Query *parse)
 {
 	RangeTblEntry *rte;
 	ListCell *lc;
 	bool hasSubQueryOnRelation;
-	max_parallel_hazard_context context;
 	Relation rel;
 
+	if (!enable_parallel_dml ||
+		!IsModifySupportedInParallelMode(parse->commandType))
+		return false;
 
 	/*
 	 * UPDATE is not currently supported in parallel-mode, so prohibit
@@ -1197,7 +1208,7 @@ max_parallel_hazard_for_modify(Query *parse, char initial_max_parallel_hazard)
 	 * combo-cid and it needs to be propagated to the workers.
 	 */
 	if (parse->onConflict != NULL && parse->onConflict->action == ONCONFLICT_UPDATE)
-		return PROPARALLEL_UNSAFE;
+		return false;
 
 	/*
 	 * If there is no underlying query on a relation, a parallel
@@ -1226,7 +1237,44 @@ max_parallel_hazard_for_modify(Query *parse, char initial_max_parallel_hazard)
 		}
 	}
 	if (!hasSubQueryOnRelation)
-		return PROPARALLEL_UNSAFE;
+		return false;
+
+	rte = rt_fetch(parse->resultRelation, parse->rtable);
+	rel = table_open(rte->relid, NoLock);
+
+	/*
+	 * Check if parallel_dml_enabled is enabled for the target table,
+	 * if not, skip the safety checks.
+	 *
+	 * (Note: if the target table is partitioned, the parallel_dml_enabled
+	 * option setting of the partitions are ignored).
+	 */
+	if (!RelationGetParallelDML(rel, true))
+	{
+		table_close(rel, NoLock);
+		return false;
+	}
+
+	table_close(rel, NoLock);
+
+	return true;
+}
+
+/*
+ * max_parallel_hazard_for_modify
+ *
+ * Determines the worst parallel-mode hazard level for the specified
+ * table-modification statement, based on the statement attributes and
+ * target table. An initial max parallel hazard level may optionally be
+ * supplied. The search returns the earliest in the following list:
+ * PROPARALLEL_UNSAFE, PROPARALLEL_RESTRICTED, PROPARALLEL_SAFE
+ */
+char
+max_parallel_hazard_for_modify(Query *parse, char initial_max_parallel_hazard)
+{
+	RangeTblEntry *rte;
+	max_parallel_hazard_context context;
+	Relation rel;
 
 	/*
 	 * Setup the context used in finding the max parallel-mode hazard.
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 17f7265..89b5fe5 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1110,6 +1110,7 @@ static const char *const table_storage_parameters[] = {
 	"autovacuum_vacuum_threshold",
 	"fillfactor",
 	"log_autovacuum_min_duration",
+	"parallel_dml_enabled",
 	"parallel_workers",
 	"toast.autovacuum_enabled",
 	"toast.autovacuum_freeze_max_age",
diff --git a/src/include/optimizer/clauses.h b/src/include/optimizer/clauses.h
index 3cdddbf..ccbc35a 100644
--- a/src/include/optimizer/clauses.h
+++ b/src/include/optimizer/clauses.h
@@ -53,5 +53,6 @@ extern void CommuteOpExpr(OpExpr *clause);
 extern Query *inline_set_returning_function(PlannerInfo *root,
 											RangeTblEntry *rte);
 extern char max_parallel_hazard_for_modify(Query *parse, char initial_max_parallel_hazard);
+extern bool is_parallel_possible_for_modify(Query *parse);
 
 #endif							/* CLAUSES_H */
diff --git a/src/include/utils/rel.h b/src/include/utils/rel.h
index 2a41a00..f8d0778 100644
--- a/src/include/utils/rel.h
+++ b/src/include/utils/rel.h
@@ -307,6 +307,7 @@ typedef struct StdRdOptions
 	int			parallel_workers;	/* max number of parallel workers */
 	bool		vacuum_index_cleanup;	/* enables index vacuuming and cleanup */
 	bool		vacuum_truncate;	/* enables vacuum to truncate a relation */
+	bool		parallel_dml_enabled;	/* enables planner's use of parallel DML */
 } StdRdOptions;
 
 #define HEAP_MIN_FILLFACTOR			10
@@ -425,6 +426,28 @@ typedef struct ViewOptions
 	  VIEW_OPTION_CHECK_OPTION_CASCADED)
 
 /*
+ * PartitionedOptions
+ *		Contents of rd_options for partitioned tables
+ */
+typedef struct PartitionedOptions
+{
+	int32		vl_len_;		/* varlena header (do not touch directly!) */
+	bool		parallel_dml_enabled;	/* enables planner's use of parallel DML */
+} PartitionedOptions;
+
+/*
+ * RelationGetParallelDML
+ *		Returns the relation's parallel_dml_enabled reloption setting.
+ *		Note multiple eval of argument!
+ */
+#define RelationGetParallelDML(relation, defaultpd) 						\
+	((relation)->rd_options ?												\
+	 (relation->rd_rel->relkind == RELKIND_PARTITIONED_TABLE ?				\
+	 ((PartitionedOptions *) (relation)->rd_options)->parallel_dml_enabled :\
+	 ((StdRdOptions *) (relation)->rd_options)->parallel_dml_enabled) :		\
+	 (defaultpd))
+
+/*
  * RelationIsValid
  *		True iff relation descriptor is valid.
  */
-- 
2.7.2.windows.1

From b34e392905c0ec4b1835f05d2af8b297390ce325 Mon Sep 17 00:00:00 2001
From: Greg Nancarrow <gregn4422@gmail.com>
Date: Tue, 2 Feb 2021 10:03:45 +1100
Subject: [PATCH v3 2/2] reloption parallel_dml_enabled test and doc

Test and documentation updates for reloption parallel_dml_enabled.
---
 doc/src/sgml/ref/alter_table.sgml             |  2 +-
 doc/src/sgml/ref/create_table.sgml            | 27 +++++++++++++++++++
 src/test/regress/expected/insert_parallel.out | 24 ++++++++++++++---
 src/test/regress/sql/insert_parallel.sql      | 19 ++++++++++---
 4 files changed, 65 insertions(+), 7 deletions(-)

diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index c25ef5abd6..ecb047021d 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -722,7 +722,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
      <para>
       <literal>SHARE UPDATE EXCLUSIVE</literal> lock will be taken for
       fillfactor, toast and autovacuum storage parameters, as well as the
-      planner parameter <varname>parallel_workers</varname>.
+      planner parameter <varname>parallel_workers</varname> and <varname>parallel_dml_enabled</varname>.
      </para>
     </listitem>
    </varlistentry>
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 569f4c9da7..420cbcfd7c 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -1408,6 +1408,33 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
     </listitem>
    </varlistentry>
 
+   <varlistentry id="reloption-parallel-dml-enabled" xreflabel="parallel_dml_enabled">
+    <term><literal>parallel_dml_enabled</literal> (<type>boolean</type>)
+     <indexterm>
+     <primary><varname>parallel_dml_enabled</varname> storage parameter</primary>
+    </indexterm>
+    </term>
+    <listitem>
+     <para>
+      Enables or disables the query planner's use of parallel DML for
+      this table. When enabled (and provided that
+      <xref linkend="guc-enable-parallel-dml"/> is also <literal>true</literal>),
+      the planner performs additional parallel-safety checks on the table's
+      attributes and indexes, in order to determine if it's safe to use a
+      parallel plan for table-modification. The default is
+      <literal>true</literal>.
+      In cases such as when the table has a large number of partitions, and
+      particularly also when that table uses a parallel-unsafe feature that
+      prevents parallelism, the overhead of these checks may become prohibitively
+      high. To address this potential overhead in these cases, this option can be
+      used to disable the use of parallel DML for this table.
+      Note that if the target table of the parallel DML is partitioned, the
+      <literal>parallel_dml_enabled</literal> option values of the partitions are
+      ignored.
+     </para>
+    </listitem>
+   </varlistentry>
+
    <varlistentry id="reloption-autovacuum-enabled" xreflabel="autovacuum_enabled">
     <term><literal>autovacuum_enabled</literal>, <literal>toast.autovacuum_enabled</literal> (<type>boolean</type>)
     <indexterm>
diff --git a/src/test/regress/expected/insert_parallel.out b/src/test/regress/expected/insert_parallel.out
index f98d1aec7f..641a5cc0ca 100644
--- a/src/test/regress/expected/insert_parallel.out
+++ b/src/test/regress/expected/insert_parallel.out
@@ -70,13 +70,13 @@ set max_parallel_workers_per_gather=4;
 create table para_insert_p1 (
 	unique1		int4	PRIMARY KEY,
 	stringu1	name
-);
+) with (parallel_dml_enabled = off);
 create table para_insert_f1 (
 	unique1		int4	REFERENCES para_insert_p1(unique1),
 	stringu1	name
 );
 --
--- Test INSERT with underlying query when enable_parallel_dml=off.
+-- Test INSERT with underlying query when enable_parallel_dml=off and reloption.parallel_dml_enabled=off.
 -- (should create plan with serial INSERT + SELECT)
 --
 explain(costs off) insert into para_insert_p1 select unique1, stringu1 from tenk1;
@@ -88,10 +88,28 @@ explain(costs off) insert into para_insert_p1 select unique1, stringu1 from tenk
 
 insert into para_insert_p1 select unique1, stringu1 from tenk1;
 --
--- Enable parallel dml
+-- Enable guc option enable_parallel_dml
 --
 set enable_parallel_dml = on;
 --
+-- Test INSERT with underlying query when enable_parallel_dml=on and reloption.parallel_dml_enabled=off.
+-- (should create plan with serial INSERT + SELECT)
+--
+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;
+        QUERY PLAN        
+--------------------------
+ Insert on para_insert_p1
+   ->  Seq Scan on tenk1
+(2 rows)
+
+insert into para_insert_p1 select unique1, stringu1 from tenk1;
+--
+-- Enable reloption parallel_dml_enabled
+--
+alter table para_insert_p1 set (parallel_dml_enabled = on);
+--
 -- Test INSERT with underlying query.
 -- (should create plan with parallel INSERT+SELECT, Gather parent node)
 --
diff --git a/src/test/regress/sql/insert_parallel.sql b/src/test/regress/sql/insert_parallel.sql
index 531731378e..4c6b352986 100644
--- a/src/test/regress/sql/insert_parallel.sql
+++ b/src/test/regress/sql/insert_parallel.sql
@@ -87,7 +87,7 @@ set max_parallel_workers_per_gather=4;
 create table para_insert_p1 (
 	unique1		int4	PRIMARY KEY,
 	stringu1	name
-);
+) with (parallel_dml_enabled = off);
 
 create table para_insert_f1 (
 	unique1		int4	REFERENCES para_insert_p1(unique1),
@@ -95,17 +95,30 @@ create table para_insert_f1 (
 );
 
 --
--- Test INSERT with underlying query when enable_parallel_dml=off.
+-- Test INSERT with underlying query when enable_parallel_dml=off and reloption.parallel_dml_enabled=off.
 -- (should create plan with serial INSERT + SELECT)
 --
 explain(costs off) insert into para_insert_p1 select unique1, stringu1 from tenk1;
 insert into para_insert_p1 select unique1, stringu1 from tenk1;
 
 --
--- Enable parallel dml
+-- Enable guc option enable_parallel_dml
 --
 set enable_parallel_dml = on;
 
+--
+-- Test INSERT with underlying query when enable_parallel_dml=on and reloption.parallel_dml_enabled=off.
+-- (should create plan with serial INSERT + SELECT)
+--
+truncate para_insert_p1 cascade;
+explain(costs off) insert into para_insert_p1 select unique1, stringu1 from tenk1;
+insert into para_insert_p1 select unique1, stringu1 from tenk1;
+
+--
+-- Enable reloption parallel_dml_enabled
+--
+alter table para_insert_p1 set (parallel_dml_enabled = on);
+
 --
 -- Test INSERT with underlying query.
 -- (should create plan with parallel INSERT+SELECT, Gather parent node)
-- 
2.27.0

From b34e392905c0ec4b1835f05d2af8b297390ce325 Mon Sep 17 00:00:00 2001
From: Greg Nancarrow <gregn4422@gmail.com>
Date: Tue, 2 Feb 2021 10:03:45 +1100
Subject: [PATCH v3 2/2] reloption parallel_dml_enabled test and doc

Test and documentation updates for reloption parallel_dml_enabled.
---
 doc/src/sgml/ref/alter_table.sgml             |  2 +-
 doc/src/sgml/ref/create_table.sgml            | 27 +++++++++++++++++++
 src/test/regress/expected/insert_parallel.out | 24 ++++++++++++++---
 src/test/regress/sql/insert_parallel.sql      | 19 ++++++++++---
 4 files changed, 65 insertions(+), 7 deletions(-)

diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index c25ef5abd6..ecb047021d 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -722,7 +722,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
      <para>
       <literal>SHARE UPDATE EXCLUSIVE</literal> lock will be taken for
       fillfactor, toast and autovacuum storage parameters, as well as the
-      planner parameter <varname>parallel_workers</varname>.
+      planner parameter <varname>parallel_workers</varname> and <varname>parallel_dml_enabled</varname>.
      </para>
     </listitem>
    </varlistentry>
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 569f4c9da7..420cbcfd7c 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -1408,6 +1408,33 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
     </listitem>
    </varlistentry>
 
+   <varlistentry id="reloption-parallel-dml-enabled" xreflabel="parallel_dml_enabled">
+    <term><literal>parallel_dml_enabled</literal> (<type>boolean</type>)
+     <indexterm>
+     <primary><varname>parallel_dml_enabled</varname> storage parameter</primary>
+    </indexterm>
+    </term>
+    <listitem>
+     <para>
+      Enables or disables the query planner's use of parallel DML for
+      this table. When enabled (and provided that
+      <xref linkend="guc-enable-parallel-dml"/> is also <literal>true</literal>),
+      the planner performs additional parallel-safety checks on the table's
+      attributes and indexes, in order to determine if it's safe to use a
+      parallel plan for table-modification. The default is
+      <literal>true</literal>.
+      In cases such as when the table has a large number of partitions, and
+      particularly also when that table uses a parallel-unsafe feature that
+      prevents parallelism, the overhead of these checks may become prohibitively
+      high. To address this potential overhead in these cases, this option can be
+      used to disable the use of parallel DML for this table.
+      Note that if the target table of the parallel DML is partitioned, the
+      <literal>parallel_dml_enabled</literal> option values of the partitions are
+      ignored.
+     </para>
+    </listitem>
+   </varlistentry>
+
    <varlistentry id="reloption-autovacuum-enabled" xreflabel="autovacuum_enabled">
     <term><literal>autovacuum_enabled</literal>, <literal>toast.autovacuum_enabled</literal> (<type>boolean</type>)
     <indexterm>
diff --git a/src/test/regress/expected/insert_parallel.out b/src/test/regress/expected/insert_parallel.out
index f98d1aec7f..641a5cc0ca 100644
--- a/src/test/regress/expected/insert_parallel.out
+++ b/src/test/regress/expected/insert_parallel.out
@@ -70,13 +70,13 @@ set max_parallel_workers_per_gather=4;
 create table para_insert_p1 (
 	unique1		int4	PRIMARY KEY,
 	stringu1	name
-);
+) with (parallel_dml_enabled = off);
 create table para_insert_f1 (
 	unique1		int4	REFERENCES para_insert_p1(unique1),
 	stringu1	name
 );
 --
--- Test INSERT with underlying query when enable_parallel_dml=off.
+-- Test INSERT with underlying query when enable_parallel_dml=off and reloption.parallel_dml_enabled=off.
 -- (should create plan with serial INSERT + SELECT)
 --
 explain(costs off) insert into para_insert_p1 select unique1, stringu1 from tenk1;
@@ -88,10 +88,28 @@ explain(costs off) insert into para_insert_p1 select unique1, stringu1 from tenk
 
 insert into para_insert_p1 select unique1, stringu1 from tenk1;
 --
--- Enable parallel dml
+-- Enable guc option enable_parallel_dml
 --
 set enable_parallel_dml = on;
 --
+-- Test INSERT with underlying query when enable_parallel_dml=on and reloption.parallel_dml_enabled=off.
+-- (should create plan with serial INSERT + SELECT)
+--
+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;
+        QUERY PLAN        
+--------------------------
+ Insert on para_insert_p1
+   ->  Seq Scan on tenk1
+(2 rows)
+
+insert into para_insert_p1 select unique1, stringu1 from tenk1;
+--
+-- Enable reloption parallel_dml_enabled
+--
+alter table para_insert_p1 set (parallel_dml_enabled = on);
+--
 -- Test INSERT with underlying query.
 -- (should create plan with parallel INSERT+SELECT, Gather parent node)
 --
diff --git a/src/test/regress/sql/insert_parallel.sql b/src/test/regress/sql/insert_parallel.sql
index 531731378e..4c6b352986 100644
--- a/src/test/regress/sql/insert_parallel.sql
+++ b/src/test/regress/sql/insert_parallel.sql
@@ -87,7 +87,7 @@ set max_parallel_workers_per_gather=4;
 create table para_insert_p1 (
 	unique1		int4	PRIMARY KEY,
 	stringu1	name
-);
+) with (parallel_dml_enabled = off);
 
 create table para_insert_f1 (
 	unique1		int4	REFERENCES para_insert_p1(unique1),
@@ -95,17 +95,30 @@ create table para_insert_f1 (
 );
 
 --
--- Test INSERT with underlying query when enable_parallel_dml=off.
+-- Test INSERT with underlying query when enable_parallel_dml=off and reloption.parallel_dml_enabled=off.
 -- (should create plan with serial INSERT + SELECT)
 --
 explain(costs off) insert into para_insert_p1 select unique1, stringu1 from tenk1;
 insert into para_insert_p1 select unique1, stringu1 from tenk1;
 
 --
--- Enable parallel dml
+-- Enable guc option enable_parallel_dml
 --
 set enable_parallel_dml = on;
 
+--
+-- Test INSERT with underlying query when enable_parallel_dml=on and reloption.parallel_dml_enabled=off.
+-- (should create plan with serial INSERT + SELECT)
+--
+truncate para_insert_p1 cascade;
+explain(costs off) insert into para_insert_p1 select unique1, stringu1 from tenk1;
+insert into para_insert_p1 select unique1, stringu1 from tenk1;
+
+--
+-- Enable reloption parallel_dml_enabled
+--
+alter table para_insert_p1 set (parallel_dml_enabled = on);
+
 --
 -- Test INSERT with underlying query.
 -- (should create plan with parallel INSERT+SELECT, Gather parent node)
-- 
2.27.0

From 76a811086b11eb2d3d0b1131bbc94f5619e95c63 Mon Sep 17 00:00:00 2001
From: Greg Nancarrow <gregn4422@gmail.com>
Date: Mon, 1 Feb 2021 14:54:26 +1100
Subject: [PATCH v3 1/2] Add new GUC option: enable_parallel_dml (boolean)

The current implementation of parallel "INSERT ... SELECT ..." may incur
non-negligible overhead in the additional parallel-safety checks that it
performs, even when, in the end, those checks determine that parallelism
can't be used. This is normally only ever a problem for large complex tables,
particularly in the case of when the target table has a large number of
partitions.

To address this potential isse, a new GUC option "enable_parallel_dml" is
added, to allow parallel DML to be enabled/disabled.

The default is false.
---
 src/backend/optimizer/path/costsize.c         |  2 ++
 src/backend/optimizer/plan/planner.c          |  3 ++-
 src/backend/utils/misc/guc.c                  | 11 +++++++++++
 src/backend/utils/misc/postgresql.conf.sample |  1 +
 src/include/optimizer/cost.h                  |  1 +
 5 files changed, 17 insertions(+), 1 deletion(-)

diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c
index f7c13bed7a..778f71b98a 100644
--- a/src/backend/optimizer/path/costsize.c
+++ b/src/backend/optimizer/path/costsize.c
@@ -129,6 +129,8 @@ Cost		disable_cost = 1.0e10;
 
 int			max_parallel_workers_per_gather = 2;
 
+bool		enable_parallel_dml = false;
+
 bool		enable_seqscan = true;
 bool		enable_indexscan = true;
 bool		enable_indexonlyscan = true;
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index 6efce22a1a..3fb108d120 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -339,7 +339,8 @@ standard_planner(Query *parse, const char *query_string, int cursorOptions,
 	if ((cursorOptions & CURSOR_OPT_PARALLEL_OK) != 0 &&
 		IsUnderPostmaster &&
 		(parse->commandType == CMD_SELECT ||
-		 IsModifySupportedInParallelMode(parse->commandType)) &&
+		(enable_parallel_dml &&
+		IsModifySupportedInParallelMode(parse->commandType))) &&
 		!parse->hasModifyingCTE &&
 		max_parallel_workers_per_gather > 0 &&
 		!IsParallelWorker())
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index eafdb1118e..6fdb991a26 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -2048,6 +2048,17 @@ static struct config_bool ConfigureNamesBool[] =
 		NULL, NULL, NULL
 	},
 
+	{
+		{"enable_parallel_dml", PGC_USERSET, QUERY_TUNING_METHOD,
+			gettext_noop("Enables the planner's use of parallel plans for table-modification commands."),
+			NULL,
+			GUC_EXPLAIN
+		},
+		&enable_parallel_dml,
+		false,
+		NULL, NULL, NULL
+	},
+
 	/* End-of-list marker */
 	{
 		{NULL, 0, 0, NULL, NULL}, NULL, false, NULL, NULL, NULL
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index bd57e917e1..1f699b0f2d 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -370,6 +370,7 @@
 #enable_partitionwise_aggregate = off
 #enable_parallel_hash = on
 #enable_partition_pruning = on
+#enable_parallel_dml = off
 
 # - Planner Cost Constants -
 
diff --git a/src/include/optimizer/cost.h b/src/include/optimizer/cost.h
index 9f15fcb240..4af0beb9c8 100644
--- a/src/include/optimizer/cost.h
+++ b/src/include/optimizer/cost.h
@@ -47,6 +47,7 @@ typedef enum
 /* parameter variables and flags (see also optimizer.h) */
 extern PGDLLIMPORT Cost disable_cost;
 extern PGDLLIMPORT int max_parallel_workers_per_gather;
+extern PGDLLIMPORT bool enable_parallel_dml;
 extern PGDLLIMPORT bool enable_seqscan;
 extern PGDLLIMPORT bool enable_indexscan;
 extern PGDLLIMPORT bool enable_indexonlyscan;
-- 
2.27.0

From 47aeb15eca62ec530231a6ddc63d5ab9fff1de76 Mon Sep 17 00:00:00 2001
From: Greg Nancarrow <gregn4422@gmail.com>
Date: Tue, 2 Feb 2021 10:30:35 +1100
Subject: [PATCH v3 2/2] Test and documentation updates for the GUC option
 "enable_parallel_dml".

---
 doc/src/sgml/config.sgml                      | 23 +++++++++++++++++++
 src/test/regress/expected/insert_parallel.out | 18 +++++++++++++++
 src/test/regress/expected/sysviews.out        |  3 ++-
 src/test/regress/sql/insert_parallel.sql      | 12 ++++++++++
 4 files changed, 55 insertions(+), 1 deletion(-)

diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index e17cdcc816..7102265dde 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -5026,6 +5026,29 @@ ANY <replaceable class="parameter">num_sync</replaceable> ( <replaceable class="
       </listitem>
      </varlistentry>
 
+     <varlistentry id="guc-enable-parallel-dml" xreflabel="enable_parallel_dml">
+      <term><varname>enable_parallel_dml</varname> (<type>boolean</type>)
+      <indexterm>
+       <primary><varname>enable_parallel_dml</varname> configuration parameter</primary>
+      </indexterm>
+      </term>
+      <listitem>
+       <para>
+        Enables or disables the query planner's use of parallel plans for
+        table-modification commands. The default is <literal>off</literal>.
+        When enabled, the planner performs additional parallel-safety checks
+        on the target table's attributes and indexes, in order to determine
+        if it's safe to use a parallel plan for table-modification. In cases
+        such as when the target table has a large number of partitions, and
+        particularly also when that table uses something parallel-unsafe that
+        prevents parallelism, the overhead of these checks may become
+        prohibitively high. To address this potential overhead in these cases,
+        this option can be used to disable the use of parallel plans for
+        table-modification.
+       </para>
+      </listitem>
+     </varlistentry>
+
      </variablelist>
      </sect2>
      <sect2 id="runtime-config-query-constants">
diff --git a/src/test/regress/expected/insert_parallel.out b/src/test/regress/expected/insert_parallel.out
index 3b922a2c3d..f98d1aec7f 100644
--- a/src/test/regress/expected/insert_parallel.out
+++ b/src/test/regress/expected/insert_parallel.out
@@ -76,9 +76,27 @@ create table para_insert_f1 (
 	stringu1	name
 );
 --
+-- Test INSERT with underlying query when enable_parallel_dml=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)
+
+insert into para_insert_p1 select unique1, stringu1 from tenk1;
+--
+-- Enable parallel dml
+--
+set enable_parallel_dml = on;
+--
 -- Test INSERT with underlying query.
 -- (should create plan with parallel INSERT+SELECT, Gather 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;
                QUERY PLAN               
 ----------------------------------------
diff --git a/src/test/regress/expected/sysviews.out b/src/test/regress/expected/sysviews.out
index 81bdacf59d..3f11216809 100644
--- a/src/test/regress/expected/sysviews.out
+++ b/src/test/regress/expected/sysviews.out
@@ -99,6 +99,7 @@ select name, setting from pg_settings where name like 'enable%';
  enable_mergejoin               | on
  enable_nestloop                | on
  enable_parallel_append         | on
+ enable_parallel_dml            | off
  enable_parallel_hash           | on
  enable_partition_pruning       | on
  enable_partitionwise_aggregate | off
@@ -106,7 +107,7 @@ select name, setting from pg_settings where name like 'enable%';
  enable_seqscan                 | on
  enable_sort                    | on
  enable_tidscan                 | on
-(18 rows)
+(19 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/sql/insert_parallel.sql b/src/test/regress/sql/insert_parallel.sql
index 34a191f7c5..531731378e 100644
--- a/src/test/regress/sql/insert_parallel.sql
+++ b/src/test/regress/sql/insert_parallel.sql
@@ -94,11 +94,23 @@ create table para_insert_f1 (
 	stringu1	name
 );
 
+--
+-- Test INSERT with underlying query when enable_parallel_dml=off.
+-- (should create plan with serial INSERT + SELECT)
+--
+explain(costs off) insert into para_insert_p1 select unique1, stringu1 from tenk1;
+insert into para_insert_p1 select unique1, stringu1 from tenk1;
+
+--
+-- Enable parallel dml
+--
+set enable_parallel_dml = on;
 
 --
 -- Test INSERT with underlying query.
 -- (should create plan with parallel INSERT+SELECT, Gather parent node)
 --
+truncate para_insert_p1 cascade;
 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
-- 
2.27.0

From c50251c03a93504456051df2cf88495dbb5660cb Mon Sep 17 00:00:00 2001
From: houzj <houzj.fnst@cn.fujitsu.com>
Date: Wed, 3 Feb 2021 08:39:27 +0800
Subject: [PATCH] reloption parallel_dml_enabled

Add new table option parallel_dml_enabled.

In addition to the GUC option, the user may want a mechanism for
specifying parallel dml with finer granularity, to specify the
use of parallel dml for specific tables.
The new table option parallel_dml_enabled allows this.

The default is true.
---
 src/backend/access/common/reloptions.c | 25 ++++++++---
 src/backend/optimizer/plan/planner.c   |  3 +-
 src/backend/optimizer/util/clauses.c   | 76 +++++++++++++++++++++++++++-------
 src/bin/psql/tab-complete.c            |  1 +
 src/include/optimizer/clauses.h        |  1 +
 src/include/utils/rel.h                | 23 ++++++++++
 6 files changed, 105 insertions(+), 24 deletions(-)

diff --git a/src/backend/access/common/reloptions.c b/src/backend/access/common/reloptions.c
index c687d3e..938131a 100644
--- a/src/backend/access/common/reloptions.c
+++ b/src/backend/access/common/reloptions.c
@@ -168,6 +168,15 @@ static relopt_bool boolRelOpts[] =
 		},
 		true
 	},
+	{
+		{
+			"parallel_dml_enabled",
+			"Enables \"parallel dml\" feature for this table",
+			RELOPT_KIND_HEAP | RELOPT_KIND_PARTITIONED,
+			ShareUpdateExclusiveLock
+		},
+		true
+	},
 	/* list terminator */
 	{{NULL}}
 };
@@ -1859,7 +1868,9 @@ default_reloptions(Datum reloptions, bool validate, relopt_kind kind)
 		{"vacuum_index_cleanup", RELOPT_TYPE_BOOL,
 		offsetof(StdRdOptions, vacuum_index_cleanup)},
 		{"vacuum_truncate", RELOPT_TYPE_BOOL,
-		offsetof(StdRdOptions, vacuum_truncate)}
+		offsetof(StdRdOptions, vacuum_truncate)},
+		{"parallel_dml_enabled", RELOPT_TYPE_BOOL,
+		offsetof(StdRdOptions, parallel_dml_enabled)}
 	};
 
 	return (bytea *) build_reloptions(reloptions, validate, kind,
@@ -1961,13 +1972,15 @@ build_local_reloptions(local_relopts *relopts, Datum options, bool validate)
 bytea *
 partitioned_table_reloptions(Datum reloptions, bool validate)
 {
-	/*
-	 * There are no options for partitioned tables yet, but this is able to do
-	 * some validation.
-	 */
+	static const relopt_parse_elt tab[] = {
+		{"parallel_dml_enabled", RELOPT_TYPE_BOOL,
+		offsetof(PartitionedOptions, parallel_dml_enabled)}
+	};
+
 	return (bytea *) build_reloptions(reloptions, validate,
 									  RELOPT_KIND_PARTITIONED,
-									  0, NULL, 0);
+									  sizeof(PartitionedOptions),
+									  tab, lengthof(tab));
 }
 
 /*
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index f6ac972..cac11a9 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -339,8 +339,7 @@ standard_planner(Query *parse, const char *query_string, int cursorOptions,
 	if ((cursorOptions & CURSOR_OPT_PARALLEL_OK) != 0 &&
 		IsUnderPostmaster &&
 		(parse->commandType == CMD_SELECT ||
-		(enable_parallel_dml &&
-		IsModifySupportedInParallelMode(parse->commandType))) &&
+		is_parallel_possible_for_modify(parse)) &&
 		!parse->hasModifyingCTE &&
 		max_parallel_workers_per_gather > 0 &&
 		!IsParallelWorker())
diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index 0b3d12e..639fbd3 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -1169,23 +1169,35 @@ rel_max_parallel_hazard_for_modify(Relation rel,
 }
 
 /*
- * max_parallel_hazard_for_modify
+ * is_parallel_possible_for_modify
  *
- * Determines the worst parallel-mode hazard level for the specified
- * table-modification statement, based on the statement attributes and
- * target table. An initial max parallel hazard level may optionally be
- * supplied. The search returns the earliest in the following list:
- * PROPARALLEL_UNSAFE, PROPARALLEL_RESTRICTED, PROPARALLEL_SAFE
+ * Check at a high-level if parallel mode is able to be used for the specified
+ * table-modification statement.
+ * It's not possible in the following cases:
+ *
+ *  1) enable_parallel_dml is off
+ *  2) UPDATE or DELETE command
+ *  3) INSERT...ON CONFLICT...DO UPDATE
+ *  4) INSERT without SELECT on a relation
+ *  5) the reloption parallel_dml_enabled is not set for the target table
+ *
+ * (Note: we don't do in-depth parallel-safety checks here, we do only the
+ * cheaper tests that can quickly exclude obvious cases for which
+ * parallelism isn't supported, to avoid having to do further parallel-safety
+ * checks for these)
  */
-char
-max_parallel_hazard_for_modify(Query *parse, char initial_max_parallel_hazard)
+bool
+is_parallel_possible_for_modify(Query *parse)
 {
-	RangeTblEntry *rte;
-	ListCell *lc;
-	bool hasSubQuery;
-	max_parallel_hazard_context context;
-	Relation rel;
-
+	bool			hasSubQuery;
+	bool			parallel_enabled;
+	Relation		rel;
+	RangeTblEntry  *rte;
+	ListCell	   *lc;
+
+	if (!enable_parallel_dml ||
+		!IsModifySupportedInParallelMode(parse->commandType))
+		return false;
 
 	/*
 	 * UPDATE is not currently supported in parallel-mode, so prohibit
@@ -1197,7 +1209,7 @@ max_parallel_hazard_for_modify(Query *parse, char initial_max_parallel_hazard)
 	 * combo-cid and it needs to be propagated to the workers.
 	 */
 	if (parse->onConflict != NULL && parse->onConflict->action == ONCONFLICT_UPDATE)
-		return PROPARALLEL_UNSAFE;
+		return false;
 
 	/*
 	 * If there is no underlying SELECT, a parallel table-modification
@@ -1214,7 +1226,39 @@ max_parallel_hazard_for_modify(Query *parse, char initial_max_parallel_hazard)
 		}
 	}
 	if (!hasSubQuery)
-		return PROPARALLEL_UNSAFE;
+		return false;
+
+	/*
+	 * Check if parallel_dml_enabled is enabled for the target table,
+	 * if not, skip the safety checks.
+	 *
+	 * (Note: if the target table is partitioned, the parallel_dml_enabled
+	 * option setting of the partitions are ignored).
+	 */
+	rte = rt_fetch(parse->resultRelation, parse->rtable);
+	rel = table_open(rte->relid, NoLock);
+
+	parallel_enabled = RelationGetParallelDML(rel, true);
+	table_close(rel, NoLock);
+
+	return parallel_enabled;
+}
+
+/*
+ * max_parallel_hazard_for_modify
+ *
+ * Determines the worst parallel-mode hazard level for the specified
+ * table-modification statement, based on the statement attributes and
+ * target table. An initial max parallel hazard level may optionally be
+ * supplied. The search returns the earliest in the following list:
+ * PROPARALLEL_UNSAFE, PROPARALLEL_RESTRICTED, PROPARALLEL_SAFE
+ */
+char
+max_parallel_hazard_for_modify(Query *parse, char initial_max_parallel_hazard)
+{
+	RangeTblEntry *rte;
+	max_parallel_hazard_context context;
+	Relation rel;
 
 	/*
 	 * Setup the context used in finding the max parallel-mode hazard.
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 17f7265..89b5fe5 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1110,6 +1110,7 @@ static const char *const table_storage_parameters[] = {
 	"autovacuum_vacuum_threshold",
 	"fillfactor",
 	"log_autovacuum_min_duration",
+	"parallel_dml_enabled",
 	"parallel_workers",
 	"toast.autovacuum_enabled",
 	"toast.autovacuum_freeze_max_age",
diff --git a/src/include/optimizer/clauses.h b/src/include/optimizer/clauses.h
index 3cdddbf..ccbc35a 100644
--- a/src/include/optimizer/clauses.h
+++ b/src/include/optimizer/clauses.h
@@ -53,5 +53,6 @@ extern void CommuteOpExpr(OpExpr *clause);
 extern Query *inline_set_returning_function(PlannerInfo *root,
 											RangeTblEntry *rte);
 extern char max_parallel_hazard_for_modify(Query *parse, char initial_max_parallel_hazard);
+extern bool is_parallel_possible_for_modify(Query *parse);
 
 #endif							/* CLAUSES_H */
diff --git a/src/include/utils/rel.h b/src/include/utils/rel.h
index 2a41a00..f8d0778 100644
--- a/src/include/utils/rel.h
+++ b/src/include/utils/rel.h
@@ -307,6 +307,7 @@ typedef struct StdRdOptions
 	int			parallel_workers;	/* max number of parallel workers */
 	bool		vacuum_index_cleanup;	/* enables index vacuuming and cleanup */
 	bool		vacuum_truncate;	/* enables vacuum to truncate a relation */
+	bool		parallel_dml_enabled;	/* enables planner's use of parallel DML */
 } StdRdOptions;
 
 #define HEAP_MIN_FILLFACTOR			10
@@ -425,6 +426,28 @@ typedef struct ViewOptions
 	  VIEW_OPTION_CHECK_OPTION_CASCADED)
 
 /*
+ * PartitionedOptions
+ *		Contents of rd_options for partitioned tables
+ */
+typedef struct PartitionedOptions
+{
+	int32		vl_len_;		/* varlena header (do not touch directly!) */
+	bool		parallel_dml_enabled;	/* enables planner's use of parallel DML */
+} PartitionedOptions;
+
+/*
+ * RelationGetParallelDML
+ *		Returns the relation's parallel_dml_enabled reloption setting.
+ *		Note multiple eval of argument!
+ */
+#define RelationGetParallelDML(relation, defaultpd) 						\
+	((relation)->rd_options ?												\
+	 (relation->rd_rel->relkind == RELKIND_PARTITIONED_TABLE ?				\
+	 ((PartitionedOptions *) (relation)->rd_options)->parallel_dml_enabled :\
+	 ((StdRdOptions *) (relation)->rd_options)->parallel_dml_enabled) :		\
+	 (defaultpd))
+
+/*
  * RelationIsValid
  *		True iff relation descriptor is valid.
  */
-- 
2.7.2.windows.1

From b8fb29126fdc6083bf2d020fc5e55f3cb9ad050d Mon Sep 17 00:00:00 2001
From: houzj <houzj.fnst@cn.fujitsu.com>
Date: Thu, 18 Feb 2021 11:17:35 +0800
Subject: [PATCH] reloption parallel parallel_dml_enabled test and doc

Test and documentation updates for reloption parallel_dml_enabled.
---
 doc/src/sgml/ref/alter_table.sgml             |  2 +-
 doc/src/sgml/ref/create_table.sgml            | 27 +++++++++++++++++
 src/test/regress/expected/insert_parallel.out | 42 ++++++++++++++++++++++++---
 src/test/regress/sql/insert_parallel.sql      | 34 +++++++++++++++++++---
 4 files changed, 96 insertions(+), 9 deletions(-)

diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index c25ef5a..ecb0470 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -722,7 +722,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
      <para>
       <literal>SHARE UPDATE EXCLUSIVE</literal> lock will be taken for
       fillfactor, toast and autovacuum storage parameters, as well as the
-      planner parameter <varname>parallel_workers</varname>.
+      planner parameter <varname>parallel_workers</varname> and <varname>parallel_dml_enabled</varname>.
      </para>
     </listitem>
    </varlistentry>
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 569f4c9..f402174 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -1408,6 +1408,33 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
     </listitem>
    </varlistentry>
 
+   <varlistentry id="reloption-parallel-dml-enabled" xreflabel="parallel_dml_enabled">
+    <term><literal>parallel_dml_enabled</literal> (<type>boolean</type>)
+     <indexterm>
+     <primary><varname>parallel_dml_enabled</varname> storage parameter</primary>
+    </indexterm>
+    </term>
+    <listitem>
+     <para>
+      Enables or disables the query planner's use of parallel DML for
+      this table. When enabled (and provided that
+      <xref linkend="guc-enable-parallel-dml"/> is also <literal>true</literal>),
+      the planner performs additional parallel-safety checks on the table's
+      attributes and indexes, in order to determine if it's safe to use a
+      parallel plan for table-modification. The default is
+      <literal>true</literal>.
+      In cases such as when the table has a large number of partitions, and
+      particularly also when that table uses a parallel-unsafe feature that
+      prevents parallelism, the overhead of these checks may become prohibitively
+      high. To address this potential overhead in these cases, this option can be
+      used to disable the use of parallel DML for this table.
+      Note that if the target table of the parallel DML is partitioned, the
+      <literal>parallel_dml_enabled</literal> option values of the partitions are
+      ignored.
+     </para>
+    </listitem>
+   </varlistentry>
+
    <varlistentry id="reloption-autovacuum-enabled" xreflabel="autovacuum_enabled">
     <term><literal>autovacuum_enabled</literal>, <literal>toast.autovacuum_enabled</literal> (<type>boolean</type>)
     <indexterm>
diff --git a/src/test/regress/expected/insert_parallel.out b/src/test/regress/expected/insert_parallel.out
index 73330ef..997d8b5 100644
--- a/src/test/regress/expected/insert_parallel.out
+++ b/src/test/regress/expected/insert_parallel.out
@@ -70,13 +70,13 @@ set max_parallel_workers_per_gather=4;
 create table para_insert_p1 (
 	unique1		int4	PRIMARY KEY,
 	stringu1	name
-);
+) with (parallel_dml_enabled = off);
 create table para_insert_f1 (
 	unique1		int4	REFERENCES para_insert_p1(unique1),
 	stringu1	name
 );
 --
--- Test INSERT with underlying query when enable_parallel_dml=off.
+-- Test INSERT with underlying query when enable_parallel_dml=off and reloption.parallel_dml_enabled=off.
 -- (should create plan with serial INSERT + SELECT)
 --
 explain(costs off) insert into para_insert_p1 select unique1, stringu1 from tenk1;
@@ -87,10 +87,25 @@ explain(costs off) insert into para_insert_p1 select unique1, stringu1 from tenk
 (2 rows)
 
 --
--- Enable parallel dml
+-- Enable guc option enable_parallel_dml
 --
 set enable_parallel_dml = on;
 --
+-- Test INSERT with underlying query when enable_parallel_dml=on and reloption.parallel_dml_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_dml_enabled
+--
+alter table para_insert_p1 set (parallel_dml_enabled = on);
+--
 -- Test INSERT with underlying query.
 -- (should create plan with parallel INSERT+SELECT, Gather parent node)
 --
@@ -868,9 +883,28 @@ truncate testdef;
 --
 -- Test INSERT into partition with underlying query.
 --
-create table parttable1 (a int, b name) partition by range (a);
+create table parttable1 (a int, b name) partition by range (a) with (parallel_dml_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_dml_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_dml_enabled
+--
+alter table parttable1 set (parallel_dml_enabled = on);
+--
+-- Test INSERT into partition when reloption.parallel_dml_enabled=off
+-- (should create a parallel plan)
+--
 explain (costs off) insert into parttable1 select unique1,stringu1 from tenk1;
                QUERY PLAN               
 ----------------------------------------
diff --git a/src/test/regress/sql/insert_parallel.sql b/src/test/regress/sql/insert_parallel.sql
index eba30d2..8cb439b 100644
--- a/src/test/regress/sql/insert_parallel.sql
+++ b/src/test/regress/sql/insert_parallel.sql
@@ -87,7 +87,7 @@ set max_parallel_workers_per_gather=4;
 create table para_insert_p1 (
 	unique1		int4	PRIMARY KEY,
 	stringu1	name
-);
+) with (parallel_dml_enabled = off);
 
 create table para_insert_f1 (
 	unique1		int4	REFERENCES para_insert_p1(unique1),
@@ -95,17 +95,28 @@ create table para_insert_f1 (
 );
 
 --
--- Test INSERT with underlying query when enable_parallel_dml=off.
+-- Test INSERT with underlying query when enable_parallel_dml=off and reloption.parallel_dml_enabled=off.
 -- (should create plan with serial INSERT + SELECT)
 --
 explain(costs off) insert into para_insert_p1 select unique1, stringu1 from tenk1;
 
 --
--- Enable parallel dml
+-- Enable guc option enable_parallel_dml
 --
 set enable_parallel_dml = on;
 
 --
+-- Test INSERT with underlying query when enable_parallel_dml=on and reloption.parallel_dml_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_dml_enabled
+--
+alter table para_insert_p1 set (parallel_dml_enabled = on);
+
+--
 -- Test INSERT with underlying query.
 -- (should create plan with parallel INSERT+SELECT, Gather parent node)
 --
@@ -359,10 +370,25 @@ truncate testdef;
 --
 -- Test INSERT into partition with underlying query.
 --
-create table parttable1 (a int, b name) partition by range (a);
+create table parttable1 (a int, b name) partition by range (a) with (parallel_dml_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_dml_enabled=off
+-- (should not create a parallel plan)
+--
+explain (costs off) insert into parttable1 select unique1,stringu1 from tenk1;
+
+--
+-- Enable reloption parallel_dml_enabled
+--
+alter table parttable1 set (parallel_dml_enabled = on);
+
+--
+-- Test INSERT into partition when reloption.parallel_dml_enabled=off
+-- (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;
-- 
2.7.2.windows.1

From 5330f1df94b1549cef7de1a4d9be2b88a7d2866b Mon Sep 17 00:00:00 2001
From: houzj <houzj.fnst@cn.fujitsu.com>
Date: Thu, 18 Feb 2021 10:02:06 +0800
Subject: [PATCH 1/4] Add new GUC option: enable_parallel_dml (boolean)

The current implementation of parallel "INSERT ... SELECT ..." may incur
non-negligible overhead in the additional parallel-safety checks that it
performs, even when, in the end, those checks determine that parallelism
can't be used. This is normally only ever a problem for large complex tables,
particularly in the case of when the target table has a large number of
partitions.

To address this potential isse, a new GUC option "enable_parallel_dml" is
added, to allow parallel DML to be enabled/disabled.

The default is false.
---
 src/backend/optimizer/path/costsize.c         |  2 ++
 src/backend/optimizer/util/clauses.c          |  8 ++++++--
 src/backend/utils/misc/guc.c                  | 11 +++++++++++
 src/backend/utils/misc/postgresql.conf.sample |  1 +
 src/include/optimizer/cost.h                  |  1 +
 5 files changed, 21 insertions(+), 2 deletions(-)

diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c
index f7c13be..778f71b 100644
--- a/src/backend/optimizer/path/costsize.c
+++ b/src/backend/optimizer/path/costsize.c
@@ -129,6 +129,8 @@ Cost		disable_cost = 1.0e10;
 
 int			max_parallel_workers_per_gather = 2;
 
+bool		enable_parallel_dml = false;
+
 bool		enable_seqscan = true;
 bool		enable_indexscan = true;
 bool		enable_indexonlyscan = true;
diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index de16a28..eb6eee5 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -1219,8 +1219,9 @@ target_rel_max_parallel_hazard_recurse(Relation rel,
  * table-modification statement.
  * It's not possible in the following cases:
  *
- *  1) INSERT...ON CONFLICT...DO UPDATE
- *  2) INSERT without SELECT
+ *  1) enable_parallel_dml is off
+ *  2) INSERT...ON CONFLICT...DO UPDATE
+ *  3) INSERT without SELECT
  *
  * (Note: we don't do in-depth parallel-safety checks here, we do only the
  * cheaper tests that can quickly exclude obvious cases for which
@@ -1236,6 +1237,9 @@ is_parallel_possible_for_modify(Query *parse)
 
 	Assert(IsModifySupportedInParallelMode(parse->commandType));
 
+	if (!enable_parallel_dml)
+		return false;
+
 	/*
 	 * UPDATE is not currently supported in parallel-mode, so prohibit
 	 * INSERT...ON CONFLICT...DO UPDATE...
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index eafdb11..6fdb991 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -2048,6 +2048,17 @@ static struct config_bool ConfigureNamesBool[] =
 		NULL, NULL, NULL
 	},
 
+	{
+		{"enable_parallel_dml", PGC_USERSET, QUERY_TUNING_METHOD,
+			gettext_noop("Enables the planner's use of parallel plans for table-modification commands."),
+			NULL,
+			GUC_EXPLAIN
+		},
+		&enable_parallel_dml,
+		false,
+		NULL, NULL, NULL
+	},
+
 	/* End-of-list marker */
 	{
 		{NULL, 0, 0, NULL, NULL}, NULL, false, NULL, NULL, NULL
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index db6db37..fdd1fce 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -370,6 +370,7 @@
 #enable_partitionwise_aggregate = off
 #enable_parallel_hash = on
 #enable_partition_pruning = on
+#enable_parallel_dml = off
 
 # - Planner Cost Constants -
 
diff --git a/src/include/optimizer/cost.h b/src/include/optimizer/cost.h
index 9f15fcb..4af0beb 100644
--- a/src/include/optimizer/cost.h
+++ b/src/include/optimizer/cost.h
@@ -47,6 +47,7 @@ typedef enum
 /* parameter variables and flags (see also optimizer.h) */
 extern PGDLLIMPORT Cost disable_cost;
 extern PGDLLIMPORT int max_parallel_workers_per_gather;
+extern PGDLLIMPORT bool enable_parallel_dml;
 extern PGDLLIMPORT bool enable_seqscan;
 extern PGDLLIMPORT bool enable_indexscan;
 extern PGDLLIMPORT bool enable_indexonlyscan;
-- 
2.7.2.windows.1

From b0db4d1cf66a5b98ba6627ff26bf8c70a3fcfbce Mon Sep 17 00:00:00 2001
From: houzj <houzj.fnst@cn.fujitsu.com>
Date: Thu, 18 Feb 2021 10:12:08 +0800
Subject: [PATCH 2/4] guc option enable_parallel_dml test and doc

Test and documentation updates for guc option enable_parallel_dml.
---
 doc/src/sgml/config.sgml                      | 23 +++++++++++++++++++++++
 src/test/regress/expected/insert_parallel.out | 15 +++++++++++++++
 src/test/regress/expected/sysviews.out        |  3 ++-
 src/test/regress/sql/insert_parallel.sql      | 10 ++++++++++
 4 files changed, 50 insertions(+), 1 deletion(-)

diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 4df1405..5b93df4 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -5026,6 +5026,29 @@ ANY <replaceable class="parameter">num_sync</replaceable> ( <replaceable class="
       </listitem>
      </varlistentry>
 
+     <varlistentry id="guc-enable-parallel-dml" xreflabel="enable_parallel_dml">
+      <term><varname>enable_parallel_dml</varname> (<type>boolean</type>)
+      <indexterm>
+       <primary><varname>enable_parallel_dml</varname> configuration parameter</primary>
+      </indexterm>
+      </term>
+      <listitem>
+       <para>
+        Enables or disables the query planner's use of parallel plans for
+        table-modification commands. The default is <literal>off</literal>.
+        When enabled, the planner performs additional parallel-safety checks
+        on the target table's attributes and indexes, in order to determine
+        if it's safe to use a parallel plan for table-modification. In cases
+        such as when the target table has a large number of partitions, and
+        particularly also when that table uses something parallel-unsafe that
+        prevents parallelism, the overhead of these checks may become
+        prohibitively high. To address this potential overhead in these cases,
+        this option can be used to disable the use of parallel plans for
+        table-modification.
+       </para>
+      </listitem>
+     </varlistentry>
+
      </variablelist>
      </sect2>
      <sect2 id="runtime-config-query-constants">
diff --git a/src/test/regress/expected/insert_parallel.out b/src/test/regress/expected/insert_parallel.out
index a5a309a..73330ef 100644
--- a/src/test/regress/expected/insert_parallel.out
+++ b/src/test/regress/expected/insert_parallel.out
@@ -76,6 +76,21 @@ create table para_insert_f1 (
 	stringu1	name
 );
 --
+-- Test INSERT with underlying query when enable_parallel_dml=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 parallel dml
+--
+set enable_parallel_dml = on;
+--
 -- Test INSERT with underlying query.
 -- (should create plan with parallel INSERT+SELECT, Gather parent node)
 --
diff --git a/src/test/regress/expected/sysviews.out b/src/test/regress/expected/sysviews.out
index 81bdacf..3f11216 100644
--- a/src/test/regress/expected/sysviews.out
+++ b/src/test/regress/expected/sysviews.out
@@ -99,6 +99,7 @@ select name, setting from pg_settings where name like 'enable%';
  enable_mergejoin               | on
  enable_nestloop                | on
  enable_parallel_append         | on
+ enable_parallel_dml            | off
  enable_parallel_hash           | on
  enable_partition_pruning       | on
  enable_partitionwise_aggregate | off
@@ -106,7 +107,7 @@ select name, setting from pg_settings where name like 'enable%';
  enable_seqscan                 | on
  enable_sort                    | on
  enable_tidscan                 | on
-(18 rows)
+(19 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/sql/insert_parallel.sql b/src/test/regress/sql/insert_parallel.sql
index 9183032..eba30d2 100644
--- a/src/test/regress/sql/insert_parallel.sql
+++ b/src/test/regress/sql/insert_parallel.sql
@@ -94,6 +94,16 @@ create table para_insert_f1 (
 	stringu1	name
 );
 
+--
+-- Test INSERT with underlying query when enable_parallel_dml=off.
+-- (should create plan with serial INSERT + SELECT)
+--
+explain(costs off) insert into para_insert_p1 select unique1, stringu1 from tenk1;
+
+--
+-- Enable parallel dml
+--
+set enable_parallel_dml = on;
 
 --
 -- Test INSERT with underlying query.
-- 
2.7.2.windows.1

From 9d46fec85b84db27f840e2e0a06eefd0cfcfc2ed Mon Sep 17 00:00:00 2001
From: houzj <houzj.fnst@cn.fujitsu.com>
Date: Thu, 18 Feb 2021 10:35:11 +0800
Subject: [PATCH 3/4] reloption parallel_dml_enabled

Add new table option parallel_dml_enabled.

In addition to the GUC option, the user may want a mechanism for
specifying parallel dml with finer granularity, to specify the
use of parallel dml for specific tables.
The new table option parallel_dml_enabled allows this.

The default is true.
---
 src/backend/access/common/reloptions.c | 25 +++++++++++++++++++------
 src/backend/optimizer/util/clauses.c   | 18 +++++++++++++++++-
 src/bin/psql/tab-complete.c            |  1 +
 src/include/utils/rel.h                | 23 +++++++++++++++++++++++
 4 files changed, 60 insertions(+), 7 deletions(-)

diff --git a/src/backend/access/common/reloptions.c b/src/backend/access/common/reloptions.c
index c687d3e..938131a 100644
--- a/src/backend/access/common/reloptions.c
+++ b/src/backend/access/common/reloptions.c
@@ -168,6 +168,15 @@ static relopt_bool boolRelOpts[] =
 		},
 		true
 	},
+	{
+		{
+			"parallel_dml_enabled",
+			"Enables \"parallel dml\" feature for this table",
+			RELOPT_KIND_HEAP | RELOPT_KIND_PARTITIONED,
+			ShareUpdateExclusiveLock
+		},
+		true
+	},
 	/* list terminator */
 	{{NULL}}
 };
@@ -1859,7 +1868,9 @@ default_reloptions(Datum reloptions, bool validate, relopt_kind kind)
 		{"vacuum_index_cleanup", RELOPT_TYPE_BOOL,
 		offsetof(StdRdOptions, vacuum_index_cleanup)},
 		{"vacuum_truncate", RELOPT_TYPE_BOOL,
-		offsetof(StdRdOptions, vacuum_truncate)}
+		offsetof(StdRdOptions, vacuum_truncate)},
+		{"parallel_dml_enabled", RELOPT_TYPE_BOOL,
+		offsetof(StdRdOptions, parallel_dml_enabled)}
 	};
 
 	return (bytea *) build_reloptions(reloptions, validate, kind,
@@ -1961,13 +1972,15 @@ build_local_reloptions(local_relopts *relopts, Datum options, bool validate)
 bytea *
 partitioned_table_reloptions(Datum reloptions, bool validate)
 {
-	/*
-	 * There are no options for partitioned tables yet, but this is able to do
-	 * some validation.
-	 */
+	static const relopt_parse_elt tab[] = {
+		{"parallel_dml_enabled", RELOPT_TYPE_BOOL,
+		offsetof(PartitionedOptions, parallel_dml_enabled)}
+	};
+
 	return (bytea *) build_reloptions(reloptions, validate,
 									  RELOPT_KIND_PARTITIONED,
-									  0, NULL, 0);
+									  sizeof(PartitionedOptions),
+									  tab, lengthof(tab));
 }
 
 /*
diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index eb6eee5..f134681 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -1222,6 +1222,7 @@ target_rel_max_parallel_hazard_recurse(Relation rel,
  *  1) enable_parallel_dml is off
  *  2) INSERT...ON CONFLICT...DO UPDATE
  *  3) INSERT without SELECT
+ *  4) the reloption parallel_dml_enabled is set to off
  *
  * (Note: we don't do in-depth parallel-safety checks here, we do only the
  * cheaper tests that can quickly exclude obvious cases for which
@@ -1232,8 +1233,10 @@ bool
 is_parallel_possible_for_modify(Query *parse)
 {
 	bool			hasSubQuery;
+	bool			parallel_enabled;
 	RangeTblEntry  *rte;
 	ListCell	   *lc;
+	Relation		rel;
 
 	Assert(IsModifySupportedInParallelMode(parse->commandType));
 
@@ -1271,7 +1274,20 @@ is_parallel_possible_for_modify(Query *parse)
 	if (!hasSubQuery)
 		return false;
 
-	return true;
+	/*
+	 * Check if parallel_dml_enabled is enabled for the target table,
+	 * if not, skip the safety checks.
+	 *
+	 * (Note: if the target table is partitioned, the parallel_dml_enabled
+	 * option setting of the partitions are ignored).
+	 */
+	rte = rt_fetch(parse->resultRelation, parse->rtable);
+	rel = table_open(rte->relid, NoLock);
+
+	parallel_enabled = RelationGetParallelDML(rel, true);
+	table_close(rel, NoLock);
+
+	return parallel_enabled;
 }
 
 /*****************************************************************************
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index b64db82..ee7f48c 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1110,6 +1110,7 @@ static const char *const table_storage_parameters[] = {
 	"autovacuum_vacuum_threshold",
 	"fillfactor",
 	"log_autovacuum_min_duration",
+	"parallel_dml_enabled",
 	"parallel_workers",
 	"toast.autovacuum_enabled",
 	"toast.autovacuum_freeze_max_age",
diff --git a/src/include/utils/rel.h b/src/include/utils/rel.h
index 2a41a00..1197ae2 100644
--- a/src/include/utils/rel.h
+++ b/src/include/utils/rel.h
@@ -307,6 +307,7 @@ typedef struct StdRdOptions
 	int			parallel_workers;	/* max number of parallel workers */
 	bool		vacuum_index_cleanup;	/* enables index vacuuming and cleanup */
 	bool		vacuum_truncate;	/* enables vacuum to truncate a relation */
+	bool		parallel_dml_enabled;	/* enables planner's use of parallel DML */
 } StdRdOptions;
 
 #define HEAP_MIN_FILLFACTOR			10
@@ -425,6 +426,28 @@ typedef struct ViewOptions
 	  VIEW_OPTION_CHECK_OPTION_CASCADED)
 
 /*
+ * PartitionedOptions
+ *		Contents of rd_options for partitioned tables
+ */
+typedef struct PartitionedOptions
+{
+	int32		vl_len_;		/* varlena header (do not touch directly!) */
+	bool		parallel_dml_enabled;	/* enables planner's use of parallel DML */
+} PartitionedOptions;
+
+/*
+ * RelationGetParallelDML
+ *		Returns the relation's parallel_dml_enabled reloption setting.
+ *		Note multiple eval of argument!
+ */
+#define RelationGetParallelDML(relation, defaultpd) 						\
+	((relation)->rd_options ?												\
+	 (relation->rd_rel->relkind == RELKIND_PARTITIONED_TABLE ?				\
+	 ((PartitionedOptions *) (relation)->rd_options)->parallel_dml_enabled :\
+	 ((StdRdOptions *) (relation)->rd_options)->parallel_dml_enabled) :		\
+	 (defaultpd))
+
+/*
  * RelationIsValid
  *		True iff relation descriptor is valid.
  */
-- 
2.7.2.windows.1

From b8fb29126fdc6083bf2d020fc5e55f3cb9ad050d Mon Sep 17 00:00:00 2001
From: houzj <houzj.fnst@cn.fujitsu.com>
Date: Thu, 18 Feb 2021 11:17:35 +0800
Subject: [PATCH] reloption parallel parallel_dml_enabled test and doc

Test and documentation updates for reloption parallel_dml_enabled.
---
 doc/src/sgml/ref/alter_table.sgml             |  2 +-
 doc/src/sgml/ref/create_table.sgml            | 27 +++++++++++++++++
 src/test/regress/expected/insert_parallel.out | 42 ++++++++++++++++++++++++---
 src/test/regress/sql/insert_parallel.sql      | 34 +++++++++++++++++++---
 4 files changed, 96 insertions(+), 9 deletions(-)

diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index c25ef5a..ecb0470 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -722,7 +722,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
      <para>
       <literal>SHARE UPDATE EXCLUSIVE</literal> lock will be taken for
       fillfactor, toast and autovacuum storage parameters, as well as the
-      planner parameter <varname>parallel_workers</varname>.
+      planner parameter <varname>parallel_workers</varname> and <varname>parallel_dml_enabled</varname>.
      </para>
     </listitem>
    </varlistentry>
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 569f4c9..f402174 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -1408,6 +1408,33 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
     </listitem>
    </varlistentry>
 
+   <varlistentry id="reloption-parallel-dml-enabled" xreflabel="parallel_dml_enabled">
+    <term><literal>parallel_dml_enabled</literal> (<type>boolean</type>)
+     <indexterm>
+     <primary><varname>parallel_dml_enabled</varname> storage parameter</primary>
+    </indexterm>
+    </term>
+    <listitem>
+     <para>
+      Enables or disables the query planner's use of parallel DML for
+      this table. When enabled (and provided that
+      <xref linkend="guc-enable-parallel-dml"/> is also <literal>true</literal>),
+      the planner performs additional parallel-safety checks on the table's
+      attributes and indexes, in order to determine if it's safe to use a
+      parallel plan for table-modification. The default is
+      <literal>true</literal>.
+      In cases such as when the table has a large number of partitions, and
+      particularly also when that table uses a parallel-unsafe feature that
+      prevents parallelism, the overhead of these checks may become prohibitively
+      high. To address this potential overhead in these cases, this option can be
+      used to disable the use of parallel DML for this table.
+      Note that if the target table of the parallel DML is partitioned, the
+      <literal>parallel_dml_enabled</literal> option values of the partitions are
+      ignored.
+     </para>
+    </listitem>
+   </varlistentry>
+
    <varlistentry id="reloption-autovacuum-enabled" xreflabel="autovacuum_enabled">
     <term><literal>autovacuum_enabled</literal>, <literal>toast.autovacuum_enabled</literal> (<type>boolean</type>)
     <indexterm>
diff --git a/src/test/regress/expected/insert_parallel.out b/src/test/regress/expected/insert_parallel.out
index 73330ef..997d8b5 100644
--- a/src/test/regress/expected/insert_parallel.out
+++ b/src/test/regress/expected/insert_parallel.out
@@ -70,13 +70,13 @@ set max_parallel_workers_per_gather=4;
 create table para_insert_p1 (
 	unique1		int4	PRIMARY KEY,
 	stringu1	name
-);
+) with (parallel_dml_enabled = off);
 create table para_insert_f1 (
 	unique1		int4	REFERENCES para_insert_p1(unique1),
 	stringu1	name
 );
 --
--- Test INSERT with underlying query when enable_parallel_dml=off.
+-- Test INSERT with underlying query when enable_parallel_dml=off and reloption.parallel_dml_enabled=off.
 -- (should create plan with serial INSERT + SELECT)
 --
 explain(costs off) insert into para_insert_p1 select unique1, stringu1 from tenk1;
@@ -87,10 +87,25 @@ explain(costs off) insert into para_insert_p1 select unique1, stringu1 from tenk
 (2 rows)
 
 --
--- Enable parallel dml
+-- Enable guc option enable_parallel_dml
 --
 set enable_parallel_dml = on;
 --
+-- Test INSERT with underlying query when enable_parallel_dml=on and reloption.parallel_dml_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_dml_enabled
+--
+alter table para_insert_p1 set (parallel_dml_enabled = on);
+--
 -- Test INSERT with underlying query.
 -- (should create plan with parallel INSERT+SELECT, Gather parent node)
 --
@@ -868,9 +883,28 @@ truncate testdef;
 --
 -- Test INSERT into partition with underlying query.
 --
-create table parttable1 (a int, b name) partition by range (a);
+create table parttable1 (a int, b name) partition by range (a) with (parallel_dml_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_dml_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_dml_enabled
+--
+alter table parttable1 set (parallel_dml_enabled = on);
+--
+-- Test INSERT into partition when reloption.parallel_dml_enabled=off
+-- (should create a parallel plan)
+--
 explain (costs off) insert into parttable1 select unique1,stringu1 from tenk1;
                QUERY PLAN               
 ----------------------------------------
diff --git a/src/test/regress/sql/insert_parallel.sql b/src/test/regress/sql/insert_parallel.sql
index eba30d2..8cb439b 100644
--- a/src/test/regress/sql/insert_parallel.sql
+++ b/src/test/regress/sql/insert_parallel.sql
@@ -87,7 +87,7 @@ set max_parallel_workers_per_gather=4;
 create table para_insert_p1 (
 	unique1		int4	PRIMARY KEY,
 	stringu1	name
-);
+) with (parallel_dml_enabled = off);
 
 create table para_insert_f1 (
 	unique1		int4	REFERENCES para_insert_p1(unique1),
@@ -95,17 +95,28 @@ create table para_insert_f1 (
 );
 
 --
--- Test INSERT with underlying query when enable_parallel_dml=off.
+-- Test INSERT with underlying query when enable_parallel_dml=off and reloption.parallel_dml_enabled=off.
 -- (should create plan with serial INSERT + SELECT)
 --
 explain(costs off) insert into para_insert_p1 select unique1, stringu1 from tenk1;
 
 --
--- Enable parallel dml
+-- Enable guc option enable_parallel_dml
 --
 set enable_parallel_dml = on;
 
 --
+-- Test INSERT with underlying query when enable_parallel_dml=on and reloption.parallel_dml_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_dml_enabled
+--
+alter table para_insert_p1 set (parallel_dml_enabled = on);
+
+--
 -- Test INSERT with underlying query.
 -- (should create plan with parallel INSERT+SELECT, Gather parent node)
 --
@@ -359,10 +370,25 @@ truncate testdef;
 --
 -- Test INSERT into partition with underlying query.
 --
-create table parttable1 (a int, b name) partition by range (a);
+create table parttable1 (a int, b name) partition by range (a) with (parallel_dml_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_dml_enabled=off
+-- (should not create a parallel plan)
+--
+explain (costs off) insert into parttable1 select unique1,stringu1 from tenk1;
+
+--
+-- Enable reloption parallel_dml_enabled
+--
+alter table parttable1 set (parallel_dml_enabled = on);
+
+--
+-- Test INSERT into partition when reloption.parallel_dml_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;
-- 
2.7.2.windows.1

From 5330f1df94b1549cef7de1a4d9be2b88a7d2866b Mon Sep 17 00:00:00 2001
From: houzj <houzj.fnst@cn.fujitsu.com>
Date: Thu, 18 Feb 2021 10:02:06 +0800
Subject: [PATCH 1/4] Add new GUC option: enable_parallel_dml (boolean)

The current implementation of parallel "INSERT ... SELECT ..." may incur
non-negligible overhead in the additional parallel-safety checks that it
performs, even when, in the end, those checks determine that parallelism
can't be used. This is normally only ever a problem for large complex tables,
particularly in the case of when the target table has a large number of
partitions.

To address this potential isse, a new GUC option "enable_parallel_dml" is
added, to allow parallel DML to be enabled/disabled.

The default is false.
---
 src/backend/optimizer/path/costsize.c         |  2 ++
 src/backend/optimizer/util/clauses.c          |  8 ++++++--
 src/backend/utils/misc/guc.c                  | 11 +++++++++++
 src/backend/utils/misc/postgresql.conf.sample |  1 +
 src/include/optimizer/cost.h                  |  1 +
 5 files changed, 21 insertions(+), 2 deletions(-)

diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c
index f7c13be..778f71b 100644
--- a/src/backend/optimizer/path/costsize.c
+++ b/src/backend/optimizer/path/costsize.c
@@ -129,6 +129,8 @@ Cost		disable_cost = 1.0e10;
 
 int			max_parallel_workers_per_gather = 2;
 
+bool		enable_parallel_dml = false;
+
 bool		enable_seqscan = true;
 bool		enable_indexscan = true;
 bool		enable_indexonlyscan = true;
diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index de16a28..eb6eee5 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -1219,8 +1219,9 @@ target_rel_max_parallel_hazard_recurse(Relation rel,
  * table-modification statement.
  * It's not possible in the following cases:
  *
- *  1) INSERT...ON CONFLICT...DO UPDATE
- *  2) INSERT without SELECT
+ *  1) enable_parallel_dml is off
+ *  2) INSERT...ON CONFLICT...DO UPDATE
+ *  3) INSERT without SELECT
  *
  * (Note: we don't do in-depth parallel-safety checks here, we do only the
  * cheaper tests that can quickly exclude obvious cases for which
@@ -1236,6 +1237,9 @@ is_parallel_possible_for_modify(Query *parse)
 
 	Assert(IsModifySupportedInParallelMode(parse->commandType));
 
+	if (!enable_parallel_dml)
+		return false;
+
 	/*
 	 * UPDATE is not currently supported in parallel-mode, so prohibit
 	 * INSERT...ON CONFLICT...DO UPDATE...
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index eafdb11..6fdb991 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -2048,6 +2048,17 @@ static struct config_bool ConfigureNamesBool[] =
 		NULL, NULL, NULL
 	},
 
+	{
+		{"enable_parallel_dml", PGC_USERSET, QUERY_TUNING_METHOD,
+			gettext_noop("Enables the planner's use of parallel plans for table-modification commands."),
+			NULL,
+			GUC_EXPLAIN
+		},
+		&enable_parallel_dml,
+		false,
+		NULL, NULL, NULL
+	},
+
 	/* End-of-list marker */
 	{
 		{NULL, 0, 0, NULL, NULL}, NULL, false, NULL, NULL, NULL
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index db6db37..fdd1fce 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -370,6 +370,7 @@
 #enable_partitionwise_aggregate = off
 #enable_parallel_hash = on
 #enable_partition_pruning = on
+#enable_parallel_dml = off
 
 # - Planner Cost Constants -
 
diff --git a/src/include/optimizer/cost.h b/src/include/optimizer/cost.h
index 9f15fcb..4af0beb 100644
--- a/src/include/optimizer/cost.h
+++ b/src/include/optimizer/cost.h
@@ -47,6 +47,7 @@ typedef enum
 /* parameter variables and flags (see also optimizer.h) */
 extern PGDLLIMPORT Cost disable_cost;
 extern PGDLLIMPORT int max_parallel_workers_per_gather;
+extern PGDLLIMPORT bool enable_parallel_dml;
 extern PGDLLIMPORT bool enable_seqscan;
 extern PGDLLIMPORT bool enable_indexscan;
 extern PGDLLIMPORT bool enable_indexonlyscan;
-- 
2.7.2.windows.1

From b0db4d1cf66a5b98ba6627ff26bf8c70a3fcfbce Mon Sep 17 00:00:00 2001
From: houzj <houzj.fnst@cn.fujitsu.com>
Date: Thu, 18 Feb 2021 10:12:08 +0800
Subject: [PATCH 2/4] guc option enable_parallel_dml test and doc

Test and documentation updates for guc option enable_parallel_dml.
---
 doc/src/sgml/config.sgml                      | 23 +++++++++++++++++++++++
 src/test/regress/expected/insert_parallel.out | 15 +++++++++++++++
 src/test/regress/expected/sysviews.out        |  3 ++-
 src/test/regress/sql/insert_parallel.sql      | 10 ++++++++++
 4 files changed, 50 insertions(+), 1 deletion(-)

diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 4df1405..5b93df4 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -5026,6 +5026,29 @@ ANY <replaceable class="parameter">num_sync</replaceable> ( <replaceable class="
       </listitem>
      </varlistentry>
 
+     <varlistentry id="guc-enable-parallel-dml" xreflabel="enable_parallel_dml">
+      <term><varname>enable_parallel_dml</varname> (<type>boolean</type>)
+      <indexterm>
+       <primary><varname>enable_parallel_dml</varname> configuration parameter</primary>
+      </indexterm>
+      </term>
+      <listitem>
+       <para>
+        Enables or disables the query planner's use of parallel plans for
+        table-modification commands. The default is <literal>off</literal>.
+        When enabled, the planner performs additional parallel-safety checks
+        on the target table's attributes and indexes, in order to determine
+        if it's safe to use a parallel plan for table-modification. In cases
+        such as when the target table has a large number of partitions, and
+        particularly also when that table uses something parallel-unsafe that
+        prevents parallelism, the overhead of these checks may become
+        prohibitively high. To address this potential overhead in these cases,
+        this option can be used to disable the use of parallel plans for
+        table-modification.
+       </para>
+      </listitem>
+     </varlistentry>
+
      </variablelist>
      </sect2>
      <sect2 id="runtime-config-query-constants">
diff --git a/src/test/regress/expected/insert_parallel.out b/src/test/regress/expected/insert_parallel.out
index a5a309a..73330ef 100644
--- a/src/test/regress/expected/insert_parallel.out
+++ b/src/test/regress/expected/insert_parallel.out
@@ -76,6 +76,21 @@ create table para_insert_f1 (
 	stringu1	name
 );
 --
+-- Test INSERT with underlying query when enable_parallel_dml=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 parallel dml
+--
+set enable_parallel_dml = on;
+--
 -- Test INSERT with underlying query.
 -- (should create plan with parallel INSERT+SELECT, Gather parent node)
 --
diff --git a/src/test/regress/expected/sysviews.out b/src/test/regress/expected/sysviews.out
index 81bdacf..3f11216 100644
--- a/src/test/regress/expected/sysviews.out
+++ b/src/test/regress/expected/sysviews.out
@@ -99,6 +99,7 @@ select name, setting from pg_settings where name like 'enable%';
  enable_mergejoin               | on
  enable_nestloop                | on
  enable_parallel_append         | on
+ enable_parallel_dml            | off
  enable_parallel_hash           | on
  enable_partition_pruning       | on
  enable_partitionwise_aggregate | off
@@ -106,7 +107,7 @@ select name, setting from pg_settings where name like 'enable%';
  enable_seqscan                 | on
  enable_sort                    | on
  enable_tidscan                 | on
-(18 rows)
+(19 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/sql/insert_parallel.sql b/src/test/regress/sql/insert_parallel.sql
index 9183032..eba30d2 100644
--- a/src/test/regress/sql/insert_parallel.sql
+++ b/src/test/regress/sql/insert_parallel.sql
@@ -94,6 +94,16 @@ create table para_insert_f1 (
 	stringu1	name
 );
 
+--
+-- Test INSERT with underlying query when enable_parallel_dml=off.
+-- (should create plan with serial INSERT + SELECT)
+--
+explain(costs off) insert into para_insert_p1 select unique1, stringu1 from tenk1;
+
+--
+-- Enable parallel dml
+--
+set enable_parallel_dml = on;
 
 --
 -- Test INSERT with underlying query.
-- 
2.7.2.windows.1

From 72e82f202c43aa272fdd7f345bb9173fc38eef23 Mon Sep 17 00:00:00 2001
From: houzj <houzj.fnst@cn.fujitsu.com>
Date: Mon, 22 Feb 2021 11:04:46 +0800
Subject: [PATCH] reloption parallel_dml_enabled

Add new table option parallel_dml_enabled.

In addition to the GUC option, the user may want a mechanism for
specifying parallel dml with finer granularity, to specify the
use of parallel dml for specific tables.
The new table option parallel_dml_enabled allows this.

The default is true.
---
 src/backend/access/common/reloptions.c | 25 +++++++++++++++++++------
 src/backend/optimizer/util/clauses.c   | 21 ++++++++++++++++++++-
 src/bin/psql/tab-complete.c            |  1 +
 src/include/utils/rel.h                | 23 +++++++++++++++++++++++
 4 files changed, 63 insertions(+), 7 deletions(-)

diff --git a/src/backend/access/common/reloptions.c b/src/backend/access/common/reloptions.c
index c687d3e..938131a 100644
--- a/src/backend/access/common/reloptions.c
+++ b/src/backend/access/common/reloptions.c
@@ -168,6 +168,15 @@ static relopt_bool boolRelOpts[] =
 		},
 		true
 	},
+	{
+		{
+			"parallel_dml_enabled",
+			"Enables \"parallel dml\" feature for this table",
+			RELOPT_KIND_HEAP | RELOPT_KIND_PARTITIONED,
+			ShareUpdateExclusiveLock
+		},
+		true
+	},
 	/* list terminator */
 	{{NULL}}
 };
@@ -1859,7 +1868,9 @@ default_reloptions(Datum reloptions, bool validate, relopt_kind kind)
 		{"vacuum_index_cleanup", RELOPT_TYPE_BOOL,
 		offsetof(StdRdOptions, vacuum_index_cleanup)},
 		{"vacuum_truncate", RELOPT_TYPE_BOOL,
-		offsetof(StdRdOptions, vacuum_truncate)}
+		offsetof(StdRdOptions, vacuum_truncate)},
+		{"parallel_dml_enabled", RELOPT_TYPE_BOOL,
+		offsetof(StdRdOptions, parallel_dml_enabled)}
 	};
 
 	return (bytea *) build_reloptions(reloptions, validate, kind,
@@ -1961,13 +1972,15 @@ build_local_reloptions(local_relopts *relopts, Datum options, bool validate)
 bytea *
 partitioned_table_reloptions(Datum reloptions, bool validate)
 {
-	/*
-	 * There are no options for partitioned tables yet, but this is able to do
-	 * some validation.
-	 */
+	static const relopt_parse_elt tab[] = {
+		{"parallel_dml_enabled", RELOPT_TYPE_BOOL,
+		offsetof(PartitionedOptions, parallel_dml_enabled)}
+	};
+
 	return (bytea *) build_reloptions(reloptions, validate,
 									  RELOPT_KIND_PARTITIONED,
-									  0, NULL, 0);
+									  sizeof(PartitionedOptions),
+									  tab, lengthof(tab));
 }
 
 /*
diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c
index 71ee33d..5e82819 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -1225,6 +1225,7 @@ target_rel_max_parallel_hazard_recurse(Relation rel,
  *  1) enable_parallel_dml is off
  *  2) INSERT...ON CONFLICT...DO UPDATE
  *  3) INSERT without SELECT
+ *  4) the reloption parallel_dml_enabled is set to off
  *
  * (Note: we don't do in-depth parallel-safety checks here, we do only the
  * cheaper tests that can quickly exclude obvious cases for which
@@ -1235,8 +1236,10 @@ bool
 is_parallel_possible_for_modify(Query *parse)
 {
 	bool			hasSubQuery;
+	bool			parallel_enabled;
 	RangeTblEntry  *rte;
 	ListCell	   *lc;
+	Relation		rel;
 
 	Assert(IsModifySupportedInParallelMode(parse->commandType));
 
@@ -1272,7 +1275,23 @@ is_parallel_possible_for_modify(Query *parse)
 		}
 	}
 
-	return hasSubQuery;
+	if(!hasSubQuery)
+		return false;
+
+	/*
+	 * Check if parallel_dml_enabled is enabled for the target table,
+	 * if not, skip the safety checks.
+	 *
+	 * (Note: if the target table is partitioned, the parallel_dml_enabled
+	 * option setting of the partitions are ignored).
+	 */
+	rte = rt_fetch(parse->resultRelation, parse->rtable);
+	rel = table_open(rte->relid, NoLock);
+
+	parallel_enabled = RelationGetParallelDML(rel, true);
+	table_close(rel, NoLock);
+
+	return parallel_enabled;
 }
 
 /*****************************************************************************
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index b64db82..ee7f48c 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1110,6 +1110,7 @@ static const char *const table_storage_parameters[] = {
 	"autovacuum_vacuum_threshold",
 	"fillfactor",
 	"log_autovacuum_min_duration",
+	"parallel_dml_enabled",
 	"parallel_workers",
 	"toast.autovacuum_enabled",
 	"toast.autovacuum_freeze_max_age",
diff --git a/src/include/utils/rel.h b/src/include/utils/rel.h
index 2a41a00..1197ae2 100644
--- a/src/include/utils/rel.h
+++ b/src/include/utils/rel.h
@@ -307,6 +307,7 @@ typedef struct StdRdOptions
 	int			parallel_workers;	/* max number of parallel workers */
 	bool		vacuum_index_cleanup;	/* enables index vacuuming and cleanup */
 	bool		vacuum_truncate;	/* enables vacuum to truncate a relation */
+	bool		parallel_dml_enabled;	/* enables planner's use of parallel DML */
 } StdRdOptions;
 
 #define HEAP_MIN_FILLFACTOR			10
@@ -425,6 +426,28 @@ typedef struct ViewOptions
 	  VIEW_OPTION_CHECK_OPTION_CASCADED)
 
 /*
+ * PartitionedOptions
+ *		Contents of rd_options for partitioned tables
+ */
+typedef struct PartitionedOptions
+{
+	int32		vl_len_;		/* varlena header (do not touch directly!) */
+	bool		parallel_dml_enabled;	/* enables planner's use of parallel DML */
+} PartitionedOptions;
+
+/*
+ * RelationGetParallelDML
+ *		Returns the relation's parallel_dml_enabled reloption setting.
+ *		Note multiple eval of argument!
+ */
+#define RelationGetParallelDML(relation, defaultpd) 						\
+	((relation)->rd_options ?												\
+	 (relation->rd_rel->relkind == RELKIND_PARTITIONED_TABLE ?				\
+	 ((PartitionedOptions *) (relation)->rd_options)->parallel_dml_enabled :\
+	 ((StdRdOptions *) (relation)->rd_options)->parallel_dml_enabled) :		\
+	 (defaultpd))
+
+/*
  * RelationIsValid
  *		True iff relation descriptor is valid.
  */
-- 
2.7.2.windows.1