Skip to content

Commit 26acb54

Browse files
author
Amit Kapila
committed
Revert "Enable parallel SELECT for "INSERT INTO ... SELECT ..."."
To allow inserts in parallel-mode this feature has to ensure that all the constraints, triggers, etc. are parallel-safe for the partition hierarchy which is costly and we need to find a better way to do that. Additionally, we could have used existing cached information in some cases like indexes, domains, etc. to determine the parallel-safety. List of commits reverted, in reverse chronological order: ed62d37 Doc: Update description for parallel insert reloption. c8f78b6 Add a new GUC and a reloption to enable inserts in parallel-mode. c5be48f Improve FK trigger parallel-safety check added by 05c8482. e2cda3c Fix use of relcache TriggerDesc field introduced by commit 05c8482. e4e87a3 Fix valgrind issue in commit 05c8482. 05c8482 Enable parallel SELECT for "INSERT INTO ... SELECT ...". Discussion: https://postgr.es/m/E1lMiB9-0001c3-SY@gemulon.postgresql.org
1 parent 8400704 commit 26acb54

31 files changed

+35
-1761
lines changed

‎doc/src/sgml/config.sgml

Lines changed: 0 additions & 23 deletions
Original file line numberDiff line numberDiff line change
@@ -5072,29 +5072,6 @@ ANY <replaceable class="parameter">num_sync</replaceable> ( <replaceable class="
50725072
</listitem>
50735073
</varlistentry>
50745074

5075-
<varlistentry id="guc-enable-parallel-insert" xreflabel="enable_parallel_insert">
5076-
<term><varname>enable_parallel_insert</varname> (<type>boolean</type>)
5077-
<indexterm>
5078-
<primary><varname>enable_parallel_insert</varname> configuration parameter</primary>
5079-
</indexterm>
5080-
</term>
5081-
<listitem>
5082-
<para>
5083-
Enables or disables the query planner's use of parallel plans for
5084-
<command>INSERT</command> commands. The default is <literal>on</literal>.
5085-
When enabled, the planner performs additional parallel-safety checks
5086-
on the target table's attributes and indexes, in order to determine
5087-
if it's safe to use a parallel plan for <command>INSERT</command>. In
5088-
cases such as when the target table has a large number of partitions,
5089-
and particularly also when that table uses something parallel-unsafe
5090-
that prevents parallelism, the overhead of these checks may become
5091-
prohibitively high. To address this potential overhead in these cases,
5092-
this option can be used to disable the use of parallel plans for
5093-
<command>INSERT</command>.
5094-
</para>
5095-
</listitem>
5096-
</varlistentry>
5097-
50985075
</variablelist>
50995076
</sect2>
51005077
<sect2 id="runtime-config-query-constants">

‎doc/src/sgml/parallel.sgml

Lines changed: 0 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -155,9 +155,6 @@ EXPLAIN SELECT * FROM pgbench_accounts WHERE filler LIKE '%x%';
155155
<listitem>
156156
<para><command>SELECT INTO</command></para>
157157
</listitem>
158-
<listitem>
159-
<para><command>INSERT INTO ... SELECT</command></para>
160-
</listitem>
161158
<listitem>
162159
<para><command>CREATE MATERIALIZED VIEW</command></para>
163160
</listitem>

‎doc/src/sgml/ref/alter_table.sgml

Lines changed: 1 addition & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -738,8 +738,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
738738
<para>
739739
<literal>SHARE UPDATE EXCLUSIVE</literal> lock will be taken for
740740
fillfactor, toast and autovacuum storage parameters, as well as the
741-
planner parameters <varname>parallel_workers</varname> and
742-
<varname>parallel_insert_enabled</varname>.
741+
planner parameter <varname>parallel_workers</varname>.
743742
</para>
744743
</listitem>
745744
</varlistentry>

‎doc/src/sgml/ref/create_table.sgml

Lines changed: 2 additions & 29 deletions
Original file line numberDiff line numberDiff line change
@@ -1369,9 +1369,8 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
13691369
If a table parameter value is set and the
13701370
equivalent <literal>toast.</literal> parameter is not, the TOAST table
13711371
will use the table's parameter value.
1372-
These parameters, with the exception of
1373-
<literal>parallel_insert_enabled</literal>, are not supported on partitioned
1374-
tables, but may be specified for individual leaf partitions.
1372+
Specifying these parameters for partitioned tables is not supported,
1373+
but you may specify them for individual leaf partitions.
13751374
</para>
13761375

13771376
<variablelist>
@@ -1441,32 +1440,6 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
14411440
</listitem>
14421441
</varlistentry>
14431442

1444-
<varlistentry id="reloption-parallel-insert-enabled" xreflabel="parallel_insert_enabled">
1445-
<term><literal>parallel_insert_enabled</literal> (<type>boolean</type>)
1446-
<indexterm>
1447-
<primary><varname>parallel_insert_enabled</varname> storage parameter</primary>
1448-
</indexterm>
1449-
</term>
1450-
<listitem>
1451-
<para>
1452-
Enables or disables the query planner's use of parallel insert for
1453-
this table. When enabled (and provided that
1454-
<xref linkend="guc-enable-parallel-insert"/> is also <literal>true</literal>),
1455-
the planner performs additional parallel-safety checks on the table's
1456-
attributes and indexes, in order to determine if it's safe to use a
1457-
parallel plan for <command>INSERT</command>. The default is
1458-
<literal>true</literal>. In cases such as when the table has a large
1459-
number of partitions, and particularly also when that table uses a
1460-
parallel-unsafe feature that prevents parallelism, the overhead of these
1461-
checks may become prohibitively high. To address this potential overhead
1462-
in these cases, this option can be used to disable the use of parallel
1463-
insert for this table. Note that if the target table of the parallel
1464-
insert is partitioned, the <literal>parallel_insert_enabled</literal>
1465-
option values of the partitions are ignored.
1466-
</para>
1467-
</listitem>
1468-
</varlistentry>
1469-
14701443
<varlistentry id="reloption-autovacuum-enabled" xreflabel="autovacuum_enabled">
14711444
<term><literal>autovacuum_enabled</literal>, <literal>toast.autovacuum_enabled</literal> (<type>boolean</type>)
14721445
<indexterm>

‎src/backend/access/common/reloptions.c

Lines changed: 6 additions & 19 deletions
Original file line numberDiff line numberDiff line change
@@ -168,15 +168,6 @@ static relopt_bool boolRelOpts[] =
168168
},
169169
true
170170
},
171-
{
172-
{
173-
"parallel_insert_enabled",
174-
"Enables \"parallel insert\" feature for this table",
175-
RELOPT_KIND_HEAP | RELOPT_KIND_PARTITIONED,
176-
ShareUpdateExclusiveLock
177-
},
178-
true
179-
},
180171
/* list terminator */
181172
{{NULL}}
182173
};
@@ -1868,9 +1859,7 @@ default_reloptions(Datum reloptions, bool validate, relopt_kind kind)
18681859
{"vacuum_index_cleanup", RELOPT_TYPE_BOOL,
18691860
offsetof(StdRdOptions, vacuum_index_cleanup)},
18701861
{"vacuum_truncate", RELOPT_TYPE_BOOL,
1871-
offsetof(StdRdOptions, vacuum_truncate)},
1872-
{"parallel_insert_enabled", RELOPT_TYPE_BOOL,
1873-
offsetof(StdRdOptions, parallel_insert_enabled)}
1862+
offsetof(StdRdOptions, vacuum_truncate)}
18741863
};
18751864

