0002-Support-for-pg_dump.patch

From 110b2f60bdd53d3b4eb31dc71c9ffb83afac59ea Mon Sep 17 00:00:00 2001
From: Rushabh Lathia <rushabh.lathia@enterprisedb.com>
Date: Thu, 20 Mar 2025 11:39:56 +0530
Subject: [PATCH 2/2] Support for pg_dump.

---
 src/bin/pg_dump/pg_dump.c | 167 ++++++++++++++++++++++++++++++++++++--
 src/bin/pg_dump/pg_dump.h |   1 +
 2 files changed, 159 insertions(+), 9 deletions(-)

diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index c371570501a..96b7d181f91 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -8876,6 +8876,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
 	PQExpBuffer q = createPQExpBuffer();
 	PQExpBuffer tbloids = createPQExpBuffer();
 	PQExpBuffer checkoids = createPQExpBuffer();
+	PQExpBuffer invalidnotnulloids = createPQExpBuffer();
 	PGresult   *res;
 	int			ntups;
 	int			curtblindx;
@@ -8892,6 +8893,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
 	int			i_attlen;
 	int			i_attalign;
 	int			i_attislocal;
+	int			i_notnull_validated;
 	int			i_notnull_name;
 	int			i_notnull_noinherit;
 	int			i_notnull_islocal;
@@ -8988,11 +8990,13 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
 	 */
 	if (fout->remoteVersion >= 180000)
 		appendPQExpBufferStr(q,
+							 "co.convalidated as notnull_validated,\n"
 							 "co.conname AS notnull_name,\n"
 							 "co.connoinherit AS notnull_noinherit,\n"
 							 "co.conislocal AS notnull_islocal,\n");
 	else
 		appendPQExpBufferStr(q,
+							 "false as notnull_validated,\n"
 							 "CASE WHEN a.attnotnull THEN '' ELSE NULL END AS notnull_name,\n"
 							 "false AS notnull_noinherit,\n"
 							 "a.attislocal AS notnull_islocal,\n");
@@ -9067,6 +9071,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
 	i_attlen = PQfnumber(res, "attlen");
 	i_attalign = PQfnumber(res, "attalign");
 	i_attislocal = PQfnumber(res, "attislocal");
+	i_notnull_validated = PQfnumber(res, "notnull_validated");
 	i_notnull_name = PQfnumber(res, "notnull_name");
 	i_notnull_noinherit = PQfnumber(res, "notnull_noinherit");
 	i_notnull_islocal = PQfnumber(res, "notnull_islocal");
@@ -9086,6 +9091,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
 	 * r is handled by the inner loop.
 	 */
 	curtblindx = -1;
