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

Commit39d74e3

Browse files
committed
Add support for renaming constraints
reviewed by Josh Berkus and Dimitri Fontaine
1 parente914a14 commit39d74e3

File tree

7 files changed

+306
-3
lines changed

7 files changed

+306
-3
lines changed

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

Lines changed: 13 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -25,6 +25,8 @@ ALTER TABLE [ IF EXISTS ] [ ONLY ] <replaceable class="PARAMETER">name</replacea
2525
<replaceable class="PARAMETER">action</replaceable> [, ... ]
2626
ALTER TABLE [ IF EXISTS ] [ ONLY ] <replaceable class="PARAMETER">name</replaceable> [ * ]
2727
RENAME [ COLUMN ] <replaceable class="PARAMETER">column</replaceable> TO <replaceable class="PARAMETER">new_column</replaceable>
28+
ALTER TABLE [ IF EXISTS ] [ ONLY ] <replaceable class="PARAMETER">name</replaceable> [ * ]
29+
RENAME CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> TO <replaceable class="PARAMETER">new_constraint_name</replaceable>
2830
ALTER TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable>
2931
RENAME TO <replaceable class="PARAMETER">new_name</replaceable>
3032
ALTER TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable>
@@ -569,8 +571,8 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable>
569571
<listitem>
570572
<para>
571573
The <literal>RENAME</literal> forms change the name of a table
572-
(or an index, sequence, or view) or the name of an individual column in
573-
a table. There is no effect on the stored data.
574+
(or an index, sequence, or view), the name of an individual column in
575+
a table, or the name of a constraint of the table. There is no effect on the stored data.
574576
</para>
575577
</listitem>
576578
</varlistentry>
@@ -883,7 +885,8 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable>
883885

884886
<para>
885887
If a table has any descendant tables, it is not permitted to add,
886-
rename, or change the type of a column in the parent table without doing
888+
rename, or change the type of a column, or rename an inherited constraint
889+
in the parent table without doing
887890
the same to the descendants. That is, <command>ALTER TABLE ONLY</command>
888891
will be rejected. This ensures that the descendants always have
889892
columns matching the parent.
@@ -982,6 +985,13 @@ ALTER TABLE distributors RENAME TO suppliers;
982985
</programlisting>
983986
</para>
984987

988+
<para>
989+
To rename an existing constraint:
990+
<programlisting>
991+
ALTER TABLE distributors RENAME CONSTRAINT zipchk TO zip_check;
992+
</programlisting>
993+
</para>
994+
985995
<para>
986996
To add a not-null constraint to a column:
987997
<programlisting>

‎src/backend/commands/alter.c‎

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -57,6 +57,10 @@ ExecRenameStmt(RenameStmt *stmt)
5757
RenameCollation(stmt->object,stmt->newname);
5858
break;
5959

60+
caseOBJECT_CONSTRAINT:
61+
RenameConstraint(stmt);
62+
break;
63+
6064
caseOBJECT_CONVERSION:
6165
RenameConversion(stmt->object,stmt->newname);
6266
break;

‎src/backend/commands/tablecmds.c‎

Lines changed: 102 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2327,6 +2327,108 @@ renameatt(RenameStmt *stmt)
23272327
stmt->behavior);
23282328
}
23292329

