Skip to content

Commit da71717

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 cb937e4 commit da71717

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"
@@ -6187,6 +6188,7 @@ getTypes(Archive *fout)
61876188
*/
61886189
tyinfo[i].nDomChecks = 0;
61896190
tyinfo[i].domChecks = NULL;
6191+
tyinfo[i].notnull = NULL;
61906192
if ((tyinfo[i].dobj.dump & DUMP_COMPONENT_DEFINITION) &&
61916193
tyinfo[i].typtype == TYPTYPE_DOMAIN)
61926194
getDomainConstraints(fout, &(tyinfo[i]));
@@ -8312,27 +8314,33 @@ addConstrChildIdxDeps(DumpableObject *dobj, const IndxInfo *refidx)
83128314
static void
83138315
getDomainConstraints(Archive *fout, TypeInfo *tyinfo)
83148316
{
8315-
int i;
83168317
ConstraintInfo *constrinfo;
83178318
PQExpBuffer query = createPQExpBuffer();
83188319
PGresult *res;
83198320
int i_tableoid,
83208321
i_oid,
83218322
i_conname,
8322-
i_consrc;
8323+
i_consrc,
8324+
i_convalidated,
8325+
i_contype;
83238326
int ntups;
83248327

83258328
if (!fout->is_prepared[PREPQUERY_GETDOMAINCONSTRAINTS])
83268329
{
8327-
/* Set up query for constraint-specific details */
8328-
appendPQExpBufferStr(query,
8329-
"PREPARE getDomainConstraints(pg_catalog.oid) AS\n"
8330-
"SELECT tableoid, oid, conname, "
8331-
"pg_catalog.pg_get_constraintdef(oid) AS consrc, "
8332-
"convalidated "
8333-
"FROM pg_catalog.pg_constraint "
8334-
"WHERE contypid = $1 AND contype = 'c' "
8335-
"ORDER BY conname");
8330+
/*
8331+
* Set up query for constraint-specific details. For servers 17 and
8332+
* up, domains have constraints of type 'n' as well as 'c', otherwise
8333+
* just the latter.
8334+
*/
8335+
appendPQExpBuffer(query,
8336+
"PREPARE getDomainConstraints(pg_catalog.oid) AS\n"
8337+
"SELECT tableoid, oid, conname, "
8338+
"pg_catalog.pg_get_constraintdef(oid) AS consrc, "
8339+
"convalidated, contype "
8340+
"FROM pg_catalog.pg_constraint "
8341+
"WHERE contypid = $1 AND contype IN (%s) "
8342+
"ORDER BY conname",
8343+
fout->remoteVersion < 170000 ? "'c'" : "'c', 'n'");
83368344

83378345
ExecuteSqlStatement(fout, query->data);
83388346

@@ -8351,33 +8359,50 @@ getDomainConstraints(Archive *fout, TypeInfo *tyinfo)
83518359
i_oid = PQfnumber(res, "oid");
83528360
i_conname = PQfnumber(res, "conname");
83538361
i_consrc = PQfnumber(res, "consrc");
8362+
i_convalidated = PQfnumber(res, "convalidated");
8363+
i_contype = PQfnumber(res, "contype");
83548364

83558365
constrinfo = (ConstraintInfo *) pg_malloc(ntups * sizeof(ConstraintInfo));
8356-
8357-
tyinfo->nDomChecks = ntups;
83588366
tyinfo->domChecks = constrinfo;
83598367

8360-
for (i = 0; i < ntups; i++)
8368+
/* 'i' tracks result rows; 'j' counts CHECK constraints */
8369+
for (int i = 0, j = 0; i < ntups; i++)
83618370
{
8362-
bool validated = PQgetvalue(res, i, 4)[0] == 't';
8363-
8364-
constrinfo[i].dobj.objType = DO_CONSTRAINT;
8365-
constrinfo[i].dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_tableoid));
8366-
constrinfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
8367-
AssignDumpId(&constrinfo[i].dobj);
8368-
constrinfo[i].dobj.name = pg_strdup(PQgetvalue(res, i, i_conname));
8369-
constrinfo[i].dobj.namespace = tyinfo->dobj.namespace;
8370-
constrinfo[i].contable = NULL;
8371-
constrinfo[i].condomain = tyinfo;
8372-
constrinfo[i].contype = 'c';
8373-
constrinfo[i].condef = pg_strdup(PQgetvalue(res, i, i_consrc));
8374-
constrinfo[i].confrelid = InvalidOid;
8375-
constrinfo[i].conindex = 0;
8376-
constrinfo[i].condeferrable = false;
8377-
constrinfo[i].condeferred = false;
8378-
constrinfo[i].conislocal = true;
8379-
8380-
constrinfo[i].separate = !validated;
8371+
bool validated = PQgetvalue(res, i, i_convalidated)[0] == 't';
8372+
char contype = (PQgetvalue(res, i, i_contype))[0];
8373+
ConstraintInfo *constraint;
8374+
8375+
if (contype == CONSTRAINT_CHECK)
8376+
{
8377+
constraint = &constrinfo[j++];
8378+
tyinfo->nDomChecks++;
8379+
}
8380+
else
8381+
{
8382+
Assert(contype == CONSTRAINT_NOTNULL);
8383+
Assert(tyinfo->notnull == NULL);
8384+
/* use last item in array for the not-null constraint */
8385+
tyinfo->notnull = &(constrinfo[ntups - 1]);
8386+
constraint = tyinfo->notnull;
8387+
}
8388+
8389+
constraint->dobj.objType = DO_CONSTRAINT;
8390+
constraint->dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_tableoid));
8391+
constraint->dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
8392+
AssignDumpId(&(constraint->dobj));
8393+
constraint->dobj.name = pg_strdup(PQgetvalue(res, i, i_conname));
8394+
constraint->dobj.namespace = tyinfo->dobj.namespace;
8395+
constraint->contable = NULL;
8396+
constraint->condomain = tyinfo;
8397+
constraint->contype = contype;
8398+
constraint->condef = pg_strdup(PQgetvalue(res, i, i_consrc));
8399+
constraint->confrelid = InvalidOid;
8400+
constraint->conindex = 0;
8401+
constraint->condeferrable = false;
8402+
constraint->condeferred = false;
8403+
constraint->conislocal = true;
8404+
8405+
constraint->separate = !validated;
83818406

