On Wed, Jun 18, 2025 at 11:05 PM Álvaro Herrera <alvhe...@kurilemu.de> wrote:
>
> I agree that this is roughly the right approach, but I think you're
> doing it harder than it needs to be -- it might be easier to add a JOIN
> to pg_description to the big query in getTableAttrs(), and add a pointer
> to the returned string in tbinfo->notnull_comments[j] (for versions
> earlier than 18, don't add the join and have it return constant NULL).
> Then in dumpTableSchema, in the place where you added the new query,
> just scan that array and print COMMENT ON commands for each valid
> constraint where that's not a null pointer.
>

Previously I was worried about print_notnull, shouldPrintColumn.
if there is a not-null constraint that is not dumped separately, it has comments
then we should dump these comments, then no need to worry about print_notnull.

using notnull_comments saves us one more query.
however, in determineNotNullFlags we have:

                char       *default_name;
                /* XXX should match ChooseConstraintName better */
                default_name = psprintf("%s_%s_not_null", tbinfo->dobj.name,
                                        tbinfo->attnames[j]);
                if (strcmp(default_name,
                           PQgetvalue(res, r, i_notnull_name)) == 0)
                    tbinfo->notnull_constrs[j] = "";

then we can not blindly use tbinfo->notnull_constrs as the not-null
constraint name.
if tbinfo->notnull_constrs is an empty string, we need to use the above
"%s_%s_not_null" trick to get the default no-null constraint name.
From 4cce00779490001f4e40fb3055c7bddd539e1ad2 Mon Sep 17 00:00:00 2001
From: jian he <jian.universal...@gmail.com>
Date: Thu, 19 Jun 2025 10:26:44 +0800
Subject: [PATCH v2 1/1] fix pg_dump not dump comments on not-null constraints
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit

dumpComment doesn't handle these inlined NOT NULL constraint comments, because
collectComments doesn't collect them—they aren't associated with a separate
dumpable object.  Rather than modifying collectComments, we manually dump these
inlined not-null constraint comments within dumpTableSchema.

reported by: Fujii Masao <masao(dot)fujii(at)oss(dot)nttdata(dot)com>

discussion: https://postgr.es/m/d50ff977-c728-4e9e-8488-fc2688e08...@oss.nttdata.com
---
 src/bin/pg_dump/pg_dump.c | 95 ++++++++++++++++++++++++++++++++++++++-
 src/bin/pg_dump/pg_dump.h |  1 +
 2 files changed, 95 insertions(+), 1 deletion(-)

diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 7bc0724cd30..dbd557b7484 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -9006,6 +9006,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
 	int			i_notnull_name;
 	int			i_notnull_noinherit;
 	int			i_notnull_islocal;
+	int			i_notnull_comments;
 	int			i_notnull_invalidoid;
 	int			i_attoptions;
 	int			i_attcollation;
@@ -9116,6 +9117,11 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
 							 "false AS notnull_noinherit,\n"
 							 "a.attislocal AS notnull_islocal,\n");
 
+	if (fout->remoteVersion >= 180000)
+		appendPQExpBufferStr(q, "pt.description AS notnull_comments,\n");
+	else
+		appendPQExpBufferStr(q, "NULL AS notnull_comments,\n");
+
 	if (fout->remoteVersion >= 140000)
 		appendPQExpBufferStr(q,
 							 "a.attcompression AS attcompression,\n");
@@ -9159,12 +9165,16 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
 	 * backing a primary key.
 	 */
 	if (fout->remoteVersion >= 180000)
+	{
 		appendPQExpBufferStr(q,
 							 " LEFT JOIN pg_catalog.pg_constraint co ON "
 							 "(a.attrelid = co.conrelid\n"
 							 "   AND co.contype = 'n' AND "
 							 "co.conkey = array[a.attnum])\n");
-
+		appendPQExpBufferStr(q,
+							 " LEFT JOIN pg_catalog.pg_description pt ON "
+							 "(pt.classoid = co.tableoid AND pt.objoid = co.oid)\n");
+	}
 	appendPQExpBufferStr(q,
 						 "WHERE a.attnum > 0::pg_catalog.int2\n"
 						 "ORDER BY a.attrelid, a.attnum");
@@ -9190,6 +9200,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
 	i_notnull_invalidoid = PQfnumber(res, "notnull_invalidoid");
 	i_notnull_noinherit = PQfnumber(res, "notnull_noinherit");
 	i_notnull_islocal = PQfnumber(res, "notnull_islocal");
+	i_notnull_comments = PQfnumber(res, "notnull_comments");
 	i_attoptions = PQfnumber(res, "attoptions");
 	i_attcollation = PQfnumber(res, "attcollation");
 	i_attcompression = PQfnumber(res, "attcompression");
@@ -9258,6 +9269,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
 		tbinfo->notnull_invalid = (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->notnull_comments = (char **) pg_malloc(numatts * sizeof(char *));
 		tbinfo->attrdefs = (AttrDefInfo **) pg_malloc(numatts * sizeof(AttrDefInfo *));
 		hasdefaults = false;
 
@@ -9291,6 +9303,10 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
 								  i_notnull_islocal,
 								  &invalidnotnulloids);
 
