0001-pg_restore-freeze.patch

From 912a0cb3b31d4884fd6b2ae8869b96483d620641 Mon Sep 17 00:00:00 2001
From: Stepan Neretin <slpmcf@gmail.com>
Date: Mon, 21 Jul 2025 12:22:02 +0700
Subject: [PATCH] pg_restore --freeze

pg_restore now invokes

  COPY FROM ... WITH (FREEZE)

Needs also options

  --section=data --clean -j N

so that each pg_restore worker process wraps each COPY in a transaction
and precedes it with TRUNCATE. That way the data insertion is optimized
and the visibility table is written without needing VACUUMing.

Rebased-by: Stepan Neretin <slpmcf@gmail.com>
---
 src/bin/pg_dump/pg_backup.h          |  1 +
 src/bin/pg_dump/pg_backup_archiver.c | 43 +++++++++++++++++++++++++++-
 src/bin/pg_dump/pg_dump.c            |  9 +++++-
 src/bin/pg_dump/pg_restore.c         |  4 +++
 4 files changed, 55 insertions(+), 2 deletions(-)

diff --git a/src/bin/pg_dump/pg_backup.h b/src/bin/pg_dump/pg_backup.h
index af0007fb6d2..befb524c9d3 100644
--- a/src/bin/pg_dump/pg_backup.h
+++ b/src/bin/pg_dump/pg_backup.h
@@ -116,6 +116,7 @@ typedef struct _restoreOptions
 	int			no_security_labels; /* Skip security label entries */
 	int			no_subscriptions;	/* Skip subscription entries */
 	int			strict_names;
+	int			freeze;			/* COPY FREEZE */
 
 	const char *filename;
 	int			dumpSections;
diff --git a/src/bin/pg_dump/pg_backup_archiver.c b/src/bin/pg_dump/pg_backup_archiver.c
index 30e0da31aa3..f190ca77836 100644
--- a/src/bin/pg_dump/pg_backup_archiver.c
+++ b/src/bin/pg_dump/pg_backup_archiver.c
@@ -99,6 +99,9 @@ static void restore_toc_entries_parallel(ArchiveHandle *AH,
 										 TocEntry *pending_list);
 static void restore_toc_entries_postfork(ArchiveHandle *AH,
 										 TocEntry *pending_list);
+static void do_copy(ArchiveHandle *AH,
+					const char *cp,
+					bool freeze);
 static void pending_list_header_init(TocEntry *l);
 static void pending_list_append(TocEntry *l, TocEntry *te);
 static void pending_list_remove(TocEntry *te);
@@ -1021,7 +1024,7 @@ restore_toc_entry(ArchiveHandle *AH, TocEntry *te, bool is_parallel)
 					 */
 					if (te->copyStmt && strlen(te->copyStmt) > 0)
 					{
-						ahprintf(AH, "%s", te->copyStmt);
+						do_copy(AH, te->copyStmt, ropt->freeze);
 						AH->outputKind = OUTPUT_COPYDATA;
 					}
 					else
@@ -1084,6 +1087,44 @@ restore_toc_entry(ArchiveHandle *AH, TocEntry *te, bool is_parallel)
 	return status;
 }
 
+static void
+do_copy(ArchiveHandle *AH, const char *cp, bool freeze)
+{
+	const size_t cp_len = strlen(cp);
+	bool		cp_is_well_formed = false;
+	const char *cp_end;
+
+	Assert(cp_len > 0);			/* Have checked it in caller */
+
+	/*
+	 * Check if the COPY statement is well written so that we can inject the
+	 * FREEZE option.
+	 */
+	if (freeze)
+	{
+		int			i = cp_len - 1;
+
+		/* Cut off the trailing semicolon and whitespace. */
+		while (i > 0 &&
+			   (cp[i] == ' ' || cp[i] == '\n' || cp[i] == ';'))
+			i--;
+		cp_end = &cp[i + 1];
+
+		if (cp_end - 10 > cp &&
+			strncmp(cp_end - 10, "FROM stdin", 10) == 0
+			)
+			cp_is_well_formed = true;
+		else
+			pg_log_warning("COPY statement from dump file is not in the right form; Will not inject the FREEZE option");
+	}
+
+	if (freeze && cp_is_well_formed)
+		ahprintf(AH, "%.*s WITH (FREEZE);\n",
+				 (int) (cp_end - cp), cp);
+	else
+		ahprintf(AH, "%s", cp);
+}
+
 /*
  * Allocate a new RestoreOptions block.
  * This is mainly so we can initialize it, but also for future expansion,
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 604fc109416..e69c3d6b99b 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -2856,7 +2856,14 @@ dumpTableData(Archive *fout, const TableDataInfo *tdinfo)
 
 	if (dopt->dump_inserts == 0)
 	{
-		/* Dump/restore using COPY */
+		/*
+		 * Dump/restore using COPY.
+		 *
+		 * NOTE: do not use options in the COPY statement, as the restore
+		 * process appends its own options. In fact pg_restore always expects
+		 *
+		 * COPY ... FROM stdin;
+		 */
 		dumpFn = dumpTableData_copy;
 		/* must use 2 steps here 'cause fmtId is nonreentrant */
 		printfPQExpBuffer(copyBuf, "COPY %s ",
diff --git a/src/bin/pg_dump/pg_restore.c b/src/bin/pg_dump/pg_restore.c
index 6ef789cb06d..f9a659de16e 100644
--- a/src/bin/pg_dump/pg_restore.c
+++ b/src/bin/pg_dump/pg_restore.c
@@ -114,6 +114,7 @@ main(int argc, char **argv)
 	static int	with_data = 0;
 	static int	with_schema = 0;
 	static int	with_statistics = 0;
+	static int	freeze = 0;
 
 	struct option cmdopts[] = {
 		{"clean", 0, NULL, 'c'},
@@ -175,6 +176,7 @@ main(int argc, char **argv)
 		{"statistics-only", no_argument, &statistics_only, 1},
 		{"filter", required_argument, NULL, 4},
 		{"exclude-database", required_argument, NULL, 6},
+		{"freeze", no_argument, &freeze, 1},
 
 		{NULL, 0, NULL, 0}
 	};
@@ -467,6 +469,7 @@ main(int argc, char **argv)
 	opts->no_publications = no_publications;
 	opts->no_security_labels = no_security_labels;
 	opts->no_subscriptions = no_subscriptions;
+	opts->freeze = freeze;
 
 	if (if_exists && !opts->dropSchema)
 		pg_fatal("option --if-exists requires option -c/--clean");
@@ -715,6 +718,7 @@ usage(const char *progname)
 	printf(_("  --with-data                  restore the data\n"));
 	printf(_("  --with-schema                restore the schema\n"));
 	printf(_("  --with-statistics            restore the statistics\n"));
+	printf(_("  --freeze                     COPY FREEZE (read the manual for caveats)\n"));
 
 	printf(_("\nConnection options:\n"));
 	printf(_("  -h, --host=HOSTNAME      database server host or socket directory\n"));
-- 
2.48.1