Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Tom Lane wrote:
> >> This patch appears seriously broken, in particular every routine I
> >> looked at contained incorrect locking assumptions. Nor do I care
> >> for using pg_depend for the purposes it's being used for here.
>
> > OK, how do we proceed? Revert or apply a second patch?
>
> I'd say revert; the patch is going to need significant rework.
OK, patch reverted. Updated version attached. Please adjust and
resubmit. Thanks.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
Index: doc/src/sgml/ddl.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/ddl.sgml,v
retrieving revision 1.52
retrieving revision 1.53
diff -c -r1.52 -r1.53
*** doc/src/sgml/ddl.sgml 4 Feb 2006 23:03:19 -0000 1.52
--- doc/src/sgml/ddl.sgml 11 Feb 2006 22:17:18 -0000 1.53
***************
*** 543,548 ****
--- 543,552 ----
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>
Index: doc/src/sgml/ref/alter_index.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/alter_index.sgml,v
retrieving revision 1.6
retrieving revision 1.7
diff -c -r1.6 -r1.7
*** doc/src/sgml/ref/alter_index.sgml 22 Aug 2005 19:39:52 -0000 1.6
--- doc/src/sgml/ref/alter_index.sgml 11 Feb 2006 22:17:18 -0000 1.7
***************
*** 108,113 ****
--- 108,122 ----
</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
Index: doc/src/sgml/ref/alter_table.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/alter_table.sgml,v
retrieving revision 1.82
retrieving revision 1.83
diff -c -r1.82 -r1.83
*** doc/src/sgml/ref/alter_table.sgml 8 Dec 2005 21:35:36 -0000 1.82
--- doc/src/sgml/ref/alter_table.sgml 11 Feb 2006 22:17:18 -0000 1.83
***************
*** 24,29 ****
--- 24,31 ----
<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,175 ****
--- 172,189 ----
</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>
Index: src/backend/catalog/pg_constraint.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/catalog/pg_constraint.c,v
retrieving revision 1.28
retrieving revision 1.29
diff -c -r1.28 -r1.29
*** src/backend/catalog/pg_constraint.c 22 Nov 2005 18:17:08 -0000 1.28
--- src/backend/catalog/pg_constraint.c 11 Feb 2006 22:17:18 -0000 1.29
***************
*** 664,666 ****
--- 664,854 ----
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;
+ Form_pg_constraint conform;
+
+ /* 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)));
+ }
+
+ 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;
+ }
+
Index: src/backend/catalog/pg_depend.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/catalog/pg_depend.c,v
retrieving revision 1.17
retrieving revision 1.18
diff -c -r1.17 -r1.18
*** src/backend/catalog/pg_depend.c 22 Nov 2005 18:17:08 -0000 1.17
--- src/backend/catalog/pg_depend.c 11 Feb 2006 22:17:18 -0000 1.18
***************
*** 361,363 ****
--- 361,462 ----
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;
+ }
+
Index: src/backend/commands/alter.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/commands/alter.c,v
retrieving revision 1.15
retrieving revision 1.16
diff -c -r1.15 -r1.16
*** src/backend/commands/alter.c 15 Oct 2005 02:49:14 -0000 1.15
--- src/backend/commands/alter.c 11 Feb 2006 22:17:18 -0000 1.16
***************
*** 16,23 ****
--- 16,25 ----
#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,93 ****
--- 90,96 ----
case OBJECT_INDEX:
case OBJECT_COLUMN:
case OBJECT_TRIGGER:
+ case OBJECT_CONSTRAINT:
{
Oid relid;
***************
*** 109,120 ****
AclResult
aclresult;
aclresult =
pg_namespace_aclcheck(namespaceId,
!
GetUserId(),
!
ACL_CREATE);
if (aclresult !=
ACLCHECK_OK)
aclcheck_error(aclresult, ACL_KIND_NAMESPACE,
get_namespace_name(namespaceId));
renamerel(relid,
stmt->newname);
break;
}
--- 112,149 ----
AclResult
aclresult;
aclresult =
pg_namespace_aclcheck(namespaceId,
!
GetUserId(), ACL_CREATE);
if (aclresult !=
ACLCHECK_OK)
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,135 ****
--- 159,210 ----
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 */ ;
}
Index: src/backend/parser/gram.y
===================================================================
RCS file: /cvsroot/pgsql/src/backend/parser/gram.y,v
retrieving revision 2.526
retrieving revision 2.527
diff -c -r2.526 -r2.527
*** src/backend/parser/gram.y 4 Feb 2006 19:06:46 -0000 2.526
--- src/backend/parser/gram.y 11 Feb 2006 22:17:18 -0000 2.527
***************
*** 4096,4101 ****
--- 4096,4110 ----
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);
Index: src/backend/tcop/utility.c
===================================================================
RCS file: /cvsroot/pgsql/src/backend/tcop/utility.c,v
retrieving revision 1.250
retrieving revision 1.251
diff -c -r1.250 -r1.251
*** src/backend/tcop/utility.c 29 Nov 2005 01:25:49 -0000 1.250
--- src/backend/tcop/utility.c 11 Feb 2006 22:17:19 -0000 1.251
***************
*** 1406,1411 ****
--- 1406,1412 ----
case OBJECT_SCHEMA:
tag = "ALTER SCHEMA";
break;
+ case OBJECT_CONSTRAINT:
case OBJECT_COLUMN:
case OBJECT_TABLE:
tag = "ALTER TABLE";
Index: src/include/catalog/dependency.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/catalog/dependency.h,v
retrieving revision 1.18
retrieving revision 1.19
diff -c -r1.18 -r1.19
*** src/include/catalog/dependency.h 21 Nov 2005 12:49:32 -0000 1.18
--- src/include/catalog/dependency.h 11 Feb 2006 22:17:19 -0000 1.19
***************
*** 179,184 ****
--- 179,190 ----
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,
Index: src/include/catalog/pg_constraint.h
===================================================================
RCS file: /cvsroot/pgsql/src/include/catalog/pg_constraint.h,v
retrieving revision 1.19
retrieving revision 1.20
diff -c -r1.19 -r1.20
*** src/include/catalog/pg_constraint.h 22 Nov 2005 18:17:30 -0000 1.19
--- src/include/catalog/pg_constraint.h 11 Feb 2006 22:17:19 -0000 1.20
***************
*** 187,190 ****
--- 187,196 ----
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 */
Index: src/test/regress/expected/alter_table.out
===================================================================
RCS file: /cvsroot/pgsql/src/test/regress/expected/alter_table.out,v
retrieving revision 1.90
retrieving revision 1.91
diff -c -r1.90 -r1.91
*** src/test/regress/expected/alter_table.out 10 Feb 2006 00:39:04 -0000
1.90
--- src/test/regress/expected/alter_table.out 11 Feb 2006 22:17:19 -0000
1.91
***************
*** 159,164 ****
--- 159,168 ----
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,191 ****
--- 190,211 ----
-- 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;
Index: src/test/regress/sql/alter_table.sql
===================================================================
RCS file: /cvsroot/pgsql/src/test/regress/sql/alter_table.sql,v
retrieving revision 1.52
retrieving revision 1.53
diff -c -r1.52 -r1.53
*** src/test/regress/sql/alter_table.sql 1 Aug 2005 04:03:59 -0000
1.52
--- src/test/regress/sql/alter_table.sql 11 Feb 2006 22:17:19 -0000
1.53
***************
*** 196,201 ****
--- 196,205 ----
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,232 ****
--- 231,251 ----
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 6: explain analyze is your friend
