From 04d1271254731f9f4e878f932816832599af3d7d Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Fri, 15 Mar 2024 19:02:40 -0400 Subject: [PATCH v11 6/6] Be more conservative about --transaction-size in parallel pg_upgrade. Patch 0004 just blindly set the --transaction-size for pg_restore invoked by parallel pg_upgrade to 1000. If the user asks for 5 or 10 parallel restore jobs, our headroom to avoid running out of lock table entries disappears. Be safe by dividing the transaction size by the number of parallel jobs. We could go further and give users direct control of this setting, but it's quite unclear that the extra API complexity would be useful. --- src/bin/pg_upgrade/pg_upgrade.c | 16 +++++++++++++++- 1 file changed, 15 insertions(+), 1 deletion(-) diff --git a/src/bin/pg_upgrade/pg_upgrade.c b/src/bin/pg_upgrade/pg_upgrade.c index 8c6fb96478..af370768b6 100644 --- a/src/bin/pg_upgrade/pg_upgrade.c +++ b/src/bin/pg_upgrade/pg_upgrade.c @@ -587,6 +587,7 @@ create_new_objects(void) log_file_name[MAXPGPATH]; DbInfo *old_db = &old_cluster.dbarr.dbs[dbnum]; const char *create_opts; + int txn_size; /* Skip template1 in this pass */ if (strcmp(old_db->db_name, "template1") == 0) @@ -606,6 +607,19 @@ create_new_objects(void) else create_opts = "--create"; + /* + * In parallel mode, reduce the --transaction-size of each restore job + * so that the total number of locks that could be held across all the + * jobs stays in bounds. + */ + txn_size = RESTORE_TRANSACTION_SIZE; + if (user_opts.jobs > 1) + { + txn_size /= user_opts.jobs; + /* Keep some sanity if -j is huge */ + txn_size = Max(txn_size, 10); + } + parallel_exec_prog(log_file_name, NULL, "\"%s/pg_restore\" %s %s --exit-on-error --verbose " @@ -614,7 +628,7 @@ create_new_objects(void) new_cluster.bindir, cluster_conn_opts(&new_cluster), create_opts, - RESTORE_TRANSACTION_SIZE, + txn_size, log_opts.dumpdir, sql_file_name); } -- 2.39.3