+			if (PQgetisnull(res, r, i_notnull_comments))
+				tbinfo->notnull_comments[j] = NULL;
+			else
+				tbinfo->notnull_comments[j] = pg_strdup(PQgetvalue(res, r, i_notnull_comments));
 			tbinfo->attoptions[j] = pg_strdup(PQgetvalue(res, r, i_attoptions));
 			tbinfo->attcollation[j] = atooid(PQgetvalue(res, r, i_attcollation));
 			tbinfo->attcompression[j] = *(PQgetvalue(res, r, i_attcompression));
@@ -17684,6 +17700,83 @@ dumpTableSchema(Archive *fout, const TableInfo *tbinfo)
 	if (tbinfo->dobj.dump & DUMP_COMPONENT_SECLABEL)
 		dumpTableSecLabel(fout, tbinfo, reltypename);
 
+	/*
+	 * Dump Table not-null constraint comments.
+	 * Invalid not-null constraints are dumped separately.  So we only need to
+	 * handle comments for these "inlined" not-null constraints.
+	 * collectComments function does not collect comments for inlined not-null
+	 * constraints, as they don't have separate dumpable object associated with
+	 * it. Therefore, we need to explicitly dump comments for these "inlined"
+	 * not-null constraints now, since they won't be dumped separately.
+	 */
+	if (!fout->dopt->no_comments &&
+		 dopt->dumpSchema &&
+		 fout->remoteVersion >= 180000)
+	{
+		char	*qtabname;
+		const char 	*namespace = tbinfo->dobj.namespace->dobj.name;
+		const char 	*owner = tbinfo->rolname;
+		PQExpBuffer comments = createPQExpBuffer();
+		PQExpBuffer conprefix = createPQExpBuffer();
+		PQExpBuffer tag = createPQExpBuffer();
+		DumpId	dumpId = tbinfo->dobj.dumpId;
+		qtabname = pg_strdup(fmtId(tbinfo->dobj.name));
+
+		for (j = 0; j < tbinfo->numatts; j++)
+		{
+			if (tbinfo->notnull_constrs[j] != NULL &&
+				tbinfo->notnull_comments[j] != NULL)
+			{
+				/*
+				 * For some not-null constraints, the notnull_constrs is an
+				 * empty string, because in certain cases we don't need to emit
+				 * the constraint name.  See determineNotNullFlags too.
+				*/
+				if (tbinfo->notnull_constrs[j][0] == '\0')
+				{
+					char	   *default_name;
+					default_name = psprintf("%s_%s_not_null", tbinfo->dobj.name,
+											tbinfo->attnames[j]);
+
+					appendPQExpBuffer(conprefix, "CONSTRAINT %s ON",
+									  fmtId(default_name));
+				}
+				else
+					appendPQExpBuffer(conprefix, "CONSTRAINT %s ON",
+									  fmtId(tbinfo->notnull_constrs[j]));
+
+				appendPQExpBuffer(comments, "COMMENT ON %s ", conprefix->data);
+				if (namespace && *namespace)
+					appendPQExpBuffer(comments, "%s.", fmtId(namespace));
+
+				appendPQExpBuffer(comments, "%s IS ", qtabname);
+				appendStringLiteralAH(comments, tbinfo->notnull_comments[j], fout);
+				appendPQExpBufferStr(comments, ";\n");
+
+				appendPQExpBuffer(tag, "%s %s", conprefix->data, qtabname);
+
+				/* see dumpCommentExtended also */
+				ArchiveEntry(fout, nilCatalogId, createDumpId(),
+							ARCHIVE_OPTS(.tag = tag->data,
+										.namespace = namespace,
+										.owner = owner,
+										.description = "COMMENT",
+										.section = SECTION_NONE,
+										.createStmt = comments->data,
+										.deps = &dumpId,
+										.nDeps = 1));
+
+				resetPQExpBuffer(comments);
+				resetPQExpBuffer(tag);
+				resetPQExpBuffer(conprefix);
+			}
+		}
+		destroyPQExpBuffer(conprefix);
+		destroyPQExpBuffer(comments);
+		destroyPQExpBuffer(tag);
+		free(qtabname);
+	}
+
 	/* Dump comments on inlined table constraints */
 	for (j = 0; j < tbinfo->ncheck; j++)
 	{
diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h
index 7417eab6aef..8092d5fddc2 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -368,6 +368,7 @@ typedef struct _tableInfo
 	bool	   *notnull_invalid;	/* true for NOT NULL NOT VALID */
 	bool	   *notnull_noinh;	/* NOT NULL is NO INHERIT */
 	bool	   *notnull_islocal;	/* true if NOT NULL has local definition */
+	char	  **notnull_comments;	/* per-attribute not-null constraint's comments */
 	struct _attrDefInfo **attrdefs; /* DEFAULT expressions */
 	struct _constraintInfo *checkexprs; /* CHECK constraints */
 	struct _relStatsInfo *stats;	/* only set for matviews */
-- 
2.34.1

Reply via email to