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

Commitb577a57

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

File tree

6 files changed

+204
-0
lines changed

6 files changed

+204
-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: 138 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -275,6 +275,8 @@ static void AlterIndexNamespaces(Relation classRel, Relation rel,
275275
staticvoidAlterSeqNamespaces(RelationclassRel,Relationrel,
276276
OidoldNspOid,OidnewNspOid,ObjectAddresses*objsMoved,
277277
LOCKMODElockmode);
278+
staticvoidATExecAlterConstraint(Relationrel,AlterTableCmd*cmd,
279+
boolrecurse,boolrecursing,LOCKMODElockmode);
278280
staticvoidATExecValidateConstraint(Relationrel,char*constrName,
279281
boolrecurse,boolrecursing,LOCKMODElockmode);
280282
staticinttransformColumnNameList(OidrelId,List*colList,
@@ -2886,6 +2888,7 @@ AlterTableGetLockLevel(List *cmds)
28862888
caseAT_SetOptions:
28872889
caseAT_ResetOptions:
28882890
caseAT_SetStorage:
2891+
caseAT_AlterConstraint:
28892892
caseAT_ValidateConstraint:
28902893
cmd_lockmode=ShareUpdateExclusiveLock;
28912894
break;
@@ -3124,6 +3127,9 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
31243127
ATPrepAddInherit(rel);
31253128
pass=AT_PASS_MISC;
31263129
break;
3130+
caseAT_AlterConstraint:/* ALTER CONSTRAINT */
3131+
ATSimplePermissions(rel,ATT_TABLE);
3132+
break;
31273133
caseAT_ValidateConstraint:/* VALIDATE CONSTRAINT */
31283134
ATSimplePermissions(rel,ATT_TABLE);
31293135
/* Recursion occurs during execution phase */
@@ -3302,6 +3308,9 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab, Relation rel,
33023308
caseAT_AddIndexConstraint:/* ADD CONSTRAINT USING INDEX */
33033309
ATExecAddIndexConstraint(tab,rel, (IndexStmt*)cmd->def,lockmode);
33043310
break;
3311+
caseAT_AlterConstraint:/* ALTER CONSTRAINT */
3312+
ATExecAlterConstraint(rel,cmd, false, false,lockmode);
3313+
break;
33053314
caseAT_ValidateConstraint:/* VALIDATE CONSTRAINT */
33063315
ATExecValidateConstraint(rel,cmd->name, false, false,lockmode);
33073316
break;
@@ -6173,6 +6182,135 @@ ATAddForeignKeyConstraint(AlteredTableInfo *tab, Relation rel,
61736182
heap_close(pkrel,NoLock);
61746183
}
61756184

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

‎src/backend/parser/gram.y

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

‎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