2330+
2331+
/*
2332+
* same logic as renameatt_internal
2333+
*/
2334+
staticvoid
2335+
rename_constraint_internal(Oidmyrelid,
2336+
constchar*oldconname,
2337+
constchar*newconname,
2338+
boolrecurse,
2339+
boolrecursing,
2340+
intexpected_parents)
2341+
{
2342+
Relationtargetrelation;
2343+
OidconstraintOid;
2344+
HeapTupletuple;
2345+
Form_pg_constraintcon;
2346+
2347+
targetrelation=relation_open(myrelid,AccessExclusiveLock);
2348+
/* don't tell it whether we're recursing; we allow changing typed tables here */
2349+
renameatt_check(myrelid,RelationGetForm(targetrelation), false);
2350+
2351+
constraintOid=get_constraint_oid(myrelid,oldconname, false);
2352+
2353+
tuple=SearchSysCache1(CONSTROID,ObjectIdGetDatum(constraintOid));
2354+
if (!HeapTupleIsValid(tuple))
2355+
elog(ERROR,"cache lookup failed for constraint %u",
2356+
constraintOid);
2357+
con= (Form_pg_constraint)GETSTRUCT(tuple);
2358+
2359+
if (con->contype==CONSTRAINT_CHECK&& !con->conisonly)
2360+
{
2361+
if (recurse)
2362+
{
2363+
List*child_oids,
2364+
*child_numparents;
2365+
ListCell*lo,
2366+
*li;
2367+
2368+
child_oids=find_all_inheritors(myrelid,AccessExclusiveLock,
2369+
&child_numparents);
2370+
2371+
forboth(lo,child_oids,li,child_numparents)
2372+
{
2373+
Oidchildrelid=lfirst_oid(lo);
2374+
intnumparents=lfirst_int(li);
2375+
2376+
if (childrelid==myrelid)
2377+
continue;
2378+
2379+
rename_constraint_internal(childrelid,oldconname,newconname, false, true,numparents);
2380+
}
2381+
}
2382+
else
2383+
{
2384+
if (expected_parents==0&&
2385+
find_inheritance_children(myrelid,NoLock)!=NIL)
2386+
ereport(ERROR,
2387+
(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
2388+
errmsg("inherited constraint \"%s\" must be renamed in child tables too",
2389+
oldconname)));
2390+
}
2391+
2392+
if (con->coninhcount>expected_parents)
2393+
ereport(ERROR,
2394+
(errcode(ERRCODE_INVALID_TABLE_DEFINITION),
2395+
errmsg("cannot rename inherited constraint \"%s\"",
2396+
oldconname)));
2397+
}
2398+
2399+
if (con->conindid
2400+
&& (con->contype==CONSTRAINT_PRIMARY
2401+
||con->contype==CONSTRAINT_UNIQUE
2402+
||con->contype==CONSTRAINT_EXCLUSION))
2403+
/* rename the index; this renames the constraint as well */
2404+
RenameRelationInternal(con->conindid,newconname);
2405+
else
2406+
RenameConstraintById(constraintOid,newconname);
2407+
2408+
ReleaseSysCache(tuple);
2409+
2410+
relation_close(targetrelation,NoLock);/* close rel but keep lock */
2411+
}
2412+
2413+
void
2414+
RenameConstraint(RenameStmt*stmt)
2415+
{
2416+
Oidrelid;
2417+
2418+
/* lock level taken here should match rename_constraint_internal */
2419+
relid=RangeVarGetRelidExtended(stmt->relation,AccessExclusiveLock,
2420+
false, false,
2421+
RangeVarCallbackForRenameAttribute,
2422+
NULL);
2423+
2424+
rename_constraint_internal(relid,
2425+
stmt->subname,
2426+
stmt->newname,
2427+
interpretInhOption(stmt->relation->inhOpt),/* recursive? */
2428+
false,/* recursing? */
2429+
0/* expected inhcount */);
2430+
}
2431+
23302432
/*
23312433
* Execute ALTER TABLE/INDEX/SEQUENCE/VIEW/FOREIGN TABLE RENAME
23322434
*/

