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

Commitf177cbf

Browse files
ALTER TABLE ... ALTER CONSTRAINT for FKs
Allow constraint attributes to be altered,so the default setting of NOT DEFERRABLEcan be altered to DEFERRABLE and back.Review by Abhijit Menon-Sen
1 parent2f74e4e commitf177cbf

File tree

6 files changed

+205
-0
lines changed

6 files changed

+205
-0
lines changed

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

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -46,6 +46,7 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable>
4646
ALTER [ COLUMN ] <replaceable class="PARAMETER">column_name</replaceable> SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
4747
ADD <replaceable class="PARAMETER">table_constraint</replaceable> [ NOT VALID ]
4848
ADD <replaceable class="PARAMETER">table_constraint_using_index</replaceable>
49+
ALTER CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
4950
VALIDATE CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable>
5051
DROP CONSTRAINT [ IF EXISTS ] <replaceable class="PARAMETER">constraint_name</replaceable> [ RESTRICT | CASCADE ]
5152
DISABLE TRIGGER [ <replaceable class="PARAMETER">trigger_name</replaceable> | ALL | USER ]
@@ -316,6 +317,16 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable>
316317
</listitem>
317318
</varlistentry>
318319

320+
<varlistentry>
321+
<term><literal>ALTER CONSTRAINT</literal></term>
322+
<listitem>
323+
<para>
324+
This form alters the attributes of a constraint that was previously
325+
created. Currently only foreign key constraints may be altered.
326+
</para>
327+
</listitem>
328+
</varlistentry>
329+
319330
<varlistentry>
320331
<term><literal>VALIDATE CONSTRAINT</literal></term>
321332
<listitem>

‎src/backend/commands/tablecmds.c‎

