Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitf9545e9

Browse files
alvherrejianhe-fun
andcommitted
pg_dump: include comments on not-null constraints on domains, too
Commite5da0fe introduced catalog entries for not-null constraintson domains; but because commitb0e96f3 (the original work forcatalogued not-null constraints on tables) forgot to teach pg_dump toprocess the comments for them, this one also forgot. Add that now.We also need to teach repairDependencyLoop() about the new type ofconstraints being possible for domains.Backpatch-through: 17Co-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 parent6cf5b10 commitf9545e9

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-
inti;
82518252
ConstraintInfo *constrinfo;
82528253
PQExpBuffer query = createPQExpBuffer();
82538254
PGresult *res;
82548255
inti_tableoid,
82558256
i_oid,
82568257
i_conname,
8257-
i_consrc;
8258+
i_consrc,
8259+
i_convalidated,
8260+
i_contype;
82588261
intntups;
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-
boolvalidated = 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+
boolvalidated = PQgetvalue(res, i, i_convalidated)[0] == 't';
8307+
charcontype = (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
boolisDefined;/* 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
intnDomChecks;
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
staticvoid
913913
repairDomainConstraintLoop(DumpableObject*domainobj,
@@ -1173,11 +1173,12 @@ repairDependencyLoop(DumpableObject **loop,
11731173
}
11741174
}
11751175

1176-
/* Domain and CHECK constraint */
1176+
/* Domain and CHECKor NOT NULLconstraint */
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 CHECKor NOT NULLconstraint */
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 NULLDEFAULT '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)
[8]ページ先頭
©2009-2025 Movatter.jp