v2-0001-Preserve-database-OIDs-in-pg_upgrade.patch

From 6af195ee95bbde8e75fd6bc81350ede33256d866 Mon Sep 17 00:00:00 2001
From: shruthikc-gowda <shruthi.kc@enterprisedb.com>
Date: Fri, 20 Aug 2021 22:07:42 +0530
Subject: [PATCH v2] Preserve database OIDs in pg_upgrade

The patch aims to preserve the database OIDs during binary upgrade
so that the database OIDs are same across old and new cluster.

Author: Shruthi KC, based on an earlier patch from Antonin Houska
Discussion: https://www.postgresql.org/message-id/7082.1562337694@localhost
---
 src/backend/commands/dbcommands.c                  | 22 +++++++++++++---
 src/backend/utils/adt/pg_upgrade_support.c         | 11 ++++++++
 src/bin/pg_dump/pg_dump.c                          | 30 ++++++++++++++++++++++
 src/bin/pg_upgrade/pg_upgrade.c                    |  3 +++
 src/include/catalog/binary_upgrade.h               |  2 ++
 src/include/catalog/pg_proc.dat                    |  4 +++
 .../spgist_name_ops/expected/spgist_name_ops.out   |  6 +++--
 7 files changed, 72 insertions(+), 6 deletions(-)

diff --git a/src/backend/commands/dbcommands.c b/src/backend/commands/dbcommands.c
index 029fab4..a1e4a83 100644
--- a/src/backend/commands/dbcommands.c
+++ b/src/backend/commands/dbcommands.c
@@ -92,6 +92,7 @@ static void remove_dbtablespaces(Oid db_id);
 static bool check_db_file_conflict(Oid db_id);
 static int	errdetail_busy_db(int notherbackends, int npreparedxacts);
 
+Oid			binary_upgrade_next_pg_database_oid = InvalidOid;
 
 /*
  * CREATE DATABASE
@@ -504,11 +505,24 @@ createdb(ParseState *pstate, const CreatedbStmt *stmt)
 	 */
 	pg_database_rel = table_open(DatabaseRelationId, RowExclusiveLock);
 