83828407
/*
83838408
* Make the domain depend on the constraint, ensuring it won't be
@@ -8386,8 +8411,7 @@ getDomainConstraints(Archive *fout, TypeInfo *tyinfo)
83868411
* anyway, so this doesn't matter.
83878412
*/
83888413
if (validated)
8389-
addObjectDependency(&tyinfo->dobj,
8390-
constrinfo[i].dobj.dumpId);
8414+
addObjectDependency(&tyinfo->dobj, constraint->dobj.dumpId);
83918415
}
83928416

83938417
PQclear(res);
@@ -12597,8 +12621,36 @@ dumpDomain(Archive *fout, const TypeInfo *tyinfo)
1259712621
appendPQExpBuffer(q, " COLLATE %s", fmtQualifiedDumpable(coll));
1259812622
}
1259912623

12624+
/*
12625+
* Print a not-null constraint if there's one. In servers older than 17
12626+
* these don't have names, so just print it unadorned; in newer ones they
12627+
* do, but most of the time it's going to be the standard generated one,
12628+
* so omit the name in that case also.
12629+
*/
1260012630
if (typnotnull[0] == 't')
12601-
appendPQExpBufferStr(q, " NOT NULL");
12631+
{
12632+
if (fout->remoteVersion < 170000 || tyinfo->notnull == NULL)
12633+
appendPQExpBufferStr(q, " NOT NULL");
12634+
else
12635+
{
12636+
ConstraintInfo *notnull = tyinfo->notnull;
12637+
12638+
if (!notnull->separate)
12639+
{
12640+
char *default_name;
12641+
12642+
/* XXX should match ChooseConstraintName better */
12643+
default_name = psprintf("%s_not_null", tyinfo->dobj.name);
12644+
12645+
if (strcmp(default_name, notnull->dobj.name) == 0)
12646+
appendPQExpBufferStr(q, " NOT NULL");
12647+
else
12648+
appendPQExpBuffer(q, " CONSTRAINT %s %s",
12649+
fmtId(notnull->dobj.name), notnull->condef);
12650+
free(default_name);
12651+
}
12652+
}
12653+
}
1260212654

1260312655
if (typdefault != NULL)
1260412656
{
@@ -12618,7 +12670,7 @@ dumpDomain(Archive *fout, const TypeInfo *tyinfo)
1261812670
{
1261912671
ConstraintInfo *domcheck = &(tyinfo->domChecks[i]);
1262012672

12621-
if (!domcheck->separate)
12673+
if (!domcheck->separate && domcheck->contype == 'c')
1262212674
appendPQExpBuffer(q, "\n\tCONSTRAINT %s %s",
1262312675
fmtId(domcheck->dobj.name), domcheck->condef);
1262412676
}
@@ -12682,6 +12734,25 @@ dumpDomain(Archive *fout, const TypeInfo *tyinfo)
1268212734
destroyPQExpBuffer(conprefix);
1268312735
}
1268412736

