Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commite9baa5e

Browse files
committed
Allow DML commands that create tables to use parallel query.
Haribabu Kommi, reviewed by Dilip Kumar and Rafia Sabih. Variouscosmetic changes by me to explain why this appears to be safe butallowing inserts in parallel mode in general wouldn't be. Also, Iremoved the REFRESH MATERIALIZED VIEW case from Haribabu's patch,since I'm not convinced that case is OK, and hacked on thedocumentation somewhat.Discussion:http://postgr.es/m/CAJrrPGdo5bak6qnPWe8Kpi8g_jfQEs-G4SYmG9y+OFaw2-dPvA@mail.gmail.com
1 parent4d85c29 commite9baa5e

File tree

10 files changed

+151
-28
lines changed

10 files changed

+151
-28
lines changed

‎doc/src/sgml/parallel.sgml‎

Lines changed: 4 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -151,9 +151,10 @@ EXPLAIN SELECT * FROM pgbench_accounts WHERE filler LIKE '%x%';
151151
<para>
152152
The query writes any data or locks any database rows. If a query
153153
contains a data-modifying operation either at the top level or within
154-
a CTE, no parallel plans for that query will be generated. This is a
155-
limitation of the current implementation which could be lifted in a
156-
future release.
154+
a CTE, no parallel plans for that query will be generated. As an
155+
exception, the commands <literal>CREATE TABLE</>, <literal>SELECT
156+
INTO</>, and <literal>CREATE MATERIALIZED VIEW</> which create a new
157+
table and populate it can use a parallel plan.
157158
</para>
158159
</listitem>
159160

@@ -241,15 +242,6 @@ EXPLAIN SELECT * FROM pgbench_accounts WHERE filler LIKE '%x%';
241242
</para>
242243
</listitem>
243244

244-
<listitem>
245-
<para>
246-
A prepared statement is executed using a <literal>CREATE TABLE .. AS
247-
EXECUTE ..</literal> statement. This construct converts what otherwise
248-
would have been a read-only operation into a read-write operation,
249-
making it ineligible for parallel query.
250-
</para>
251-
</listitem>
252-
253245
<listitem>
254246
<para>
255247
The transaction isolation level is serializable. This situation

‎src/backend/access/heap/heapam.c‎

Lines changed: 9 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -2598,15 +2598,17 @@ heap_prepare_insert(Relation relation, HeapTuple tup, TransactionId xid,
25982598
CommandIdcid,intoptions)
25992599
{
26002600
/*
2601-
* For now, parallel operations are required to be strictly read-only.
2602-
* Unlike heap_update() and heap_delete(), an insert should never create a
2603-
* combo CID, so it might be possible to relax this restriction, but not
2604-
* without more thought and testing.
2605-
*/
2606-
if (IsInParallelMode())
2601+
* Parallel operations are required to be strictly read-only in a parallel
2602+
* worker. Parallel inserts are not safe even in the leader in the
2603+
* general case, because group locking means that heavyweight locks for
2604+
* relation extension or GIN page locks will not conflict between members
2605+
* of a lock group, but we don't prohibit that case here because there are
2606+
* useful special cases that we can safely allow, such as CREATE TABLE AS.
2607+
*/
2608+
if (IsParallelWorker())
26072609
ereport(ERROR,
26082610
(errcode(ERRCODE_INVALID_TRANSACTION_STATE),
2609-
errmsg("cannot insert tuplesduring a paralleloperation")));
2611+
errmsg("cannot insert tuplesin a parallelworker")));
26102612

