Skip to content

Commit 6b755d8

Browse files
Álvaro Herrerajianhe-fun
Álvaro Herrera
and
jianhe-fun
committed
pg_dump: include comments on not-null constraints on domains, too
Commit e5da0fe introduced catalog entries for not-null constraints on domains; but because commit b0e96f3 (the original work for catalogued not-null constraints on tables) forgot to teach pg_dump to process the comments for them, this one also forgot. Add that now. We also need to teach repairDependencyLoop() about the new type of constraints being possible for domains. Backpatch-through: 17 Co-authored-by: jian he <jian.universality@gmail.com> Co-authored-by: Álvaro Herrera <alvherre@kurilemu.de> Reported-by: jian he <jian.universality@gmail.com> Discussion: https://postgr.es/m/CACJufxF-0bqVR=j4jonS6N2Ka6hHUpFyu3_3TWKNhOW_4yFSSg@mail.gmail.com
1 parent 227d0ce commit 6b755d8

File tree

4 files changed

+160
-49
lines changed

4 files changed

+160
-49
lines changed

‎src/bin/pg_dump/pg_dump.c

Lines changed: 120 additions & 40 deletions
Original file line numberDiff line numberDiff line change
@@ -47,6 +47,7 @@
4747
#include "catalog/pg_authid_d.h"
4848
#include "catalog/pg_cast_d.h"
4949
#include "catalog/pg_class_d.h"
50+
#include "catalog/pg_constraint_d.h"
5051
#include "catalog/pg_default_acl_d.h"
5152
#include "catalog/pg_largeobject_d.h"
5253
#include "catalog/pg_largeobject_metadata_d.h"
@@ -5929,6 +5930,7 @@ getTypes(Archive *fout, int *numTypes)
59295930
*/
59305931
tyinfo[i].nDomChecks = 0;
59315932
tyinfo[i].domChecks = NULL;
5933+
tyinfo[i].notnull = NULL;
59325934
if ((tyinfo[i].dobj.dump & DUMP_COMPONENT_DEFINITION) &&
59335935
tyinfo[i].typtype == TYPTYPE_DOMAIN)
59345936
getDomainConstraints(fout, &(tyinfo[i]));
@@ -7949,27 +7951,33 @@ addConstrChildIdxDeps(DumpableObject *dobj, const IndxInfo *refidx)
79497951
static void
79507952
getDomainConstraints(Archive *fout, TypeInfo *tyinfo)
79517953
{
7952-
int i;
79537954
ConstraintInfo *constrinfo;
79547955
PQExpBuffer query = createPQExpBuffer();
79557956
PGresult *res;
79567957
int i_tableoid,
79577958
i_oid,
79587959
i_conname,
7959-
i_consrc;
7960+
i_consrc,
7961+
i_convalidated,
7962+
i_contype;
79607963
int ntups;
79617964

79627965
if (!fout->is_prepared[PREPQUERY_GETDOMAINCONSTRAINTS])
79637966
{
7964-
/* Set up query for constraint-specific details */
7965-
appendPQExpBufferStr(query,
7966-
"PREPARE getDomainConstraints(pg_catalog.oid) AS\n"
7967-
"SELECT tableoid, oid, conname, "
7968-
"pg_catalog.pg_get_constraintdef(oid) AS consrc, "
7969-
"convalidated "
7970-
"FROM pg_catalog.pg_constraint "
7971-
"WHERE contypid = $1 AND contype = 'c' "
7972-
"ORDER BY conname");
7967+
/*
7968+
* Set up query for constraint-specific details. For servers 17 and
7969+
* up, domains have constraints of type 'n' as well as 'c', otherwise
7970+
* just the latter.
7971+
*/
7972+
appendPQExpBuffer(query,
7973+
"PREPARE getDomainConstraints(pg_catalog.oid) AS\n"
7974+
"SELECT tableoid, oid, conname, "
7975+
"pg_catalog.pg_get_constraintdef(oid) AS consrc, "
7976+
"convalidated, contype "
7977+
"FROM pg_catalog.pg_constraint "
7978+
"WHERE contypid = $1 AND contype IN (%s) "
7979+
"ORDER BY conname",
7980+
fout->remoteVersion < 170000 ? "'c'" : "'c', 'n'");
79737981

79747982
ExecuteSqlStatement(fout, query->data);
79757983

@@ -7988,33 +7996,50 @@ getDomainConstraints(Archive *fout, TypeInfo *tyinfo)
79887996
i_oid = PQfnumber(res, "oid");
79897997
i_conname = PQfnumber(res, "conname");
79907998
i_consrc = PQfnumber(res, "consrc");
7999+
i_convalidated = PQfnumber(res, "convalidated");
8000+
i_contype = PQfnumber(res, "contype");
79918001

79928002
constrinfo = (ConstraintInfo *) pg_malloc(ntups * sizeof(ConstraintInfo));
7993-
7994-
tyinfo->nDomChecks = ntups;
79958003
tyinfo->domChecks = constrinfo;
79968004

7997-
for (i = 0; i < ntups; i++)
8005+
/* 'i' tracks result rows; 'j' counts CHECK constraints */
8006+
for (int i = 0, j = 0; i < ntups; i++)
79988007
{
7999-
bool validated = PQgetvalue(res, i, 4)[0] == 't';
8000-
8001-
constrinfo[i].dobj.objType = DO_CONSTRAINT;
8002-
constrinfo[i].dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_tableoid));
8003-
constrinfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
8004-
AssignDumpId(&constrinfo[i].dobj);
8005-
constrinfo[i].dobj.name = pg_strdup(PQgetvalue(res, i, i_conname));
8006-
constrinfo[i].dobj.namespace = tyinfo->dobj.namespace;
8007-
constrinfo[i].contable = NULL;
8008-
constrinfo[i].condomain = tyinfo;
8009-
constrinfo[i].contype = 'c';
8010-
constrinfo[i].condef = pg_strdup(PQgetvalue(res, i, i_consrc));
8011-
constrinfo[i].confrelid = InvalidOid;
8012-
constrinfo[i].conindex = 0;
8013-
constrinfo[i].condeferrable = false;
8014-
constrinfo[i].condeferred = false;
8015-
constrinfo[i].conislocal = true;
8016-
8017-
constrinfo[i].separate = !validated;
8008+
bool validated = PQgetvalue(res, i, i_convalidated)[0] == 't';
8009+
char contype = (PQgetvalue(res, i, i_contype))[0];
8010+
ConstraintInfo *constraint;
8011+
8012+
if (contype == CONSTRAINT_CHECK)
8013+
{
8014+
constraint = &constrinfo[j++];
8015+
tyinfo->nDomChecks++;
8016+
}
8017+
else
8018+
{
8019+
Assert(contype == CONSTRAINT_NOTNULL);
8020+
Assert(tyinfo->notnull == NULL);
8021+
/* use last item in array for the not-null constraint */
8022+
tyinfo->notnull = &(constrinfo[ntups - 1]);
8023+
constraint = tyinfo->notnull;
8024+
}
8025+
8026+
constraint->dobj.objType = DO_CONSTRAINT;
8027+
constraint->dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_tableoid));
8028+
constraint->dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
8029+
AssignDumpId(&(constraint->dobj));
8030+
constraint->dobj.name = pg_strdup(PQgetvalue(res, i, i_conname));
8031+
constraint->dobj.namespace = tyinfo->dobj.namespace;
8032+
constraint->contable = NULL;
8033+
constraint->condomain = tyinfo;
8034+
constraint->contype = contype;
8035+
constraint->condef = pg_strdup(PQgetvalue(res, i, i_consrc));
8036+
constraint->confrelid = InvalidOid;
8037+
constraint->conindex = 0;
8038+
constraint->condeferrable = false;
8039+
constraint->condeferred = false;
8040+
constraint->conislocal = true;
8041+
8042+
constraint->separate = !validated;
80188043

