Skip to content

Commit f9545e9

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 6cf5b10 commit f9545e9

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_proc_d.h"
@@ -6122,6 +6123,7 @@ getTypes(Archive *fout)
61226123
*/
61236124
tyinfo[i].nDomChecks = 0;
61246125
tyinfo[i].domChecks = NULL;
6126+
tyinfo[i].notnull = NULL;
61256127
if ((tyinfo[i].dobj.dump & DUMP_COMPONENT_DEFINITION) &&
61266128
tyinfo[i].typtype == TYPTYPE_DOMAIN)
61276129
getDomainConstraints(fout, &(tyinfo[i]));
@@ -8247,27 +8249,33 @@ addConstrChildIdxDeps(DumpableObject *dobj, const IndxInfo *refidx)
82478249
static void
82488250
getDomainConstraints(Archive *fout, TypeInfo *tyinfo)
82498251
{
8250-
int i;
82518252
ConstraintInfo *constrinfo;
82528253
PQExpBuffer query = createPQExpBuffer();
82538254
PGresult *res;
82548255
int i_tableoid,
82558256
i_oid,
82568257
i_conname,
8257-
i_consrc;
8258+
i_consrc,
8259+
i_convalidated,
8260+
i_contype;
82588261
int ntups;
82598262

82608263
if (!fout->is_prepared[PREPQUERY_GETDOMAINCONSTRAINTS])
82618264
{
8262-
/* Set up query for constraint-specific details */
8263-
appendPQExpBufferStr(query,
8264-
"PREPARE getDomainConstraints(pg_catalog.oid) AS\n"
8265-
"SELECT tableoid, oid, conname, "
8266-
"pg_catalog.pg_get_constraintdef(oid) AS consrc, "
8267-
"convalidated "
8268-
"FROM pg_catalog.pg_constraint "
8269-
"WHERE contypid = $1 AND contype = 'c' "
8270-
"ORDER BY conname");
8265+
/*
8266+
* Set up query for constraint-specific details. For servers 17 and
8267+
* up, domains have constraints of type 'n' as well as 'c', otherwise
8268+
* just the latter.
8269+
*/
8270+
appendPQExpBuffer(query,
8271+
"PREPARE getDomainConstraints(pg_catalog.oid) AS\n"
8272+
"SELECT tableoid, oid, conname, "
8273+
"pg_catalog.pg_get_constraintdef(oid) AS consrc, "
8274+
"convalidated, contype "
8275+
"FROM pg_catalog.pg_constraint "
8276+
"WHERE contypid = $1 AND contype IN (%s) "
8277+
"ORDER BY conname",
8278+
fout->remoteVersion < 170000 ? "'c'" : "'c', 'n'");
82718279

82728280
ExecuteSqlStatement(fout, query->data);
82738281

@@ -8286,33 +8294,50 @@ getDomainConstraints(Archive *fout, TypeInfo *tyinfo)
82868294
i_oid = PQfnumber(res, "oid");
82878295
i_conname = PQfnumber(res, "conname");
82888296
i_consrc = PQfnumber(res, "consrc");
8297+
i_convalidated = PQfnumber(res, "convalidated");
8298+
i_contype = PQfnumber(res, "contype");
82898299

82908300
constrinfo = (ConstraintInfo *) pg_malloc(ntups * sizeof(ConstraintInfo));
8291-
8292-
tyinfo->nDomChecks = ntups;
82938301
tyinfo->domChecks = constrinfo;
82948302

8295-
for (i = 0; i < ntups; i++)
8303+
/* 'i' tracks result rows; 'j' counts CHECK constraints */
8304+
for (int i = 0, j = 0; i < ntups; i++)
82968305
{
8297-
bool validated = PQgetvalue(res, i, 4)[0] == 't';
8298-
8299-
constrinfo[i].dobj.objType = DO_CONSTRAINT;
8300-
constrinfo[i].dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_tableoid));
8301-
constrinfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
8302-
AssignDumpId(&constrinfo[i].dobj);
8303-
constrinfo[i].dobj.name = pg_strdup(PQgetvalue(res, i, i_conname));
8304-
constrinfo[i].dobj.namespace = tyinfo->dobj.namespace;
8305-
constrinfo[i].contable = NULL;
8306-
constrinfo[i].condomain = tyinfo;
8307-
constrinfo[i].contype = 'c';
8308-
constrinfo[i].condef = pg_strdup(PQgetvalue(res, i, i_consrc));
8309-
constrinfo[i].confrelid = InvalidOid;
8310-
constrinfo[i].conindex = 0;
8311-
constrinfo[i].condeferrable = false;
8312-
constrinfo[i].condeferred = false;
8313-
constrinfo[i].conislocal = true;
8314-
8315-
constrinfo[i].separate = !validated;
8306+
bool validated = PQgetvalue(res, i, i_convalidated)[0] == 't';
8307+
char contype = (PQgetvalue(res, i, i_contype))[0];
8308+
ConstraintInfo *constraint;
8309+
8310+
if (contype == CONSTRAINT_CHECK)
8311+
{
8312+
constraint = &constrinfo[j++];
8313+
tyinfo->nDomChecks++;
8314+
}
8315+
else
8316+
{
8317+
Assert(contype == CONSTRAINT_NOTNULL);
8318+
Assert(tyinfo->notnull == NULL);
8319+
/* use last item in array for the not-null constraint */
8320+
tyinfo->notnull = &(constrinfo[ntups - 1]);
8321+
constraint = tyinfo->notnull;
8322+
}
8323+
8324+
constraint->dobj.objType = DO_CONSTRAINT;
8325+
constraint->dobj.catId.tableoid = atooid(PQgetvalue(res, i, i_tableoid));
8326+
constraint->dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
8327+
AssignDumpId(&(constraint->dobj));
8328+
constraint->dobj.name = pg_strdup(PQgetvalue(res, i, i_conname));
8329+
constraint->dobj.namespace = tyinfo->dobj.namespace;
8330+
constraint->contable = NULL;
8331+
constraint->condomain = tyinfo;
8332+
constraint->contype = contype;
8333+
constraint->condef = pg_strdup(PQgetvalue(res, i, i_consrc));
8334+
constraint->confrelid = InvalidOid;
8335+
constraint->conindex = 0;
8336+
constraint->condeferrable = false;
8337+
constraint->condeferred = false;
8338+
constraint->conislocal = true;
8339+
8340+
constraint->separate = !validated;
83168341

83178342
/*
83188343
* Make the domain depend on the constraint, ensuring it won't be
@@ -8321,8 +8346,7 @@ getDomainConstraints(Archive *fout, TypeInfo *tyinfo)
83218346
* anyway, so this doesn't matter.
83228347
*/
83238348
if (validated)
8324-
addObjectDependency(&tyinfo->dobj,
8325-
constrinfo[i].dobj.dumpId);
8349+
addObjectDependency(&tyinfo->dobj, constraint->dobj.dumpId);
83268350
}
83278351

