hi. ---------------------------------------------------- begin; create schema test; set search_path to test; create domain d1 as int; alter domain d1 add constraint nn not null; alter domain d1 add constraint cc check (value is not null); comment on constraint nn on domain d1 is 'not null constraint on domain d1'; comment on constraint cc on domain d1 is 'check constraint on domain d1'; commit;
------the above is the test script------------------ in PG17 and master, pg_dump (--schema=test --no-owner) will only produce COMMENT ON CONSTRAINT cc ON DOMAIN test.d1 IS 'check constraint on domain d1'; but didn't produce COMMENT ON CONSTRAINT nn ON DOMAIN test.d1 IS 'not null constraint on domain d1'; we should make pg_dump to produce it too? The attached patch tries to make it produce comments on not-null constraints on domains. I aslo renamed struct TypeInfo fields, nDomChecks will be renamed to nDomConstrs; domChecks will be renamed to domConstrs. TypeInfo->domConstrs will also include not-null constraint information, changing from domChecks to domConstrs makes sense, IMHO.
From fcaba2b44f62ae76404095352edcecd0cbc967ff Mon Sep 17 00:00:00 2001 From: jian he <jian.universal...@gmail.com> Date: Wed, 7 May 2025 16:59:35 +0800 Subject: [PATCH v1 1/1] pg_dump does not dump domain not-null constraint's comments this patch trying to resolve this issue. discussion: https://postgr.es/m/ --- src/bin/pg_dump/pg_dump.c | 66 ++++++++++++++++++++++------------ src/bin/pg_dump/pg_dump.h | 4 +-- src/bin/pg_dump/pg_dump_sort.c | 5 +-- 3 files changed, 46 insertions(+), 29 deletions(-) diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index e2e7975b34e..7253937f9a3 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -6118,8 +6118,8 @@ getTypes(Archive *fout) /* * If it's a domain, fetch info about its constraints, if any */ - tyinfo[i].nDomChecks = 0; - tyinfo[i].domChecks = NULL; + tyinfo[i].nDomConstrs = 0; + tyinfo[i].domConstrs = NULL; if ((tyinfo[i].dobj.dump & DUMP_COMPONENT_DEFINITION) && tyinfo[i].typtype == TYPTYPE_DOMAIN) getDomainConstraints(fout, &(tyinfo[i])); @@ -8250,7 +8250,8 @@ getDomainConstraints(Archive *fout, TypeInfo *tyinfo) int i_tableoid, i_oid, i_conname, - i_consrc; + i_consrc, + i_contype; int ntups; if (!fout->is_prepared[PREPQUERY_GETDOMAINCONSTRAINTS]) @@ -8260,10 +8261,19 @@ getDomainConstraints(Archive *fout, TypeInfo *tyinfo) "PREPARE getDomainConstraints(pg_catalog.oid) AS\n" "SELECT tableoid, oid, conname, " "pg_catalog.pg_get_constraintdef(oid) AS consrc, " - "convalidated " + "convalidated, " + "contype " "FROM pg_catalog.pg_constraint " - "WHERE contypid = $1 AND contype = 'c' " - "ORDER BY conname"); + "WHERE contypid = $1 "); + + /* + * in PG17 we record not-null domain constraint catalog information into + * pg_constraint + */ + if (fout->remoteVersion < 170000) + appendPQExpBufferStr(query, "AND contype = 'c' "); + + appendPQExpBufferStr(query, "ORDER BY conname"); ExecuteSqlStatement(fout, query->data); @@ -8282,11 +8292,12 @@ getDomainConstraints(Archive *fout, TypeInfo *tyinfo) i_oid = PQfnumber(res, "oid"); i_conname = PQfnumber(res, "conname"); i_consrc = PQfnumber(res, "consrc"); + i_contype = PQfnumber(res, "contype"); constrinfo = (ConstraintInfo *) pg_malloc(ntups * sizeof(ConstraintInfo)); - tyinfo->nDomChecks = ntups; - tyinfo->domChecks = constrinfo; + tyinfo->nDomConstrs = ntups; + tyinfo->domConstrs = constrinfo; for (i = 0; i < ntups; i++) { @@ -8300,7 +8311,7 @@ getDomainConstraints(Archive *fout, TypeInfo *tyinfo) constrinfo[i].dobj.namespace = tyinfo->dobj.namespace; constrinfo[i].contable = NULL; constrinfo[i].condomain = tyinfo; - constrinfo[i].contype = 'c'; + constrinfo[i].contype = *(PQgetvalue(res, i, i_contype)); constrinfo[i].condef = pg_strdup(PQgetvalue(res, i, i_consrc)); constrinfo[i].confrelid = InvalidOid; constrinfo[i].conindex = 0; @@ -12479,7 +12490,7 @@ dumpDomain(Archive *fout, const TypeInfo *tyinfo) appendPQExpBuffer(q, " COLLATE %s", fmtQualifiedDumpable(coll)); } - if (typnotnull[0] == 't') + if (typnotnull[0] == 't' && fout->remoteVersion < 170000) appendPQExpBufferStr(q, " NOT NULL"); if (typdefault != NULL) @@ -12494,15 +12505,15 @@ dumpDomain(Archive *fout, const TypeInfo *tyinfo) PQclear(res); /* - * Add any CHECK constraints for the domain + * Add any CHECK, NOT NULL constraints for the domain */ - for (i = 0; i < tyinfo->nDomChecks; i++) + for (i = 0; i < tyinfo->nDomConstrs; i++) { - ConstraintInfo *domcheck = &(tyinfo->domChecks[i]); + ConstraintInfo *domconstr = &(tyinfo->domConstrs[i]); - if (!domcheck->separate) + if (!domconstr->separate) appendPQExpBuffer(q, "\n\tCONSTRAINT %s %s", - fmtId(domcheck->dobj.name), domcheck->condef); + fmtId(domconstr->dobj.name), domconstr->condef); } appendPQExpBufferStr(q, ";\n"); @@ -12542,19 +12553,19 @@ dumpDomain(Archive *fout, const TypeInfo *tyinfo) NULL, tyinfo->rolname, &tyinfo->dacl); /* Dump any per-constraint comments */ - for (i = 0; i < tyinfo->nDomChecks; i++) + for (i = 0; i < tyinfo->nDomConstrs; i++) { - ConstraintInfo *domcheck = &(tyinfo->domChecks[i]); + ConstraintInfo *domconstr = &(tyinfo->domConstrs[i]); PQExpBuffer conprefix = createPQExpBuffer(); appendPQExpBuffer(conprefix, "CONSTRAINT %s ON DOMAIN", - fmtId(domcheck->dobj.name)); + fmtId(domconstr->dobj.name)); - if (domcheck->dobj.dump & DUMP_COMPONENT_COMMENT) + if (domconstr->dobj.dump & DUMP_COMPONENT_COMMENT) dumpComment(fout, conprefix->data, qtypname, tyinfo->dobj.namespace->dobj.name, tyinfo->rolname, - domcheck->dobj.catId, 0, tyinfo->dobj.dumpId); + domconstr->dobj.catId, 0, tyinfo->dobj.dumpId); destroyPQExpBuffer(conprefix); } @@ -18370,14 +18381,23 @@ dumpConstraint(Archive *fout, const ConstraintInfo *coninfo) .dropStmt = delq->data)); } } - else if (coninfo->contype == 'c' && tbinfo == NULL) + else if (tbinfo == NULL) { - /* CHECK constraint on a domain */ + /* CHECK, NOT NULL constraint on a domain */ TypeInfo *tyinfo = coninfo->condomain; + Assert(coninfo->contype == 'c' || coninfo->contype == 'n'); + /* Ignore if not to be dumped separately */ if (coninfo->separate) { + const char *keyword; + + if (coninfo->contype == 'c') + keyword = "CHECK CONSTRAINT"; + else + keyword = "CONSTRAINT"; + appendPQExpBuffer(q, "ALTER DOMAIN %s\n", fmtQualifiedDumpable(tyinfo)); appendPQExpBuffer(q, " ADD CONSTRAINT %s %s;\n", @@ -18396,7 +18416,7 @@ dumpConstraint(Archive *fout, const ConstraintInfo *coninfo) ARCHIVE_OPTS(.tag = tag, .namespace = tyinfo->dobj.namespace->dobj.name, .owner = tyinfo->rolname, - .description = "CHECK CONSTRAINT", + .description = keyword, .section = SECTION_POST_DATA, .createStmt = q->data, .dropStmt = delq->data)); diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h index 7417eab6aef..b5e19ecc2e3 100644 --- a/src/bin/pg_dump/pg_dump.h +++ b/src/bin/pg_dump/pg_dump.h @@ -223,8 +223,8 @@ typedef struct _typeInfo /* If needed, we'll create a "shell type" entry for it; link that here: */ struct _shellTypeInfo *shellType; /* shell-type entry, or NULL */ /* If it's a domain, we store links to its constraints here: */ - int nDomChecks; - struct _constraintInfo *domChecks; + int nDomConstrs; + struct _constraintInfo *domConstrs; } TypeInfo; typedef struct _shellTypeInfo diff --git a/src/bin/pg_dump/pg_dump_sort.c b/src/bin/pg_dump/pg_dump_sort.c index 0b0977788f1..0497d7e37fc 100644 --- a/src/bin/pg_dump/pg_dump_sort.c +++ b/src/bin/pg_dump/pg_dump_sort.c @@ -907,7 +907,7 @@ repairTableAttrDefMultiLoop(DumpableObject *tableobj, } /* - * CHECK constraints on domains work just like those on tables ... + * CHECK, NOT NULL constraints on domains work just like those on tables ... */ static void repairDomainConstraintLoop(DumpableObject *domainobj, @@ -1177,7 +1177,6 @@ repairDependencyLoop(DumpableObject **loop, if (nLoop == 2 && loop[0]->objType == DO_TYPE && loop[1]->objType == DO_CONSTRAINT && - ((ConstraintInfo *) loop[1])->contype == 'c' && ((ConstraintInfo *) loop[1])->condomain == (TypeInfo *) loop[0]) { repairDomainConstraintLoop(loop[0], loop[1]); @@ -1186,7 +1185,6 @@ repairDependencyLoop(DumpableObject **loop, if (nLoop == 2 && loop[1]->objType == DO_TYPE && loop[0]->objType == DO_CONSTRAINT && - ((ConstraintInfo *) loop[0])->contype == 'c' && ((ConstraintInfo *) loop[0])->condomain == (TypeInfo *) loop[1]) { repairDomainConstraintLoop(loop[1], loop[0]); @@ -1203,7 +1201,6 @@ repairDependencyLoop(DumpableObject **loop, for (j = 0; j < nLoop; j++) { if (loop[j]->objType == DO_CONSTRAINT && - ((ConstraintInfo *) loop[j])->contype == 'c' && ((ConstraintInfo *) loop[j])->condomain == (TypeInfo *) loop[i]) { repairDomainConstraintMultiLoop(loop[i], loop[j]); -- 2.34.1