Lines changed: 139 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -276,6 +276,8 @@ static void AlterIndexNamespaces(Relation classRel, Relation rel,
276276
staticvoidAlterSeqNamespaces(RelationclassRel,Relationrel,
277277
OidoldNspOid,OidnewNspOid,ObjectAddresses*objsMoved,
278278
LOCKMODElockmode);
279+
staticvoidATExecAlterConstraint(Relationrel,AlterTableCmd*cmd,
280+
boolrecurse,boolrecursing,LOCKMODElockmode);
279281
staticvoidATExecValidateConstraint(Relationrel,char*constrName,
280282
boolrecurse,boolrecursing,LOCKMODElockmode);
281283
staticinttransformColumnNameList(OidrelId,List*colList,
@@ -2887,6 +2889,7 @@ AlterTableGetLockLevel(List *cmds)
28872889
caseAT_SetOptions:
28882890
caseAT_ResetOptions:
28892891
caseAT_SetStorage:
2892+
caseAT_AlterConstraint:
28902893
caseAT_ValidateConstraint:
28912894
cmd_lockmode=ShareUpdateExclusiveLock;
28922895
break;
@@ -3125,6 +3128,10 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
31253128
ATPrepAddInherit(rel);
31263129
pass=AT_PASS_MISC;
31273130
break;
3131+
caseAT_AlterConstraint:/* ALTER CONSTRAINT */
3132+
ATSimplePermissions(rel,ATT_TABLE);
3133+
pass=AT_PASS_MISC;
3134+
break;
31283135
caseAT_ValidateConstraint:/* VALIDATE CONSTRAINT */
31293136
ATSimplePermissions(rel,ATT_TABLE);
31303137
/* Recursion occurs during execution phase */
@@ -3304,6 +3311,9 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab, Relation rel,
33043311
caseAT_AddIndexConstraint:/* ADD CONSTRAINT USING INDEX */
33053312
ATExecAddIndexConstraint(tab,rel, (IndexStmt*)cmd->def,lockmode);
33063313
break;
3314+
caseAT_AlterConstraint:/* ALTER CONSTRAINT */
3315+
ATExecAlterConstraint(rel,cmd, false, false,lockmode);
3316+
break;
33073317
caseAT_ValidateConstraint:/* VALIDATE CONSTRAINT */
33083318
ATExecValidateConstraint(rel,cmd->name, false, false,lockmode);
33093319
break;
@@ -6175,6 +6185,135 @@ ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel,
61756185
heap_close(pkrel,NoLock);
61766186
}
61776187

6188+
/*
6189+
* ALTER TABLE ALTER CONSTRAINT
6190+
*
6191+
* Update the attributes of a constraint.
6192+
*
6193+
* Currently only works for Foreign Key constraints.
6194+
* Foreign keys do not inherit, so we purposely ignore the
6195+
* recursion bit here, but we keep the API the same for when
6196+
* other constraint types are supported.
6197+
*/
6198+
staticvoid
6199+
ATExecAlterConstraint(Relationrel,AlterTableCmd*cmd,
6200+
boolrecurse,boolrecursing,LOCKMODElockmode)
6201+
{
6202+
Relationconrel;
6203+
SysScanDescscan;
6204+
ScanKeyDatakey;
6205+
HeapTuplecontuple;
6206+
Form_pg_constraintcurrcon=NULL;
6207+
Constraint*cmdcon=NULL;
6208+
boolfound= false;
6209+
6210+
Assert(IsA(cmd->def,Constraint));
6211+
cmdcon= (Constraint*)cmd->def;
6212+
6213+
conrel=heap_open(ConstraintRelationId,RowExclusiveLock);
6214+
6215+
/*
6216+
* Find and check the target constraint
6217+
*/
6218+
ScanKeyInit(&key,
6219+
Anum_pg_constraint_conrelid,
6220+
BTEqualStrategyNumber,F_OIDEQ,
6221+
ObjectIdGetDatum(RelationGetRelid(rel)));
6222+
scan=systable_beginscan(conrel,ConstraintRelidIndexId,
6223+
true,SnapshotNow,1,&key);
6224+
6225+
while (HeapTupleIsValid(contuple=systable_getnext(scan)))
6226+
{
6227+
currcon= (Form_pg_constraint)GETSTRUCT(contuple);
6228+
if (strcmp(NameStr(currcon->conname),cmdcon->conname)==0)
6229+
{
6230+
found= true;
6231+
break;
6232+
}
6233+
}
6234+
6235+
if (!found)
6236+
ereport(ERROR,
6237+
(errcode(ERRCODE_UNDEFINED_OBJECT),
6238+
errmsg("constraint \"%s\" of relation \"%s\" does not exist",
6239+
cmdcon->conname,RelationGetRelationName(rel))));
6240+
6241+
if (currcon->contype!=CONSTRAINT_FOREIGN)
6242+
ereport(ERROR,
6243+
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
6244+
errmsg("constraint \"%s\" of relation \"%s\" is not a foreign key constraint",
6245+
cmdcon->conname,RelationGetRelationName(rel))));
6246+
6247+
if (currcon->condeferrable!=cmdcon->deferrable||
6248+
currcon->condeferred!=cmdcon->initdeferred)
6249+
{
6250+
HeapTuplecopyTuple;
6251+
HeapTupletgtuple;
6252+
Form_pg_constraintcopy_con;
6253+
Form_pg_triggercopy_tg;
6254+
ScanKeyDatatgkey;
6255+
SysScanDesctgscan;
6256+
Relationtgrel;
6257+
6258+
/*
6259+
* Now update the catalog, while we have the door open.
6260+
*/
6261+
copyTuple=heap_copytuple(contuple);
6262+
copy_con= (Form_pg_constraint)GETSTRUCT(copyTuple);
6263+
copy_con->condeferrable=cmdcon->deferrable;
6264+
copy_con->condeferred=cmdcon->initdeferred;
6265+
simple_heap_update(conrel,&copyTuple->t_self,copyTuple);
6266+
CatalogUpdateIndexes(conrel,copyTuple);
6267+
6268+
InvokeObjectPostAlterHook(ConstraintRelationId,
6269+
HeapTupleGetOid(contuple),0);
6270+
6271+
heap_freetuple(copyTuple);
6272+
6273+
/*
6274+
* Now we need to update the multiple entries in pg_trigger
6275+
* that implement the constraint.
6276+
*/
6277+
tgrel=heap_open(TriggerRelationId,RowExclusiveLock);
6278+
6279+
ScanKeyInit(&tgkey,
6280+
Anum_pg_trigger_tgconstraint,
6281+
BTEqualStrategyNumber,F_OIDEQ,
6282+
ObjectIdGetDatum(HeapTupleGetOid(contuple)));
6283+
6284+
tgscan=systable_beginscan(tgrel,TriggerConstraintIndexId, true,
6285+
SnapshotNow,1,&tgkey);
6286+
6287+
while (HeapTupleIsValid(tgtuple=systable_getnext(tgscan)))
6288+
{
6289+
copyTuple=heap_copytuple(tgtuple);
6290+
copy_tg= (Form_pg_trigger)GETSTRUCT(copyTuple);
6291+
copy_tg->tgdeferrable=cmdcon->deferrable;
6292+
copy_tg->tginitdeferred=cmdcon->initdeferred;
6293+
simple_heap_update(tgrel,&copyTuple->t_self,copyTuple);
6294+
CatalogUpdateIndexes(tgrel,copyTuple);
6295+
6296+
InvokeObjectPostAlterHook(TriggerRelationId,
6297+
HeapTupleGetOid(tgtuple),0);
6298+
6299+
heap_freetuple(copyTuple);
6300+
}
6301+
6302+
systable_endscan(tgscan);
6303+
6304+
heap_close(tgrel,RowExclusiveLock);
6305+
6306+
/*
6307+
* Invalidate relcache so that others see the new attributes.
6308+
*/
6309+
CacheInvalidateRelcache(rel);
6310+
}
6311+
6312+
systable_endscan(scan);
6313+
6314+
heap_close(conrel,RowExclusiveLock);
6315+
}
6316+
61786317
/*
61796318
* ALTER TABLE VALIDATE CONSTRAINT
61806319
*

‎src/backend/parser/gram.y‎

Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1943,6 +1943,21 @@ alter_table_cmd:
19431943
n->def =$2;
19441944
$$ = (Node *)n;
19451945
}
1946+
/* ALTER TABLE <name> ALTER CONSTRAINT ...*/
1947+
|ALTERCONSTRAINTnameConstraintAttributeSpec
1948+
{
1949+
AlterTableCmd *n = makeNode(AlterTableCmd);
1950+
Constraint *c = makeNode(Constraint);
1951+
n->subtype = AT_AlterConstraint;
1952+
n->def = (Node *) c;
1953+
c->contype = CONSTR_FOREIGN;/* others not supported, yet*/
1954+
c->conname =$3;
1955+
processCASbits($4, @4,"ALTER CONSTRAINT statement",
1956+
&c->deferrable,
1957+
&c->initdeferred,
1958+
NULL,NULL, yyscanner);
1959+
$$ = (Node *)n;
1960+
}
19461961
/* ALTER TABLE <name> VALIDATE CONSTRAINT ...*/
19471962
|VALIDATECONSTRAINTname
19481963
{

‎src/include/nodes/parsenodes.h‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1209,6 +1209,7 @@ typedef enum AlterTableType
12091209
AT_AddConstraint,/* add constraint */
12101210
AT_AddConstraintRecurse,/* internal to commands/tablecmds.c */
12111211
AT_ReAddConstraint,/* internal to commands/tablecmds.c */
1212+
AT_AlterConstraint,/* alter constraint */
12121213
AT_ValidateConstraint,/* validate constraint */
12131214
AT_ValidateConstraintRecurse,/* internal to commands/tablecmds.c */
12141215
AT_ProcessedConstraint,/* pre-processed add constraint (local in

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

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1132,6 +1132,15 @@ CREATE TEMP TABLE fktable (
11321132
id int primary key,
11331133
fk int references pktable deferrable initially deferred
11341134
);
1135+
-- check ALTER CONSTRAINT
1136+
ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey NOT DEFERRABLE;
1137+
-- illegal option
1138+
ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey NOT DEFERRABLE INITIALLY DEFERRED;
1139+
ERROR: constraint declared INITIALLY DEFERRED must be DEFERRABLE
1140+
LINE 1: ...e ALTER CONSTRAINT fktable_fk_fkey NOT DEFERRABLE INITIALLY ...
1141+
^
1142+
-- reset
1143+
ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey DEFERRABLE INITIALLY DEFERRED;
11351144
INSERT INTO pktable VALUES (5, 10);
11361145
BEGIN;
11371146
-- doesn't match PK, but no error yet
@@ -1142,6 +1151,16 @@ UPDATE fktable SET id = id + 1;
11421151
COMMIT;
11431152
ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_fk_fkey"
11441153
DETAIL: Key (fk)=(20) is not present in table "pktable".
1154+
-- change the constraint definition and retest
1155+
ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey DEFERRABLE INITIALLY IMMEDIATE;
1156+
BEGIN;
1157+
-- doesn't match PK, should throw error now
1158+
INSERT INTO fktable VALUES (0, 20);
1159+
ERROR: insert or update on table "fktable" violates foreign key constraint "fktable_fk_fkey"
1160+
DETAIL: Key (fk)=(20) is not present in table "pktable".
1161+
COMMIT;
1162+
-- reset
1163+
ALTER TABLE fktable ALTER CONSTRAINT fktable_fk_fkey DEFERRABLE INITIALLY DEFERRED;
11451164
-- check same case when insert is in a different subtransaction than update
11461165
BEGIN;
11471166
-- doesn't match PK, but no error yet

‎src/test/regress/sql/foreign_key.sql‎

Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -818,6 +818,13 @@ CREATE TEMP TABLE fktable (
818818
fkintreferences pktable deferrable initially deferred
819819
);
820820

821+
-- check ALTER CONSTRAINT
822+
ALTERTABLE fktable ALTERCONSTRAINT fktable_fk_fkey NOT DEFERRABLE;
823+
-- illegal option
824+
ALTERTABLE fktable ALTERCONSTRAINT fktable_fk_fkey NOT DEFERRABLE INITIALLY DEFERRED;
825+
-- reset
826+
ALTERTABLE fktable ALTERCONSTRAINT fktable_fk_fkey DEFERRABLE INITIALLY DEFERRED;
827+
821828
INSERT INTO pktableVALUES (5,10);
822829

823830
BEGIN;
@@ -831,6 +838,19 @@ UPDATE fktable SET id = id + 1;
831838
-- should catch error from initial INSERT
832839
COMMIT;
833840

841+
-- change the constraint definition and retest
842+
ALTERTABLE fktable ALTERCONSTRAINT fktable_fk_fkey DEFERRABLE INITIALLY IMMEDIATE;
843+
844+
BEGIN;
845+
846+
-- doesn't match PK, should throw error now
847+
INSERT INTO fktableVALUES (0,20);
848+
849+
COMMIT;
850+
851+
-- reset
852+
ALTERTABLE fktable ALTERCONSTRAINT fktable_fk_fkey DEFERRABLE INITIALLY DEFERRED;
853+
834854
-- check same case when insert is in a different subtransaction than update
835855

836856
BEGIN;

0 commit comments

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