-	do
+	if (IsBinaryUpgrade)
 	{
-		dboid = GetNewOidWithIndex(pg_database_rel, DatabaseOidIndexId,
-								   Anum_pg_database_oid);
-	} while (check_db_file_conflict(dboid));
+		if (!OidIsValid(binary_upgrade_next_pg_database_oid))
+			ereport(ERROR,
+					(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+					 errmsg("pg_database OID value not set when in binary upgrade mode")));
+
+		dboid = binary_upgrade_next_pg_database_oid;
+		binary_upgrade_next_pg_database_oid = InvalidOid;
+	}
+	else
+	{
+		do
+		{
+			dboid = GetNewOidWithIndex(pg_database_rel, DatabaseOidIndexId,
+									   Anum_pg_database_oid);
+		} while (check_db_file_conflict(dboid));
+	}
 
 	/*
 	 * Insert a new tuple into pg_database.  This establishes our ownership of
diff --git a/src/backend/utils/adt/pg_upgrade_support.c b/src/backend/utils/adt/pg_upgrade_support.c
index b5b46d7..c499434 100644
--- a/src/backend/utils/adt/pg_upgrade_support.c
+++ b/src/backend/utils/adt/pg_upgrade_support.c
@@ -30,6 +30,17 @@ do {															\
 } while (0)
 
 Datum
+binary_upgrade_set_next_pg_database_oid(PG_FUNCTION_ARGS)
+{
+	Oid			dboid = PG_GETARG_OID(0);
+
+	CHECK_IS_BINARY_UPGRADE;
+	binary_upgrade_next_pg_database_oid = dboid;
+
+	PG_RETURN_VOID();
+}
+
+Datum
 binary_upgrade_set_next_pg_type_oid(PG_FUNCTION_ARGS)
 {
 	Oid			typoid = PG_GETARG_OID(0);
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 90ac445..0a938a2 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -47,6 +47,7 @@
 #include "catalog/pg_authid_d.h"
 #include "catalog/pg_cast_d.h"
 #include "catalog/pg_class_d.h"
+#include "catalog/pg_database.h"
 #include "catalog/pg_default_acl_d.h"
 #include "catalog/pg_largeobject_d.h"
 #include "catalog/pg_largeobject_metadata_d.h"
@@ -2781,6 +2782,7 @@ dumpDatabase(Archive *fout)
 	PQExpBuffer dbQry = createPQExpBuffer();
 	PQExpBuffer delQry = createPQExpBuffer();
 	PQExpBuffer creaQry = createPQExpBuffer();
+	PQExpBuffer setDBIdQry = createPQExpBuffer();
 	PQExpBuffer labelq = createPQExpBuffer();
 	PGconn	   *conn = GetConnection(fout);
 	PGresult   *res;
@@ -2950,6 +2952,34 @@ dumpDatabase(Archive *fout)
 
 	qdatname = pg_strdup(fmtId(datname));
 
+	/* Make sure that pg_upgrade does not change database OID. */
+	if (dopt->binary_upgrade)
+	{
+		appendPQExpBufferStr(setDBIdQry, "\n-- For binary upgrade, must preserve pg_database oid\n");
+		appendPQExpBuffer(setDBIdQry,
+						  "SELECT pg_catalog.binary_upgrade_set_next_pg_database_oid('%u'::pg_catalog.oid);\n",
+						  dbCatId.oid);
+
+		dbDumpId = createDumpId();
+
+		/*
+		 * Need a separate entry, otherwise the command will be run in the
+		 * same transaction as the CREATE DATABASE command, which is not
+		 * allowed.
+		 */
+		ArchiveEntry(fout,
+					 dbCatId,	/* catalog ID */
+					 dbDumpId,	/* dump ID */
+					 ARCHIVE_OPTS(.tag = datname,
+								  .owner = dba,
+								  .description = "SET_DB_OID",
+								  .section = SECTION_PRE_DATA,
+								  .createStmt = setDBIdQry->data,
+								  .dropStmt = NULL));
+
+		destroyPQExpBuffer(setDBIdQry);
+	}
+
 	/*
 	 * Prepare the CREATE DATABASE command.  We must specify encoding, locale,
 	 * and tablespace since those can't be altered later.  Other DB properties
diff --git a/src/bin/pg_upgrade/pg_upgrade.c b/src/bin/pg_upgrade/pg_upgrade.c
index 3628bd7..ad25710 100644
--- a/src/bin/pg_upgrade/pg_upgrade.c
+++ b/src/bin/pg_upgrade/pg_upgrade.c
@@ -22,6 +22,9 @@
  *	this, old/new pg_class.relfilenode values will not match if CLUSTER,
  *	REINDEX, or VACUUM FULL have been performed in the old cluster.
  *
+ *  We control assignment of pg_database.oid because we want the oid to match
+ *  between the old and new cluster.
+ *
  *	We control all assignments of pg_type.oid because these oids are stored
  *	in user composite type values.
  *
diff --git a/src/include/catalog/binary_upgrade.h b/src/include/catalog/binary_upgrade.h
index f6e82e7..3809521 100644
--- a/src/include/catalog/binary_upgrade.h
+++ b/src/include/catalog/binary_upgrade.h
@@ -14,6 +14,8 @@
 #ifndef BINARY_UPGRADE_H
 #define BINARY_UPGRADE_H
 
+extern PGDLLIMPORT Oid binary_upgrade_next_pg_database_oid;
+
 extern PGDLLIMPORT Oid binary_upgrade_next_pg_type_oid;
 extern PGDLLIMPORT Oid binary_upgrade_next_array_pg_type_oid;
 extern PGDLLIMPORT Oid binary_upgrade_next_mrng_pg_type_oid;
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index b603700..1d7ae19 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -11036,6 +11036,10 @@
   proname => 'binary_upgrade_set_missing_value', provolatile => 'v',
   proparallel => 'u', prorettype => 'void', proargtypes => 'oid text text',
   prosrc => 'binary_upgrade_set_missing_value' },
+{ oid => '4548', descr => 'for use by pg_upgrade',
+  proname => 'binary_upgrade_set_next_pg_database_oid', provolatile => 'v',
+  proparallel => 'u', prorettype => 'void', proargtypes => 'oid',
+  prosrc => 'binary_upgrade_set_next_pg_database_oid' },
 
 # conversion functions
 { oid => '4302',
diff --git a/src/test/modules/spgist_name_ops/expected/spgist_name_ops.out b/src/test/modules/spgist_name_ops/expected/spgist_name_ops.out
index ac0ddce..1dcdad4 100644
--- a/src/test/modules/spgist_name_ops/expected/spgist_name_ops.out
+++ b/src/test/modules/spgist_name_ops/expected/spgist_name_ops.out
@@ -56,10 +56,11 @@ select * from t
  binary_upgrade_set_next_multirange_array_pg_type_oid |  1 | binary_upgrade_set_next_multirange_array_pg_type_oid
  binary_upgrade_set_next_multirange_pg_type_oid       |  1 | binary_upgrade_set_next_multirange_pg_type_oid
  binary_upgrade_set_next_pg_authid_oid                |    | binary_upgrade_set_next_pg_authid_oid
+ binary_upgrade_set_next_pg_database_oid              |    | binary_upgrade_set_next_pg_database_oid
  binary_upgrade_set_next_pg_enum_oid                  |    | binary_upgrade_set_next_pg_enum_oid
  binary_upgrade_set_next_pg_type_oid                  |    | binary_upgrade_set_next_pg_type_oid
  binary_upgrade_set_next_toast_pg_class_oid           |  1 | binary_upgrade_set_next_toast_pg_class_oid
-(9 rows)
+(10 rows)
 
 -- Verify clean failure when INCLUDE'd columns result in overlength tuple
 -- The error message details are platform-dependent, so show only SQLSTATE
@@ -101,10 +102,11 @@ select * from t
  binary_upgrade_set_next_multirange_array_pg_type_oid |  1 | binary_upgrade_set_next_multirange_array_pg_type_oid
  binary_upgrade_set_next_multirange_pg_type_oid       |  1 | binary_upgrade_set_next_multirange_pg_type_oid
  binary_upgrade_set_next_pg_authid_oid                |    | binary_upgrade_set_next_pg_authid_oid
+ binary_upgrade_set_next_pg_database_oid              |    | binary_upgrade_set_next_pg_database_oid
  binary_upgrade_set_next_pg_enum_oid                  |    | binary_upgrade_set_next_pg_enum_oid
  binary_upgrade_set_next_pg_type_oid                  |    | binary_upgrade_set_next_pg_type_oid
  binary_upgrade_set_next_toast_pg_class_oid           |  1 | binary_upgrade_set_next_toast_pg_class_oid
-(9 rows)
+(10 rows)
 
 \set VERBOSITY sqlstate
 insert into t values(repeat('xyzzy', 12), 42, repeat('xyzzy', 4000));
-- 
1.8.3.1