+	appendPQExpBufferChar(invalidnotnulloids, '{');
 	for (int r = 0; r < ntups;)
 	{
 		Oid			attrelid = atooid(PQgetvalue(res, r, i_attrelid));
@@ -9135,6 +9141,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
 		tbinfo->attfdwoptions = (char **) pg_malloc(numatts * sizeof(char *));
 		tbinfo->attmissingval = (char **) pg_malloc(numatts * sizeof(char *));
 		tbinfo->notnull_constrs = (char **) pg_malloc(numatts * sizeof(char *));
+		tbinfo->notnull_validated = (bool *) pg_malloc(numatts * sizeof(bool));
 		tbinfo->notnull_noinh = (bool *) pg_malloc(numatts * sizeof(bool));
 		tbinfo->notnull_islocal = (bool *) pg_malloc(numatts * sizeof(bool));
 		tbinfo->attrdefs = (AttrDefInfo **) pg_malloc(numatts * sizeof(AttrDefInfo *));
@@ -9160,6 +9167,28 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
 			tbinfo->attlen[j] = atoi(PQgetvalue(res, r, i_attlen));
 			tbinfo->attalign[j] = *(PQgetvalue(res, r, i_attalign));
 			tbinfo->attislocal[j] = (PQgetvalue(res, r, i_attislocal)[0] == 't');
+			tbinfo->notnull_validated[j] = (PQgetvalue(res, r, i_notnull_validated)[0] == 't');
+
+			/*
+			 * Dump the invalid NOT NULL constraint like the Check constraints
+			 */
+			if (tbinfo->notnull_validated[j])
+				/* Handle not-null constraint name and flags */
+				determineNotNullFlags(fout, res, r,
+									  tbinfo, j,
+									  i_notnull_name, i_notnull_noinherit,
+									  i_notnull_islocal);
+			else if (!PQgetisnull(res, r, i_notnull_name))
+			{
+				/*
+				 * Add the entry into invalidnotnull list so NOT NULL
+				 * constraint get dump as separate constraints.
+				 */
+				if (invalidnotnulloids->len > 1)    /* do we have more than
+													 * the '{'? */
+					appendPQExpBufferChar(invalidnotnulloids, ',');
+				appendPQExpBuffer(invalidnotnulloids, "%u", tbinfo->dobj.catId.oid);
+			}
 
 			/* Handle not-null constraint name and flags */
 			determineNotNullFlags(fout, res, r,
@@ -9187,6 +9216,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
 	}
 
 	PQclear(res);
+	appendPQExpBufferChar(invalidnotnulloids, '}');
 
 	/*
 	 * Now get info about column defaults.  This is skipped for a data-only
@@ -9450,6 +9480,128 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
 		PQclear(res);
 	}
 
+	/*
+	 * Get info about table INVALID NOT NULL constraints.  This is skipped for
+	 * a data-only dump, as it is only needed for table schemas.
+	 */
+	if (dopt->dumpSchema && invalidnotnulloids->len > 2)
+	{
+		ConstraintInfo *constrs;
+		int         numConstrs;
+		int         i_tableoid;
+		int         i_oid;
+		int         i_conrelid;
+		int         i_conname;
+		int         i_consrc;
+		int         i_conislocal;
+		int         i_convalidated;
+
+		pg_log_info("finding table invalid not null constraints");
+
+		resetPQExpBuffer(q);
+		appendPQExpBuffer(q,
+						  "SELECT c.tableoid, c.oid, conrelid, conname, "
+						  "pg_catalog.pg_get_constraintdef(c.oid) AS consrc, "
+						  "conislocal, convalidated "
+						  "FROM unnest('%s'::pg_catalog.oid[]) AS src(tbloid)\n"
+						  "JOIN pg_catalog.pg_constraint c ON (src.tbloid = c.conrelid)\n"
+						  "WHERE contype = 'n' AND convalidated = 'f'"
+						  "ORDER BY c.conrelid, c.conname",
+						  invalidnotnulloids->data);
+
+		res = ExecuteSqlQuery(fout, q->data, PGRES_TUPLES_OK);
+
+		numConstrs = PQntuples(res);
+		constrs = (ConstraintInfo *) pg_malloc(numConstrs * sizeof(ConstraintInfo));
+
+		i_tableoid = PQfnumber(res, "tableoid");
+		i_oid = PQfnumber(res, "oid");
+		i_conrelid = PQfnumber(res, "conrelid");
+		i_conname = PQfnumber(res, "conname");
+		i_consrc = PQfnumber(res, "consrc");
+		i_conislocal = PQfnumber(res, "conislocal");
+		i_convalidated = PQfnumber(res, "convalidated");
+
+		/* As above, this loop iterates once per table, not once per row */
+		curtblindx = -1;
+		for (int j = 0; j < numConstrs;)
+		{
+			Oid         conrelid = atooid(PQgetvalue(res, j, i_conrelid));
+			TableInfo  *tbinfo = NULL;
+			int         numcons;
+
+			/* Count rows for this table */
+			for (numcons = 1; numcons < numConstrs - j; numcons++)
+				if (atooid(PQgetvalue(res, j + numcons, i_conrelid)) != conrelid)
+					break;
+
+			/*
+			 * Locate the associated TableInfo; we rely on tblinfo[] being in
+			 * OID order.
+			 */
+			while (++curtblindx < numTables)
+			{
+				tbinfo = &tblinfo[curtblindx];
+				if (tbinfo->dobj.catId.oid == conrelid)
+					break;
+			}
+			if (curtblindx >= numTables)
+				pg_fatal("unrecognized table OID %u", conrelid);
+
+
+			tbinfo->checkexprs = constrs + j;
+
+			for (int c = 0; c < numcons; c++, j++)
+			{
+				bool        validated = PQgetvalue(res, j, i_convalidated)[0] == 't';
+
+				constrs[j].dobj.objType = DO_CONSTRAINT;
+				constrs[j].dobj.catId.tableoid = atooid(PQgetvalue(res, j, i_tableoid));
+				constrs[j].dobj.catId.oid = atooid(PQgetvalue(res, j, i_oid));
+				AssignDumpId(&constrs[j].dobj);
+				constrs[j].dobj.name = pg_strdup(PQgetvalue(res, j, i_conname));
+				constrs[j].dobj.namespace = tbinfo->dobj.namespace;
+				constrs[j].contable = tbinfo;
+				constrs[j].condomain = NULL;
+				constrs[j].contype = 'n';
+				constrs[j].condef = pg_strdup(PQgetvalue(res, j, i_consrc));
+				constrs[j].confrelid = InvalidOid;
+				constrs[j].conindex = 0;
+				constrs[j].condeferrable = false;
+				constrs[j].condeferred = false;
+				constrs[j].conislocal = (PQgetvalue(res, j, i_conislocal)[0] == 't');
+
+				/*
+				 * An unvalidated constraint needs to be dumped separately, so
+				 * that potentially-violating existing data is loaded before
+				 * the constraint.
+				 */
+				constrs[j].separate = !validated;
+
+				constrs[j].dobj.dump = tbinfo->dobj.dump;
+
+				/*
+				 * Mark the constraint as needing to appear before the table
+				 * --- this is so that any other dependencies of the
+				 * constraint will be emitted before we try to create the
+				 * table.  If the constraint is to be dumped separately, it
+				 * will be dumped after data is loaded anyway, so don't do it.
+				 * (There's an automatic dependency in the opposite direction
+				 * anyway, so don't need to add one manually here.)
+				 */
+				if (!constrs[j].separate)
+					addObjectDependency(&tbinfo->dobj,
+										constrs[j].dobj.dumpId);
+
+				/*
+				 * We will detect later whether the constraint must be split
+				 * out from the table definition.
+				 */
+			}
+		}
+		PQclear(res);
+	}
+
 	destroyPQExpBuffer(q);
 	destroyPQExpBuffer(tbloids);
 	destroyPQExpBuffer(checkoids);
@@ -16543,7 +16695,8 @@ dumpTableSchema(Archive *fout, const TableInfo *tbinfo)
 					 * defined, or if binary upgrade.  (In the latter case, we
 					 * reset conislocal below.)
 					 */
-					print_notnull = (tbinfo->notnull_constrs[j] != NULL &&
+					print_notnull = (tbinfo->notnull_validated[j] &&
+									 tbinfo->notnull_constrs[j] != NULL &&
 									 (tbinfo->notnull_islocal[j] ||
 									  dopt->binary_upgrade ||
 									  tbinfo->ispartition));
@@ -16605,11 +16758,6 @@ dumpTableSchema(Archive *fout, const TableInfo *tbinfo)
 											  tbinfo->attrdefs[j]->adef_expr);
 					}
 
