summary | shortlog | log | commit | commitdiff | tree
raw | patch | inline | side by side (parent: 2ce253c)
raw | patch | inline | side by side (parent: 2ce253c)
| author | Tom Lane <tgl@sss.pgh.pa.us> | |
| Tue, 4 Sep 2018 17:45:35 +0000 (13:45 -0400) | ||
| committer | Tom Lane <tgl@sss.pgh.pa.us> | |
| Tue, 4 Sep 2018 17:45:35 +0000 (13:45 -0400) |
It's been true for a long time that we expect names of table and domain
constraints to be unique among the constraints of that table or domain.
However, the enforcement of that has been pretty haphazard, and it missed
some corner cases such as creating a CHECK constraint and then an index
constraint of the same name (as per recent report from André Hänsel).
Also, due to the lack of an actual unique index enforcing this, duplicates
could be created through race conditions.
Moreover, the code that searches pg_constraint has been quite inconsistent
about how to handle duplicate names if one did occur: some places checked
and threw errors if there was more than one match, while others just
processed the first match they came to.
To fix, create a unique index on (conrelid, contypid, conname). Since
either conrelid or contypid is zero, this will separately enforce
uniqueness of constraint names among constraints of any one table and any
one domain. (If we ever implement SQL assertions, and put them into this
catalog, more thought might be needed. But it'd be at least as reasonable
to put them into a new catalog; having overloaded this one catalog with
two kinds of constraints was a mistake already IMO.) This index can replace
the existing non-unique index on conrelid, though we need to keep the one
on contypid for query performance reasons.
Having done that, we can simplify the logic in various places that either
coped with duplicates or neglected to, as well as potentially improve
lookup performance when searching for a constraint by name.
Also, as per our usual practice, install a preliminary check so that you
get something more friendly than a unique-index violation report in the
case complained of by André. And teach ChooseIndexName to avoid choosing
autogenerated names that would draw such a failure.
While it's not possible to make such a change in the back branches,
it doesn't seem quite too late to put this into v11, so do so.
Discussion: https://postgr.es/m/0c1001d4428f$0942b430$1bc81c90$@webkr.de
constraints to be unique among the constraints of that table or domain.
However, the enforcement of that has been pretty haphazard, and it missed
some corner cases such as creating a CHECK constraint and then an index
constraint of the same name (as per recent report from André Hänsel).
Also, due to the lack of an actual unique index enforcing this, duplicates
could be created through race conditions.
Moreover, the code that searches pg_constraint has been quite inconsistent
about how to handle duplicate names if one did occur: some places checked
and threw errors if there was more than one match, while others just
processed the first match they came to.
To fix, create a unique index on (conrelid, contypid, conname). Since
either conrelid or contypid is zero, this will separately enforce
uniqueness of constraint names among constraints of any one table and any
one domain. (If we ever implement SQL assertions, and put them into this
catalog, more thought might be needed. But it'd be at least as reasonable
to put them into a new catalog; having overloaded this one catalog with
two kinds of constraints was a mistake already IMO.) This index can replace
the existing non-unique index on conrelid, though we need to keep the one
on contypid for query performance reasons.
Having done that, we can simplify the logic in various places that either
coped with duplicates or neglected to, as well as potentially improve
lookup performance when searching for a constraint by name.
Also, as per our usual practice, install a preliminary check so that you
get something more friendly than a unique-index violation report in the
case complained of by André. And teach ChooseIndexName to avoid choosing
autogenerated names that would draw such a failure.
While it's not possible to make such a change in the back branches,
it doesn't seem quite too late to put this into v11, so do so.
Discussion: https://postgr.es/m/0c1001d4428f$0942b430$1bc81c90$@webkr.de
17 files changed:
index 7290d9a5bdac02f5a74c95c02b43de2a8a8d45e2..d0a62123583fda93c85e7a179bce8bce959932d7 100644 (file)
<listitem>
<para>
The <literal>RENAME</literal> form changes the name of the index.
+ If the index is associated with a table constraint (either
+ <literal>UNIQUE</literal>, <literal>PRIMARY KEY</literal>,
+ or <literal>EXCLUDE</literal>), the constraint is renamed as well.
There is no effect on the stored data.
</para>
</listitem>
index 1cce00eaf92fcdedc08514e9bd8faaf869bf7492..ec6b4c331137d62398d75bfa0fbae92eec39ac31 100644 (file)
@@ -474,7 +474,8 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<term><literal>DROP CONSTRAINT [ IF EXISTS ]</literal></term>
<listitem>
<para>
- This form drops the specified constraint on a table.
+ This form drops the specified constraint on a table, along with
+ any index underlying the constraint.
If <literal>IF EXISTS</literal> is specified and the constraint
does not exist, no error is thrown. In this case a notice is issued instead.
</para>
@@ -822,8 +823,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<listitem>
<para>
The <literal>RENAME</literal> forms change the name of a table
- (or an index, sequence, view, materialized view, or foreign table), the name
- of an individual column in a table, or the name of a constraint of the table.
+ (or an index, sequence, view, materialized view, or foreign table), the
+ name of an individual column in a table, or the name of a constraint of
+ the table. When renaming a constraint that has an underlying index,
+ the index is renamed as well.
There is no effect on the stored data.
</para>
</listitem>
@@ -1270,10 +1273,12 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
<para>
If a table has any descendant tables, it is not permitted to add,
rename, or change the type of a column in the parent table without doing
- same to the descendants. This ensures that the descendants always have
- columns matching the parent. Similarly, a constraint cannot be renamed
- in the parent without also renaming it in all descendants, so that
- constraints also match between the parent and its descendants.
+ the same to the descendants. This ensures that the descendants always
+ have columns matching the parent. Similarly, a <literal>CHECK</literal>
+ constraint cannot be renamed in the parent without also renaming it in
+ all descendants, so that <literal>CHECK</literal> constraints also match
+ between the parent and its descendants. (That restriction does not apply
+ to index-based constraints, however.)
Also, because selecting from the parent also selects from its descendants,
a constraint on the parent cannot be marked valid unless it is also marked
valid for those descendants. In all of these cases, <command>ALTER TABLE
</programlisting></para>
<para>
- Attach a partition to range partitioned table:
+ To attach a partition to a range-partitioned table:
<programlisting>
ALTER TABLE measurement
ATTACH PARTITION measurement_y2016m07 FOR VALUES FROM ('2016-07-01') TO ('2016-08-01');
</programlisting></para>
<para>
- Attach a partition to list partitioned table:
+ To attach a partition to a list-partitioned table:
<programlisting>
ALTER TABLE cities
ATTACH PARTITION cities_ab FOR VALUES IN ('a', 'b');
</programlisting></para>
<para>
- Attach a default partition to a partitioned table:
+ To attach a partition to a hash-partitioned table:
<programlisting>
-ALTER TABLE cities
- ATTACH PARTITION cities_partdef DEFAULT;
+ALTER TABLE orders
+ ATTACH PARTITION orders_p4 FOR VALUES WITH (MODULUS 4, REMAINDER 3);
</programlisting></para>
<para>
- Attach a partition to hash partitioned table:
+ To attach a default partition to a partitioned table:
<programlisting>
-ALTER TABLE orders
- ATTACH PARTITION orders_p4 FOR VALUES WITH (MODULUS 4, REMAINDER 3);
+ALTER TABLE cities
+ ATTACH PARTITION cities_partdef DEFAULT;
</programlisting></para>
<para>
- Detach a partition from partitioned table:
+ To detach a partition from a partitioned table:
<programlisting>
ALTER TABLE measurement
DETACH PARTITION measurement_y2015m12;
index db0751d13d45d67285448ef627db55c13f84cd28..a89a7e00ed617c22614a8c76138d6c7be6adba86 100644 (file)
</para>
</refsect2>
+ <refsect2>
+ <title>Constraint Naming</title>
+
+ <para>
+ The SQL standard says that table and domain constraints must have names
+ that are unique across the schema containing the table or domain.
+ <productname>PostgreSQL</productname> is laxer: it only requires
+ constraint names to be unique across the constraints attached to a
+ particular table or domain. However, this extra freedom does not exist
+ for index-based constraints (<literal>UNIQUE</literal>,
+ <literal>PRIMARY KEY</literal>, and <literal>EXCLUDE</literal>
+ constraints), because the associated index is named the same as the
+ constraint, and index names must be unique across all relations within
+ the same schema.
+ </para>
+
+ <para>
+ Currently, <productname>PostgreSQL</productname> does not record names
+ for <literal>NOT NULL</literal> constraints at all, so they are not
+ subject to the uniqueness restriction. This might change in a future
+ release.
+ </para>
+ </refsect2>
+
<refsect2>
<title>Inheritance</title>
index 4cfc0c89116438b71f78453ff3c5ffd6d161be33..8c52a1543d5b2bd3bc28c86884ed3e59c524bb9e 100644 (file)
bool found;
Relation conDesc;
SysScanDesc conscan;
- ScanKeyData skey[2];
+ ScanKeyData skey[3];
HeapTuple tup;
/* Search for a pg_constraint entry with same name and relation */
found = false;
ScanKeyInit(&skey[0],
+ Anum_pg_constraint_conrelid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(RelationGetRelid(rel)));
+ ScanKeyInit(&skey[1],
+ Anum_pg_constraint_contypid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(InvalidOid));
+ ScanKeyInit(&skey[2],
Anum_pg_constraint_conname,
BTEqualStrategyNumber, F_NAMEEQ,
CStringGetDatum(ccname));
- ScanKeyInit(&skey[1],
- Anum_pg_constraint_connamespace,
- BTEqualStrategyNumber, F_OIDEQ,
- ObjectIdGetDatum(RelationGetNamespace(rel)));
-
- conscan = systable_beginscan(conDesc, ConstraintNameNspIndexId, true,
- NULL, 2, skey);
+ conscan = systable_beginscan(conDesc, ConstraintRelidTypidNameIndexId, true,
+ NULL, 3, skey);
- while (HeapTupleIsValid(tup = systable_getnext(conscan)))
+ /* There can be at most one matching row */
+ if (HeapTupleIsValid(tup = systable_getnext(conscan)))
{
Form_pg_constraint con = (Form_pg_constraint) GETSTRUCT(tup);
- if (con->conrelid == RelationGetRelid(rel))
+ /* Found it. Conflicts if not identical check constraint */
+ if (con->contype == CONSTRAINT_CHECK)
{
- /* Found it. Conflicts if not identical check constraint */
- if (con->contype == CONSTRAINT_CHECK)
- {
- Datum val;
- bool isnull;
-
- val = fastgetattr(tup,
- Anum_pg_constraint_conbin,
- conDesc->rd_att, &isnull);
- if (isnull)
- elog(ERROR, "null conbin for rel %s",
- RelationGetRelationName(rel));
- if (equal(expr, stringToNode(TextDatumGetCString(val))))
- found = true;
- }
+ Datum val;
+ bool isnull;
+
+ val = fastgetattr(tup,
+ Anum_pg_constraint_conbin,
+ conDesc->rd_att, &isnull);
+ if (isnull)
+ elog(ERROR, "null conbin for rel %s",
+ RelationGetRelationName(rel));
+ if (equal(expr, stringToNode(TextDatumGetCString(val))))
+ found = true;
+ }
- /*
- * If the existing constraint is purely inherited (no local
- * definition) then interpret addition of a local constraint as a
- * legal merge. This allows ALTER ADD CONSTRAINT on parent and
- * child tables to be given in either order with same end state.
- * However if the relation is a partition, all inherited
- * constraints are always non-local, including those that were
- * merged.
- */
- if (is_local && !con->conislocal && !rel->rd_rel->relispartition)
- allow_merge = true;
+ /*
+ * If the existing constraint is purely inherited (no local
+ * definition) then interpret addition of a local constraint as a
+ * legal merge. This allows ALTER ADD CONSTRAINT on parent and child
+ * tables to be given in either order with same end state. However if
+ * the relation is a partition, all inherited constraints are always
+ * non-local, including those that were merged.
+ */
+ if (is_local && !con->conislocal && !rel->rd_rel->relispartition)
+ allow_merge = true;
- if (!found || !allow_merge)
- ereport(ERROR,
- (errcode(ERRCODE_DUPLICATE_OBJECT),
- errmsg("constraint \"%s\" for relation \"%s\" already exists",
- ccname, RelationGetRelationName(rel))));
+ if (!found || !allow_merge)
+ ereport(ERROR,
+ (errcode(ERRCODE_DUPLICATE_OBJECT),
+ errmsg("constraint \"%s\" for relation \"%s\" already exists",
+ ccname, RelationGetRelationName(rel))));
- /* If the child constraint is "no inherit" then cannot merge */
- if (con->connoinherit)
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
- errmsg("constraint \"%s\" conflicts with non-inherited constraint on relation \"%s\"",
- ccname, RelationGetRelationName(rel))));
+ /* If the child constraint is "no inherit" then cannot merge */
+ if (con->connoinherit)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("constraint \"%s\" conflicts with non-inherited constraint on relation \"%s\"",
+ ccname, RelationGetRelationName(rel))));
- /*
- * Must not change an existing inherited constraint to "no
- * inherit" status. That's because inherited constraints should
- * be able to propagate to lower-level children.
- */
- if (con->coninhcount > 0 && is_no_inherit)
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
- errmsg("constraint \"%s\" conflicts with inherited constraint on relation \"%s\"",
- ccname, RelationGetRelationName(rel))));
+ /*
+ * Must not change an existing inherited constraint to "no inherit"
+ * status. That's because inherited constraints should be able to
+ * propagate to lower-level children.
+ */
+ if (con->coninhcount > 0 && is_no_inherit)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("constraint \"%s\" conflicts with inherited constraint on relation \"%s\"",
+ ccname, RelationGetRelationName(rel))));
- /*
- * If the child constraint is "not valid" then cannot merge with a
- * valid parent constraint
- */
- if (is_initially_valid && !con->convalidated)
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
- errmsg("constraint \"%s\" conflicts with NOT VALID constraint on relation \"%s\"",
- ccname, RelationGetRelationName(rel))));
+ /*
+ * If the child constraint is "not valid" then cannot merge with a
+ * valid parent constraint.
+ */
+ if (is_initially_valid && !con->convalidated)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("constraint \"%s\" conflicts with NOT VALID constraint on relation \"%s\"",
+ ccname, RelationGetRelationName(rel))));
- /* OK to update the tuple */
- ereport(NOTICE,
- (errmsg("merging constraint \"%s\" with inherited definition",
- ccname)));
+ /* OK to update the tuple */
+ ereport(NOTICE,
+ (errmsg("merging constraint \"%s\" with inherited definition",
+ ccname)));
- tup = heap_copytuple(tup);
- con = (Form_pg_constraint) GETSTRUCT(tup);
+ tup = heap_copytuple(tup);
+ con = (Form_pg_constraint) GETSTRUCT(tup);
- /*
- * In case of partitions, an inherited constraint must be
- * inherited only once since it cannot have multiple parents and
- * it is never considered local.
- */
- if (rel->rd_rel->relispartition)
- {
- con->coninhcount = 1;
- con->conislocal = false;
- }
+ /*
+ * In case of partitions, an inherited constraint must be inherited
+ * only once since it cannot have multiple parents and it is never
+ * considered local.
+ */
+ if (rel->rd_rel->relispartition)
+ {
+ con->coninhcount = 1;
+ con->conislocal = false;
+ }
+ else
+ {
+ if (is_local)
+ con->conislocal = true;
else
- {
- if (is_local)
- con->conislocal = true;
- else
- con->coninhcount++;
- }
+ con->coninhcount++;
+ }
- if (is_no_inherit)
- {
- Assert(is_local);
- con->connoinherit = true;
- }
- CatalogTupleUpdate(conDesc, &tup->t_self, tup);
- break;
+ if (is_no_inherit)
+ {
+ Assert(is_local);
+ con->connoinherit = true;
}
+
+ CatalogTupleUpdate(conDesc, &tup->t_self, tup);
}
systable_endscan(conscan);
index 2dad7b059e9fec1ec1a1fb2fa0218ae3a9d41d95..4debe6ee87a3f5e583641b052c9882adba4d11ad 100644 (file)
if (shared_relation && tableSpaceId != GLOBALTABLESPACE_OID)
elog(ERROR, "shared relations must be placed in pg_global tablespace");
+ /*
+ * Check for duplicate name (both as to the index, and as to the
+ * associated constraint if any). Such cases would fail on the relevant
+ * catalogs' unique indexes anyway, but we prefer to give a friendlier
+ * error message.
+ */
if (get_relname_relid(indexRelationName, namespaceId))
{
if ((flags & INDEX_CREATE_IF_NOT_EXISTS) != 0)
indexRelationName)));
}
+ if ((flags & INDEX_CREATE_ADD_CONSTRAINT) != 0 &&
+ ConstraintNameIsUsed(CONSTRAINT_RELATION, heapRelationId,
+ indexRelationName))
+ {
+ /*
+ * INDEX_CREATE_IF_NOT_EXISTS does not apply here, since the
+ * conflicting constraint is not an index.
+ */
+ ereport(ERROR,
+ (errcode(ERRCODE_DUPLICATE_OBJECT),
+ errmsg("constraint \"%s\" for relation \"%s\" already exists",
+ indexRelationName, RelationGetRelationName(heapRelation))));
+ }
+
/*
* construct tuple descriptor for index tuples
*/
index ea84441360828e40a8506259f4fcfdc9081a4c78..6781b00c6e6a3674ccdd4851b2df173b7892b6e3 100644 (file)
ScanKeyInit(&key,
Anum_pg_constraint_conrelid, BTEqualStrategyNumber,
F_OIDEQ, ObjectIdGetDatum(parentId));
- scan = systable_beginscan(pg_constraint, ConstraintRelidIndexId, true,
+ scan = systable_beginscan(pg_constraint, ConstraintRelidTypidNameIndexId, true,
NULL, 1, &key);
while ((tuple = systable_getnext(scan)) != NULL)
*/
bool
ConstraintNameIsUsed(ConstraintCategory conCat, Oid objId,
- Oid objNamespace, const char *conname)
+ const char *conname)
{
bool found;
Relation conDesc;
SysScanDesc conscan;
- ScanKeyData skey[2];
- HeapTuple tup;
+ ScanKeyData skey[3];
conDesc = heap_open(ConstraintRelationId, AccessShareLock);
- found = false;
+ ScanKeyInit(&skey[0],
+ Anum_pg_constraint_conrelid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum((conCat == CONSTRAINT_RELATION)
+ ? objId : InvalidOid));
+ ScanKeyInit(&skey[1],
+ Anum_pg_constraint_contypid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum((conCat == CONSTRAINT_DOMAIN)
+ ? objId : InvalidOid));
+ ScanKeyInit(&skey[2],
+ Anum_pg_constraint_conname,
+ BTEqualStrategyNumber, F_NAMEEQ,
+ CStringGetDatum(conname));
+
+ conscan = systable_beginscan(conDesc, ConstraintRelidTypidNameIndexId,
+ true, NULL, 3, skey);
+
+ /* There can be at most one matching row */
+ found = (HeapTupleIsValid(systable_getnext(conscan)));
+
+ systable_endscan(conscan);
+ heap_close(conDesc, AccessShareLock);
+
+ return found;
+}
+
+/*
+ * Does any constraint of the given name exist in the given namespace?
+ *
+ * This is used for code that wants to match ChooseConstraintName's rule
+ * that we should avoid autogenerating duplicate constraint names within a
+ * namespace.
+ */
+bool
+ConstraintNameExists(const char *conname, Oid namespaceid)
+{
+ bool found;
+ Relation conDesc;
+ SysScanDesc conscan;
+ ScanKeyData skey[2];
+
+ conDesc = heap_open(ConstraintRelationId, AccessShareLock);
ScanKeyInit(&skey[0],
Anum_pg_constraint_conname,
ScanKeyInit(&skey[1],
Anum_pg_constraint_connamespace,
BTEqualStrategyNumber, F_OIDEQ,
- ObjectIdGetDatum(objNamespace));
+ ObjectIdGetDatum(namespaceid));
conscan = systable_beginscan(conDesc, ConstraintNameNspIndexId, true,
NULL, 2, skey);
- while (HeapTupleIsValid(tup = systable_getnext(conscan)))
- {
- Form_pg_constraint con = (Form_pg_constraint) GETSTRUCT(tup);
-
- if (conCat == CONSTRAINT_RELATION && con->conrelid == objId)
- {
- found = true;
- break;
- }
- else if (conCat == CONSTRAINT_DOMAIN && con->contypid == objId)
- {
- found = true;
- break;
- }
- }
+ found = (HeapTupleIsValid(systable_getnext(conscan)));
systable_endscan(conscan);
heap_close(conDesc, AccessShareLock);
con = (Form_pg_constraint) GETSTRUCT(tuple);
/*
- * We need to check whether the name is already in use --- note that there
- * currently is not a unique index that would catch this.
+ * For user-friendliness, check whether the name is already in use.
*/
if (OidIsValid(con->conrelid) &&
ConstraintNameIsUsed(CONSTRAINT_RELATION,
con->conrelid,
- con->connamespace,
newname))
ereport(ERROR,
(errcode(ERRCODE_DUPLICATE_OBJECT),
if (OidIsValid(con->contypid) &&
ConstraintNameIsUsed(CONSTRAINT_DOMAIN,
con->contypid,
- con->connamespace,
newname))
ereport(ERROR,
(errcode(ERRCODE_DUPLICATE_OBJECT),
Oid newNspId, bool isType, ObjectAddresses *objsMoved)
{
Relation conRel;
- ScanKeyData key[1];
+ ScanKeyData key[2];
SysScanDesc scan;
HeapTuple tup;
conRel = heap_open(ConstraintRelationId, RowExclusiveLock);
- if (isType)
- {
- ScanKeyInit(&key[0],
- Anum_pg_constraint_contypid,
- BTEqualStrategyNumber, F_OIDEQ,
- ObjectIdGetDatum(ownerId));
-
- scan = systable_beginscan(conRel, ConstraintTypidIndexId, true,
- NULL, 1, key);
- }
- else
- {
- ScanKeyInit(&key[0],
- Anum_pg_constraint_conrelid,
- BTEqualStrategyNumber, F_OIDEQ,
- ObjectIdGetDatum(ownerId));
+ ScanKeyInit(&key[0],
+ Anum_pg_constraint_conrelid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(isType ? InvalidOid : ownerId));
+ ScanKeyInit(&key[1],
+ Anum_pg_constraint_contypid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(isType ? ownerId : InvalidOid));
- scan = systable_beginscan(conRel, ConstraintRelidIndexId, true,
- NULL, 1, key);
- }
+ scan = systable_beginscan(conRel, ConstraintRelidTypidNameIndexId, true,
+ NULL, 2, key);
while (HeapTupleIsValid((tup = systable_getnext(scan))))
{
@@ -1038,38 +1053,30 @@ get_relation_constraint_oid(Oid relid, const char *conname, bool missing_ok)
Relation pg_constraint;
HeapTuple tuple;
SysScanDesc scan;
- ScanKeyData skey[1];
+ ScanKeyData skey[3];
Oid conOid = InvalidOid;
- /*
- * Fetch the constraint tuple from pg_constraint. There may be more than
- * one match, because constraints are not required to have unique names;
- * if so, error out.
- */
pg_constraint = heap_open(ConstraintRelationId, AccessShareLock);
ScanKeyInit(&skey[0],
Anum_pg_constraint_conrelid,
BTEqualStrategyNumber, F_OIDEQ,
ObjectIdGetDatum(relid));
+ ScanKeyInit(&skey[1],
+ Anum_pg_constraint_contypid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(InvalidOid));
+ ScanKeyInit(&skey[2],
+ Anum_pg_constraint_conname,
+ BTEqualStrategyNumber, F_NAMEEQ,
+ CStringGetDatum(conname));
- scan = systable_beginscan(pg_constraint, ConstraintRelidIndexId, true,
- NULL, 1, skey);
-
- while (HeapTupleIsValid(tuple = systable_getnext(scan)))
- {
- Form_pg_constraint con = (Form_pg_constraint) GETSTRUCT(tuple);
+ scan = systable_beginscan(pg_constraint, ConstraintRelidTypidNameIndexId, true,
+ NULL, 3, skey);
- if (strcmp(NameStr(con->conname), conname) == 0)
- {
- if (OidIsValid(conOid))
- ereport(ERROR,
- (errcode(ERRCODE_DUPLICATE_OBJECT),
- errmsg("table \"%s\" has multiple constraints named \"%s\"",
- get_rel_name(relid), conname)));
- conOid = HeapTupleGetOid(tuple);
- }
- }
+ /* There can be at most one matching row */
+ if (HeapTupleIsValid(tuple = systable_getnext(scan)))
+ conOid = HeapTupleGetOid(tuple);
systable_endscan(scan);
Relation pg_constraint;
HeapTuple tuple;
SysScanDesc scan;
- ScanKeyData skey[1];
+ ScanKeyData skey[3];
/* Set *constraintOid, to avoid complaints about uninitialized vars */
*constraintOid = InvalidOid;
- /*
- * Fetch the constraint tuple from pg_constraint. There may be more than
- * one match, because constraints are not required to have unique names;
- * if so, error out.
- */
pg_constraint = heap_open(ConstraintRelationId, AccessShareLock);
ScanKeyInit(&skey[0],
Anum_pg_constraint_conrelid,
BTEqualStrategyNumber, F_OIDEQ,
ObjectIdGetDatum(relid));
+ ScanKeyInit(&skey[1],
+ Anum_pg_constraint_contypid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(InvalidOid));
+ ScanKeyInit(&skey[2],
+ Anum_pg_constraint_conname,
+ BTEqualStrategyNumber, F_NAMEEQ,
+ CStringGetDatum(conname));
- scan = systable_beginscan(pg_constraint, ConstraintRelidIndexId, true,
- NULL, 1, skey);
+ scan = systable_beginscan(pg_constraint, ConstraintRelidTypidNameIndexId, true,
+ NULL, 3, skey);
- while (HeapTupleIsValid(tuple = systable_getnext(scan)))
+ /* There can be at most one matching row */
+ if (HeapTupleIsValid(tuple = systable_getnext(scan)))
{
- Form_pg_constraint con = (Form_pg_constraint) GETSTRUCT(tuple);
Datum adatum;
bool isNull;
- ArrayType *arr;
- int16 *attnums;
- int numcols;
- int i;
-
- /* Check the constraint name */
- if (strcmp(NameStr(con->conname), conname) != 0)
- continue;
- if (OidIsValid(*constraintOid))
- ereport(ERROR,
- (errcode(ERRCODE_DUPLICATE_OBJECT),
- errmsg("table \"%s\" has multiple constraints named \"%s\"",
- get_rel_name(relid), conname)));
*constraintOid = HeapTupleGetOid(tuple);
/* Extract the conkey array, ie, attnums of constrained columns */
adatum = heap_getattr(tuple, Anum_pg_constraint_conkey,
RelationGetDescr(pg_constraint), &isNull);
- if (isNull)
- continue; /* no constrained columns */
-
- arr = DatumGetArrayTypeP(adatum); /* ensure not toasted */
- numcols = ARR_DIMS(arr)[0];
- if (ARR_NDIM(arr) != 1 ||
- numcols < 0 ||
- ARR_HASNULL(arr) ||
- ARR_ELEMTYPE(arr) != INT2OID)
- elog(ERROR, "conkey is not a 1-D smallint array");
- attnums = (int16 *) ARR_DATA_PTR(arr);
-
- /* Construct the result value */
- for (i = 0; i < numcols; i++)
+ if (!isNull)
{
- conattnos = bms_add_member(conattnos,
- attnums[i] - FirstLowInvalidHeapAttributeNumber);
+ ArrayType *arr;
+ int numcols;
+ int16 *attnums;
+ int i;
+
+ arr = DatumGetArrayTypeP(adatum); /* ensure not toasted */
+ numcols = ARR_DIMS(arr)[0];
+ if (ARR_NDIM(arr) != 1 ||
+ numcols < 0 ||
+ ARR_HASNULL(arr) ||
+ ARR_ELEMTYPE(arr) != INT2OID)
+ elog(ERROR, "conkey is not a 1-D smallint array");
+ attnums = (int16 *) ARR_DATA_PTR(arr);
+
+ /* Construct the result value */
+ for (i = 0; i < numcols; i++)
+ {
+ conattnos = bms_add_member(conattnos,
+ attnums[i] - FirstLowInvalidHeapAttributeNumber);
+ }
}
}
BTEqualStrategyNumber,
F_OIDEQ,
ObjectIdGetDatum(relationId));
- scan = systable_beginscan(pg_constraint, ConstraintRelidIndexId,
+ scan = systable_beginscan(pg_constraint, ConstraintRelidTypidNameIndexId,
true, NULL, 1, &key);
while ((tuple = systable_getnext(scan)) != NULL)
{
Relation pg_constraint;
HeapTuple tuple;
SysScanDesc scan;
- ScanKeyData skey[1];
+ ScanKeyData skey[3];
Oid conOid = InvalidOid;
- /*
- * Fetch the constraint tuple from pg_constraint. There may be more than
- * one match, because constraints are not required to have unique names;
- * if so, error out.
- */
pg_constraint = heap_open(ConstraintRelationId, AccessShareLock);
ScanKeyInit(&skey[0],
+ Anum_pg_constraint_conrelid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(InvalidOid));
+ ScanKeyInit(&skey[1],
Anum_pg_constraint_contypid,
BTEqualStrategyNumber, F_OIDEQ,
ObjectIdGetDatum(typid));
+ ScanKeyInit(&skey[2],
+ Anum_pg_constraint_conname,
+ BTEqualStrategyNumber, F_NAMEEQ,
+ CStringGetDatum(conname));
- scan = systable_beginscan(pg_constraint, ConstraintTypidIndexId, true,
- NULL, 1, skey);
-
- while (HeapTupleIsValid(tuple = systable_getnext(scan)))
- {
- Form_pg_constraint con = (Form_pg_constraint) GETSTRUCT(tuple);
+ scan = systable_beginscan(pg_constraint, ConstraintRelidTypidNameIndexId, true,
+ NULL, 3, skey);
- if (strcmp(NameStr(con->conname), conname) == 0)
- {
- if (OidIsValid(conOid))
- ereport(ERROR,
- (errcode(ERRCODE_DUPLICATE_OBJECT),
- errmsg("domain %s has multiple constraints named \"%s\"",
- format_type_be(typid), conname)));
- conOid = HeapTupleGetOid(tuple);
- }
- }
+ /* There can be at most one matching row */
+ if (HeapTupleIsValid(tuple = systable_getnext(scan)))
+ conOid = HeapTupleGetOid(tuple);
systable_endscan(scan);
BTEqualStrategyNumber, F_OIDEQ,
ObjectIdGetDatum(relid));
- scan = systable_beginscan(pg_constraint, ConstraintRelidIndexId, true,
+ scan = systable_beginscan(pg_constraint, ConstraintRelidTypidNameIndexId, true,
NULL, 1, skey);
while (HeapTupleIsValid(tuple = systable_getnext(scan)))
index d54c78c35272663d5e6b4f224fc906f9bb790bff..ab3d9a0a48924279cb783e8a5fabc27a0cbf43d5 100644 (file)
* except that the label can't be NULL; digits will be appended to the label
* if needed to create a name that is unique within the specified namespace.
*
+ * If isconstraint is true, we also avoid choosing a name matching any
+ * existing constraint in the same namespace. (This is stricter than what
+ * Postgres itself requires, but the SQL standard says that constraint names
+ * should be unique within schemas, so we follow that for autogenerated
+ * constraint names.)
+ *
* Note: it is theoretically possible to get a collision anyway, if someone
* else chooses the same name concurrently. This is fairly unlikely to be
* a problem in practice, especially if one is holding an exclusive lock on
*/
char *
ChooseRelationName(const char *name1, const char *name2,
- const char *label, Oid namespaceid)
+ const char *label, Oid namespaceid,
+ bool isconstraint)
{
int pass = 0;
char *relname = NULL;
relname = makeObjectName(name1, name2, modlabel);
if (!OidIsValid(get_relname_relid(relname, namespaceid)))
- break;
+ {
+ if (!isconstraint ||
+ !ConstraintNameExists(relname, namespaceid))
+ break;
+ }
/* found a conflict, so try a new name component */
pfree(relname);
indexname = ChooseRelationName(tabname,
NULL,
"pkey",
- namespaceId);
+ namespaceId,
+ true);
}
else if (exclusionOpNames != NIL)
{
indexname = ChooseRelationName(tabname,
ChooseIndexNameAddition(colnames),
"excl",
- namespaceId);
+ namespaceId,
+ true);
}
else if (isconstraint)
{
indexname = ChooseRelationName(tabname,
ChooseIndexNameAddition(colnames),
"key",
- namespaceId);
+ namespaceId,
+ true);
}
else
{
indexname = ChooseRelationName(tabname,
ChooseIndexNameAddition(colnames),
"idx",
- namespaceId);
+ namespaceId,
+ false);
}
return indexname;
index c979f70397d74981b3754457c15e75c3e5bcb3d2..2c86bf4304aa5bd03fa1e78c922a57a0e2e3e58a 100644 (file)
{
if (ConstraintNameIsUsed(CONSTRAINT_RELATION,
RelationGetRelid(rel),
- RelationGetNamespace(rel),
newConstraint->conname))
ereport(ERROR,
(errcode(ERRCODE_DUPLICATE_OBJECT),
Constraint *cmdcon;
Relation conrel;
SysScanDesc scan;
- ScanKeyData key;
+ ScanKeyData skey[3];
HeapTuple contuple;
- Form_pg_constraint currcon = NULL;
- bool found = false;
+ Form_pg_constraint currcon;
ObjectAddress address;
cmdcon = castNode(Constraint, cmd->def);
/*
* Find and check the target constraint
*/
- ScanKeyInit(&key,
+ ScanKeyInit(&skey[0],
Anum_pg_constraint_conrelid,
BTEqualStrategyNumber, F_OIDEQ,
ObjectIdGetDatum(RelationGetRelid(rel)));
- scan = systable_beginscan(conrel, ConstraintRelidIndexId,
- true, NULL, 1, &key);
-
- while (HeapTupleIsValid(contuple = systable_getnext(scan)))
- {
- currcon = (Form_pg_constraint) GETSTRUCT(contuple);
- if (strcmp(NameStr(currcon->conname), cmdcon->conname) == 0)
- {
- found = true;
- break;
- }
- }
+ ScanKeyInit(&skey[1],
+ Anum_pg_constraint_contypid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(InvalidOid));
+ ScanKeyInit(&skey[2],
+ Anum_pg_constraint_conname,
+ BTEqualStrategyNumber, F_NAMEEQ,
+ CStringGetDatum(cmdcon->conname));
+ scan = systable_beginscan(conrel, ConstraintRelidTypidNameIndexId,
+ true, NULL, 3, skey);
- if (!found)
+ /* There can be at most one matching row */
+ if (!HeapTupleIsValid(contuple = systable_getnext(scan)))
ereport(ERROR,
(errcode(ERRCODE_UNDEFINED_OBJECT),
errmsg("constraint \"%s\" of relation \"%s\" does not exist",
cmdcon->conname, RelationGetRelationName(rel))));
+ currcon = (Form_pg_constraint) GETSTRUCT(contuple);
if (currcon->contype != CONSTRAINT_FOREIGN)
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
{
Relation conrel;
SysScanDesc scan;
- ScanKeyData key;
+ ScanKeyData skey[3];
HeapTuple tuple;
- Form_pg_constraint con = NULL;
- bool found = false;
+ Form_pg_constraint con;
ObjectAddress address;
conrel = heap_open(ConstraintRelationId, RowExclusiveLock);
/*
* Find and check the target constraint
*/
- ScanKeyInit(&key,
+ ScanKeyInit(&skey[0],
Anum_pg_constraint_conrelid,
BTEqualStrategyNumber, F_OIDEQ,
ObjectIdGetDatum(RelationGetRelid(rel)));
- scan = systable_beginscan(conrel, ConstraintRelidIndexId,
- true, NULL, 1, &key);
-
- while (HeapTupleIsValid(tuple = systable_getnext(scan)))
- {
- con = (Form_pg_constraint) GETSTRUCT(tuple);
- if (strcmp(NameStr(con->conname), constrName) == 0)
- {
- found = true;
- break;
- }
- }
+ ScanKeyInit(&skey[1],
+ Anum_pg_constraint_contypid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(InvalidOid));
+ ScanKeyInit(&skey[2],
+ Anum_pg_constraint_conname,
+ BTEqualStrategyNumber, F_NAMEEQ,
+ CStringGetDatum(constrName));
+ scan = systable_beginscan(conrel, ConstraintRelidTypidNameIndexId,
+ true, NULL, 3, skey);
- if (!found)
+ /* There can be at most one matching row */
+ if (!HeapTupleIsValid(tuple = systable_getnext(scan)))
ereport(ERROR,
(errcode(ERRCODE_UNDEFINED_OBJECT),
errmsg("constraint \"%s\" of relation \"%s\" does not exist",
constrName, RelationGetRelationName(rel))));
+ con = (Form_pg_constraint) GETSTRUCT(tuple);
if (con->contype != CONSTRAINT_FOREIGN &&
con->contype != CONSTRAINT_CHECK)
ereport(ERROR,
Relation conrel;
Form_pg_constraint con;
SysScanDesc scan;
- ScanKeyData key;
+ ScanKeyData skey[3];
HeapTuple tuple;
bool found = false;
bool is_no_inherit_constraint = false;
/*
* Find and drop the target constraint
*/
- ScanKeyInit(&key,
+ ScanKeyInit(&skey[0],
Anum_pg_constraint_conrelid,
BTEqualStrategyNumber, F_OIDEQ,
ObjectIdGetDatum(RelationGetRelid(rel)));
- scan = systable_beginscan(conrel, ConstraintRelidIndexId,
- true, NULL, 1, &key);
+ ScanKeyInit(&skey[1],
+ Anum_pg_constraint_contypid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(InvalidOid));
+ ScanKeyInit(&skey[2],
+ Anum_pg_constraint_conname,
+ BTEqualStrategyNumber, F_NAMEEQ,
+ CStringGetDatum(constrName));
+ scan = systable_beginscan(conrel, ConstraintRelidTypidNameIndexId,
+ true, NULL, 3, skey);
- while (HeapTupleIsValid(tuple = systable_getnext(scan)))
+ /* There can be at most one matching row */
+ if (HeapTupleIsValid(tuple = systable_getnext(scan)))
{
ObjectAddress conobj;
con = (Form_pg_constraint) GETSTRUCT(tuple);
- if (strcmp(NameStr(con->conname), constrName) != 0)
- continue;
-
/* Don't drop inherited constraints */
if (con->coninhcount > 0 && !recursing)
ereport(ERROR,
performDeletion(&conobj, behavior, 0);
found = true;
-
- /* constraint found and dropped -- no need to keep looping */
- break;
}
systable_endscan(scan);
childrel = heap_open(childrelid, NoLock);
CheckTableNotInUse(childrel, "ALTER TABLE");
- ScanKeyInit(&key,
+ ScanKeyInit(&skey[0],
Anum_pg_constraint_conrelid,
BTEqualStrategyNumber, F_OIDEQ,
ObjectIdGetDatum(childrelid));
- scan = systable_beginscan(conrel, ConstraintRelidIndexId,
- true, NULL, 1, &key);
-
- /* scan for matching tuple - there should only be one */
- while (HeapTupleIsValid(tuple = systable_getnext(scan)))
- {
- con = (Form_pg_constraint) GETSTRUCT(tuple);
-
- /* Right now only CHECK constraints can be inherited */
- if (con->contype != CONSTRAINT_CHECK)
- continue;
-
- if (strcmp(NameStr(con->conname), constrName) == 0)
- break;
- }
-
- if (!HeapTupleIsValid(tuple))
+ ScanKeyInit(&skey[1],
+ Anum_pg_constraint_contypid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(InvalidOid));
+ ScanKeyInit(&skey[2],
+ Anum_pg_constraint_conname,
+ BTEqualStrategyNumber, F_NAMEEQ,
+ CStringGetDatum(constrName));
+ scan = systable_beginscan(conrel, ConstraintRelidTypidNameIndexId,
+ true, NULL, 3, skey);
+
+ /* There can be at most one matching row */
+ if (!HeapTupleIsValid(tuple = systable_getnext(scan)))
ereport(ERROR,
(errcode(ERRCODE_UNDEFINED_OBJECT),
errmsg("constraint \"%s\" of relation \"%s\" does not exist",
con = (Form_pg_constraint) GETSTRUCT(copy_tuple);
+ /* Right now only CHECK constraints can be inherited */
+ if (con->contype != CONSTRAINT_CHECK)
+ elog(ERROR, "inherited constraint is not a CHECK constraint");
+
if (con->coninhcount <= 0) /* shouldn't happen */
elog(ERROR, "relation %u has non-inherited constraint \"%s\"",
childrelid, constrName);
Anum_pg_constraint_conrelid,
BTEqualStrategyNumber, F_OIDEQ,
ObjectIdGetDatum(RelationGetRelid(parent_rel)));
- parent_scan = systable_beginscan(catalog_relation, ConstraintRelidIndexId,
+ parent_scan = systable_beginscan(catalog_relation, ConstraintRelidTypidNameIndexId,
true, NULL, 1, &parent_key);
while (HeapTupleIsValid(parent_tuple = systable_getnext(parent_scan)))
Anum_pg_constraint_conrelid,
BTEqualStrategyNumber, F_OIDEQ,
ObjectIdGetDatum(RelationGetRelid(child_rel)));
- child_scan = systable_beginscan(catalog_relation, ConstraintRelidIndexId,
+ child_scan = systable_beginscan(catalog_relation, ConstraintRelidTypidNameIndexId,
true, NULL, 1, &child_key);
while (HeapTupleIsValid(child_tuple = systable_getnext(child_scan)))
Anum_pg_constraint_conrelid,
BTEqualStrategyNumber, F_OIDEQ,
ObjectIdGetDatum(RelationGetRelid(parent_rel)));
- scan = systable_beginscan(catalogRelation, ConstraintRelidIndexId,
+ scan = systable_beginscan(catalogRelation, ConstraintRelidTypidNameIndexId,
true, NULL, 1, key);
connames = NIL;
Anum_pg_constraint_conrelid,
BTEqualStrategyNumber, F_OIDEQ,
ObjectIdGetDatum(RelationGetRelid(child_rel)));
- scan = systable_beginscan(catalogRelation, ConstraintRelidIndexId,
+ scan = systable_beginscan(catalogRelation, ConstraintRelidTypidNameIndexId,
true, NULL, 1, key);
while (HeapTupleIsValid(constraintTuple = systable_getnext(scan)))
BTEqualStrategyNumber, F_OIDEQ,
ObjectIdGetDatum(RelationGetRelid(rel)));
scan = systable_beginscan(pg_constraint,
- toLogged ? ConstraintRelidIndexId : InvalidOid,
+ toLogged ? ConstraintRelidTypidNameIndexId : InvalidOid,
true, NULL, 1, skey);
while (HeapTupleIsValid(tuple = systable_getnext(scan)))
index 175ecc8b484fa42581bfa38b911b8085dd832ce7..b018585aef8e01e93caa150ddcb48b8a1a2f34af 100644 (file)
* AlterDomainDropConstraint
*
* Implements the ALTER DOMAIN DROP CONSTRAINT statement
+ *
+ * Returns ObjectAddress of the modified domain.
*/
ObjectAddress
AlterDomainDropConstraint(List *names, const char *constrName,
Relation rel;
Relation conrel;
SysScanDesc conscan;
- ScanKeyData key[1];
+ ScanKeyData skey[3];
HeapTuple contup;
bool found = false;
- ObjectAddress address = InvalidObjectAddress;
+ ObjectAddress address;
/* Make a TypeName so we can use standard type lookup machinery */
typename = makeTypeNameFromNameList(names);
/* Grab an appropriate lock on the pg_constraint relation */
conrel = heap_open(ConstraintRelationId, RowExclusiveLock);
- /* Use the index to scan only constraints of the target relation */
- ScanKeyInit(&key[0],
+ /* Find and remove the target constraint */
+ ScanKeyInit(&skey[0],
+ Anum_pg_constraint_conrelid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(InvalidOid));
+ ScanKeyInit(&skey[1],
Anum_pg_constraint_contypid,
BTEqualStrategyNumber, F_OIDEQ,
- ObjectIdGetDatum(HeapTupleGetOid(tup)));
+ ObjectIdGetDatum(domainoid));
+ ScanKeyInit(&skey[2],
+ Anum_pg_constraint_conname,
+ BTEqualStrategyNumber, F_NAMEEQ,
+ CStringGetDatum(constrName));
- conscan = systable_beginscan(conrel, ConstraintTypidIndexId, true,
- NULL, 1, key);
+ conscan = systable_beginscan(conrel, ConstraintRelidTypidNameIndexId, true,
+ NULL, 3, skey);
- /*
- * Scan over the result set, removing any matching entries.
- */
- while ((contup = systable_getnext(conscan)) != NULL)
+ /* There can be at most one matching row */
+ if ((contup = systable_getnext(conscan)) != NULL)
{
- Form_pg_constraint con = (Form_pg_constraint) GETSTRUCT(contup);
-
- if (strcmp(NameStr(con->conname), constrName) == 0)
- {
- ObjectAddress conobj;
+ ObjectAddress conobj;
- conobj.classId = ConstraintRelationId;
- conobj.objectId = HeapTupleGetOid(contup);
- conobj.objectSubId = 0;
+ conobj.classId = ConstraintRelationId;
+ conobj.objectId = HeapTupleGetOid(contup);
+ conobj.objectSubId = 0;
- performDeletion(&conobj, behavior, 0);
- found = true;
- }
+ performDeletion(&conobj, behavior, 0);
+ found = true;
}
- ObjectAddressSet(address, TypeRelationId, domainoid);
-
/* Clean up after the scan */
systable_endscan(conscan);
heap_close(conrel, RowExclusiveLock);
constrName, TypeNameToString(typename))));
}
+ ObjectAddressSet(address, TypeRelationId, domainoid);
+
return address;
}
Relation typrel;
Relation conrel;
HeapTuple tup;
- Form_pg_constraint con = NULL;
+ Form_pg_constraint con;
Form_pg_constraint copy_con;
char *conbin;
SysScanDesc scan;
Datum val;
- bool found = false;
bool isnull;
HeapTuple tuple;
HeapTuple copyTuple;
- ScanKeyData key;
+ ScanKeyData skey[3];
ObjectAddress address;
/* Make a TypeName so we can use standard type lookup machinery */
* Find and check the target constraint
*/
conrel = heap_open(ConstraintRelationId, RowExclusiveLock);
- ScanKeyInit(&key,
+
+ ScanKeyInit(&skey[0],
+ Anum_pg_constraint_conrelid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(InvalidOid));
+ ScanKeyInit(&skey[1],
Anum_pg_constraint_contypid,
BTEqualStrategyNumber, F_OIDEQ,
ObjectIdGetDatum(domainoid));
- scan = systable_beginscan(conrel, ConstraintTypidIndexId,
- true, NULL, 1, &key);
+ ScanKeyInit(&skey[2],
+ Anum_pg_constraint_conname,
+ BTEqualStrategyNumber, F_NAMEEQ,
+ CStringGetDatum(constrName));
- while (HeapTupleIsValid(tuple = systable_getnext(scan)))
- {
- con = (Form_pg_constraint) GETSTRUCT(tuple);
- if (strcmp(NameStr(con->conname), constrName) == 0)
- {
- found = true;
- break;
- }
- }
+ scan = systable_beginscan(conrel, ConstraintRelidTypidNameIndexId, true,
+ NULL, 3, skey);
- if (!found)
+ /* There can be at most one matching row */
+ if (!HeapTupleIsValid(tuple = systable_getnext(scan)))
ereport(ERROR,
(errcode(ERRCODE_UNDEFINED_OBJECT),
errmsg("constraint \"%s\" of domain \"%s\" does not exist",
constrName, TypeNameToString(typename))));
+ con = (Form_pg_constraint) GETSTRUCT(tuple);
if (con->contype != CONSTRAINT_CHECK)
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
{
if (ConstraintNameIsUsed(CONSTRAINT_DOMAIN,
domainOid,
- domainNamespace,
constr->conname))
ereport(ERROR,
(errcode(ERRCODE_DUPLICATE_OBJECT),
index 17b54b20cccc1434af13d79192cf8f37b9e56722..ff879f089662c5afffab2173e204de14165c4fcd 100644 (file)
sname = ChooseRelationName(cxt->relation->relname,
column->colname,
"seq",
- snamespaceid);
+ snamespaceid,
+ false);
}
ereport(DEBUG1,
index 6125421d39a50b50b87b3bcd03b73b676127e9c9..a4fc0011031ec4dabc7e5f58feb1c26663cf5147 100644 (file)
ObjectIdGetDatum(RelationGetRelid(relation)));
conrel = heap_open(ConstraintRelationId, AccessShareLock);
- conscan = systable_beginscan(conrel, ConstraintRelidIndexId, true,
+ conscan = systable_beginscan(conrel, ConstraintRelidTypidNameIndexId, true,
NULL, 1, skey);
while (HeapTupleIsValid(htup = systable_getnext(conscan)))
ObjectIdGetDatum(RelationGetRelid(relation)));
conrel = heap_open(ConstraintRelationId, AccessShareLock);
- conscan = systable_beginscan(conrel, ConstraintRelidIndexId, true,
+ conscan = systable_beginscan(conrel, ConstraintRelidTypidNameIndexId, true,
NULL, 1, &skey);
while (HeapTupleIsValid(htup = systable_getnext(conscan)))
* Search pg_constraint for the constraint associated with the index. To
* make this not too painfully slow, we use the index on conrelid; that
* will hold the parent relation's OID not the index's own OID.
+ *
+ * Note: if we wanted to rely on the constraint name matching the index's
+ * name, we could just do a direct lookup using pg_constraint's unique
+ * index. For the moment it doesn't seem worth requiring that.
*/
ScanKeyInit(&skey[0],
Anum_pg_constraint_conrelid,
ObjectIdGetDatum(indexRelation->rd_index->indrelid));
conrel = heap_open(ConstraintRelationId, AccessShareLock);
- conscan = systable_beginscan(conrel, ConstraintRelidIndexId, true,
+ conscan = systable_beginscan(conrel, ConstraintRelidTypidNameIndexId, true,
NULL, 1, skey);
found = false;
index 0585e9b3badd26459ca1400eb6df325021fc9616..485f63ab595dc12a3184a039ca6397339cbffcf2 100644 (file)
*/
/* yyyymmddN */
-#define CATALOG_VERSION_NO 201809031
+#define CATALOG_VERSION_NO 201809041
#endif
index 24915824caac4e9060003b0a9610ed8228320e25..254fbef1f78474b1c9b3ec2bb20349a8eacfa37c 100644 (file)
@@ -121,8 +121,8 @@ DECLARE_UNIQUE_INDEX(pg_collation_oid_index, 3085, on pg_collation using btree(o
DECLARE_INDEX(pg_constraint_conname_nsp_index, 2664, on pg_constraint using btree(conname name_ops, connamespace oid_ops));
#define ConstraintNameNspIndexId 2664
-DECLARE_INDEX(pg_constraint_conrelid_index, 2665, on pg_constraint using btree(conrelid oid_ops));
-#define ConstraintRelidIndexId 2665
+DECLARE_UNIQUE_INDEX(pg_constraint_conrelid_contypid_conname_index, 2665, on pg_constraint using btree(conrelid oid_ops, contypid oid_ops, conname name_ops));
+#define ConstraintRelidTypidNameIndexId 2665
DECLARE_INDEX(pg_constraint_contypid_index, 2666, on pg_constraint using btree(contypid oid_ops));
#define ConstraintTypidIndexId 2666
DECLARE_UNIQUE_INDEX(pg_constraint_oid_index, 2667, on pg_constraint using btree(oid oid_ops));
index 9d209c9d1900919c3edd65e46e0562d58718e0c2..66b3f13f74abf3b91d9c3174538a4b170a98d073 100644 (file)
* global lock to generate a globally unique name for a nameless
* constraint. We associate a namespace with constraint names only for
* SQL-spec compatibility.
+ *
+ * However, we do require conname to be unique among the constraints of a
+ * single relation or domain. This is enforced by a unique index on
+ * conrelid + contypid + conname.
*/
NameData conname; /* name of this constraint */
Oid connamespace; /* OID of namespace containing constraint */
extern void RenameConstraintById(Oid conId, const char *newname);
extern bool ConstraintNameIsUsed(ConstraintCategory conCat, Oid objId,
- Oid objNamespace, const char *conname);
+ const char *conname);
+extern bool ConstraintNameExists(const char *conname, Oid namespaceid);
extern char *ChooseConstraintName(const char *name1, const char *name2,
const char *label, Oid namespaceid,
List *others);
index 6b837236d4d25c2184b715868d85d0603e448d5b..1d05a4bcdc88e32e5cb2d39fe61e0f4ef3000abf 100644 (file)
extern char *makeObjectName(const char *name1, const char *name2,
const char *label);
extern char *ChooseRelationName(const char *name1, const char *name2,
- const char *label, Oid namespaceid);
+ const char *label, Oid namespaceid,
+ bool isconstraint);
extern bool CheckIndexCompatible(Oid oldId,
const char *accessMethodName,
List *attributeList,
index 37440f6aec442895234610482a6fb0eb3f67f087..d9badd62cb811d18d7ec393fed6c9ea1e2855ed2 100644 (file)
DROP TABLE alter2.tt8;
DROP SCHEMA alter2;
+--
+-- Check conflicts between index and CHECK constraint names
+--
+CREATE TABLE tt9(c integer);
+ALTER TABLE tt9 ADD CHECK(c > 1);
+ALTER TABLE tt9 ADD CHECK(c > 2); -- picks nonconflicting name
+ALTER TABLE tt9 ADD CONSTRAINT foo CHECK(c > 3);
+ALTER TABLE tt9 ADD CONSTRAINT foo CHECK(c > 4); -- fail, dup name
+ERROR: constraint "foo" for relation "tt9" already exists
+ALTER TABLE tt9 ADD UNIQUE(c);
+ALTER TABLE tt9 ADD UNIQUE(c); -- picks nonconflicting name
+ALTER TABLE tt9 ADD CONSTRAINT tt9_c_key UNIQUE(c); -- fail, dup name
+ERROR: relation "tt9_c_key" already exists
+ALTER TABLE tt9 ADD CONSTRAINT foo UNIQUE(c); -- fail, dup name
+ERROR: constraint "foo" for relation "tt9" already exists
+ALTER TABLE tt9 ADD CONSTRAINT tt9_c_key CHECK(c > 5); -- fail, dup name
+ERROR: constraint "tt9_c_key" for relation "tt9" already exists
+ALTER TABLE tt9 ADD CONSTRAINT tt9_c_key2 CHECK(c > 6);
+ALTER TABLE tt9 ADD UNIQUE(c); -- picks nonconflicting name
+\d tt9
+ Table "public.tt9"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ c | integer | | |
+Indexes:
+ "tt9_c_key" UNIQUE CONSTRAINT, btree (c)
+ "tt9_c_key1" UNIQUE CONSTRAINT, btree (c)
+ "tt9_c_key3" UNIQUE CONSTRAINT, btree (c)
+Check constraints:
+ "foo" CHECK (c > 3)
+ "tt9_c_check" CHECK (c > 1)
+ "tt9_c_check1" CHECK (c > 2)
+ "tt9_c_key2" CHECK (c > 6)
+
+DROP TABLE tt9;
-- Check that comments on constraints and indexes are not lost at ALTER TABLE.
CREATE TABLE comment_test (
id int,
index 22cf4ef0a764481d4ef36c47f34fcf2e0d2c25f6..b90497804b0b1a35dff1dd52ebc4f1a9a4e172a0 100644 (file)
DROP TABLE alter2.tt8;
DROP SCHEMA alter2;
+--
+-- Check conflicts between index and CHECK constraint names
+--
+CREATE TABLE tt9(c integer);
+ALTER TABLE tt9 ADD CHECK(c > 1);
+ALTER TABLE tt9 ADD CHECK(c > 2); -- picks nonconflicting name
+ALTER TABLE tt9 ADD CONSTRAINT foo CHECK(c > 3);
+ALTER TABLE tt9 ADD CONSTRAINT foo CHECK(c > 4); -- fail, dup name
+ALTER TABLE tt9 ADD UNIQUE(c);
+ALTER TABLE tt9 ADD UNIQUE(c); -- picks nonconflicting name
+ALTER TABLE tt9 ADD CONSTRAINT tt9_c_key UNIQUE(c); -- fail, dup name
+ALTER TABLE tt9 ADD CONSTRAINT foo UNIQUE(c); -- fail, dup name
+ALTER TABLE tt9 ADD CONSTRAINT tt9_c_key CHECK(c > 5); -- fail, dup name
+ALTER TABLE tt9 ADD CONSTRAINT tt9_c_key2 CHECK(c > 6);
+ALTER TABLE tt9 ADD UNIQUE(c); -- picks nonconflicting name
+\d tt9
+DROP TABLE tt9;
+
-- Check that comments on constraints and indexes are not lost at ALTER TABLE.
CREATE TABLE comment_test (