80198044
/*
80208045
* Make the domain depend on the constraint, ensuring it won't be
@@ -8023,8 +8048,7 @@ getDomainConstraints(Archive *fout, TypeInfo *tyinfo)
80238048
* anyway, so this doesn't matter.
80248049
*/
80258050
if (validated)
8026-
addObjectDependency(&tyinfo->dobj,
8027-
constrinfo[i].dobj.dumpId);
8051+
addObjectDependency(&tyinfo->dobj, constraint->dobj.dumpId);
80288052
}
80298053

80308054
PQclear(res);
@@ -11557,8 +11581,36 @@ dumpDomain(Archive *fout, const TypeInfo *tyinfo)
1155711581
appendPQExpBuffer(q, " COLLATE %s", fmtQualifiedDumpable(coll));
1155811582
}
1155911583

11584+
/*
11585+
* Print a not-null constraint if there's one. In servers older than 17
11586+
* these don't have names, so just print it unadorned; in newer ones they
11587+
* do, but most of the time it's going to be the standard generated one,
11588+
* so omit the name in that case also.
11589+
*/
1156011590
if (typnotnull[0] == 't')
11561-
appendPQExpBufferStr(q, " NOT NULL");
11591+
{
11592+
if (fout->remoteVersion < 170000 || tyinfo->notnull == NULL)
11593+
appendPQExpBufferStr(q, " NOT NULL");
11594+
else
11595+
{
11596+
ConstraintInfo *notnull = tyinfo->notnull;
11597+
11598+
if (!notnull->separate)
11599+
{
11600+
char *default_name;
11601+
11602+
/* XXX should match ChooseConstraintName better */
11603+
default_name = psprintf("%s_not_null", tyinfo->dobj.name);
11604+
11605+
if (strcmp(default_name, notnull->dobj.name) == 0)
11606+
appendPQExpBufferStr(q, " NOT NULL");
11607+
else
11608+
appendPQExpBuffer(q, " CONSTRAINT %s %s",
11609+
fmtId(notnull->dobj.name), notnull->condef);
11610+
free(default_name);
11611+
}
11612+
}
11613+
}
1156211614