-					print_notnull = (tbinfo->notnull_constrs[j] != NULL &&
-									 (tbinfo->notnull_islocal[j] ||
-									  dopt->binary_upgrade ||
-									  tbinfo->ispartition));
-
 					if (print_notnull)
 					{
 						if (tbinfo->notnull_constrs[j][0] == '\0')
@@ -16928,7 +17076,8 @@ dumpTableSchema(Archive *fout, const TableInfo *tbinfo)
 				 * because the latter is not available.  (We distinguish the
 				 * case because the constraint name is the empty string.)
 				 */
-				if (tbinfo->notnull_constrs[j] != NULL &&
+				if (tbinfo->notnull_validated[j] &&
+					tbinfo->notnull_constrs[j] != NULL &&
 					!tbinfo->notnull_islocal[j])
 				{
 					if (tbinfo->notnull_constrs[j][0] != '\0')
@@ -17931,9 +18080,9 @@ dumpConstraint(Archive *fout, const ConstraintInfo *coninfo)
 									  .createStmt = q->data,
 									  .dropStmt = delq->data));
 	}
-	else if (coninfo->contype == 'c' && tbinfo)
+	else if ((coninfo->contype == 'c' || coninfo->contype == 'n') && tbinfo)
 	{
-		/* CHECK constraint on a table */
+		/* CHECK/INVALID_NOTNULL constraint on a table */
 
 		/* Ignore if not to be dumped separately, or if it was inherited */
 		if (coninfo->separate && coninfo->conislocal)
diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h
index bbdb30b5f54..676bf2c53c1 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -365,6 +365,7 @@ typedef struct _tableInfo
 									 * there isn't one on this column. If
 									 * empty string, unnamed constraint
 									 * (pre-v17) */
+	bool	   *notnull_validated;	/* NOT NULL is VALIDATED */
 	bool	   *notnull_noinh;	/* NOT NULL is NO INHERIT */
 	bool	   *notnull_islocal;	/* true if NOT NULL has local definition */
 	struct _attrDefInfo **attrdefs; /* DEFAULT expressions */
-- 
2.43.0