Hi,
One of the existing limitations of parallel query is that cursors
generally do not benefit from it [1]. Commit 61c2e1a95f [2] improved the
situation for cursors from procedural languages, but unfortunately for
user-defined cursors parallelism is still disabled.
For many use cases that is perfectly fine, but for applications that
need to process large amounts of data this is rather annoying. When the
result sets are large, cursors are extremely efficient - in terms of
memory consumption, for example. So the applications have to choose
between "cursor" approach (and no parallelism), or parallelism and
uncomfortably large result sets.
I believe there are two main reasons why parallelism is disabled for
user-defined cursors (or queries that might get suspended):
(1) We can't predict what will happen while the query is suspended (and
the transaction is still in "parallel mode"), e.g. the user might run
arbitrary DML which is not allowed.
(2) If the cursor gets suspended, the parallel workers would be still
assigned to it and could not be used for anything else.
Clearly, we can't solve those issues in general, so the default will
probably remain "parallelism disabled".
I propose is to add a new cursor option (PARALLEL), which would allow
parallel plans for that particular user-defined cursor. Attached is an
experimental patch doing this (I'm sure there are some loose ends).
This does not make either any of the issues go away, of course. We still
enforce "no DML while parallel operation in progress" as before, so this
will not work:
BEGIN;
DECLARE x PARALLEL CURSOR FOR SELECT * FROM t2 WHERE ...;
FETCH 1000 FROM x;
INSERT INTO t2 VALUES (1);
FETCH 1000 FROM x;
COMMIT;
but this will
BEGIN;
DECLARE x PARALLEL CURSOR FOR SELECT * FROM t2 WHERE ...;
FETCH 1000 FROM x;
...
FETCH 1000 FROM x;
CLOSE x;
INSERT INTO t2 VALUES (1);
COMMIT;
Regarding (2), if the user suspends the cursor for a long time, bummer.
The parallel workers will remain assigned, doing nothing. I don't have
any idea how to get around that, but I don't see how we could do better.
I don't see either of these limitations as fatal.
Any opinions / obvious flaws that I missed?
regards
[1]
https://www.postgresql.org/docs/9.6/static/when-can-parallel-query-be-used.html
[2]
https://www.postgresql.org/message-id/CAOGQiiMfJ%2B4SQwgG%3D6CVHWoisiU0%2B7jtXSuiyXBM3y%3DA%3DeJzmg%40mail.gmail.com
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
diff --git a/src/backend/commands/portalcmds.c b/src/backend/commands/portalcmds.c
index 76d6cf1..ffaa096 100644
--- a/src/backend/commands/portalcmds.c
+++ b/src/backend/commands/portalcmds.c
@@ -66,6 +66,12 @@ PerformCursorOpen(DeclareCursorStmt *cstmt, ParamListInfo params,
RequireTransactionChain(isTopLevel, "DECLARE CURSOR");
/*
+ * Enable parallel plans for cursors that explicitly requested it.
+ */
+ if (cstmt->options & CURSOR_OPT_PARALLEL)
+ cstmt->options |= CURSOR_OPT_PARALLEL_OK;
+
+ /*
* Parse analysis was done already, but we still have to run the rule
* rewriter. We do not do AcquireRewriteLocks: we assume the query either
* came straight from the parser, or suitable locks were acquired by
diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c
index 9689429..64f8a32 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -423,6 +423,13 @@ standard_ExecutorFinish(QueryDesc *queryDesc)
/* This should be run once and only once per Executor instance */
Assert(!estate->es_finished);
+ /* If this was PARALLEL cursor, do cleanup and exit parallel mode. */
+ if (queryDesc->parallel_cursor)
+ {
+ ExecShutdownNode(queryDesc->planstate);
+ ExitParallelMode();
+ }
+
/* Switch into per-query memory context */
oldcontext = MemoryContextSwitchTo(estate->es_query_cxt);
@@ -1085,6 +1092,18 @@ InitPlan(QueryDesc *queryDesc, int eflags)
queryDesc->tupDesc = tupType;
queryDesc->planstate = planstate;
+
+ /* If this was PARALLEL cursor, enter parallel mode, except in EXPLAIN-only. */
+
+ queryDesc->parallel_cursor
+ = (eflags & EXEC_FLAG_PARALLEL) && !(eflags & EXEC_FLAG_EXPLAIN_ONLY);
+
+ /*
+ * In PARALLEL cursors we have to enter the parallel mode once, at the very
+ * beginning (and not in ExecutePlan, as we do for execute_once plans).
+ */
+ if (queryDesc->parallel_cursor)
+ EnterParallelMode();
}
/*
@@ -1725,7 +1744,8 @@ ExecutePlan(EState *estate,
if (TupIsNull(slot))
{
/* Allow nodes to release or shut down resources. */
- (void) ExecShutdownNode(planstate);
+ if (execute_once)
+ (void) ExecShutdownNode(planstate);
break;
}
@@ -1772,7 +1792,8 @@ ExecutePlan(EState *estate,
if (numberTuples && numberTuples == current_tuple_count)
{
/* Allow nodes to release or shut down resources. */
- (void) ExecShutdownNode(planstate);
+ if (execute_once)
+ (void) ExecShutdownNode(planstate);
break;
}
}
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 4c83a63..592881d 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -10759,6 +10759,7 @@ cursor_options: /*EMPTY*/ { $$ = 0; }
| cursor_options SCROLL { $$ = $1 | CURSOR_OPT_SCROLL; }
| cursor_options BINARY { $$ = $1 | CURSOR_OPT_BINARY; }
| cursor_options INSENSITIVE { $$ = $1 | CURSOR_OPT_INSENSITIVE; }
+ | cursor_options PARALLEL { $$ = $1 | CURSOR_OPT_PARALLEL; }
;
opt_hold: /* EMPTY */ { $$ = 0; }
diff --git a/src/backend/tcop/pquery.c b/src/backend/tcop/pquery.c
index cc462ef..9c4c9e8 100644
--- a/src/backend/tcop/pquery.c
+++ b/src/backend/tcop/pquery.c
@@ -515,6 +515,12 @@ PortalStart(Portal portal, ParamListInfo params,
myeflags = eflags;
/*
+ * If the cursor was declared as PARALLEL, enable parallel mode.
+ */
+ if (portal->cursorOptions & CURSOR_OPT_PARALLEL)
+ myeflags = myeflags | EXEC_FLAG_PARALLEL;
+
+ /*
* Call ExecutorStart to prepare the plan for execution
*/
ExecutorStart(queryDesc, myeflags);
diff --git a/src/include/executor/execdesc.h b/src/include/executor/execdesc.h
index 8c09961..af20aae 100644
--- a/src/include/executor/execdesc.h
+++ b/src/include/executor/execdesc.h
@@ -47,6 +47,7 @@ typedef struct QueryDesc
TupleDesc tupDesc; /* descriptor for result tuples */
EState *estate; /* executor's query-wide state */
PlanState *planstate; /* tree of per-plan-node state */
+ bool parallel_cursor; /* should run in parallel mode */
/* This field is set by ExecutorRun */
bool already_executed; /* true if previously executed */
diff --git a/src/include/executor/executor.h b/src/include/executor/executor.h
index c4ecf0d..768cefd 100644
--- a/src/include/executor/executor.h
+++ b/src/include/executor/executor.h
@@ -63,6 +63,7 @@
#define EXEC_FLAG_WITH_OIDS 0x0020 /* force OIDs in returned tuples */
#define EXEC_FLAG_WITHOUT_OIDS 0x0040 /* force no OIDs in returned tuples */
#define EXEC_FLAG_WITH_NO_DATA 0x0080 /* rel scannability doesn't matter */
+#define EXEC_FLAG_PARALLEL 0x0100 /* parallel cursor */
/* Hook for plugins to get control in ExecutorStart() */
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 732e5d6..034807c 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2632,11 +2632,12 @@ typedef struct SecLabelStmt
#define CURSOR_OPT_NO_SCROLL 0x0004 /* NO SCROLL explicitly given */
#define CURSOR_OPT_INSENSITIVE 0x0008 /* INSENSITIVE */
#define CURSOR_OPT_HOLD 0x0010 /* WITH HOLD */
+#define CURSOR_OPT_PARALLEL 0x0020 /* PARALLEL */
/* these planner-control flags do not correspond to any SQL grammar: */
-#define CURSOR_OPT_FAST_PLAN 0x0020 /* prefer fast-start plan */
-#define CURSOR_OPT_GENERIC_PLAN 0x0040 /* force use of generic plan */
-#define CURSOR_OPT_CUSTOM_PLAN 0x0080 /* force use of custom plan */
-#define CURSOR_OPT_PARALLEL_OK 0x0100 /* parallel mode OK */
+#define CURSOR_OPT_FAST_PLAN 0x0040 /* prefer fast-start plan */
+#define CURSOR_OPT_GENERIC_PLAN 0x0080 /* force use of generic plan */
+#define CURSOR_OPT_CUSTOM_PLAN 0x0100 /* force use of custom plan */
+#define CURSOR_OPT_PARALLEL_OK 0x0200 /* parallel mode OK */
typedef struct DeclareCursorStmt
{
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
