v5-0002-Preserve-database-OIDs-in-pg_upgrade.patch

From 554d92617025bb33d97a68b60697009c2ad2c030 Mon Sep 17 00:00:00 2001
From: shruthikc-gowda <shruthi.kc@enterprisedb.com>
Date: Tue, 26 Oct 2021 18:40:51 +0530
Subject: [PATCH v5 2/2] Preserve database OIDs in pg_upgrade

The patch aims to preserve the database OIDs across binary upgrade

Author: Shruthi KC, based on an earlier patch from Antonin Houska
Discussion: https://www.postgresql.org/message-id/7082.1562337694@localhost
---
 doc/src/sgml/ref/create_database.sgml | 15 ++++++++++++++-
 src/backend/commands/dbcommands.c     | 35 ++++++++++++++++++++++++++++++-----
 src/backend/parser/gram.y             |  3 ++-
 src/bin/initdb/initdb.c               | 23 +++++++++++++++--------
 src/bin/pg_dump/pg_dump.c             | 16 ++++++++++++++--
 src/bin/pg_upgrade/IMPLEMENTATION     |  2 +-
 src/bin/pg_upgrade/info.c             |  9 +++------
 src/bin/pg_upgrade/pg_upgrade.h       |  3 +--
 src/bin/pg_upgrade/relfilenode.c      |  4 ++--
 src/bin/psql/tab-complete.c           |  2 +-
 src/include/access/transam.h          |  3 +++
 src/include/catalog/unused_oids       |  5 +++++
 12 files changed, 91 insertions(+), 29 deletions(-)

diff --git a/doc/src/sgml/ref/create_database.sgml b/doc/src/sgml/ref/create_database.sgml
index 41cb406..fc108a8 100644
--- a/doc/src/sgml/ref/create_database.sgml
+++ b/doc/src/sgml/ref/create_database.sgml
@@ -31,7 +31,8 @@ CREATE DATABASE <replaceable class="parameter">name</replaceable>
            [ TABLESPACE [=] <replaceable class="parameter">tablespace_name</replaceable> ]
            [ ALLOW_CONNECTIONS [=] <replaceable class="parameter">allowconn</replaceable> ]
            [ CONNECTION LIMIT [=] <replaceable class="parameter">connlimit</replaceable> ]
-           [ IS_TEMPLATE [=] <replaceable class="parameter">istemplate</replaceable> ] ]
+           [ IS_TEMPLATE [=] <replaceable class="parameter">istemplate</replaceable> ]
+           [ OID [=] <replaceable class="parameter">db_oid</replaceable> ] ]
 </synopsis>
  </refsynopsisdiv>
 
@@ -203,6 +204,18 @@ CREATE DATABASE <replaceable class="parameter">name</replaceable>
         </para>
        </listitem>
       </varlistentry>
+
+      <varlistentry>
+       <term><replaceable class="parameter">oid</replaceable></term>
+       <listitem>
+        <para>
+         The object identifier with which the database gets created.
+         The OID range for user objects starts from 16384. CREATE DATABASE fails if a 
+         database with specified oid already exists.
+        </para>
+       </listitem>
+      </varlistentry>
+
     </variablelist>
 
   <para>
diff --git a/src/backend/commands/dbcommands.c b/src/backend/commands/dbcommands.c
index 029fab4..e8cdb7f 100644
--- a/src/backend/commands/dbcommands.c
+++ b/src/backend/commands/dbcommands.c
@@ -117,7 +117,7 @@ createdb(ParseState *pstate, const CreatedbStmt *stmt)
 	HeapTuple	tuple;
 	Datum		new_record[Natts_pg_database];
 	bool		new_record_nulls[Natts_pg_database];
-	Oid			dboid;
+	Oid			dboid = InvalidOid;
 	Oid			datdba;
 	ListCell   *option;
 	DefElem    *dtablespacename = NULL;
@@ -217,6 +217,27 @@ createdb(ParseState *pstate, const CreatedbStmt *stmt)
 					 errhint("Consider using tablespaces instead."),
 					 parser_errposition(pstate, defel->location)));
 		}