26112613
if (relation->rd_rel->relhasoids)
26122614
{

‎src/backend/commands/createas.c‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -326,8 +326,8 @@ ExecCreateTableAs(CreateTableAsStmt *stmt, const char *queryString,
326326
query=linitial_node(Query,rewritten);
327327
Assert(query->commandType==CMD_SELECT);
328328

329-
/* plan the query--- note we disallow parallelism*/
330-
plan=pg_plan_query(query,0,params);
329+
/* plan the query */
330+
plan=pg_plan_query(query,CURSOR_OPT_PARALLEL_OK,params);
331331

332332
/*
333333
* Use a snapshot with an updated command ID to ensure this query sees

‎src/backend/commands/explain.c‎

Lines changed: 1 addition & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -396,16 +396,14 @@ ExplainOneUtility(Node *utilityStmt, IntoClause *into, ExplainState *es,
396396
* We have to rewrite the contained SELECT and then pass it back to
397397
* ExplainOneQuery. It's probably not really necessary to copy the
398398
* contained parsetree another time, but let's be safe.
399-
*
400-
* Like ExecCreateTableAs, disallow parallelism in the plan.
401399
*/
402400
CreateTableAsStmt*ctas= (CreateTableAsStmt*)utilityStmt;
403401
List*rewritten;
404402

405403
rewritten=QueryRewrite(castNode(Query,copyObject(ctas->query)));
406404
Assert(list_length(rewritten)==1);
407405
ExplainOneQuery(linitial_node(Query,rewritten),
408-
0,ctas->into,es,
406+
CURSOR_OPT_PARALLEL_OK,ctas->into,es,
409407
queryString,params,queryEnv);
410408
}
411409
elseif (IsA(utilityStmt,DeclareCursorStmt))

‎src/backend/executor/execMain.c‎

Lines changed: 2 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1697,11 +1697,9 @@ ExecutePlan(EState *estate,
16971697

16981698
/*
16991699
* If the plan might potentially be executed multiple times, we must force
1700-
* it to run without parallelism, because we might exit early. Also
1701-
* disable parallelism when writing into a relation, because no database
1702-
* changes are allowed in parallel mode.
1700+
* it to run without parallelism, because we might exit early.
17031701
*/
1704-
if (!execute_once||dest->mydest==DestIntoRel)
1702+
if (!execute_once)
17051703
use_parallel_mode= false;
17061704

17071705
if (use_parallel_mode)

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

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -257,6 +257,16 @@ standard_planner(Query *parse, int cursorOptions, ParamListInfo boundParams)
257257
* to values that don't permit parallelism, or if parallel-unsafe
258258
* functions are present in the query tree.
259259
*
260+
* (Note that we do allow CREATE TABLE AS, SELECT INTO, and CREATE
261+
* MATERIALIZED VIEW to use parallel plans, but this is safe only because
262+
* the command is writing into a completely new table which workers won't
263+
* be able to see. If the workers could see the table, the fact that
264+
* group locking would cause them to ignore the leader's heavyweight
265+
* relation extension lock and GIN page locks would make this unsafe.
266+
* We'll have to fix that somehow if we want to allow parallel inserts in
267+
* general; updates and deletes have additional problems especially around
268+
* combo CIDs.)
269+
*
260270
* For now, we don't try to use parallel mode if we're running inside a
261271
* parallel worker. We might eventually be able to relax this
262272
* restriction, but for now it seems best not to have parallel workers
Lines changed: 79 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,79 @@
1+
--
2+
-- PARALLEL
3+
--
4+
-- Serializable isolation would disable parallel query, so explicitly use an
5+
-- arbitrary other level.
6+
begin isolation level repeatable read;
7+
-- encourage use of parallel plans
8+
set parallel_setup_cost=0;
9+
set parallel_tuple_cost=0;
10+
set min_parallel_table_scan_size=0;
11+
set max_parallel_workers_per_gather=4;
12+
--
13+
-- Test write operations that has an underlying query that is eligble
14+
-- for parallel plans
15+
--
16+
explain (costs off) create table parallel_write as
17+
select length(stringu1) from tenk1 group by length(stringu1);
18+
QUERY PLAN
19+
---------------------------------------------------
20+
Finalize HashAggregate
21+
Group Key: (length((stringu1)::text))
22+
-> Gather
23+
Workers Planned: 4
24+
-> Partial HashAggregate
25+
Group Key: length((stringu1)::text)
26+
-> Parallel Seq Scan on tenk1
27+
(7 rows)
28+
29+
create table parallel_write as
30+
select length(stringu1) from tenk1 group by length(stringu1);
31+
drop table parallel_write;
32+
explain (costs off) select length(stringu1) into parallel_write
33+
from tenk1 group by length(stringu1);
34+
QUERY PLAN
35+
---------------------------------------------------
36+
Finalize HashAggregate
37+
Group Key: (length((stringu1)::text))
38+
-> Gather
39+
Workers Planned: 4
40+
-> Partial HashAggregate
41+
Group Key: length((stringu1)::text)
42+
-> Parallel Seq Scan on tenk1
43+
(7 rows)
44+
45+
select length(stringu1) into parallel_write
46+
from tenk1 group by length(stringu1);
47+
drop table parallel_write;
48+
explain (costs off) create materialized view parallel_mat_view as
49+
select length(stringu1) from tenk1 group by length(stringu1);
50+
QUERY PLAN
51+
---------------------------------------------------
52+
Finalize HashAggregate
53+
Group Key: (length((stringu1)::text))
54+
-> Gather
55+
Workers Planned: 4
56+
-> Partial HashAggregate
57+
Group Key: length((stringu1)::text)
58+
-> Parallel Seq Scan on tenk1
59+
(7 rows)
60+
61+
create materialized view parallel_mat_view as
62+
select length(stringu1) from tenk1 group by length(stringu1);
63+
drop materialized view parallel_mat_view;
64+
prepare prep_stmt as select length(stringu1) from tenk1 group by length(stringu1);
65+
explain (costs off) create table parallel_write as execute prep_stmt;
66+
QUERY PLAN
67+
---------------------------------------------------
68+
Finalize HashAggregate
69+
Group Key: (length((stringu1)::text))
70+
-> Gather
71+
Workers Planned: 4
72+
-> Partial HashAggregate
73+
Group Key: length((stringu1)::text)
74+
-> Parallel Seq Scan on tenk1
75+
(7 rows)
76+
77+
create table parallel_write as execute prep_stmt;
78+
drop table parallel_write;
79+
rollback;

‎src/test/regress/parallel_schedule‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -96,6 +96,7 @@ test: rules psql_crosstab amutils
9696

9797
# run by itself so it can run parallel workers
9898
test: select_parallel
99+
test: write_parallel
99100

100101
# no relation related tests can be put in this group
101102
test: publication subscription

‎src/test/regress/serial_schedule‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -134,6 +134,7 @@ test: stats_ext
134134
test: rules
135135
test: psql_crosstab
136136
test: select_parallel
137+
test: write_parallel
137138
test: publication
138139
test: subscription
139140
test: amutils
Lines changed: 42 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,42 @@
1+
--
2+
-- PARALLEL
3+
--
4+
5+
-- Serializable isolation would disable parallel query, so explicitly use an
6+
-- arbitrary other level.
7+
begin isolation level repeatable read;
8+
9+
-- encourage use of parallel plans
10+
set parallel_setup_cost=0;
11+
set parallel_tuple_cost=0;
12+
set min_parallel_table_scan_size=0;
13+
set max_parallel_workers_per_gather=4;
14+
15+
--
16+
-- Test write operations that has an underlying query that is eligble
17+
-- for parallel plans
18+
--
19+
explain (costs off) create table parallel_writeas
20+
select length(stringu1)from tenk1group by length(stringu1);
21+
createtableparallel_writeas
22+
select length(stringu1)from tenk1group by length(stringu1);
23+
droptable parallel_write;
24+
25+
explain (costs off)select length(stringu1) into parallel_write
26+
from tenk1group by length(stringu1);
27+
select length(stringu1) into parallel_write
28+
from tenk1group by length(stringu1);
29+
droptable parallel_write;
30+
31+
explain (costs off) create materialized view parallel_mat_viewas
32+
select length(stringu1)from tenk1group by length(stringu1);
33+
create materialized view parallel_mat_viewas
34+
select length(stringu1)from tenk1group by length(stringu1);
35+
drop materialized view parallel_mat_view;
36+
37+
prepare prep_stmtasselect length(stringu1)from tenk1group by length(stringu1);
38+
explain (costs off) create table parallel_writeas execute prep_stmt;
39+
createtableparallel_writeas execute prep_stmt;
40+
droptable parallel_write;
41+
42+
rollback;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp