Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commita02f6ce

Browse files
committed
Allow ALTER TABLE ... ALTER CONSTRAINT ... RENAME
Joachim Wieland
1 parent3fcb38f commita02f6ce

File tree

12 files changed

+462
-12
lines changed

12 files changed

+462
-12
lines changed

‎doc/src/sgml/ddl.sgml

Lines changed: 5 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/ddl.sgml,v 1.52 2006/02/04 23:03:19 tgl Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/ddl.sgml,v 1.53 2006/02/11 22:17:18 momjian Exp $ -->
22

33
<chapter id="ddl">
44
<title>Data Definition</title>
@@ -543,6 +543,10 @@ CREATE TABLE products (
543543
price numeric
544544
);
545545
</programlisting>
546+
Since <productname>PostgreSQL</productname> implements a UNIQUE constraint by
547+
means of an index, the above command will also create an index with the same
548+
name as the constraint. If you later on change the name of one of those, the
549+
name of the corresponding object will be changed automatically as well.
546550
</para>
547551

548552
<indexterm>

‎doc/src/sgml/ref/alter_index.sgml

Lines changed: 10 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/alter_index.sgml,v 1.6 2005/08/22 19:39:52 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/alter_index.sgml,v 1.7 2006/02/11 22:17:18 momjian Exp $
33
PostgreSQL documentation
44
-->
55

@@ -107,6 +107,15 @@ ALTER INDEX <replaceable class="PARAMETER">name</replaceable> SET TABLESPACE <re
107107
of <command>ALTER TABLE</> that apply to indexes.
108108
</para>
109109

110+
<para>
111+
Indexes are also used internally by constraints, namely by UNIQUE and
112+
PRIMARY KEY constraints. If you rename an index that is used internally by
113+
a constraint of that type, this constraint will implicitly be renamed as
114+
well. On the other hand, if you rename such a constraint, it will
115+
implicitly rename its corresponding index such that both objects always
116+
have the same name.
117+
</para>
118+
110119
<para>
111120
There was formerly an <command>ALTER INDEX OWNER</> variant, but
112121
this is now ignored (with a warning). An index cannot have an owner

‎doc/src/sgml/ref/alter_table.sgml

Lines changed: 15 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/alter_table.sgml,v 1.82 2005/12/08 21:35:36 momjian Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/alter_table.sgml,v 1.83 2006/02/11 22:17:18 momjian Exp $
33
PostgreSQL documentation
44
-->
55

