Skip to content

Commit 7eca575

Browse files
alvherrealvherre
committed
get_object_address: separate domain constraints from table constraints
Apart from enabling comments on domain constraints, this enables a future project to replicate object dropping to remote servers: with the current mechanism there's no way to distinguish between the two types of constraints, so there's no way to know what to drop. Also added support for the domain constraint comments in psql's \dd and pg_dump. Catalog version bumped due to the change in ObjectType enum.
1 parent 584e35d commit 7eca575

File tree

13 files changed

+141
-19
lines changed

13 files changed

+141
-19
lines changed

‎doc/src/sgml/ref/comment.sgml

Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -28,6 +28,7 @@ COMMENT ON
2828
COLLATION <replaceable class="PARAMETER">object_name</replaceable> |
2929
COLUMN <replaceable class="PARAMETER">relation_name</replaceable>.<replaceable class="PARAMETER">column_name</replaceable> |
3030
CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> ON <replaceable class="PARAMETER">table_name</replaceable> |
31+
CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> ON DOMAIN <replaceable class="PARAMETER">domain_name</replaceable> |
3132
CONVERSION <replaceable class="PARAMETER">object_name</replaceable> |
3233
DATABASE <replaceable class="PARAMETER">object_name</replaceable> |
3334
DOMAIN <replaceable class="PARAMETER">object_name</replaceable> |
@@ -126,6 +127,18 @@ COMMENT ON
126127
</listitem>
127128
</varlistentry>
128129

130+
<varlistentry>
131+
<term><replaceable class="parameter">table_name</replaceable></term>
132+
<term><replaceable class="parameter">domain_name</replaceable></term>
133+
<listitem>
134+
<para>
135+
When creating a comment on a constraint on a table or a domain, these
136+
parameteres specify the name of the table or domain on which the
137+
constraint is defined.
138+
</para>
139+
</listitem>
140+
</varlistentry>
141+
129142
<varlistentry>
130143
<term><replaceable>source_type</replaceable></term>
131144
<listitem>
@@ -266,6 +279,7 @@ COMMENT ON COLLATION "fr_CA" IS 'Canadian French';
266279
COMMENT ON COLUMN my_table.my_column IS 'Employee ID number';
267280
COMMENT ON CONVERSION my_conv IS 'Conversion to UTF8';
268281
COMMENT ON CONSTRAINT bar_col_cons ON bar IS 'Constrains column col';
282+
COMMENT ON CONSTRAINT dom_col_constr ON DOMAIN dom IS 'Constrains col of domain';
269283
COMMENT ON DATABASE my_database IS 'Development Database';
270284
COMMENT ON DOMAIN my_domain IS 'Email Address Domain';
271285
COMMENT ON EXTENSION hstore IS 'implements the hstore data type';

‎src/backend/catalog/objectaddress.c

