Hi
I propose the appended patch for the following TODO-items:
o %Allow ALTER TABLE ... ALTER CONSTRAINT ... RENAME
o Have ALTER INDEX update the name of a constraint using that index
o Add ALTER TABLE RENAME CONSTRAINT, update index name also
Joachim
diff -ur cvs/pgsql/doc/src/sgml/ddl.sgml cvs.build/pgsql/doc/src/sgml/ddl.sgml
--- cvs/pgsql/doc/src/sgml/ddl.sgml 2005-11-20 13:42:45.000000000 +0100
+++ cvs.build/pgsql/doc/src/sgml/ddl.sgml 2005-12-05 08:38:08.000000000
+0100
@@ -543,6 +543,10 @@
price numeric
);
</programlisting>
+ Since <productname>PostgreSQL</productname> implements a UNIQUE constraint
by
+ means of an index, the above command will also create an index with the same
+ name as the constraint. If you later on change the name of one of those, the
+ name of the corresponding object will be changed automatically as well.
</para>
<indexterm>
diff -ur cvs/pgsql/doc/src/sgml/ref/alter_index.sgml
cvs.build/pgsql/doc/src/sgml/ref/alter_index.sgml
--- cvs/pgsql/doc/src/sgml/ref/alter_index.sgml 2005-08-28 23:04:33.000000000
+0200
+++ cvs.build/pgsql/doc/src/sgml/ref/alter_index.sgml 2005-12-05
08:47:09.000000000 +0100
@@ -108,6 +108,15 @@
</para>
<para>
+ Indexes are also used internally by constraints, namely by UNIQUE and
+ PRIMARY KEY constraints. If you rename an index that is used internally by
+ a constraint of that type, this constraint will implicitly be renamed as
+ well. On the other hand, if you rename such a constraint, it will
+ implicitly rename its corresponding index such that both objects always
+ have the same name.
+ </para>
+
+ <para>
There was formerly an <command>ALTER INDEX OWNER</> variant, but
this is now ignored (with a warning). An index cannot have an owner
different from its table's owner. Changing the table's owner
diff -ur cvs/pgsql/doc/src/sgml/ref/alter_table.sgml
cvs.build/pgsql/doc/src/sgml/ref/alter_table.sgml
--- cvs/pgsql/doc/src/sgml/ref/alter_table.sgml 2005-08-28 23:04:33.000000000
+0200
+++ cvs.build/pgsql/doc/src/sgml/ref/alter_table.sgml 2005-12-05
08:38:08.000000000 +0100
@@ -24,6 +24,8 @@
<replaceable class="PARAMETER">action</replaceable> [, ... ]
ALTER TABLE [ ONLY ] <replaceable class="PARAMETER">name</replaceable> [ * ]
RENAME [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> TO
<replaceable class="PARAMETER">new_column</replaceable>
+ALTER TABLE [ ONLY ] <replaceable class="PARAMETER">name</replaceable> [ * ]
+ ALTER CONSTRAINT <replaceable
class="PARAMETER">constraint_name</replaceable> RENAME TO <replaceable
class="PARAMETER">new_constraint_name</replaceable>
ALTER TABLE <replaceable class="PARAMETER">name</replaceable>
RENAME TO <replaceable class="PARAMETER">new_name</replaceable>
ALTER TABLE <replaceable class="PARAMETER">name</replaceable>
@@ -170,6 +172,18 @@
</varlistentry>
<varlistentry>
+ <term><literal>ALTER CONSTRAINT <replaceable
class="PARAMETER">constraint_name</replaceable> RENAME TO <replaceable
class="PARAMETER">new_constraint_name</replaceable></literal></term>
+ <listitem>
+ <para>
+ This form renames a constraint that is defined on the table. Note that if
+ a constraint is using an index internally (<literal>UNIQUE</> or
+ <literal>PRIMARY KEY</> constraints), the corresponding index will be
+ renamed as well.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><literal>ADD <replaceable
class="PARAMETER">table_constraint</replaceable></literal></term>
<listitem>
<para>
diff -ur cvs/pgsql/src/backend/catalog/pg_constraint.c
cvs.build/pgsql/src/backend/catalog/pg_constraint.c
--- cvs/pgsql/src/backend/catalog/pg_constraint.c 2005-12-01
22:38:13.000000000 +0100
+++ cvs.build/pgsql/src/backend/catalog/pg_constraint.c 2005-12-05
08:56:44.000000000 +0100
@@ -664,3 +664,190 @@
heap_close(conRel, RowExclusiveLock);
}
+
+
+/*
+ * RenameConstraint
+ * Rename a single constraint record
+ * conId: The OID of the constraint to rename
+ * newName: The new name of the constraint
+ * implicitRename: is this an implicit rename? If so, we will issue
+ * a notice about the implicit rename
+ * cmdName: the command that triggered the rename for the
"implicitly
+ * renames" notice message
+ */
+void
+RenameConstraint(Oid conId, const char* newName,
+ bool implicitRename, const char* cmdName)
+{
+ Relation conRel;
+ ScanKeyData key[1];
+ SysScanDesc scan;
+ HeapTuple tup;
+ NameData newNameData;
+ Relation rel;
+ Oid relId;
+ Oid nspOid;
+
+ /* before reading the tuple, lock the table it constraints in
+ * AccessExclusiveLock mode. Otherwise, if we read it before locking
this
+ * table, the tuple might be changed by another transaction and our copy
+ * would be out of date
+ */
+ relId = GetConstraintRelationId(conId);
+ if (!OidIsValid(relId))
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("constraint with OID %d does not
exist", conId)));
+ }
+
+ rel = relation_open(relId, AccessExclusiveLock);
+ nspOid = get_rel_namespace(relId);
+
+ conRel = heap_open(ConstraintRelationId, RowExclusiveLock);
+
+ ScanKeyInit(&key[0],
+ ObjectIdAttributeNumber,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(conId));
+
+ scan = systable_beginscan(conRel, ConstraintOidIndexId, true,
+ SnapshotNow, 1, key);
+ if (!HeapTupleIsValid((tup = systable_getnext(scan))))
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("constraint with OID %d does not
exist", conId)));
+ }
+
+ Form_pg_constraint conform = (Form_pg_constraint) GETSTRUCT(tup);
+
+ if (ConstraintNameIsUsed(CONSTRAINT_RELATION,
+ conform->conrelid,
+
get_rel_namespace(conform->conrelid),
+ newName))
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_DUPLICATE_OBJECT),
+ errmsg("constraint \"%s\" for relation \"%s\"
already exists",
+ newName,
+ RelationGetRelationName(rel))));
+ }
+ tup = heap_copytuple(tup);
+ conform = (Form_pg_constraint) GETSTRUCT(tup);
+
+ if (implicitRename && cmdName)
+ {
+ ereport(NOTICE,
+ (errmsg("%s will implicitly rename constraint "
+ "\"%s\" to \"%s\" on table
\"%s.%s\"",
+ cmdName,
+ NameStr(conform->conname),
+ newName,
+ get_namespace_name(nspOid),
+ RelationGetRelationName(rel))));
+ }
+
+ namestrcpy(&newNameData, newName);
+ conform->conname = newNameData;
+
+ simple_heap_update(conRel, &tup->t_self, tup);
+ CatalogUpdateIndexes(conRel, tup);
+ heap_freetuple(tup);
+
+ systable_endscan(scan);
+ heap_close(conRel, RowExclusiveLock);
+
+ /* close relation but hold lock until end of transaction */
+ relation_close(rel, NoLock);
+}
+
+
+/* GetRelationConstraintOid
+ *
+ * Get the contraint OID by the relation Id of the relation it constraints and
+ * this relations' name. We need this function in order to rename a constraint.
+ * This is done via "ALTER TABLE ... ALTER CONSTRAINT name" and the parser
+ * gives us the relation this constraint is defined on as well as the
+ * constraint's name.
+ *
+ * The function returns:
+ *
+ * - the unique OID of the constraint if the constraint could be found
+ * - the invalid OID if the constraint was not found
+ *
+ */
+Oid GetRelationConstraintOid(Oid relId, const char* name)
+{
+ Relation conRel;
+ ScanKeyData key[1];
+ SysScanDesc scan;
+ HeapTuple tup;
+ Oid conId = InvalidOid;
+
+ /* we don't change data, so an AccessShareLock is enough */
+ conRel = heap_open(ConstraintRelationId, AccessShareLock);
+
+ ScanKeyInit(&key[0],
+ Anum_pg_constraint_conrelid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(relId));
+
+ scan = systable_beginscan(conRel, ConstraintRelidIndexId, true,
+ SnapshotNow, 1, key);
+
+ while (HeapTupleIsValid((tup = systable_getnext(scan))))
+ {
+ Form_pg_constraint con = (Form_pg_constraint) GETSTRUCT(tup);
+ if (pg_strcasecmp(name, NameStr(con->conname)) == 0)
+ {
+ conId = HeapTupleGetOid(tup);
+ Assert(OidIsValid(conId));
+ }
+ }
+
+ systable_endscan(scan);
+ heap_close(conRel, AccessShareLock);
+
+ return conId;
+}
+
+
+/* GetConstraintRelationId
+ *
+ * Gets the OID of the relation where the constraint is defined on or the
+ * invalid OID if the constraint cannot be found.
+ */
+Oid GetConstraintRelationId(Oid conId)
+{
+ Relation conRel;
+ ScanKeyData key[1];
+ SysScanDesc scan;
+ HeapTuple tup;
+ Oid relId = InvalidOid;
+
+ /* we don't change data, so an AccessShareLock is enough */
+ conRel = heap_open(ConstraintRelationId, AccessShareLock);
+
+ ScanKeyInit(&key[0],
+ ObjectIdAttributeNumber,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(conId));
+
+ scan = systable_beginscan(conRel, ConstraintOidIndexId, true,
+ SnapshotNow, 1, key);
+
+ if (HeapTupleIsValid((tup = systable_getnext(scan))))
+ {
+ Form_pg_constraint con = (Form_pg_constraint) GETSTRUCT(tup);
+ relId = con->conrelid;
+ Assert(OidIsValid(relId));
+ }
+
+ systable_endscan(scan);
+ heap_close(conRel, AccessShareLock);
+
+ return relId;
+}
+
diff -ur cvs/pgsql/src/backend/catalog/pg_depend.c
cvs.build/pgsql/src/backend/catalog/pg_depend.c
--- cvs/pgsql/src/backend/catalog/pg_depend.c 2005-12-01 22:38:14.000000000
+0100
+++ cvs.build/pgsql/src/backend/catalog/pg_depend.c 2005-12-05
08:58:51.000000000 +0100
@@ -1,4 +1,4 @@
-/*-------------------------------------------------------------------------
+/*-------------------------------------------------------------------------/
*
* pg_depend.c
* routines to support manipulation of the pg_depend relation
@@ -361,3 +361,110 @@
return ret;
}
+
+List* getReferencingOids(Oid refClassId, Oid refObjId, Oid refObjSubId,
+ Oid classId, DependencyType
deptype)
+{
+ ScanKeyData key[3];
+ SysScanDesc scan;
+ HeapTuple tup;
+ Relation depRel;
+ List *list = NIL;
+
+ depRel = heap_open(DependRelationId, AccessShareLock);
+
+ ScanKeyInit(&key[0],
+ Anum_pg_depend_refclassid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(refClassId));
+
+ ScanKeyInit(&key[1],
+ Anum_pg_depend_refobjid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(refObjId));
+
+ ScanKeyInit(&key[2],
+ Anum_pg_depend_refobjsubid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(refObjSubId));
+
+ scan = systable_beginscan(depRel, DependReferenceIndexId, true,
+ SnapshotNow, 3, key);
+
+ while (HeapTupleIsValid(tup = systable_getnext(scan)))
+ {
+ Form_pg_depend depForm = (Form_pg_depend) GETSTRUCT(tup);
+
+ /* check if the class id is what we want */
+ if (depForm->classid != classId)
+ {
+ continue;
+ }
+
+ /* check if the DependencyType is what we want */
+ if (depForm->deptype != deptype)
+ {
+ continue;
+ }
+
+ /* if we are still here, we have found a match */
+ list = lcons_oid(depForm->objid, list);
+ break;
+ }
+ systable_endscan(scan);
+
+ heap_close(depRel, AccessShareLock);
+ return list;
+}
+
+
+List* getDependentOids(Oid classId, Oid objId,
+ Oid refClassId, DependencyType
deptype)
+{
+ ScanKeyData key[2];
+ SysScanDesc scan;
+ HeapTuple tup;
+ Relation depRel;
+ List *list = NIL;
+
+ depRel = heap_open(DependRelationId, AccessShareLock);
+
+ ScanKeyInit(&key[0],
+ Anum_pg_depend_classid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(classId));
+
+ ScanKeyInit(&key[1],
+ Anum_pg_depend_objid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(objId));
+
+ scan = systable_beginscan(depRel, DependDependerIndexId, true,
+ SnapshotNow, 2, key);
+
+ while (HeapTupleIsValid(tup = systable_getnext(scan)))
+ {
+ Form_pg_depend depForm = (Form_pg_depend) GETSTRUCT(tup);
+
+ /* check if the DependencyType is what we want */
+ if (depForm->deptype != deptype)
+ {
+ continue;
+ }
+
+ /* check if the referenced class id is what we want */
+ if (depForm->refclassid != refClassId)
+ {
+ continue;
+ }
+
+ /* if we are still here, we have found a match */
+ list = lcons_oid(depForm->refobjid, list);
+ break;
+ }
+ systable_endscan(scan);
+
+ heap_close(depRel, AccessShareLock);
+ return list;
+}
+
diff -ur cvs/pgsql/src/backend/commands/alter.c
cvs.build/pgsql/src/backend/commands/alter.c
--- cvs/pgsql/src/backend/commands/alter.c 2005-11-20 13:42:57.000000000
+0100
+++ cvs.build/pgsql/src/backend/commands/alter.c 2005-12-05
09:04:25.000000000 +0100
@@ -16,8 +16,10 @@
#include "access/htup.h"
#include "catalog/catalog.h"
+#include "catalog/dependency.h"
#include "catalog/namespace.h"
#include "catalog/pg_class.h"
+#include "catalog/pg_constraint.h"
#include "commands/alter.h"
#include "commands/conversioncmds.h"
#include "commands/dbcommands.h"
@@ -88,6 +90,7 @@
case OBJECT_INDEX:
case OBJECT_COLUMN:
case OBJECT_TRIGGER:
+ case OBJECT_CONSTRAINT:
{
Oid relid;
@@ -115,6 +118,30 @@
aclcheck_error(aclresult, ACL_KIND_NAMESPACE,
get_namespace_name(namespaceId));
+ /* do NOT refer to
stmt->renameType here because
+ * you can also rename
an index with ALTER TABLE */
+ if
(get_rel_relkind(relid) == RELKIND_INDEX) {
+ /* see if we
depend on a constraint */
+ List* depOids =
getDependentOids(
+
RelationRelationId,
+
relid,
+
ConstraintRelationId,
+
DEPENDENCY_INTERNAL);
+ /* there should
only be one constraint */
+
Assert(list_length(depOids) <= 1);
+ if
(list_length(depOids) == 1)
+ {
+
Oid conRelId = linitial_oid(depOids);
+
/* apply the same name to the
+
* constraint and tell it that this
+
* is an implicit rename triggered
+
* by an "ALTER INDEX" command */
+
RenameConstraint(conRelId,
+
stmt->newname,
+
true,
+
"ALTER INDEX");
+ }
+ }
renamerel(relid,
stmt->newname);
break;
}
@@ -130,6 +157,53 @@
stmt->subname, /* old att name */
stmt->newname); /* new att name */
break;
+ case OBJECT_CONSTRAINT:
+ /* XXX could do extra function
renameconstr() - but I
+ * don't know where it should
go */
+ /* renameconstr(relid,
+
stmt->subname,
+
stmt->newname); */
+ {
+ List
*depRelOids;
+ ListCell *l;
+ Oid conId =
+
GetRelationConstraintOid(relid,
+
stmt->subname);
+ if (!OidIsValid(conId))
{
+ ereport(ERROR,
+
(errcode(ERRCODE_UNDEFINED_OBJECT),
+
errmsg("constraint with name \"%s\" "
+
"does not exist",
+
stmt->subname)));
+ }
+ RenameConstraint(conId,
stmt->newname,
+
false, NULL);
+ depRelOids =
getReferencingOids(
+
ConstraintRelationId,
+
conId,
+
0,
+
RelationRelationId,
+
DEPENDENCY_INTERNAL);
+ foreach(l, depRelOids) {
+ Oid
depRelOid;
+ Oid
nspOid;
+ depRelOid =
lfirst_oid(l);
+ nspOid =
get_rel_namespace(depRelOid);
+ if
(get_rel_relkind(depRelOid) == RELKIND_INDEX)
+ {
+
ereport(NOTICE,
+
(errmsg("ALTER TABLE / CONSTRAINT will implicitly rename index "
+
"\"%s\" to \"%s\" on table \"%s.%s\"",
+
get_rel_name(depRelOid),
+
stmt->newname,
+
get_namespace_name(nspOid),
+
get_rel_name(relid))));
+
renamerel(depRelOid, stmt->newname);
+ }
+ }
+ }
+ break;
+
default:
/* can't happen */ ;
}
diff -ur cvs/pgsql/src/backend/parser/gram.y
cvs.build/pgsql/src/backend/parser/gram.y
--- cvs/pgsql/src/backend/parser/gram.y 2005-12-01 22:38:17.000000000 +0100
+++ cvs.build/pgsql/src/backend/parser/gram.y 2005-12-05 08:38:08.000000000
+0100
@@ -4051,6 +4051,15 @@
n->newname = $8;
$$ = (Node *)n;
}
+ | ALTER TABLE relation_expr ALTER CONSTRAINT name
RENAME TO name
+ {
+ RenameStmt *n = makeNode(RenameStmt);
+ n->renameType = OBJECT_CONSTRAINT;
+ n->relation = $3;
+ n->subname = $6;
+ n->newname = $9;
+ $$ = (Node *)n;
+ }
| ALTER TRIGGER name ON relation_expr RENAME TO name
{
RenameStmt *n = makeNode(RenameStmt);
diff -ur cvs/pgsql/src/backend/tcop/utility.c
cvs.build/pgsql/src/backend/tcop/utility.c
--- cvs/pgsql/src/backend/tcop/utility.c 2005-12-01 22:38:18.000000000
+0100
+++ cvs.build/pgsql/src/backend/tcop/utility.c 2005-12-05 08:38:08.000000000
+0100
@@ -1406,6 +1406,7 @@
case OBJECT_SCHEMA:
tag = "ALTER SCHEMA";
break;
+ case OBJECT_CONSTRAINT:
case OBJECT_COLUMN:
case OBJECT_TABLE:
tag = "ALTER TABLE";
diff -ur cvs/pgsql/src/include/catalog/dependency.h
cvs.build/pgsql/src/include/catalog/dependency.h
--- cvs/pgsql/src/include/catalog/dependency.h 2005-12-01 22:38:28.000000000
+0100
+++ cvs.build/pgsql/src/include/catalog/dependency.h 2005-12-05
08:39:36.000000000 +0100
@@ -179,6 +179,12 @@
extern bool objectIsInternalDependency(Oid classId, Oid objectId);
+extern List* getDependentOids(Oid classId, Oid objId,
+ Oid refClassId,
DependencyType deptype);
+
+extern List* getReferencingOids(Oid refClassId, Oid refObjId, Oid refObjSubId,
+ Oid classId,
DependencyType deptype);
+
/* in pg_shdepend.c */
extern void recordSharedDependencyOn(ObjectAddress *depender,
diff -ur cvs/pgsql/src/include/catalog/pg_constraint.h
cvs.build/pgsql/src/include/catalog/pg_constraint.h
--- cvs/pgsql/src/include/catalog/pg_constraint.h 2005-12-01
22:38:29.000000000 +0100
+++ cvs.build/pgsql/src/include/catalog/pg_constraint.h 2005-12-05
08:41:20.000000000 +0100
@@ -187,4 +187,10 @@
extern void AlterConstraintNamespaces(Oid ownerId, Oid oldNspId,
Oid newNspId, bool isType);
+extern void RenameConstraint(Oid conId, const char* newName,
+ bool implicitRename, const
char* cmdName);
+
+extern Oid GetRelationConstraintOid(Oid relId, const char* name);
+extern Oid GetConstraintRelationId(Oid conId);
+
#endif /* PG_CONSTRAINT_H */
diff -ur cvs/pgsql/src/test/regress/expected/alter_table.out
cvs.build/pgsql/src/test/regress/expected/alter_table.out
--- cvs/pgsql/src/test/regress/expected/alter_table.out 2005-08-22
10:47:06.000000000 +0200
+++ cvs.build/pgsql/src/test/regress/expected/alter_table.out 2005-12-05
08:38:08.000000000 +0100
@@ -159,6 +159,10 @@
CREATE TABLE tmp4 (a int, b int, unique(a,b));
NOTICE: CREATE TABLE / UNIQUE will create implicit index "tmp4_a_key" for
table "tmp4"
CREATE TABLE tmp5 (a int, b int);
+-- creates implicit index tmp6_a_key
+CREATE TABLE tmp6 (a int, b int, unique(a));
+NOTICE: CREATE TABLE / UNIQUE will create implicit index "tmp6_a_key" for
table "tmp6"
+CREATE INDEX tmp6_b_key ON tmp6(b);
-- Insert rows into tmp2 (pktable)
INSERT INTO tmp2 values (1);
INSERT INTO tmp2 values (2);
@@ -186,6 +190,22 @@
-- tmp4 is a,b
ALTER TABLE tmp5 add constraint tmpconstr foreign key(a) references tmp4(a)
match full;
ERROR: there is no unique constraint matching given keys for referenced table
"tmp4"
+-- check if constraint and index name stay in sync if we rename one or the
other
+-- fail here
+ALTER TABLE tmp6 ALTER CONSTRAINT tmp6_a_key RENAME TO tmp6_b_key;
+NOTICE: ALTER TABLE / CONSTRAINT will implicitly rename index "tmp6_a_key" to
"tmp6_b_key" on table "public.tmp6"
+ERROR: relation "tmp6_b_key" already exists
+-- succeed
+ALTER TABLE tmp6 ALTER CONSTRAINT tmp6_a_key RENAME TO tmp6_c_key;
+NOTICE: ALTER TABLE / CONSTRAINT will implicitly rename index "tmp6_a_key" to
"tmp6_c_key" on table "public.tmp6"
+-- Now rename the index (this fails)
+ALTER INDEX tmp6_c_key RENAME TO tmp6_b_key;
+NOTICE: ALTER INDEX will implicitly rename constraint "tmp6_c_key" to
"tmp6_b_key" on table "public.tmp6"
+ERROR: relation "tmp6_b_key" already exists
+-- this succeeds and uses ALTER TABLE syntax to rename an INDEX
+ALTER TABLE tmp6_c_key RENAME TO tmp6_a_key;
+NOTICE: ALTER INDEX will implicitly rename constraint "tmp6_c_key" to
"tmp6_a_key" on table "public.tmp6"
+DROP TABLE tmp6;
DROP TABLE tmp5;
DROP TABLE tmp4;
DROP TABLE tmp3;
diff -ur cvs/pgsql/src/test/regress/sql/alter_table.sql
cvs.build/pgsql/src/test/regress/sql/alter_table.sql
--- cvs/pgsql/src/test/regress/sql/alter_table.sql 2005-08-22
10:47:08.000000000 +0200
+++ cvs.build/pgsql/src/test/regress/sql/alter_table.sql 2005-12-05
08:38:08.000000000 +0100
@@ -196,6 +196,10 @@
CREATE TABLE tmp5 (a int, b int);
+-- creates implicit index tmp6_a_key
+CREATE TABLE tmp6 (a int, b int, unique(a));
+CREATE INDEX tmp6_b_key ON tmp6(b);
+
-- Insert rows into tmp2 (pktable)
INSERT INTO tmp2 values (1);
INSERT INTO tmp2 values (2);
@@ -227,6 +231,21 @@
ALTER TABLE tmp5 add constraint tmpconstr foreign key(a) references tmp4(a)
match full;
+-- check if constraint and index name stay in sync if we rename one or the
other
+-- fail here
+ALTER TABLE tmp6 ALTER CONSTRAINT tmp6_a_key RENAME TO tmp6_b_key;
+
+-- succeed
+ALTER TABLE tmp6 ALTER CONSTRAINT tmp6_a_key RENAME TO tmp6_c_key;
+
+-- Now rename the index (this fails)
+ALTER INDEX tmp6_c_key RENAME TO tmp6_b_key;
+
+-- this succeeds and uses ALTER TABLE syntax to rename an INDEX
+ALTER TABLE tmp6_c_key RENAME TO tmp6_a_key;
+
+DROP TABLE tmp6;
+
DROP TABLE tmp5;
DROP TABLE tmp4;
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