@@ -24,6 +24,8 @@ ALTER TABLE [ ONLY ] <replaceable class="PARAMETER">name</replaceable> [ * ]
2424
<replaceable class="PARAMETER">action</replaceable> [, ... ]
2525
ALTER TABLE [ ONLY ] <replaceable class="PARAMETER">name</replaceable> [ * ]
2626
RENAME [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> TO <replaceable class="PARAMETER">new_column</replaceable>
27+
ALTER TABLE [ ONLY ] <replaceable class="PARAMETER">name</replaceable> [ * ]
28+
ALTER CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> RENAME TO <replaceable class="PARAMETER">new_constraint_name</replaceable>
2729
ALTER TABLE <replaceable class="PARAMETER">name</replaceable>
2830
RENAME TO <replaceable class="PARAMETER">new_name</replaceable>
2931
ALTER TABLE <replaceable class="PARAMETER">name</replaceable>
@@ -169,6 +171,18 @@ where <replaceable class="PARAMETER">action</replaceable> is one of:
169171
</listitem>
170172
</varlistentry>
171173

174+
<varlistentry>
175+
<term><literal>ALTER CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> RENAME TO <replaceable class="PARAMETER">new_constraint_name</replaceable></literal></term>
176+
<listitem>
177+
<para>
178+
This form renames a constraint that is defined on the table. Note that if
179+
a constraint is using an index internally (<literal>UNIQUE</> or
180+
<literal>PRIMARY KEY</> constraints), the corresponding index will be
181+
renamed as well.
182+
</para>
183+
</listitem>
184+
</varlistentry>
185+
172186
<varlistentry>
173187
<term><literal>ADD <replaceable class="PARAMETER">table_constraint</replaceable></literal></term>
174188
<listitem>

‎src/backend/catalog/pg_constraint.c

Lines changed: 189 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/catalog/pg_constraint.c,v 1.28 2005/11/22 18:17:08 momjian Exp $
11+
* $PostgreSQL: pgsql/src/backend/catalog/pg_constraint.c,v 1.29 2006/02/11 22:17:18 momjian Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -664,3 +664,191 @@ AlterConstraintNamespaces(Oid ownerId, Oid oldNspId,
664664

665665
heap_close(conRel,RowExclusiveLock);
666666
}
667+
668+
669+
/*
670+
* RenameConstraint
671+
*Rename a single constraint record
672+
*conId: The OID of the constraint to rename
673+
*newName: The new name of the constraint
674+
*implicitRename: is this an implicit rename? If so, we will issue
675+
* a notice about the implicit rename
676+
*cmdName: the command that triggered the rename for the "implicitly
677+
* renames" notice message
678+
*/
679+
void
680+
RenameConstraint(OidconId,constchar*newName,
681+
boolimplicitRename,constchar*cmdName)
682+
{
683+
RelationconRel;
684+
ScanKeyDatakey[1];
685+
SysScanDescscan;
686+
HeapTupletup;
687+
NameDatanewNameData;
688+
Relationrel;
689+
OidrelId;
690+
OidnspOid;
691+
Form_pg_constraintconform;
692+
693+
/* before reading the tuple, lock the table it constraints in
694+
* AccessExclusiveLock mode. Otherwise, if we read it before locking this
695+
* table, the tuple might be changed by another transaction and our copy
696+
* would be out of date
697+
*/
698+
relId=GetConstraintRelationId(conId);
699+
if (!OidIsValid(relId))
700+
{
701+
ereport(ERROR,
702+
(errcode(ERRCODE_UNDEFINED_OBJECT),
703+
errmsg("constraint with OID %d does not exist",conId)));
704+
}
705+
706+
rel=relation_open(relId,AccessExclusiveLock);
707+
nspOid=get_rel_namespace(relId);
708+
709+
conRel=heap_open(ConstraintRelationId,RowExclusiveLock);
710+
711+
ScanKeyInit(&key[0],
712+
ObjectIdAttributeNumber,
713+
BTEqualStrategyNumber,F_OIDEQ,
714+
ObjectIdGetDatum(conId));
715+
716+
scan=systable_beginscan(conRel,ConstraintOidIndexId, true,
717+
SnapshotNow,1,key);
718+
if (!HeapTupleIsValid((tup=systable_getnext(scan))))
719+
{
720+
ereport(ERROR,
721+
(errcode(ERRCODE_UNDEFINED_OBJECT),
722+
errmsg("constraint with OID %d does not exist",conId)));
723+
}
724+
725+
conform= (Form_pg_constraint)GETSTRUCT(tup);
726+
727+
if (ConstraintNameIsUsed(CONSTRAINT_RELATION,
728+
conform->conrelid,
729+
get_rel_namespace(conform->conrelid),
730+
newName))
731+
{
732+
ereport(ERROR,
733+
(errcode(ERRCODE_DUPLICATE_OBJECT),
734+
errmsg("constraint \"%s\" for relation \"%s\" already exists",
735+
newName,
736+
RelationGetRelationName(rel))));
737+
}
738+
tup=heap_copytuple(tup);
739+
conform= (Form_pg_constraint)GETSTRUCT(tup);
740+
741+
if (implicitRename&&cmdName)
742+
{
743+
ereport(NOTICE,
744+
(errmsg("%s will implicitly rename constraint "
745+
"\"%s\" to \"%s\" on table \"%s.%s\"",
746+
cmdName,
747+
NameStr(conform->conname),
748+
newName,
749+
get_namespace_name(nspOid),
750+
RelationGetRelationName(rel))));
751+
}
752+
753+
namestrcpy(&newNameData,newName);
754+
conform->conname=newNameData;
755+
756+
simple_heap_update(conRel,&tup->t_self,tup);
757+
CatalogUpdateIndexes(conRel,tup);
758+
heap_freetuple(tup);
759+
760+
systable_endscan(scan);
761+
heap_close(conRel,RowExclusiveLock);
762+
763+
/* close relation but hold lock until end of transaction */
764+
relation_close(rel,NoLock);
765+
}
766+
767+
768+
/* GetRelationConstraintOid
769+
*
770+
* Get the contraint OID by the relation Id of the relation it constraints and
771+
* this relations' name. We need this function in order to rename a constraint.
772+
* This is done via "ALTER TABLE ... ALTER CONSTRAINT name" and the parser
773+
* gives us the relation this constraint is defined on as well as the
774+
* constraint's name.
775+
*
776+
* The function returns:
777+
*
778+
* - the unique OID of the constraint if the constraint could be found
779+
* - the invalid OID if the constraint was not found
780+
*
781+
*/
782+
OidGetRelationConstraintOid(OidrelId,constchar*name)
783+
{
784+
RelationconRel;
785+
ScanKeyDatakey[1];
786+
SysScanDescscan;
787+
HeapTupletup;
788+
OidconId=InvalidOid;
789+
790+
/* we don't change data, so an AccessShareLock is enough */
791+
conRel=heap_open(ConstraintRelationId,AccessShareLock);
792+
793+
ScanKeyInit(&key[0],
794+
Anum_pg_constraint_conrelid,
795+
BTEqualStrategyNumber,F_OIDEQ,
796+
ObjectIdGetDatum(relId));
797+
798+
scan=systable_beginscan(conRel,ConstraintRelidIndexId, true,
799+
SnapshotNow,1,key);
800+
801+
while (HeapTupleIsValid((tup=systable_getnext(scan))))
802+
{
803+
Form_pg_constraintcon= (Form_pg_constraint)GETSTRUCT(tup);
804+
if (pg_strcasecmp(name,NameStr(con->conname))==0)
805+
{
806+
conId=HeapTupleGetOid(tup);
807+
Assert(OidIsValid(conId));
808+
}
809+
}
810+
811+
systable_endscan(scan);
812+
heap_close(conRel,AccessShareLock);
813+
814+
returnconId;
815+
}
816+
817+
818+
/* GetConstraintRelationId
819+
*
820+
* Gets the OID of the relation where the constraint is defined on or the
821+
* invalid OID if the constraint cannot be found.
822+
*/
823+
OidGetConstraintRelationId(OidconId)
824+
{
825+
RelationconRel;
826+
ScanKeyDatakey[1];
827+
SysScanDescscan;
828+
HeapTupletup;
829+
OidrelId=InvalidOid;
830+
831+
/* we don't change data, so an AccessShareLock is enough */
832+
conRel=heap_open(ConstraintRelationId,AccessShareLock);
833+
834+
ScanKeyInit(&key[0],
835+
ObjectIdAttributeNumber,
836+
BTEqualStrategyNumber,F_OIDEQ,
837+
ObjectIdGetDatum(conId));
838+
839+
scan=systable_beginscan(conRel,ConstraintOidIndexId, true,
840+
SnapshotNow,1,key);
841+
842+
if (HeapTupleIsValid((tup=systable_getnext(scan))))
843+
{
844+
Form_pg_constraintcon= (Form_pg_constraint)GETSTRUCT(tup);
845+
relId=con->conrelid;
846+
Assert(OidIsValid(relId));
847+
}
848+
849+
systable_endscan(scan);
850+
heap_close(conRel,AccessShareLock);
851+
852+
returnrelId;
853+
}
854+

‎src/backend/catalog/pg_depend.c

Lines changed: 100 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/catalog/pg_depend.c,v 1.17 2005/11/22 18:17:08 momjian Exp $
11+
* $PostgreSQL: pgsql/src/backend/catalog/pg_depend.c,v 1.18 2006/02/11 22:17:18 momjian Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -361,3 +361,102 @@ isObjectPinned(const ObjectAddress *object, Relation rel)
361361

362362
returnret;
363363
}
364+
365+
List*getReferencingOids(OidrefClassId,OidrefObjId,OidrefObjSubId,
366+
OidclassId,DependencyTypedeptype)
367+
{
368+
ScanKeyDatakey[3];
369+
SysScanDescscan;
370+
HeapTupletup;
371+
RelationdepRel;
372+
List*list=NIL;
373+
374+
depRel=heap_open(DependRelationId,AccessShareLock);
375+
376+
ScanKeyInit(&key[0],
377+
Anum_pg_depend_refclassid,
378+
BTEqualStrategyNumber,F_OIDEQ,
379+
ObjectIdGetDatum(refClassId));
380+
381+
ScanKeyInit(&key[1],
382+
Anum_pg_depend_refobjid,
383+
BTEqualStrategyNumber,F_OIDEQ,
384+
ObjectIdGetDatum(refObjId));
385+
386+
ScanKeyInit(&key[2],
387+
Anum_pg_depend_refobjsubid,
388+
BTEqualStrategyNumber,F_OIDEQ,
389+
ObjectIdGetDatum(refObjSubId));
390+
391+
scan=systable_beginscan(depRel,DependReferenceIndexId, true,
392+
SnapshotNow,3,key);
393+
394+
while (HeapTupleIsValid(tup=systable_getnext(scan)))
395+
{
396+
Form_pg_dependdepForm= (Form_pg_depend)GETSTRUCT(tup);
397+
398+
/* check if the class id is what we want */
399+
if (depForm->classid!=classId)
400+
continue;
401+
402+
/* check if the DependencyType is what we want */
403+
if (depForm->deptype!=deptype)
404+
continue;
405+
406+
/* if we are still here, we have found a match */
407+
list=lcons_oid(depForm->objid,list);
408+
break;
409+
}
410+
systable_endscan(scan);
411+
412+
heap_close(depRel,AccessShareLock);
413+
returnlist;
414+
}
415+
416+
417+
List*getDependentOids(OidclassId,OidobjId,
418+
OidrefClassId,DependencyTypedeptype)
419+
{
420+
ScanKeyDatakey[2];
421+
SysScanDescscan;
422+
HeapTupletup;
423+
RelationdepRel;
424+
List*list=NIL;
425+
426+
depRel=heap_open(DependRelationId,AccessShareLock);
427+
428+
ScanKeyInit(&key[0],
429+
Anum_pg_depend_classid,
430+
BTEqualStrategyNumber,F_OIDEQ,
431+
ObjectIdGetDatum(classId));
432+
433+
ScanKeyInit(&key[1],
434+
Anum_pg_depend_objid,
435+
BTEqualStrategyNumber,F_OIDEQ,
436+
ObjectIdGetDatum(objId));
437+
438+
scan=systable_beginscan(depRel,DependDependerIndexId, true,
439+
SnapshotNow,2,key);
440+
441+
while (HeapTupleIsValid(tup=systable_getnext(scan)))
442+
{
443+
Form_pg_dependdepForm= (Form_pg_depend)GETSTRUCT(tup);
444+
445+
/* check if the DependencyType is what we want */
446+
if (depForm->deptype!=deptype)
447+
continue;
448+
449+
/* check if the referenced class id is what we want */
450+
if (depForm->refclassid!=refClassId)
451+
continue;
452+
453+
/* if we are still here, we have found a match */
454+
list=lcons_oid(depForm->refobjid,list);
455+
break;
456+
}
457+
systable_endscan(scan);
458+
459+
heap_close(depRel,AccessShareLock);
460+
returnlist;
461+
}
462+

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp