[Shinya Kato <Shinya11.Kato@oss.nttdata.com>]

Hi hackers!

When SQL scripts created with pg_dump/pg_dumpall/pg_restore are executed 
in psql with AUTOCOMMIT turned off, they will not succeed in many cases.
This is because the script contains SQL statements that cannot be 
executed within a transaction block.

If you simply add set AUTOCOMMIT on to the scripts created by 
pg_dump/pg_dumpall/pg_restore, they will work fine.
A patch is attached

No documentation has been added as we could not find any documentation 
on the details in the script.

Do you think?

Regards,
Shinya Kato
NTT DATA GROUP CORPORATION

Attachments:

v1-0001-Set-AUTOCOMMIT-to-on-in-script-output-by-pg_dump.patch (text/x-diff, 1.6K) download
From e5b4a6ab95ab5c798ef8c7f7062fb764a74de37a Mon Sep 17 00:00:00 2001
From: Shinya Kato <Shinya11.Kato@oss.nttdata.com>
Date: Wed, 9 Oct 2024 10:28:31 +0900
Subject: [PATCH v1] Set AUTOCOMMIT to on in script output by pg_dump

---
 src/bin/pg_dump/pg_backup_archiver.c | 7 +++++++
 src/bin/pg_dump/pg_dumpall.c         | 6 ++++++
 2 files changed, 13 insertions(+)

diff --git a/src/bin/pg_dump/pg_backup_archiver.c b/src/bin/pg_dump/pg_backup_archiver.c
index 8c20c263c4..b1077a5521 100644
--- a/src/bin/pg_dump/pg_backup_archiver.c
+++ b/src/bin/pg_dump/pg_backup_archiver.c
@@ -472,6 +472,13 @@ RestoreArchive(Archive *AHX)
 			ahprintf(AH, "BEGIN;\n\n");
 	}
 
+	/*
+	 * Set AUTOCOMMIT to on when dumping a plain-text file to prevent errors
+	 * with SQL statements that cannot be executed inside transaction block.
+	 */
+	if (AH->format == archNull || ropt->filename != NULL)
+		ahprintf(AH, "\\set AUTOCOMMIT on\n");
+
 	/*
 	 * Establish important parameter values right away.
 	 */
diff --git a/src/bin/pg_dump/pg_dumpall.c b/src/bin/pg_dump/pg_dumpall.c
index e3ad8fb295..006d0201bc 100644
--- a/src/bin/pg_dump/pg_dumpall.c
+++ b/src/bin/pg_dump/pg_dumpall.c
@@ -553,6 +553,12 @@ main(int argc, char *argv[])
 	 * database we're connected to at the moment is fine.
 	 */
 
+	/*
+	 * Set AUTOCOMMIT to on to prevent errors with SQL statements that cannot be
+	 * executed inside transaction block.
+	 */
+	fprintf(OPF, "\\set AUTOCOMMIT on\n\n");
+
 	/* Restore will need to write to the target cluster */
 	fprintf(OPF, "SET default_transaction_read_only = off;\n\n");
 
-- 
2.43.0