Lines changed: 22 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -530,11 +530,28 @@ get_object_address(ObjectType objtype, List *objname, List *objargs,
530530
break;
531531
case OBJECT_RULE:
532532
case OBJECT_TRIGGER:
533-
case OBJECT_CONSTRAINT:
533+
case OBJECT_TABCONSTRAINT:
534534
case OBJECT_POLICY:
535535
address = get_object_address_relobject(objtype, objname,
536536
&relation, missing_ok);
537537
break;
538+
case OBJECT_DOMCONSTRAINT:
539+
{
540+
List *domname;
541+
ObjectAddress domaddr;
542+
char *constrname;
543+
544+
domname = list_truncate(list_copy(objname), list_length(objname) - 1);
545+
constrname = strVal(llast(objname));
546+
domaddr = get_object_address_type(OBJECT_DOMAIN, domname, missing_ok);
547+
548+
address.classId = ConstraintRelationId;
549+
address.objectId = get_domain_constraint_oid(domaddr.objectId,
550+
constrname, missing_ok);
551+
address.objectSubId = 0;
552+
553+
}
554+
break;
538555
case OBJECT_DATABASE:
539556
case OBJECT_EXTENSION:
540557
case OBJECT_TABLESPACE:
@@ -934,7 +951,7 @@ get_object_address_relobject(ObjectType objtype, List *objname,
934951
const char *depname;
935952

936953
/* Extract name of dependent object. */
937-
depname = strVal(lfirst(list_tail(objname)));
954+
depname = strVal(llast(objname));
938955

939956
/* Separate relation name from dependent object name. */
940957
nnames = list_length(objname);
@@ -990,7 +1007,7 @@ get_object_address_relobject(ObjectType objtype, List *objname,
9901007
get_trigger_oid(reloid, depname, missing_ok) : InvalidOid;
9911008
address.objectSubId = 0;
9921009
break;
993-
case OBJECT_CONSTRAINT:
1010+
case OBJECT_TABCONSTRAINT:
9941011
address.classId = ConstraintRelationId;
9951012
address.objectId = relation ?
9961013
get_relation_constraint_oid(reloid, depname, missing_ok) :
@@ -1178,7 +1195,7 @@ check_object_ownership(Oid roleid, ObjectType objtype, ObjectAddress address,
11781195
case OBJECT_RULE:
11791196
case OBJECT_TRIGGER:
11801197
case OBJECT_POLICY:
1181-
case OBJECT_CONSTRAINT:
1198+
case OBJECT_TABCONSTRAINT:
11821199
if (!pg_class_ownercheck(RelationGetRelid(relation), roleid))
11831200
aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_CLASS,
11841201
RelationGetRelationName(relation));
@@ -1191,6 +1208,7 @@ check_object_ownership(Oid roleid, ObjectType objtype, ObjectAddress address,
11911208
case OBJECT_TYPE:
11921209
case OBJECT_DOMAIN:
11931210
case OBJECT_ATTRIBUTE:
1211+
case OBJECT_DOMCONSTRAINT:
11941212
if (!pg_type_ownercheck(address.objectId, roleid))
11951213
aclcheck_error_type(ACLCHECK_NOT_OWNER, address.objectId);
11961214
break;

‎src/backend/commands/alter.c

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -305,7 +305,8 @@ ExecRenameStmt(RenameStmt *stmt)
305305
{
306306
switch (stmt->renameType)
307307
{
308-
case OBJECT_CONSTRAINT:
308+
case OBJECT_TABCONSTRAINT:
309+
case OBJECT_DOMCONSTRAINT:
309310
return RenameConstraint(stmt);
310311

311312
case OBJECT_DATABASE:

‎src/backend/commands/event_trigger.c

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1053,10 +1053,10 @@ EventTriggerSupportsObjectType(ObjectType obtype)
10531053
case OBJECT_ATTRIBUTE:
10541054
case OBJECT_CAST:
10551055
case OBJECT_COLUMN:
1056-
case OBJECT_CONSTRAINT:
10571056
case OBJECT_COLLATION:
10581057
case OBJECT_CONVERSION:
10591058
case OBJECT_DOMAIN:
1059+
case OBJECT_DOMCONSTRAINT:
10601060
case OBJECT_EXTENSION:
10611061
case OBJECT_FDW:
10621062
case OBJECT_FOREIGN_SERVER:
@@ -1073,6 +1073,7 @@ EventTriggerSupportsObjectType(ObjectType obtype)
10731073
case OBJECT_RULE:
10741074
case OBJECT_SCHEMA:
10751075
case OBJECT_SEQUENCE:
1076+
case OBJECT_TABCONSTRAINT:
10761077
case OBJECT_TABLE:
10771078
case OBJECT_TRIGGER:
10781079
case OBJECT_TSCONFIGURATION:

‎src/backend/commands/tablecmds.c

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2457,7 +2457,7 @@ RenameConstraint(RenameStmt *stmt)
24572457
Oid relid = InvalidOid;
24582458
Oid typid = InvalidOid;
24592459

2460-
if (stmt->relationType == OBJECT_DOMAIN)
2460+
if (stmt->renameType == OBJECT_DOMCONSTRAINT)
24612461
{
24622462
Relation rel;
24632463
HeapTuple tup;

‎src/backend/parser/gram.y

Lines changed: 13 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -5572,6 +5572,7 @@ opt_restart_seqs:
55725572
* CAST (<src type> AS <dst type>) |
55735573
* COLUMN <relname>.<colname> |
55745574
* CONSTRAINT <constraintname> ON <relname> |
5575+
* CONSTRAINT <constraintname> ON DOMAIN <domainname> |
55755576
* FUNCTION <funcname> (arg1, arg2, ...) |
55765577
* LARGE OBJECT <oid> |
55775578
* OPERATOR <op> (leftoperand_typ, rightoperand_typ) |
@@ -5623,12 +5624,21 @@ CommentStmt:
56235624
| COMMENT ON CONSTRAINT name ON any_name IS comment_text
56245625
{
56255626
CommentStmt *n = makeNode(CommentStmt);
5626-
n->objtype = OBJECT_CONSTRAINT;
5627+
n->objtype = OBJECT_TABCONSTRAINT;
56275628
n->objname = lappend($6, makeString($4));
56285629
n->objargs = NIL;
56295630
n->comment = $8;
56305631
$$ = (Node *) n;
56315632
}
5633+
| COMMENT ON CONSTRAINT name ON DOMAIN_P any_name IS comment_text
5634+
{
5635+
CommentStmt *n = makeNode(CommentStmt);
5636+
n->objtype = OBJECT_DOMCONSTRAINT;
5637+
n->objname = lappend($7, makeString($4));
5638+
n->objargs = NIL;
5639+
n->comment = $9;
5640+
$$ = (Node *) n;
5641+
}
56325642
| COMMENT ON POLICY name ON any_name IS comment_text
56335643
{
56345644
CommentStmt *n = makeNode(CommentStmt);
@@ -7355,8 +7365,7 @@ RenameStmt: ALTER AGGREGATE func_name aggr_args RENAME TO name
73557365
| ALTER DOMAIN_P any_name RENAME CONSTRAINT name TO name
73567366
{
73577367
RenameStmt *n = makeNode(RenameStmt);
7358-
n->renameType = OBJECT_CONSTRAINT;
7359-
n->relationType = OBJECT_DOMAIN;
7368+
n->renameType = OBJECT_DOMCONSTRAINT;
73607369
n->object = $3;
73617370
n->subname = $6;
73627371
n->newname = $8;
@@ -7624,8 +7633,7 @@ RenameStmt: ALTER AGGREGATE func_name aggr_args RENAME TO name
76247633
| ALTER TABLE relation_expr RENAME CONSTRAINT name TO name
76257634
{
76267635
RenameStmt *n = makeNode(RenameStmt);
7627-
n->renameType = OBJECT_CONSTRAINT;
7628-
n->relationType = OBJECT_TABLE;
7636+
n->renameType = OBJECT_TABCONSTRAINT;
76297637
n->relation = $3;
76307638
n->subname = $6;
76317639
n->newname = $8;

‎src/backend/parser/parse_utilcmd.c

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -896,7 +896,7 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla
896896
{
897897
CommentStmt *stmt = makeNode(CommentStmt);
898898

899-
stmt->objtype = OBJECT_CONSTRAINT;
899+
stmt->objtype = OBJECT_TABCONSTRAINT;
900900
stmt->objname = list_make3(makeString(cxt->relation->schemaname),
901901
makeString(cxt->relation->relname),
902902
makeString(n->conname));

‎src/backend/tcop/utility.c

Lines changed: 2 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1589,16 +1589,14 @@ AlterObjectTypeCommandTag(ObjectType objtype)
15891589
case OBJECT_COLUMN:
15901590
tag = "ALTER TABLE";
15911591
break;
1592-
case OBJECT_CONSTRAINT:
1593-
tag = "ALTER TABLE";
1594-
break;
15951592
case OBJECT_CONVERSION:
15961593
tag = "ALTER CONVERSION";
15971594
break;
15981595
case OBJECT_DATABASE:
15991596
tag = "ALTER DATABASE";
16001597
break;
16011598
case OBJECT_DOMAIN:
1599+
case OBJECT_DOMCONSTRAINT:
16021600
tag = "ALTER DOMAIN";
16031601
break;
16041602
case OBJECT_EXTENSION:
@@ -1650,6 +1648,7 @@ AlterObjectTypeCommandTag(ObjectType objtype)
16501648
tag = "ALTER SEQUENCE";
16511649
break;
16521650
case OBJECT_TABLE:
1651+
case OBJECT_TABCONSTRAINT:
16531652
tag = "ALTER TABLE";
16541653
break;
16551654
case OBJECT_TABLESPACE:

‎src/bin/pg_dump/pg_dump.c

Lines changed: 17 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -9261,6 +9261,23 @@ dumpDomain(Archive *fout, DumpOptions *dopt, TypeInfo *tyinfo)
92619261
tyinfo->dobj.namespace->dobj.name,
92629262
tyinfo->rolname, tyinfo->typacl);
92639263

9264+
/* Dump any per-constraint comments */
9265+
for (i = 0; i < tyinfo->nDomChecks; i++)
9266+
{
9267+
ConstraintInfo *domcheck = &(tyinfo->domChecks[i]);
9268+
PQExpBuffer labelq = createPQExpBuffer();
9269+
9270+
appendPQExpBuffer(labelq, "CONSTRAINT %s ",
9271+
fmtId(domcheck->dobj.name));
9272+
appendPQExpBuffer(labelq, "ON DOMAIN %s",
9273+
fmtId(qtypname));
9274+
dumpComment(fout, dopt, labelq->data,
9275+
tyinfo->dobj.namespace->dobj.name,
9276+
tyinfo->rolname,
9277+
domcheck->dobj.catId, 0, tyinfo->dobj.dumpId);
9278+
destroyPQExpBuffer(labelq);
9279+
}
9280+
92649281
destroyPQExpBuffer(q);
92659282
destroyPQExpBuffer(delq);
92669283
destroyPQExpBuffer(labelq);

‎src/bin/psql/describe.c

Lines changed: 25 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -952,7 +952,7 @@ objectDescription(const char *pattern, bool showSystem)
952952
gettext_noop("Object"),
953953
gettext_noop("Description"));
954954

955-
/* Constraint descriptions */
955+
/* Table constraint descriptions */
956956
appendPQExpBuffer(&buf,
957957
" SELECT pgc.oid as oid, pgc.tableoid AS tableoid,\n"
958958
" n.nspname as nspname,\n"
@@ -963,7 +963,7 @@ objectDescription(const char *pattern, bool showSystem)
963963
"ON c.oid = pgc.conrelid\n"
964964
" LEFT JOIN pg_catalog.pg_namespace n "
965965
" ON n.oid = c.relnamespace\n",
966-
gettext_noop("constraint"));
966+
gettext_noop("table constraint"));
967967

968968
if (!showSystem && !pattern)
969969
appendPQExpBufferStr(&buf, "WHERE n.nspname <> 'pg_catalog'\n"
@@ -973,6 +973,29 @@ objectDescription(const char *pattern, bool showSystem)
973973
false, "n.nspname", "pgc.conname", NULL,
974974
"pg_catalog.pg_table_is_visible(c.oid)");
975975

976+
/* Domain constraint descriptions */
977+
appendPQExpBuffer(&buf,
978+
"UNION ALL\n"
979+
" SELECT pgc.oid as oid, pgc.tableoid AS tableoid,\n"
980+
" n.nspname as nspname,\n"
981+
" CAST(pgc.conname AS pg_catalog.text) as name,"
982+
" CAST('%s' AS pg_catalog.text) as object\n"
983+
" FROM pg_catalog.pg_constraint pgc\n"
984+
" JOIN pg_catalog.pg_type t "
985+
"ON t.oid = pgc.contypid\n"
986+
" LEFT JOIN pg_catalog.pg_namespace n "
987+
" ON n.oid = t.typnamespace\n",
988+
gettext_noop("domain constraint"));
989+
990+
if (!showSystem && !pattern)
991+
appendPQExpBufferStr(&buf, "WHERE n.nspname <> 'pg_catalog'\n"
992+
" AND n.nspname <> 'information_schema'\n");
993+
994+
processSQLNamePattern(pset.db, &buf, pattern, !showSystem && !pattern,
995+
false, "n.nspname", "pgc.conname", NULL,
996+
"pg_catalog.pg_type_is_visible(t.oid)");
997+
998+
976999
/*
9771000
* pg_opclass.opcmethod only available in 8.3+
9781001
*/

‎src/include/nodes/parsenodes.h

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1208,11 +1208,11 @@ typedef enum ObjectType
12081208
OBJECT_ATTRIBUTE, /* type's attribute, when distinct from column */
12091209
OBJECT_CAST,
12101210
OBJECT_COLUMN,
1211-
OBJECT_CONSTRAINT,
12121211
OBJECT_COLLATION,
12131212
OBJECT_CONVERSION,
12141213
OBJECT_DATABASE,
12151214
OBJECT_DOMAIN,
1215+
OBJECT_DOMCONSTRAINT,
12161216
OBJECT_EVENT_TRIGGER,
12171217
OBJECT_EXTENSION,
12181218
OBJECT_FDW,
@@ -1231,6 +1231,7 @@ typedef enum ObjectType
12311231
OBJECT_RULE,
12321232
OBJECT_SCHEMA,
12331233
OBJECT_SEQUENCE,
1234+
OBJECT_TABCONSTRAINT,
12341235
OBJECT_TABLE,
12351236
OBJECT_TABLESPACE,
12361237
OBJECT_TRIGGER,

‎src/test/regress/input/constraints.source

Lines changed: 21 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -478,3 +478,24 @@ UPDATE deferred_excl SET f1 = 3;
478478
ALTER TABLE deferred_excl ADD EXCLUDE (f1 WITH =);
479479

480480
DROP TABLE deferred_excl;
481+
482+
-- Comments
483+
CREATE TABLE constraint_comments_tbl (a int CONSTRAINT the_constraint CHECK (a > 0));
484+
CREATE DOMAIN constraint_comments_dom AS int CONSTRAINT the_constraint CHECK (value > 0);
485+
486+
COMMENT ON CONSTRAINT the_constraint ON constraint_comments_tbl IS 'yes, the comment';
487+
COMMENT ON CONSTRAINT the_constraint ON DOMAIN constraint_comments_dom IS 'yes, another comment';
488+
489+
-- no such constraint
490+
COMMENT ON CONSTRAINT no_constraint ON constraint_comments_tbl IS 'yes, the comment';
491+
COMMENT ON CONSTRAINT no_constraint ON DOMAIN constraint_comments_dom IS 'yes, another comment';
492+
493+
-- no such table/domain
494+
COMMENT ON CONSTRAINT the_constraint ON no_comments_tbl IS 'bad comment';
495+
COMMENT ON CONSTRAINT the_constraint ON DOMAIN no_comments_dom IS 'another bad comment';
496+
497+
COMMENT ON CONSTRAINT the_constraint ON constraint_comments_tbl IS NULL;
498+
COMMENT ON CONSTRAINT the_constraint ON DOMAIN constraint_comments_dom IS NULL;
499+
500+
DROP TABLE constraint_comments_tbl;
501+
DROP DOMAIN constraint_comments_dom;

‎src/test/regress/output/constraints.source

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -645,3 +645,22 @@ ALTER TABLE deferred_excl ADD EXCLUDE (f1 WITH =);
645645
ERROR: could not create exclusion constraint "deferred_excl_f1_excl"
646646
DETAIL: Key (f1)=(3) conflicts with key (f1)=(3).
647647
DROP TABLE deferred_excl;
648+
-- Comments
649+
CREATE TABLE constraint_comments_tbl (a int CONSTRAINT the_constraint CHECK (a > 0));
650+
CREATE DOMAIN constraint_comments_dom AS int CONSTRAINT the_constraint CHECK (value > 0);
651+
COMMENT ON CONSTRAINT the_constraint ON constraint_comments_tbl IS 'yes, the comment';
652+
COMMENT ON CONSTRAINT the_constraint ON DOMAIN constraint_comments_dom IS 'yes, another comment';
653+
-- no such constraint
654+
COMMENT ON CONSTRAINT no_constraint ON constraint_comments_tbl IS 'yes, the comment';
655+
ERROR: constraint "no_constraint" for table "constraint_comments_tbl" does not exist
656+
COMMENT ON CONSTRAINT no_constraint ON DOMAIN constraint_comments_dom IS 'yes, another comment';
657+
ERROR: constraint "no_constraint" for domain "constraint_comments_dom" does not exist
658+
-- no such table/domain
659+
COMMENT ON CONSTRAINT the_constraint ON no_comments_tbl IS 'bad comment';
660+
ERROR: relation "no_comments_tbl" does not exist
661+
COMMENT ON CONSTRAINT the_constraint ON DOMAIN no_comments_dom IS 'another bad comment';
662+
ERROR: type "no_comments_dom" does not exist
663+
COMMENT ON CONSTRAINT the_constraint ON constraint_comments_tbl IS NULL;
664+
COMMENT ON CONSTRAINT the_constraint ON DOMAIN constraint_comments_dom IS NULL;
665+
DROP TABLE constraint_comments_tbl;
666+
DROP DOMAIN constraint_comments_dom;

0 commit comments

Comments
 (0)