12737+
/*
12738+
* And a comment on the not-null constraint, if there's one -- but only if
12739+
* the constraint itself was dumped here
12740+
*/
12741+
if (tyinfo->notnull != NULL && !tyinfo->notnull->separate)
12742+
{
12743+
PQExpBuffer conprefix = createPQExpBuffer();
12744+
12745+
appendPQExpBuffer(conprefix, "CONSTRAINT %s ON DOMAIN",
12746+
fmtId(tyinfo->notnull->dobj.name));
12747+
12748+
if (tyinfo->notnull->dobj.dump & DUMP_COMPONENT_COMMENT)
12749+
dumpComment(fout, conprefix->data, qtypname,
12750+
tyinfo->dobj.namespace->dobj.name,
12751+
tyinfo->rolname,
12752+
tyinfo->notnull->dobj.catId, 0, tyinfo->dobj.dumpId);
12753+
destroyPQExpBuffer(conprefix);
12754+
}
12755+
1268512756
destroyPQExpBuffer(q);
1268612757
destroyPQExpBuffer(delq);
1268712758
destroyPQExpBuffer(query);
@@ -18543,14 +18614,23 @@ dumpConstraint(Archive *fout, const ConstraintInfo *coninfo)
1854318614
.dropStmt = delq->data));
1854418615
}
1854518616
}
18546-
else if (coninfo->contype == 'c' && tbinfo == NULL)
18617+
else if (tbinfo == NULL)
1854718618
{
18548-
/* CHECK constraint on a domain */
18619+
/* CHECK, NOT NULL constraint on a domain */
1854918620
TypeInfo *tyinfo = coninfo->condomain;
1855018621

18622+
Assert(coninfo->contype == 'c' || coninfo->contype == 'n');
18623+
1855118624
/* Ignore if not to be dumped separately */
1855218625
if (coninfo->separate)
1855318626
{
18627+
const char *keyword;
18628+
18629+
if (coninfo->contype == 'c')
18630+
keyword = "CHECK CONSTRAINT";
18631+
else
18632+
keyword = "CONSTRAINT";
18633+
1855418634
appendPQExpBuffer(q, "ALTER DOMAIN %s\n",
1855518635
fmtQualifiedDumpable(tyinfo));
1855618636
appendPQExpBuffer(q, " ADD CONSTRAINT %s %s;\n",
@@ -18569,7 +18649,7 @@ dumpConstraint(Archive *fout, const ConstraintInfo *coninfo)
1856918649
ARCHIVE_OPTS(.tag = tag,
1857018650
.namespace = tyinfo->dobj.namespace->dobj.name,
1857118651
.owner = tyinfo->rolname,
18572-
.description = "CHECK CONSTRAINT",
18652+
.description = keyword,
1857318653
.section = SECTION_POST_DATA,
1857418654
.createStmt = q->data,
1857518655
.dropStmt = delq->data));

‎src/bin/pg_dump/pg_dump.h

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -222,7 +222,9 @@ typedef struct _typeInfo
222222
bool isDefined; /* true if typisdefined */
223223
/* If needed, we'll create a "shell type" entry for it; link that here: */
224224
struct _shellTypeInfo *shellType; /* shell-type entry, or NULL */
225-
/* If it's a domain, we store links to its constraints here: */
225+
/* If it's a domain, its not-null constraint is here: */
226+
struct _constraintInfo *notnull;
227+
/* If it's a domain, we store links to its CHECK constraints here: */
226228
int nDomChecks;
227229
struct _constraintInfo *domChecks;
228230
} TypeInfo;

‎src/bin/pg_dump/pg_dump_sort.c

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

909909
/*
910-
* CHECK constraints on domains work just like those on tables ...
910+
* CHECK, NOT NULL constraints on domains work just like those on tables ...
911911
*/
912912
static void
913913
repairDomainConstraintLoop(DumpableObject *domainobj,
@@ -1173,11 +1173,12 @@ repairDependencyLoop(DumpableObject **loop,
11731173
}
11741174
}
11751175

1176-
/* Domain and CHECK constraint */
1176+
/* Domain and CHECK or NOT NULL constraint */
11771177
if (nLoop == 2 &&
11781178
loop[0]->objType == DO_TYPE &&
11791179
loop[1]->objType == DO_CONSTRAINT &&
1180-
((ConstraintInfo *) loop[1])->contype == 'c' &&
1180+
(((ConstraintInfo *) loop[1])->contype == 'c' ||
1181+
((ConstraintInfo *) loop[1])->contype == 'n') &&
11811182
((ConstraintInfo *) loop[1])->condomain == (TypeInfo *) loop[0])
11821183
{
11831184
repairDomainConstraintLoop(loop[0], loop[1]);
@@ -1186,14 +1187,15 @@ repairDependencyLoop(DumpableObject **loop,
11861187
if (nLoop == 2 &&
11871188
loop[1]->objType == DO_TYPE &&
11881189
loop[0]->objType == DO_CONSTRAINT &&
1189-
((ConstraintInfo *) loop[0])->contype == 'c' &&
1190+
(((ConstraintInfo *) loop[0])->contype == 'c' ||
1191+
((ConstraintInfo *) loop[0])->contype == 'n') &&
11901192
((ConstraintInfo *) loop[0])->condomain == (TypeInfo *) loop[1])
11911193
{
11921194
repairDomainConstraintLoop(loop[1], loop[0]);
11931195
return;
11941196
}
11951197

1196-
/* Indirect loop involving domain and CHECK constraint */
1198+
/* Indirect loop involving domain and CHECK or NOT NULL constraint */
11971199
if (nLoop > 2)
11981200
{
11991201
for (i = 0; i < nLoop; i++)
@@ -1203,7 +1205,8 @@ repairDependencyLoop(DumpableObject **loop,
12031205
for (j = 0; j < nLoop; j++)
12041206
{
12051207
if (loop[j]->objType == DO_CONSTRAINT &&
1206-
((ConstraintInfo *) loop[j])->contype == 'c' &&
1208+
(((ConstraintInfo *) loop[j])->contype == 'c' ||
1209+
((ConstraintInfo *) loop[j])->contype == 'n') &&
12071210
((ConstraintInfo *) loop[j])->condomain == (TypeInfo *) loop[i])
12081211
{
12091212
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
@@ -2379,17 +2379,19 @@
23792379
create_sql => 'CREATE DOMAIN dump_test.us_postal_code AS TEXT
23802380
COLLATE "C"
23812381
DEFAULT \'10014\'
2382+
CONSTRAINT nn NOT NULL
23822383
CHECK(VALUE ~ \'^\d{5}$\' OR
23832384
VALUE ~ \'^\d{5}-\d{4}$\');
2385+
COMMENT ON CONSTRAINT nn
2386+
ON DOMAIN dump_test.us_postal_code IS \'not null\';
23842387
COMMENT ON CONSTRAINT us_postal_code_check
23852388
ON DOMAIN dump_test.us_postal_code IS \'check it\';',
23862389
regexp => qr/^
2387-
\QCREATE DOMAIN dump_test.us_postal_code AS text COLLATE pg_catalog."C" DEFAULT '10014'::text\E\n\s+
2390+
\QCREATE DOMAIN dump_test.us_postal_code AS text COLLATE pg_catalog."C" CONSTRAINT nn NOT NULL DEFAULT '10014'::text\E\n\s+
23882391
\QCONSTRAINT us_postal_code_check CHECK \E
23892392
\Q(((VALUE ~ '^\d{5}\E
23902393
\$\Q'::text) OR (VALUE ~ '^\d{5}-\d{4}\E\$
23912394
\Q'::text)));\E(.|\n)*
2392-
\QCOMMENT ON CONSTRAINT us_postal_code_check ON DOMAIN dump_test.us_postal_code IS 'check it';\E
23932395
/xm,
23942396
like =>
23952397
{ %full_runs, %dump_test_schema_runs, section_pre_data => 1, },
@@ -2399,6 +2401,30 @@
23992401
},
24002402
},
24012403
2404+
'COMMENT ON CONSTRAINT ON DOMAIN (1)' => {
2405+
regexp => qr/^
2406+
\QCOMMENT ON CONSTRAINT nn ON DOMAIN dump_test.us_postal_code IS 'not null';\E
2407+
/xm,
2408+
like =>
2409+
{ %full_runs, %dump_test_schema_runs, section_pre_data => 1, },
2410+
unlike => {
2411+
exclude_dump_test_schema => 1,
2412+
only_dump_measurement => 1,
2413+
},
2414+
},
2415+
2416+
'COMMENT ON CONSTRAINT ON DOMAIN (2)' => {
2417+
regexp => qr/^
2418+
\QCOMMENT ON CONSTRAINT us_postal_code_check ON DOMAIN dump_test.us_postal_code IS 'check it';\E
2419+
/xm,
2420+
like =>
2421+
{ %full_runs, %dump_test_schema_runs, section_pre_data => 1, },
2422+
unlike => {
2423+
exclude_dump_test_schema => 1,
2424+
only_dump_measurement => 1,
2425+
},
2426+
},
2427+
24022428
'CREATE FUNCTION dump_test.pltestlang_call_handler' => {
24032429
create_order => 17,
24042430
create_sql => 'CREATE FUNCTION dump_test.pltestlang_call_handler()

0 commit comments

Comments
 (0)