On Wed, Jun 18, 2025 at 11:05 PM Álvaro Herrera <[email protected]> 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 <[email protected]>
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/[email protected]
---
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