83288352
PQclear(res);
@@ -12517,8 +12541,36 @@ dumpDomain(Archive *fout, const TypeInfo *tyinfo)
1251712541
appendPQExpBuffer(q, " COLLATE %s", fmtQualifiedDumpable(coll));
1251812542
}
1251912543

12544+
/*
12545+
* Print a not-null constraint if there's one. In servers older than 17
12546+
* these don't have names, so just print it unadorned; in newer ones they
12547+
* do, but most of the time it's going to be the standard generated one,
12548+
* so omit the name in that case also.
12549+
*/
1252012550
if (typnotnull[0] == 't')
12521-
appendPQExpBufferStr(q, " NOT NULL");
12551+
{
12552+
if (fout->remoteVersion < 170000 || tyinfo->notnull == NULL)
12553+
appendPQExpBufferStr(q, " NOT NULL");
12554+
else
12555+
{
12556+
ConstraintInfo *notnull = tyinfo->notnull;
12557+
12558+
if (!notnull->separate)
12559+
{
12560+
char *default_name;
12561+
12562+
/* XXX should match ChooseConstraintName better */
12563+
default_name = psprintf("%s_not_null", tyinfo->dobj.name);
12564+
12565+
if (strcmp(default_name, notnull->dobj.name) == 0)
12566+
appendPQExpBufferStr(q, " NOT NULL");
12567+
else
12568+
appendPQExpBuffer(q, " CONSTRAINT %s %s",
12569+
fmtId(notnull->dobj.name), notnull->condef);
12570+
free(default_name);
12571+
}
12572+
}
12573+
}
1252212574

1252312575
if (typdefault != NULL)
1252412576
{
@@ -12538,7 +12590,7 @@ dumpDomain(Archive *fout, const TypeInfo *tyinfo)
1253812590
{
1253912591
ConstraintInfo *domcheck = &(tyinfo->domChecks[i]);
1254012592

12541-
if (!domcheck->separate)
12593+
if (!domcheck->separate && domcheck->contype == 'c')
1254212594
appendPQExpBuffer(q, "\n\tCONSTRAINT %s %s",
1254312595
fmtId(domcheck->dobj.name), domcheck->condef);
1254412596
}
@@ -12602,6 +12654,25 @@ dumpDomain(Archive *fout, const TypeInfo *tyinfo)
1260212654
destroyPQExpBuffer(conprefix);
1260312655
}
1260412656