‎src/backend/parser/gram.y‎

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -6731,6 +6731,16 @@ RenameStmt: ALTER AGGREGATE func_name aggr_args RENAME TO name
67316731
n->missing_ok =true;
67326732
$$ = (Node *)n;
67336733
}
6734+
|ALTERTABLErelation_exprRENAMECONSTRAINTnameTOname
6735+
{
6736+
RenameStmt *n = makeNode(RenameStmt);
6737+
n->renameType = OBJECT_CONSTRAINT;
6738+
n->relationType = OBJECT_TABLE;
6739+
n->relation =$3;
6740+
n->subname =$6;
6741+
n->newname =$8;
6742+
$$ = (Node *)n;
6743+
}
67346744
|ALTERFOREIGNTABLErelation_exprRENAMEopt_columnnameTOname
67356745
{
67366746
RenameStmt *n = makeNode(RenameStmt);

‎src/include/commands/tablecmds.h‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -48,6 +48,8 @@ extern void SetRelationHasSubclass(Oid relationId, bool relhassubclass);
4848

4949
externvoidrenameatt(RenameStmt*stmt);
5050

51+
externvoidRenameConstraint(RenameStmt*stmt);
52+
5153
externvoidRenameRelation(RenameStmt*stmt);
5254

5355
externvoidRenameRelationInternal(Oidmyrelid,

‎src/test/regress/expected/alter_table.out‎

Lines changed: 135 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -160,6 +160,141 @@ DROP VIEW tmp_view_new;
160160
-- toast-like relation name
161161
alter table stud_emp rename to pg_toast_stud_emp;
162162
alter table pg_toast_stud_emp rename to stud_emp;
163+
-- renaming index should rename constraint as well
164+
ALTER TABLE onek ADD CONSTRAINT onek_unique1_constraint UNIQUE (unique1);
165+
NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "onek_unique1_constraint" for table "onek"
166+
ALTER INDEX onek_unique1_constraint RENAME TO onek_unique1_constraint_foo;
167+
ALTER TABLE onek DROP CONSTRAINT onek_unique1_constraint_foo;
168+
-- renaming constraint
169+
ALTER TABLE onek ADD CONSTRAINT onek_check_constraint CHECK (unique1 >= 0);
170+
ALTER TABLE onek RENAME CONSTRAINT onek_check_constraint TO onek_check_constraint_foo;
171+
ALTER TABLE onek DROP CONSTRAINT onek_check_constraint_foo;
172+
-- renaming constraint should rename index as well
173+
ALTER TABLE onek ADD CONSTRAINT onek_unique1_constraint UNIQUE (unique1);
174+
NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "onek_unique1_constraint" for table "onek"
175+
DROP INDEX onek_unique1_constraint; -- to see whether it's there
176+
ERROR: cannot drop index onek_unique1_constraint because constraint onek_unique1_constraint on table onek requires it
177+
HINT: You can drop constraint onek_unique1_constraint on table onek instead.
178+
ALTER TABLE onek RENAME CONSTRAINT onek_unique1_constraint TO onek_unique1_constraint_foo;
179+
DROP INDEX onek_unique1_constraint_foo; -- to see whether it's there
180+
ERROR: cannot drop index onek_unique1_constraint_foo because constraint onek_unique1_constraint_foo on table onek requires it
181+
HINT: You can drop constraint onek_unique1_constraint_foo on table onek instead.
182+
ALTER TABLE onek DROP CONSTRAINT onek_unique1_constraint_foo;
183+
-- renaming constraints vs. inheritance
184+
CREATE TABLE constraint_rename_test (a int CONSTRAINT con1 CHECK (a > 0), b int, c int);
185+
\d constraint_rename_test
186+
Table "public.constraint_rename_test"
187+
Column | Type | Modifiers
188+
--------+---------+-----------
189+
a | integer |
190+
b | integer |
191+
c | integer |
192+
Check constraints:
193+
"con1" CHECK (a > 0)
194+
195+
CREATE TABLE constraint_rename_test2 (a int CONSTRAINT con1 CHECK (a > 0), d int) INHERITS (constraint_rename_test);
196+
NOTICE: merging column "a" with inherited definition
197+
NOTICE: merging constraint "con1" with inherited definition
198+
\d constraint_rename_test2
199+
Table "public.constraint_rename_test2"
200+
Column | Type | Modifiers
201+
--------+---------+-----------
202+
a | integer |
203+
b | integer |
204+
c | integer |
205+
d | integer |
206+
Check constraints:
207+
"con1" CHECK (a > 0)
208+
Inherits: constraint_rename_test
209+
210+
ALTER TABLE constraint_rename_test2 RENAME CONSTRAINT con1 TO con1foo; -- fail
211+
ERROR: cannot rename inherited constraint "con1"
212+
ALTER TABLE ONLY constraint_rename_test RENAME CONSTRAINT con1 TO con1foo; -- fail
213+
ERROR: inherited constraint "con1" must be renamed in child tables too
214+
ALTER TABLE constraint_rename_test RENAME CONSTRAINT con1 TO con1foo; -- ok
215+
\d constraint_rename_test
216+
Table "public.constraint_rename_test"
217+
Column | Type | Modifiers
218+
--------+---------+-----------
219+
a | integer |
220+
b | integer |
221+
c | integer |
222+
Check constraints:
223+
"con1foo" CHECK (a > 0)
224+
Number of child tables: 1 (Use \d+ to list them.)
225+
226+
\d constraint_rename_test2
227+
Table "public.constraint_rename_test2"
228+
Column | Type | Modifiers
229+
--------+---------+-----------
230+
a | integer |
231+
b | integer |
232+
c | integer |
233+
d | integer |
234+
Check constraints:
235+
"con1foo" CHECK (a > 0)
236+
Inherits: constraint_rename_test
237+
238+
ALTER TABLE ONLY constraint_rename_test ADD CONSTRAINT con2 CHECK (b > 0);
239+
ALTER TABLE ONLY constraint_rename_test RENAME CONSTRAINT con2 TO con2foo; -- ok
240+
ALTER TABLE constraint_rename_test RENAME CONSTRAINT con2foo TO con2bar; -- ok
241+
\d constraint_rename_test
242+
Table "public.constraint_rename_test"
243+
Column | Type | Modifiers
244+
--------+---------+-----------
245+
a | integer |
246+
b | integer |
247+
c | integer |
248+
Check constraints:
249+
"con2bar" (ONLY) CHECK (b > 0)
250+
"con1foo" CHECK (a > 0)
251+
Number of child tables: 1 (Use \d+ to list them.)
252+
253+
\d constraint_rename_test2
254+
Table "public.constraint_rename_test2"
255+
Column | Type | Modifiers
256+
--------+---------+-----------
257+
a | integer |
258+
b | integer |
259+
c | integer |
260+
d | integer |
261+
Check constraints:
262+
"con1foo" CHECK (a > 0)
263+
Inherits: constraint_rename_test
264+
265+
ALTER TABLE constraint_rename_test ADD CONSTRAINT con3 PRIMARY KEY (a);
266+
NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "con3" for table "constraint_rename_test"
267+
ALTER TABLE constraint_rename_test RENAME CONSTRAINT con3 TO con3foo; -- ok
268+
\d constraint_rename_test
269+
Table "public.constraint_rename_test"
270+
Column | Type | Modifiers
271+
--------+---------+-----------
272+
a | integer | not null
273+
b | integer |
274+
c | integer |
275+
Indexes:
276+
"con3foo" PRIMARY KEY, btree (a)
277+
Check constraints:
278+
"con2bar" (ONLY) CHECK (b > 0)
279+
"con1foo" CHECK (a > 0)
280+
Number of child tables: 1 (Use \d+ to list them.)
281+
282+
\d constraint_rename_test2
283+
Table "public.constraint_rename_test2"
284+
Column | Type | Modifiers
285+
--------+---------+-----------
286+
a | integer |
287+
b | integer |
288+
c | integer |
289+
d | integer |
290+
Check constraints:
291+
"con1foo" CHECK (a > 0)
292+
Inherits: constraint_rename_test
293+
294+
DROP TABLE constraint_rename_test2;
295+
DROP TABLE constraint_rename_test;
296+
ALTER TABLE IF EXISTS constraint_rename_test ADD CONSTRAINT con4 UNIQUE (a);
297+
NOTICE: relation "constraint_rename_test" does not exist, skipping
163298
-- FOREIGN KEY CONSTRAINT adding TEST
164299
CREATE TABLE tmp2 (a int primary key);
165300
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "tmp2_pkey" for table "tmp2"

‎src/test/regress/sql/alter_table.sql‎

Lines changed: 40 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -191,6 +191,46 @@ DROP VIEW tmp_view_new;
191191
altertable stud_emp rename to pg_toast_stud_emp;
192192
altertable pg_toast_stud_emp rename to stud_emp;
193193

194+
-- renaming index should rename constraint as well
195+
ALTERTABLE onek ADDCONSTRAINT onek_unique1_constraint UNIQUE (unique1);
196+
ALTERINDEX onek_unique1_constraint RENAME TO onek_unique1_constraint_foo;
197+
ALTERTABLE onek DROPCONSTRAINT onek_unique1_constraint_foo;
198+
199+
-- renaming constraint
200+
ALTERTABLE onek ADDCONSTRAINT onek_check_constraintCHECK (unique1>=0);
201+
ALTERTABLE onek RENAMECONSTRAINT onek_check_constraint TO onek_check_constraint_foo;
202+
ALTERTABLE onek DROPCONSTRAINT onek_check_constraint_foo;
203+
204+
-- renaming constraint should rename index as well
205+
ALTERTABLE onek ADDCONSTRAINT onek_unique1_constraint UNIQUE (unique1);
206+
DROPINDEX onek_unique1_constraint;-- to see whether it's there
207+
ALTERTABLE onek RENAMECONSTRAINT onek_unique1_constraint TO onek_unique1_constraint_foo;
208+
DROPINDEX onek_unique1_constraint_foo;-- to see whether it's there
209+
ALTERTABLE onek DROPCONSTRAINT onek_unique1_constraint_foo;
210+
211+
-- renaming constraints vs. inheritance
212+
CREATETABLEconstraint_rename_test (aintCONSTRAINT con1CHECK (a>0), bint, cint);
213+
\d constraint_rename_test
214+
CREATETABLEconstraint_rename_test2 (aintCONSTRAINT con1CHECK (a>0), dint) INHERITS (constraint_rename_test);
215+
\d constraint_rename_test2
216+
ALTERTABLE constraint_rename_test2 RENAMECONSTRAINT con1 TO con1foo;-- fail
217+
ALTERTABLE ONLY constraint_rename_test RENAMECONSTRAINT con1 TO con1foo;-- fail
218+
ALTERTABLE constraint_rename_test RENAMECONSTRAINT con1 TO con1foo;-- ok
219+
\d constraint_rename_test
220+
\d constraint_rename_test2
221+
ALTERTABLE ONLY constraint_rename_test ADDCONSTRAINT con2CHECK (b>0);
222+
ALTERTABLE ONLY constraint_rename_test RENAMECONSTRAINT con2 TO con2foo;-- ok
223+
ALTERTABLE constraint_rename_test RENAMECONSTRAINT con2foo TO con2bar;-- ok
224+
\d constraint_rename_test
225+
\d constraint_rename_test2
226+
ALTERTABLE constraint_rename_test ADDCONSTRAINT con3PRIMARY KEY (a);
227+
ALTERTABLE constraint_rename_test RENAMECONSTRAINT con3 TO con3foo;-- ok
228+
\d constraint_rename_test
229+
\d constraint_rename_test2
230+
DROPTABLE constraint_rename_test2;
231+
DROPTABLE constraint_rename_test;
232+
ALTERTABLE IF EXISTS constraint_rename_test ADDCONSTRAINT con4 UNIQUE (a);
233+
194234
-- FOREIGN KEY CONSTRAINT adding TEST
195235

196236
CREATETABLEtmp2 (aintprimary key);

0 commit comments

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