Allow parallel plan for referential integrity checks?

Started by Frédéric Yhuelalmost 4 years ago22 messages

frederic.yhuel@dalibo.com

almost 4 years ago

1 attachment(s)

Hello,

I noticed that referential integrity checks aren't currently
parallelized. Is it on purpose?

From the documentation [1]https://www.postgresql.org/docs/current/when-can-parallel-query-be-used.html, the planner will not generate a parallel
plan for a given query if any of the following are true:

1) The system is running in single-user mode.
2) max_parallel_workers_per_gather = 0.
3) The query writes any data or locks any database rows.
4) The query might be suspended during execution (cursor or PL/pgSQL loop).
5) The query uses any function marked PARALLEL UNSAFE.
6) The query is running inside of another query that is already parallel.

From my understanding of the code, it seems to me that the fourth
condition is not always accurately detected. In particular, the query
triggered by a foreign key addition (a Left Excluding JOIN between the
two tables) isn't parallelized, because the flag CURSOR_OPT_PARALLEL_OK
hasn't been set at some point. I couldn't find any reason why not, but
maybe I missed something.

Attached is a (naive) patch that aims to fix the case of a FK addition,
but the handling of the flag CURSOR_OPT_PARALLEL_OK, generally speaking,
looks rather hackish.

Best regards,
Frédéric

[1]: https://www.postgresql.org/docs/current/when-can-parallel-query-be-used.html
https://www.postgresql.org/docs/current/when-can-parallel-query-be-used.html

Attachments:

0001-Allow-parallel-plan-for-referential-integrity-checks.patchtext/x-patch; charset=UTF-8; name=0001-Allow-parallel-plan-for-referential-integrity-checks.patchDownload

From 1353a4b7e7d08b13cbaa85a5f9ae26819b5cf2c8 Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Fr=C3=A9d=C3=A9ric=20Yhuel?= <frederic.yhuel@dalibo.com>
Date: Sun, 6 Feb 2022 13:31:55 +0100
Subject: [PATCH] Allow parallel plan for referential integrity checks

---
 src/backend/utils/adt/ri_triggers.c | 7 +++++--
 1 file changed, 5 insertions(+), 2 deletions(-)

diff --git a/src/backend/utils/adt/ri_triggers.c b/src/backend/utils/adt/ri_triggers.c
index 96269fc2ad..84c16b6962 100644
--- a/src/backend/utils/adt/ri_triggers.c
+++ b/src/backend/utils/adt/ri_triggers.c
@@ -1317,6 +1317,7 @@ RI_Initial_Check(Trigger *trigger, Relation fk_rel, Relation pk_rel)
 	char		workmembuf[32];
 	int			spi_result;
 	SPIPlanPtr	qplan;
+	SPIPrepareOptions options;
 
 	riinfo = ri_FetchConstraintInfo(trigger, fk_rel, false);
 
@@ -1483,10 +1484,12 @@ RI_Initial_Check(Trigger *trigger, Relation fk_rel, Relation pk_rel)
 	 * Generate the plan.  We don't need to cache it, and there are no
 	 * arguments to the plan.
 	 */
-	qplan = SPI_prepare(querybuf.data, 0, NULL);
+	memset(&options, 0, sizeof(options));
+	options.cursorOptions = CURSOR_OPT_PARALLEL_OK;
+	qplan = SPI_prepare_extended(querybuf.data, &options);
 
 	if (qplan == NULL)
-		elog(ERROR, "SPI_prepare returned %s for %s",
+		elog(ERROR, "SPI_prepare_extended returned %s for %s",
 			 SPI_result_code_string(SPI_result), querybuf.data);
 
 	/*
-- 
2.30.2