1156311615
if (typdefault != NULL)
1156411616
{
@@ -11578,7 +11630,7 @@ dumpDomain(Archive *fout, const TypeInfo *tyinfo)
1157811630
{
1157911631
ConstraintInfo *domcheck = &(tyinfo->domChecks[i]);
1158011632

11581-
if (!domcheck->separate)
11633+
if (!domcheck->separate && domcheck->contype == 'c')
1158211634
appendPQExpBuffer(q, "\n\tCONSTRAINT %s %s",
1158311635
fmtId(domcheck->dobj.name), domcheck->condef);
1158411636
}
@@ -11642,6 +11694,25 @@ dumpDomain(Archive *fout, const TypeInfo *tyinfo)
1164211694
destroyPQExpBuffer(conprefix);
1164311695
}
1164411696

11697+
/*
11698+
* And a comment on the not-null constraint, if there's one -- but only if
11699+
* the constraint itself was dumped here
11700+
*/
11701+
if (tyinfo->notnull != NULL && !tyinfo->notnull->separate)
11702+
{
11703+
PQExpBuffer conprefix = createPQExpBuffer();
11704+
11705+
appendPQExpBuffer(conprefix, "CONSTRAINT %s ON DOMAIN",
11706+
fmtId(tyinfo->notnull->dobj.name));
11707+
11708+
if (tyinfo->notnull->dobj.dump & DUMP_COMPONENT_COMMENT)
11709+
dumpComment(fout, conprefix->data, qtypname,
11710+
tyinfo->dobj.namespace->dobj.name,
11711+
tyinfo->rolname,
11712+
tyinfo->notnull->dobj.catId, 0, tyinfo->dobj.dumpId);
11713+
destroyPQExpBuffer(conprefix);
11714+
}
11715+
1164511716
destroyPQExpBuffer(q);
1164611717
destroyPQExpBuffer(delq);
1164711718
destroyPQExpBuffer(query);
@@ -17336,14 +17407,23 @@ dumpConstraint(Archive *fout, const ConstraintInfo *coninfo)
1733617407
.dropStmt = delq->data));
1733717408
}
1733817409
}
17339-
else if (coninfo->contype == 'c' && tbinfo == NULL)
17410+
else if (tbinfo == NULL)
1734017411
{
17341-
/* CHECK constraint on a domain */
17412+
/* CHECK, NOT NULL constraint on a domain */
1734217413
TypeInfo *tyinfo = coninfo->condomain;
1734317414

17415+
Assert(coninfo->contype == 'c' || coninfo->contype == 'n');
17416+
1734417417
/* Ignore if not to be dumped separately */
1734517418
if (coninfo->separate)
1734617419
{
17420+
const char *keyword;
17421+
17422+
if (coninfo->contype == 'c')
17423+
keyword = "CHECK CONSTRAINT";
17424+
else
17425+
keyword = "CONSTRAINT";
17426+
1734717427
appendPQExpBuffer(q, "ALTER DOMAIN %s\n",
1734817428
fmtQualifiedDumpable(tyinfo));
1734917429
appendPQExpBuffer(q, " ADD CONSTRAINT %s %s;\n",
@@ -17362,7 +17442,7 @@ dumpConstraint(Archive *fout, const ConstraintInfo *coninfo)
1736217442
ARCHIVE_OPTS(.tag = tag,
1736317443
.namespace = tyinfo->dobj.namespace->dobj.name,
1736417444
.owner = tyinfo->rolname,
17365-
.description = "CHECK CONSTRAINT",
17445+
.description = keyword,
1736617446
.section = SECTION_POST_DATA,
1736717447
.createStmt = q->data,
1736817448
.dropStmt = delq->data));

‎src/bin/pg_dump/pg_dump.h

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -215,7 +215,9 @@ typedef struct _typeInfo
215215
bool isDefined; /* true if typisdefined */
216216
/* If needed, we'll create a "shell type" entry for it; link that here: */
217217
struct _shellTypeInfo *shellType; /* shell-type entry, or NULL */
218-
/* If it's a domain, we store links to its constraints here: */
218+
/* If it's a domain, its not-null constraint is here: */
219+
struct _constraintInfo *notnull;
220+
/* If it's a domain, we store links to its CHECK constraints here: */
219221
int nDomChecks;
220222
struct _constraintInfo *domChecks;
221223
} TypeInfo;

‎src/bin/pg_dump/pg_dump_sort.c

Lines changed: 9 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -884,7 +884,7 @@ repairTableAttrDefMultiLoop(DumpableObject *tableobj,
884884
}
885885

886886
/*
887-
* CHECK constraints on domains work just like those on tables ...
887+
* CHECK, NOT NULL constraints on domains work just like those on tables ...
888888
*/
889889
static void
890890
repairDomainConstraintLoop(DumpableObject *domainobj,
@@ -1135,11 +1135,12 @@ repairDependencyLoop(DumpableObject **loop,
11351135
}
11361136
}
11371137

1138-
/* Domain and CHECK constraint */
1138+
/* Domain and CHECK or NOT NULL constraint */
11391139
if (nLoop == 2 &&
11401140
loop[0]->objType == DO_TYPE &&
11411141
loop[1]->objType == DO_CONSTRAINT &&
1142-
((ConstraintInfo *) loop[1])->contype == 'c' &&
1142+
(((ConstraintInfo *) loop[1])->contype == 'c' ||
1143+
((ConstraintInfo *) loop[1])->contype == 'n') &&
11431144
((ConstraintInfo *) loop[1])->condomain == (TypeInfo *) loop[0])
11441145
{
11451146
repairDomainConstraintLoop(loop[0], loop[1]);
@@ -1148,14 +1149,15 @@ repairDependencyLoop(DumpableObject **loop,
11481149
if (nLoop == 2 &&
11491150
loop[1]->objType == DO_TYPE &&
11501151
loop[0]->objType == DO_CONSTRAINT &&
1151-
((ConstraintInfo *) loop[0])->contype == 'c' &&
1152+
(((ConstraintInfo *) loop[0])->contype == 'c' ||
1153+
((ConstraintInfo *) loop[0])->contype == 'n') &&
11521154
((ConstraintInfo *) loop[0])->condomain == (TypeInfo *) loop[1])
11531155
{
11541156
repairDomainConstraintLoop(loop[1], loop[0]);
11551157
return;
11561158
}
11571159

1158-
/* Indirect loop involving domain and CHECK constraint */
1160+
/* Indirect loop involving domain and CHECK or NOT NULL constraint */
11591161
if (nLoop > 2)
11601162
{
11611163
for (i = 0; i < nLoop; i++)
@@ -1165,7 +1167,8 @@ repairDependencyLoop(DumpableObject **loop,
11651167
for (j = 0; j < nLoop; j++)
11661168
{
11671169
if (loop[j]->objType == DO_CONSTRAINT &&
1168-
((ConstraintInfo *) loop[j])->contype == 'c' &&
1170+
(((ConstraintInfo *) loop[j])->contype == 'c' ||
1171+
((ConstraintInfo *) loop[j])->contype == 'n') &&
11691172
((ConstraintInfo *) loop[j])->condomain == (TypeInfo *) loop[i])
11701173
{
11711174
repairDomainConstraintMultiLoop(loop[i], loop[j]);

‎src/bin/pg_dump/t/002_pg_dump.pl

Lines changed: 28 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -2041,17 +2041,19 @@
20412041
create_sql => 'CREATE DOMAIN dump_test.us_postal_code AS TEXT
20422042
COLLATE "C"
20432043
DEFAULT \'10014\'
2044+
CONSTRAINT nn NOT NULL
20442045
CHECK(VALUE ~ \'^\d{5}$\' OR
20452046
VALUE ~ \'^\d{5}-\d{4}$\');
2047+
COMMENT ON CONSTRAINT nn
2048+
ON DOMAIN dump_test.us_postal_code IS \'not null\';
20462049
COMMENT ON CONSTRAINT us_postal_code_check
20472050
ON DOMAIN dump_test.us_postal_code IS \'check it\';',
20482051
regexp => qr/^
2049-
\QCREATE DOMAIN dump_test.us_postal_code AS text COLLATE pg_catalog."C" DEFAULT '10014'::text\E\n\s+
2052+
\QCREATE DOMAIN dump_test.us_postal_code AS text COLLATE pg_catalog."C" CONSTRAINT nn NOT NULL DEFAULT '10014'::text\E\n\s+
20502053
\QCONSTRAINT us_postal_code_check CHECK \E
20512054
\Q(((VALUE ~ '^\d{5}\E
20522055
\$\Q'::text) OR (VALUE ~ '^\d{5}-\d{4}\E\$
20532056
\Q'::text)));\E(.|\n)*
2054-
\QCOMMENT ON CONSTRAINT us_postal_code_check ON DOMAIN dump_test.us_postal_code IS 'check it';\E
20552057
/xm,
20562058
like =>
20572059
{ %full_runs, %dump_test_schema_runs, section_pre_data => 1, },
@@ -2061,6 +2063,30 @@
20612063
},
20622064
},
20632065
2066+
'COMMENT ON CONSTRAINT ON DOMAIN (1)' => {
2067+
regexp => qr/^
2068+
\QCOMMENT ON CONSTRAINT nn ON DOMAIN dump_test.us_postal_code IS 'not null';\E
2069+
/xm,
2070+
like =>
2071+
{ %full_runs, %dump_test_schema_runs, section_pre_data => 1, },
2072+
unlike => {
2073+
exclude_dump_test_schema => 1,
2074+
only_dump_measurement => 1,
2075+
},
2076+
},
2077+
2078+
'COMMENT ON CONSTRAINT ON DOMAIN (2)' => {
2079+
regexp => qr/^
2080+
\QCOMMENT ON CONSTRAINT us_postal_code_check ON DOMAIN dump_test.us_postal_code IS 'check it';\E
2081+
/xm,
2082+
like =>
2083+
{ %full_runs, %dump_test_schema_runs, section_pre_data => 1, },
2084+
unlike => {
2085+
exclude_dump_test_schema => 1,
2086+
only_dump_measurement => 1,
2087+
},
2088+
},
2089+
20642090
'CREATE FUNCTION dump_test.pltestlang_call_handler' => {
20652091
create_order => 17,
20662092
create_sql => 'CREATE FUNCTION dump_test.pltestlang_call_handler()

0 commit comments

Comments
 (0)