+		else if (strcmp(defel->defname, "oid") == 0)
+		{
+			dboid = defGetInt32(defel);
+
+			/*
+			 * Throw an error if the user specified oid < FirstNormalObjectId for
+			 * creating the database. However, we need to allow creating database
+			 * with oid < FirstNormalObjectId for below cases:
+			 * 1. creating template0 with fixed oid during initdb
+			 * 2. creating databases with oids from the old cluster during binary
+			 *    upgrade.
+			 */
+			if ((dboid < FirstNormalObjectId) &&
+				(strcmp(dbname, "template0") != 0) &&
+				(!IsBinaryUpgrade))
+				ereport(ERROR,
+						(errcode(ERRCODE_INVALID_PARAMETER_VALUE)),
+						errmsg("Invalid value for option \"%s\"", defel->defname),
+						errhint("The specified OID %u is less than the minimum OID for user objects %u.",
+								dboid, FirstNormalObjectId));
+		}
 		else
 			ereport(ERROR,
 					(errcode(ERRCODE_SYNTAX_ERROR),
@@ -504,11 +525,15 @@ createdb(ParseState *pstate, const CreatedbStmt *stmt)
 	 */
 	pg_database_rel = table_open(DatabaseRelationId, RowExclusiveLock);
 
-	do
+	/* Select an OID for the new database if is not explicitly configured. */
+	if (!OidIsValid(dboid))
 	{
-		dboid = GetNewOidWithIndex(pg_database_rel, DatabaseOidIndexId,
-								   Anum_pg_database_oid);
-	} while (check_db_file_conflict(dboid));
+		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/parser/gram.y b/src/backend/parser/gram.y
index 08f1bf1..a8cdd43 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -687,7 +687,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
 	NOT NOTHING NOTIFY NOTNULL NOWAIT NULL_P NULLIF
 	NULLS_P NUMERIC
 
-	OBJECT_P OF OFF OFFSET OIDS OLD ON ONLY OPERATOR OPTION OPTIONS OR
+	OBJECT_P OF OFF OFFSET OID OIDS OLD ON ONLY OPERATOR OPTION OPTIONS OR
 	ORDER ORDINALITY OTHERS OUT_P OUTER_P
 	OVER OVERLAPS OVERLAY OVERRIDING OWNED OWNER
 
@@ -10254,6 +10254,7 @@ createdb_opt_name:
 			| OWNER							{ $$ = pstrdup($1); }
 			| TABLESPACE					{ $$ = pstrdup($1); }
 			| TEMPLATE						{ $$ = pstrdup($1); }
+			| OID							{ $$ = pstrdup($1); }
 		;
 
 /*
diff --git a/src/bin/initdb/initdb.c b/src/bin/initdb/initdb.c
index 1ed4808..61cac6e 100644
--- a/src/bin/initdb/initdb.c
+++ b/src/bin/initdb/initdb.c
@@ -59,6 +59,7 @@
 #include "sys/mman.h"
 #endif
 
+#include "access/transam.h"
 #include "access/xlog_internal.h"
 #include "catalog/pg_authid_d.h"
 #include "catalog/pg_class_d.h" /* pgrminclude ignore */
@@ -1836,15 +1837,13 @@ static void
 make_template0(FILE *cmdfd)
 {
 	const char *const *line;
-	static const char *const template0_setup[] = {
-		"CREATE DATABASE template0 IS_TEMPLATE = true ALLOW_CONNECTIONS = false;\n\n",
 
-		/*
-		 * We use the OID of template0 to determine datlastsysoid
-		 */
-		"UPDATE pg_database SET datlastsysoid = "
-		"    (SELECT oid FROM pg_database "
-		"    WHERE datname = 'template0');\n\n",
+	/*
+	 * Create template0 database with oid Template0ObjectId i.e, 4
+	 */
+	static const char *const template0_setup[] = {
+		"CREATE DATABASE template0 IS_TEMPLATE = true ALLOW_CONNECTIONS = false OID "
+			CppAsString2(Template0ObjectId) ";\n\n",
 
 		/*
 		 * Explicitly revoke public create-schema and create-temp-table
@@ -1877,6 +1876,14 @@ make_postgres(FILE *cmdfd)
 	static const char *const postgres_setup[] = {
 		"CREATE DATABASE postgres;\n\n",
 		"COMMENT ON DATABASE postgres IS 'default administrative connection database';\n\n",
+
+		/*
+		 * We use the OID of postgres to determine datlastsysoid
+		 */
+		"UPDATE pg_database SET datlastsysoid = "
+		"    (SELECT oid FROM pg_database "
+		"    WHERE datname = 'postgres');\n\n",
+
 		NULL
 	};
 
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 13213ff..ce37bf4 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -2957,8 +2957,20 @@ dumpDatabase(Archive *fout)
 	 * are left to the DATABASE PROPERTIES entry, so that they can be applied
 	 * after reconnecting to the target DB.
 	 */
-	appendPQExpBuffer(creaQry, "CREATE DATABASE %s WITH TEMPLATE = template0",
-					  qdatname);
+	if (dopt->binary_upgrade)
+	{
+		/*
+		 * Make sure that binary upgrade propogate the database OID to the new
+		 * cluster
+		 */
+		appendPQExpBuffer(creaQry, "CREATE DATABASE %s WITH TEMPLATE = template0 OID = %u",
+						  qdatname, dbCatId.oid);
+	}
+	else
+	{
+		appendPQExpBuffer(creaQry, "CREATE DATABASE %s WITH TEMPLATE = template0",
+						  qdatname);
+	}
 	if (strlen(encoding) > 0)
 	{
 		appendPQExpBufferStr(creaQry, " ENCODING = ");
diff --git a/src/bin/pg_upgrade/IMPLEMENTATION b/src/bin/pg_upgrade/IMPLEMENTATION
index 69fcd70..384834a 100644
--- a/src/bin/pg_upgrade/IMPLEMENTATION
+++ b/src/bin/pg_upgrade/IMPLEMENTATION
@@ -84,7 +84,7 @@ cluster using the first part of the pg_dumpall output.
 Next, pg_upgrade executes the remainder of the script produced earlier
 by pg_dumpall --- this script effectively creates the complete
 user-defined metadata from the old cluster to the new cluster.  It
-preserves the relfilenode numbers so TOAST and other references
+preserves the DB, tablespace, relfilenode OIDs so TOAST and other references
 to relfilenodes in user data is preserved.  (See binary-upgrade usage
 in pg_dump).
 
diff --git a/src/bin/pg_upgrade/info.c b/src/bin/pg_upgrade/info.c
index 775564e..fe1357d 100644
--- a/src/bin/pg_upgrade/info.c
+++ b/src/bin/pg_upgrade/info.c
@@ -196,10 +196,8 @@ create_rel_filename_map(const char *old_data, const char *new_data,
 		map->new_tablespace_suffix = new_cluster.tablespace_suffix;
 	}
 
-	map->old_db_oid = old_db->db_oid;
-	map->new_db_oid = new_db->db_oid;
-
-	/* relfilenode is preserved across old and new cluster */
+	/* DB oid and relfilenodes are preserved between old and new cluster */
+	map->db_oid = old_db->db_oid;
 	map->relfilenode = old_rel->relfilenode;
 
 	/* used only for logging and error reporting, old/new are identical */
@@ -330,8 +328,7 @@ get_db_infos(ClusterInfo *cluster)
 			 " LEFT OUTER JOIN pg_catalog.pg_tablespace t "
 			 " ON d.dattablespace = t.oid "
 			 "WHERE d.datallowconn = true "
-	/* we don't preserve pg_database.oid so we sort by name */
-			 "ORDER BY 2",
+			 "ORDER BY 1",
 	/* 9.2 removed the spclocation column */
 			 (GET_MAJOR_VERSION(cluster->major_version) <= 901) ?
 			 "t.spclocation" : "pg_catalog.pg_tablespace_location(t.oid)");
diff --git a/src/bin/pg_upgrade/pg_upgrade.h b/src/bin/pg_upgrade/pg_upgrade.h
index c79ddfc..937c45b 100644
--- a/src/bin/pg_upgrade/pg_upgrade.h
+++ b/src/bin/pg_upgrade/pg_upgrade.h
@@ -157,8 +157,7 @@ typedef struct
 	const char *new_tablespace;
 	const char *old_tablespace_suffix;
 	const char *new_tablespace_suffix;
-	Oid			old_db_oid;
-	Oid			new_db_oid;
+	Oid			db_oid;
 	Oid			relfilenode;
 	/* the rest are used only for logging and error reporting */
 	char	   *nspname;		/* namespaces */
diff --git a/src/bin/pg_upgrade/relfilenode.c b/src/bin/pg_upgrade/relfilenode.c
index 3bc5b44..a4fc298 100644
--- a/src/bin/pg_upgrade/relfilenode.c
+++ b/src/bin/pg_upgrade/relfilenode.c
@@ -203,14 +203,14 @@ transfer_relfile(FileNameMap *map, const char *type_suffix, bool vm_must_add_fro
 		snprintf(old_file, sizeof(old_file), "%s%s/%u/%u%s%s",
 				 map->old_tablespace,
 				 map->old_tablespace_suffix,
-				 map->old_db_oid,
+				 map->db_oid,
 				 map->relfilenode,
 				 type_suffix,
 				 extent_suffix);
 		snprintf(new_file, sizeof(new_file), "%s%s/%u/%u%s%s",
 				 map->new_tablespace,
 				 map->new_tablespace_suffix,
-				 map->new_db_oid,
+				 map->db_oid,
 				 map->relfilenode,
 				 type_suffix,
 				 extent_suffix);
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index ecae9df..b812f61 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -2516,7 +2516,7 @@ psql_completion(const char *text, int start, int end)
 		COMPLETE_WITH("OWNER", "TEMPLATE", "ENCODING", "TABLESPACE",
 					  "IS_TEMPLATE",
 					  "ALLOW_CONNECTIONS", "CONNECTION LIMIT",
-					  "LC_COLLATE", "LC_CTYPE", "LOCALE");
+					  "LC_COLLATE", "LC_CTYPE", "LOCALE", "OID");
 
 	else if (Matches("CREATE", "DATABASE", MatchAny, "TEMPLATE"))
 		COMPLETE_WITH_QUERY(Query_for_list_of_template_databases);
diff --git a/src/include/access/transam.h b/src/include/access/transam.h
index d22de19..716a319 100644
--- a/src/include/access/transam.h
+++ b/src/include/access/transam.h
@@ -196,6 +196,9 @@ FullTransactionIdAdvance(FullTransactionId *dest)
 #define FirstUnpinnedObjectId	12000
 #define FirstNormalObjectId		16384
 
+/* OID 4 is reserved for Templete0 database */
+#define Template0ObjectId		4
+
 /*
  * VariableCache is a data structure in shared memory that is used to track
  * OID and XID assignment state.  For largely historical reasons, there is
diff --git a/src/include/catalog/unused_oids b/src/include/catalog/unused_oids
index 5b7ce5f..2750913 100755
--- a/src/include/catalog/unused_oids
+++ b/src/include/catalog/unused_oids
@@ -32,6 +32,11 @@ my @input_files = glob("pg_*.h");
 
 my $oids = Catalog::FindAllOidsFromHeaders(@input_files);
 
+# Push the OID that is reserved for template0 database.
+my $Template0ObjectId =
+  Catalog::FindDefinedSymbol('access/transam.h', '..', 'Template0ObjectId');
+push @{$oids}, $Template0ObjectId;
+
 # Also push FirstGenbkiObjectId to serve as a terminator for the last gap.
 my $FirstGenbkiObjectId =
   Catalog::FindDefinedSymbol('access/transam.h', '..', 'FirstGenbkiObjectId');
-- 
1.8.3.1