Thread: Re: [COMMITTERS] pgsql: Allow ALTER TABLE ...

Re: [COMMITTERS] pgsql: Allow ALTER TABLE ...

From
Bruce Momjian
Date:
Tom Lane wrote:
> momjian@postgresql.org (Bruce Momjian) writes:
> > Log Message:
> > -----------
> > Allow ALTER TABLE ... ALTER CONSTRAINT ... RENAME
>
> 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?

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

Re: [COMMITTERS] pgsql: Allow ALTER TABLE ...

From
Tom Lane
Date:
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.

            regards, tom lane

ALTER CONSTRAINT RENAME patch reverted

From
Bruce Momjian
Date:
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
<replaceableclass="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;

Re: ALTER CONSTRAINT RENAME patch reverted

From
Bruce Momjian
Date:
Where are we on this patch.  It was reverted.  I someone going to clean
it up so we can apply it?

---------------------------------------------------------------------------

Bruce Momjian wrote:
> 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
  SRA OSS, Inc.   http://www.sraoss.com

  + If your life is a hard drive, Christ can be your backup. +