On Wed, Dec 07, 2005 at 09:54:44PM +0000, Simon Riggs wrote:
> On Mon, 2005-12-05 at 10:24 +0100, Joachim Wieland wrote:

> >         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


> My compiler complains:
> pg_constraint.c: In function ???RenameConstraint???:
> pg_constraint.c:726: warning: ISO C90 forbids mixed declarations and
> code

Sorry, that's some leftover from code rearrangements. I attach a new version
of the patch, this time in the requested context-diff format which I forgot
to create the other day.


> This probably allows you to rename an inherited constraint to another
> name. Not sure if that is a problem, but it probably ought to throw an
> error, but I'm not sure who would care.

I thought about that but since the constraint gets copied anyway and you
have two (more or less) independent constraints afterwards I didn't see a
reason why it should be forbidden to rename it. One could throw a warning at
least but I don't remember many cases where postgresql issues a warning
saying: "hey, what you're doing might be bad but I'll do it anyway". If
the consensus on this one however is to forbid it or issue a warning at
least, it would be no problem to detect this situation once your inherited
constraint patch is in.


> I'll test some more to see if my work on inherited constraints conflicts
> in any way.

Ok, thanks.


Joachim

diff -cr 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-08 13:37:19.000000000 
+0100
***************
*** 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>
diff -cr 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-08 
13:37:19.000000000 +0100
***************
*** 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
diff -cr 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-12-05 15:53:37.000000000 
+0100
--- cvs.build/pgsql/doc/src/sgml/ref/alter_table.sgml   2005-12-08 
13:37:19.000000000 +0100
***************
*** 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>
diff -cr 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-08 
13:37:19.000000000 +0100
***************
*** 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;
+ }
+ 
diff -cr 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-08 
13:37:19.000000000 +0100
***************
*** 361,363 ****
--- 361,470 ----
  
        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 -cr 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-08 
13:37:19.000000000 +0100
***************
*** 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;
  
***************
*** 115,120 ****
--- 118,147 ----
                                                                
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 ****
--- 157,209 ----
                                                                   
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 -cr 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-08 13:37:19.000000000 
+0100
***************
*** 4051,4056 ****
--- 4051,4065 ----
                                        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 -cr 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-08 13:37:19.000000000 
+0100
***************
*** 1406,1411 ****
--- 1406,1412 ----
                                case OBJECT_SCHEMA:
                                        tag = "ALTER SCHEMA";
                                        break;
+                               case OBJECT_CONSTRAINT:
                                case OBJECT_COLUMN:
                                case OBJECT_TABLE:
                                        tag = "ALTER TABLE";
diff -cr 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-08 
13:37:19.000000000 +0100
***************
*** 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,
diff -cr 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-08 
13:37:19.000000000 +0100
***************
*** 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 */
diff -cr 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-08 
13:37:19.000000000 +0100
***************
*** 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;
diff -cr 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-08 
13:37:19.000000000 +0100
***************
*** 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 2: Don't 'kill -9' the postmaster

Reply via email to