18761865
return (bytea *) build_reloptions(reloptions, validate, kind,
@@ -1972,15 +1961,13 @@ build_local_reloptions(local_relopts *relopts, Datum options, bool validate)
19721961
bytea *
19731962
partitioned_table_reloptions(Datum reloptions, bool validate)
19741963
{
1975-
static const relopt_parse_elt tab[] = {
1976-
{"parallel_insert_enabled", RELOPT_TYPE_BOOL,
1977-
offsetof(PartitionedTableRdOptions, parallel_insert_enabled)}
1978-
};
1979-
1964+
/*
1965+
* There are no options for partitioned tables yet, but this is able to do
1966+
* some validation.
1967+
*/
19801968
return (bytea *) build_reloptions(reloptions, validate,
19811969
RELOPT_KIND_PARTITIONED,
1982-
sizeof(PartitionedTableRdOptions),
1983-
tab, lengthof(tab));
1970+
0, NULL, 0);
19841971
}
19851972

19861973
/*

‎src/backend/access/transam/xact.c

Lines changed: 0 additions & 26 deletions
Original file line numberDiff line numberDiff line change
@@ -1014,32 +1014,6 @@ IsInParallelMode(void)
10141014
return CurrentTransactionState->parallelModeLevel != 0;
10151015
}
10161016

1017-
/*
1018-
* PrepareParallelModePlanExec
1019-
*
1020-
* Prepare for entering parallel mode plan execution, based on command-type.
1021-
*/
1022-
void
1023-
PrepareParallelModePlanExec(CmdType commandType)
1024-
{
1025-
if (IsModifySupportedInParallelMode(commandType))
1026-
{
1027-
Assert(!IsInParallelMode());
1028-
1029-
/*
1030-
* Prepare for entering parallel mode by assigning a TransactionId.
1031-
* Failure to do this now would result in heap_insert() subsequently
1032-
* attempting to assign a TransactionId whilst in parallel-mode, which
1033-
* is not allowed.
1034-
*
1035-
* This approach has a disadvantage in that if the underlying SELECT
1036-
* does not return any rows, then the TransactionId is not used,
1037-
* however that shouldn't happen in practice in many cases.
1038-
*/
1039-
(void) GetCurrentTransactionId();
1040-
}
1041-
}
1042-
10431017
/*
10441018
* CommandCounterIncrement
10451019
*/