12657+
/*
12658+
* And a comment on the not-null constraint, if there's one -- but only if
12659+
* the constraint itself was dumped here
12660+
*/
12661+
if (tyinfo->notnull != NULL && !tyinfo->notnull->separate)
12662+
{
12663+
PQExpBuffer conprefix = createPQExpBuffer();
12664+
12665+
appendPQExpBuffer(conprefix, "CONSTRAINT %s ON DOMAIN",
12666+
fmtId(tyinfo->notnull->dobj.name));
12667+
12668+
if (tyinfo->notnull->dobj.dump & DUMP_COMPONENT_COMMENT)
12669+
dumpComment(fout, conprefix->data, qtypname,
12670+
tyinfo->dobj.namespace->dobj.name,
12671+
tyinfo->rolname,
12672+
tyinfo->notnull->dobj.catId, 0, tyinfo->dobj.dumpId);
12673+
destroyPQExpBuffer(conprefix);
12674+
}
12675+
1260512676
destroyPQExpBuffer(q);
1260612677
destroyPQExpBuffer(delq);
1260712678
destroyPQExpBuffer(query);
@@ -18463,14 +18534,23 @@ dumpConstraint(Archive *fout, const ConstraintInfo *coninfo)
1846318534
.dropStmt = delq->data));
1846418535
}
1846518536
}
18466-
else if (coninfo->contype == 'c' && tbinfo == NULL)
18537+
else if (tbinfo == NULL)
1846718538
{
18468-
/* CHECK constraint on a domain */
18539+
/* CHECK, NOT NULL constraint on a domain */
1846918540
TypeInfo *tyinfo = coninfo->condomain;
1847018541

18542+
Assert(coninfo->contype == 'c' || coninfo->contype == 'n');
18543+
1847118544
/* Ignore if not to be dumped separately */
1847218545
if (coninfo->separate)
1847318546
{
18547+
const char *keyword;
18548+
18549+
if (coninfo->contype == 'c')
18550+
keyword = "CHECK CONSTRAINT";
18551+
else
18552+
keyword = "CONSTRAINT";
18553+
1847418554
appendPQExpBuffer(q, "ALTER DOMAIN %s\n",
1847518555
fmtQualifiedDumpable(tyinfo));
1847618556
appendPQExpBuffer(q, " ADD CONSTRAINT %s %s;\n",
@@ -18489,7 +18569,7 @@ dumpConstraint(Archive *fout, const ConstraintInfo *coninfo)
1848918569
ARCHIVE_OPTS(.tag = tag,
1849018570
.namespace = tyinfo->dobj.namespace->dobj.name,
1849118571
.owner = tyinfo->rolname,
18492-
.description = "CHECK CONSTRAINT",
18572+
.description = keyword,
1849318573
.section = SECTION_POST_DATA,
1849418574
.createStmt = q->data,
1849518575
.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
@@ -2377,17 +2377,19 @@
23772377
create_sql => 'CREATE DOMAIN dump_test.us_postal_code AS TEXT
23782378
COLLATE "C"
23792379
DEFAULT \'10014\'
2380+
CONSTRAINT nn NOT NULL
23802381
CHECK(VALUE ~ \'^\d{5}$\' OR
23812382
VALUE ~ \'^\d{5}-\d{4}$\');
2383+
COMMENT ON CONSTRAINT nn
2384+
ON DOMAIN dump_test.us_postal_code IS \'not null\';
23822385
COMMENT ON CONSTRAINT us_postal_code_check
23832386
ON DOMAIN dump_test.us_postal_code IS \'check it\';',
23842387
regexp => qr/^
2385-
\QCREATE DOMAIN dump_test.us_postal_code AS text COLLATE pg_catalog."C" DEFAULT '10014'::text\E\n\s+
2388+
\QCREATE DOMAIN dump_test.us_postal_code AS text COLLATE pg_catalog."C" CONSTRAINT nn NOT NULL DEFAULT '10014'::text\E\n\s+
23862389
\QCONSTRAINT us_postal_code_check CHECK \E
23872390
\Q(((VALUE ~ '^\d{5}\E
23882391
\$\Q'::text) OR (VALUE ~ '^\d{5}-\d{4}\E\$
23892392
\Q'::text)));\E(.|\n)*
2390-
\QCOMMENT ON CONSTRAINT us_postal_code_check ON DOMAIN dump_test.us_postal_code IS 'check it';\E
23912393
/xm,
23922394
like =>
23932395
{ %full_runs, %dump_test_schema_runs, section_pre_data => 1, },
@@ -2397,6 +2399,30 @@
23972399
},
23982400
},
23992401
2402+
'COMMENT ON CONSTRAINT ON DOMAIN (1)' => {
2403+
regexp => qr/^
2404+
\QCOMMENT ON CONSTRAINT nn ON DOMAIN dump_test.us_postal_code IS 'not null';\E
2405+
/xm,
2406+
like =>
2407+
{ %full_runs, %dump_test_schema_runs, section_pre_data => 1, },
2408+
unlike => {
2409+
exclude_dump_test_schema => 1,
2410+
only_dump_measurement => 1,
2411+
},
2412+
},
2413+
2414+
'COMMENT ON CONSTRAINT ON DOMAIN (2)' => {
2415+
regexp => qr/^
2416+
\QCOMMENT ON CONSTRAINT us_postal_code_check ON DOMAIN dump_test.us_postal_code IS 'check it';\E
2417+
/xm,
2418+
like =>
2419+
{ %full_runs, %dump_test_schema_runs, section_pre_data => 1, },
2420+
unlike => {
2421+
exclude_dump_test_schema => 1,
2422+
only_dump_measurement => 1,
2423+
},
2424+
},
2425+
24002426
'CREATE FUNCTION dump_test.pltestlang_call_handler' => {
24012427
create_order => 17,
24022428
create_sql => 'CREATE FUNCTION dump_test.pltestlang_call_handler()

0 commit comments

Comments
 (0)