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

From dc0a50b5513c38bfa0d1356c370475edec0b49cf Mon Sep 17 00:00:00 2001
From: shruthikc-gowda <shruthi.kc@enterprisedb.com>
Date: Wed, 22 Sep 2021 23:36:41 +0530
Subject: [PATCH v3 2/2] 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
---
 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               | 21 +++++++++++++--------
 src/bin/pg_dump/pg_dump.c             | 16 ++++++++++++++--
 src/bin/psql/tab-complete.c           |  2 +-
 6 files changed, 74 insertions(+), 18 deletions(-)

diff --git a/doc/src/sgml/ref/create_database.sgml b/doc/src/sgml/ref/create_database.sgml
index 41cb406..3865a96 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 value should be greater than 16383. 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..0251157 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("Consider using a value greater than %u.",
+						(FirstNormalObjectId - 1)));
+		}
 		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 e3068a3..b265959 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..a5d88f1 100644
--- a/src/bin/initdb/initdb.c
+++ b/src/bin/initdb/initdb.c
@@ -1836,15 +1836,12 @@ 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 fixed OID 2
+	 */
+	static const char *const template0_setup[] = {
+		"CREATE DATABASE template0 IS_TEMPLATE = true ALLOW_CONNECTIONS = false OID 2;\n\n",
 
 		/*
 		 * Explicitly revoke public create-schema and create-temp-table
@@ -1877,6 +1874,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 b148919..47c657e 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/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 5cd5838..396b4c5 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);
-- 
1.8.3.1