‎src/backend/executor/execMain.c

Lines changed: 0 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1512,10 +1512,7 @@ ExecutePlan(EState *estate,
15121512

15131513
estate->es_use_parallel_mode = use_parallel_mode;
15141514
if (use_parallel_mode)
1515-
{
1516-
PrepareParallelModePlanExec(estate->es_plannedstmt->commandType);
15171515
EnterParallelMode();
1518-
}
15191516

15201517
/*
15211518
* Loop until we've processed the proper number of tuples from the plan.

‎src/backend/nodes/copyfuncs.c

Lines changed: 0 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -96,7 +96,6 @@ _copyPlannedStmt(const PlannedStmt *from)
9696
COPY_BITMAPSET_FIELD(rewindPlanIDs);
9797
COPY_NODE_FIELD(rowMarks);
9898
COPY_NODE_FIELD(relationOids);
99-
COPY_NODE_FIELD(partitionOids);
10099
COPY_NODE_FIELD(invalItems);
101100
COPY_NODE_FIELD(paramExecTypes);
102101
COPY_NODE_FIELD(utilityStmt);

‎src/backend/nodes/outfuncs.c

Lines changed: 0 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -314,7 +314,6 @@ _outPlannedStmt(StringInfo str, const PlannedStmt *node)
314314
WRITE_BITMAPSET_FIELD(rewindPlanIDs);
315315
WRITE_NODE_FIELD(rowMarks);
316316
WRITE_NODE_FIELD(relationOids);
317-
WRITE_NODE_FIELD(partitionOids);
318317
WRITE_NODE_FIELD(invalItems);
319318
WRITE_NODE_FIELD(paramExecTypes);
320319
WRITE_NODE_FIELD(utilityStmt);
@@ -2222,7 +2221,6 @@ _outPlannerGlobal(StringInfo str, const PlannerGlobal *node)
22222221
WRITE_NODE_FIELD(resultRelations);
22232222
WRITE_NODE_FIELD(appendRelations);
22242223
WRITE_NODE_FIELD(relationOids);
2225-
WRITE_NODE_FIELD(partitionOids);
22262224
WRITE_NODE_FIELD(invalItems);
22272225
WRITE_NODE_FIELD(paramExecTypes);
22282226
WRITE_UINT_FIELD(lastPHId);

‎src/backend/nodes/readfuncs.c

Lines changed: 0 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1591,7 +1591,6 @@ _readPlannedStmt(void)
15911591
READ_BITMAPSET_FIELD(rewindPlanIDs);
15921592
READ_NODE_FIELD(rowMarks);
15931593
READ_NODE_FIELD(relationOids);
1594-
READ_NODE_FIELD(partitionOids);
15951594
READ_NODE_FIELD(invalItems);
15961595
READ_NODE_FIELD(paramExecTypes);
15971596
READ_NODE_FIELD(utilityStmt);

‎src/backend/optimizer/path/costsize.c

Lines changed: 0 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -129,8 +129,6 @@ Cost disable_cost = 1.0e10;
129129

130130
int max_parallel_workers_per_gather = 2;
131131

132-
bool enable_parallel_insert = true;
133-
134132
bool enable_seqscan = true;
135133
bool enable_indexscan = true;
136134
bool enable_indexonlyscan = true;

‎src/backend/optimizer/plan/planner.c

Lines changed: 11 additions & 26 deletions
Original file line numberDiff line numberDiff line change
@@ -305,7 +305,6 @@ standard_planner(Query *parse, const char *query_string, int cursorOptions,
305305
glob->resultRelations = NIL;
306306
glob->appendRelations = NIL;
307307
glob->relationOids = NIL;
308-
glob->partitionOids = NIL;
309308
glob->invalItems = NIL;
310309
glob->paramExecTypes = NIL;
311310
glob->lastPHId = 0;
@@ -317,16 +316,16 @@ standard_planner(Query *parse, const char *query_string, int cursorOptions,
317316
/*
318317
* Assess whether it's feasible to use parallel mode for this query. We
319318
* can't do this in a standalone backend, or if the command will try to
320-
* modify any data (except for Insert), or if this is a cursor operation,
321-
* or if GUCs are set to values that don't permit parallelism, or if
322-
* parallel-unsafe functions are present in the query tree.
319+
* modify any data, or if this is a cursor operation, or if GUCs are set
320+
* to values that don't permit parallelism, or if parallel-unsafe
321+
* functions are present in the query tree.
323322
*
324-
* (Note that we do allow CREATE TABLE AS, INSERT INTO...SELECT, SELECT
325-
* INTO, and CREATE MATERIALIZED VIEW to use parallel plans. However, as
326-
* of now, only the leader backend writes into a completely new table. In
327-
* the future, we can extend it to allow workers to write into the table.
328-
* However, to allow parallel updates and deletes, we have to solve other
329-
* problems, especially around combo CIDs.)
323+
* (Note that we do allow CREATE TABLE AS, SELECT INTO, and CREATE
324+
* MATERIALIZED VIEW to use parallel plans, but as of now, only the leader
325+
* backend writes into a completely new table. In the future, we can
326+
* extend it to allow workers to write into the table. However, to allow
327+
* parallel updates and deletes, we have to solve other problems,
328+
* especially around combo CIDs.)
330329
*
331330
* For now, we don't try to use parallel mode if we're running inside a
332331
* parallel worker. We might eventually be able to relax this
@@ -335,14 +334,13 @@ standard_planner(Query *parse, const char *query_string, int cursorOptions,
335334
*/
336335
if ((cursorOptions & CURSOR_OPT_PARALLEL_OK) != 0 &&
337336
IsUnderPostmaster &&
338-
(parse->commandType == CMD_SELECT ||
339-
is_parallel_allowed_for_modify(parse)) &&
337+
parse->commandType == CMD_SELECT &&
340338
!parse->hasModifyingCTE &&
341339
max_parallel_workers_per_gather > 0 &&
342340
!IsParallelWorker())
343341
{
344342
/* all the cheap tests pass, so scan the query tree */
345-
glob->maxParallelHazard = max_parallel_hazard(parse, glob);
343+
glob->maxParallelHazard = max_parallel_hazard(parse);
346344
glob->parallelModeOK = (glob->maxParallelHazard != PROPARALLEL_UNSAFE);
347345
}
348346
else
@@ -523,19 +521,6 @@ standard_planner(Query *parse, const char *query_string, int cursorOptions,
523521
result->rewindPlanIDs = glob->rewindPlanIDs;
524522
result->rowMarks = glob->finalrowmarks;
525523
result->relationOids = glob->relationOids;
526-
527-
/*
528-
* Register the Oids of parallel-safety-checked partitions as plan
529-
* dependencies. This is only really needed in the case of a parallel plan
530-
* so that if parallel-unsafe properties are subsequently defined on the
531-
* partitions, the cached parallel plan will be invalidated, and a
532-
* non-parallel plan will be generated.
533-
*
534-
* We also use this list to acquire locks on partitions before executing
535-
* cached plan. See AcquireExecutorLocks().
536-
*/
537-
if (glob->partitionOids != NIL && glob->parallelModeNeeded)
538-
result->partitionOids = glob->partitionOids;
539524
result->invalItems = glob->invalItems;
540525
result->paramExecTypes = glob->paramExecTypes;
541526
/* utilityStmt should be null, but we might as well copy it */

0 commit comments

Comments
 (0)