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

Commit6009bad

Browse files
committed
Fix ALTER/TYPE on columns referenced by FKs in partitioned tables
When ALTER TABLE ... SET DATA TYPE affects a column referenced byconstraints and indexes, it drop those constraints and indexes andrecreates them afterwards, so that the definitions match the new datatype. The original code did this by dropping one object at a time(commit077db40 of May 2004), which worked fine because thedependencies between the objects were pretty straightforward, andordering the objects in a specific way was enough to make this work.However, when there are foreign key constraints in partitioned tables,the dependencies are no longer so straightforward, and we were gettingerrors when attempted: ERROR: cache lookup failed for constraint 16398This can be fixed by doing all the drops in one pass instead, usingperformMultipleDeletions (introduced bydf18c51 of Aug 2006). Withthis change we can also remove the code to carefully order the list ofobjects to be deleted.Reported-by: Rajkumar Raghuwanshi <rajkumar.raghuwanshi@enterprisedb.com>Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us>Discussion:https://postgr.es/m/CAKcux6nWS_m+s=1Udk_U9B+QY7pA-Ac58qR5BdUfOyrwnWHDew@mail.gmail.com
1 parent830d756 commit6009bad

File tree

3 files changed

+47
-47
lines changed

3 files changed

+47
-47
lines changed

‎src/backend/commands/tablecmds.c

Lines changed: 24 additions & 47 deletions
Original file line numberDiff line numberDiff line change
@@ -9496,33 +9496,12 @@ ATExecAlterColumnType(AlteredTableInfo *tab, Relation rel,
94969496
{
94979497
char*defstring=pg_get_constraintdef_command(foundObject.objectId);
94989498

9499-
/*
9500-
* Put NORMAL dependencies at the front of the list and
9501-
* AUTO dependencies at the back. This makes sure that
9502-
* foreign-key constraints depending on this column will
9503-
* be dropped before unique or primary-key constraints of
9504-
* the column; which we must have because the FK
9505-
* constraints depend on the indexes belonging to the
9506-
* unique constraints.
9507-
*/
9508-
if (foundDep->deptype==DEPENDENCY_NORMAL)
9509-
{
9510-
tab->changedConstraintOids=
9511-
lcons_oid(foundObject.objectId,
9512-
tab->changedConstraintOids);
9513-
tab->changedConstraintDefs=
9514-
lcons(defstring,
9515-
tab->changedConstraintDefs);
9516-
}
9517-
else
9518-
{
9519-
tab->changedConstraintOids=
9520-
lappend_oid(tab->changedConstraintOids,
9521-
foundObject.objectId);
9522-
tab->changedConstraintDefs=
9523-
lappend(tab->changedConstraintDefs,
9524-
defstring);
9525-
}
9499+
tab->changedConstraintOids=
9500+
lappend_oid(tab->changedConstraintOids,
9501+
foundObject.objectId);
9502+
tab->changedConstraintDefs=
9503+
lappend(tab->changedConstraintDefs,
9504+
defstring);
95269505
}
95279506
break;
95289507

@@ -9862,9 +9841,17 @@ static void
98629841
ATPostAlterTypeCleanup(List**wqueue,AlteredTableInfo*tab,LOCKMODElockmode)
98639842
{
98649843
ObjectAddressobj;
9844+
ObjectAddresses*objects;
98659845
ListCell*def_item;
98669846
ListCell*oid_item;
98679847

9848+
/*
9849+
* Collect all the constraints and indexes to drop so we can process them
9850+
* in a single call. That way we don't have to worry about dependencies
9851+
* among them.
9852+
*/
9853+
objects=new_object_addresses();
9854+
98689855
/*
98699856
* Re-parse the index and constraint definitions, and attach them to the
98709857
* appropriate work queue entries. We do this before dropping because in
@@ -9906,6 +9893,9 @@ ATPostAlterTypeCleanup(List **wqueue, AlteredTableInfo *tab, LOCKMODE lockmode)
99069893
conislocal=con->conislocal;
99079894
ReleaseSysCache(tup);
99089895

9896+
ObjectAddressSet(obj,ConstraintRelationId,lfirst_oid(oid_item));
9897+
add_exact_object_address(&obj,objects);
9898+
99099899
/*
99109900
* If the constraint is inherited (only), we don't want to inject a
99119901
* new definition here; it'll get recreated when ATAddCheckConstraint
@@ -9929,31 +9919,18 @@ ATPostAlterTypeCleanup(List **wqueue, AlteredTableInfo *tab, LOCKMODE lockmode)
99299919
ATPostAlterTypeParse(oldId,relid,InvalidOid,
99309920
(char*)lfirst(def_item),
99319921
wqueue,lockmode,tab->rewrite);
9922+
9923+
ObjectAddressSet(obj,RelationRelationId,lfirst_oid(oid_item));
9924+
add_exact_object_address(&obj,objects);
99329925
}
99339926

99349927
/*
9935-
* Now we can drop the existing constraints and indexes --- constraints
9936-
* first, since some of them might depend on the indexes. In fact, we
9937-
* have to delete FOREIGN KEY constraints before UNIQUE constraints, but
9938-
* we already ordered the constraint list to ensure that would happen. It
9939-
* should be okay to use DROP_RESTRICT here, since nothing else should be
9940-
* depending on these objects.
9928+
* It should be okay to use DROP_RESTRICT here, since nothing else should
9929+
* be depending on these objects.
99419930
*/
9942-
foreach(oid_item,tab->changedConstraintOids)
9943-
{
9944-
obj.classId=ConstraintRelationId;
9945-
obj.objectId=lfirst_oid(oid_item);
9946-
obj.objectSubId=0;
9947-
performDeletion(&obj,DROP_RESTRICT,PERFORM_DELETION_INTERNAL);
9948-
}
9931+
performMultipleDeletions(objects,DROP_RESTRICT,PERFORM_DELETION_INTERNAL);
99499932

9950-
foreach(oid_item,tab->changedIndexOids)
9951-
{
9952-
obj.classId=RelationRelationId;
9953-
obj.objectId=lfirst_oid(oid_item);
9954-
obj.objectSubId=0;
9955-
performDeletion(&obj,DROP_RESTRICT,PERFORM_DELETION_INTERNAL);
9956-
}
9933+
free_object_addresses(objects);
99579934

99589935
/*
99599936
* The objects will get recreated during subsequent passes over the work

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

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1518,6 +1518,18 @@ DETAIL: Key (a, b)=(2500, 2502) is still referenced from table "fk_partitioned_
15181518
ALTER TABLE fk_partitioned_fk DROP CONSTRAINT fk_partitioned_fk_a_fkey;
15191519
-- done.
15201520
DROP TABLE fk_notpartitioned_pk, fk_partitioned_fk;
1521+
-- Altering a type referenced by a foreign key needs to drop/recreate the FK.
1522+
-- Ensure that works.
1523+
CREATE TABLE fk_notpartitioned_pk (a INT, PRIMARY KEY(a), CHECK (a > 0));
1524+
CREATE TABLE fk_partitioned_fk (a INT REFERENCES fk_notpartitioned_pk(a) PRIMARY KEY) PARTITION BY RANGE(a);
1525+
CREATE TABLE fk_partitioned_fk_1 PARTITION OF fk_partitioned_fk FOR VALUES FROM (MINVALUE) TO (MAXVALUE);
1526+
INSERT INTO fk_notpartitioned_pk VALUES (1);
1527+
INSERT INTO fk_partitioned_fk VALUES (1);
1528+
ALTER TABLE fk_notpartitioned_pk ALTER COLUMN a TYPE bigint;
1529+
DELETE FROM fk_notpartitioned_pk WHERE a = 1;
1530+
ERROR: update or delete on table "fk_notpartitioned_pk" violates foreign key constraint "fk_partitioned_fk_a_fkey" on table "fk_partitioned_fk"
1531+
DETAIL: Key (a)=(1) is still referenced from table "fk_partitioned_fk".
1532+
DROP TABLE fk_notpartitioned_pk, fk_partitioned_fk;
15211533
-- Test some other exotic foreign key features: MATCH SIMPLE, ON UPDATE/DELETE
15221534
-- actions
15231535
CREATE TABLE fk_notpartitioned_pk (a int, b int, primary key (a, b));

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

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1139,6 +1139,17 @@ ALTER TABLE fk_partitioned_fk DROP CONSTRAINT fk_partitioned_fk_a_fkey;
11391139
-- done.
11401140
DROPTABLE fk_notpartitioned_pk, fk_partitioned_fk;
11411141

1142+
-- Altering a type referenced by a foreign key needs to drop/recreate the FK.
1143+
-- Ensure that works.
1144+
CREATETABLEfk_notpartitioned_pk (aINT,PRIMARY KEY(a),CHECK (a>0));
1145+
CREATETABLEfk_partitioned_fk (aINTREFERENCES fk_notpartitioned_pk(a)PRIMARY KEY) PARTITION BY RANGE(a);
1146+
CREATETABLEfk_partitioned_fk_1 PARTITION OF fk_partitioned_fk FORVALUESFROM (MINVALUE) TO (MAXVALUE);
1147+
INSERT INTO fk_notpartitioned_pkVALUES (1);
1148+
INSERT INTO fk_partitioned_fkVALUES (1);
1149+
ALTERTABLE fk_notpartitioned_pk ALTER COLUMN a TYPEbigint;
1150+
DELETEFROM fk_notpartitioned_pkWHERE a=1;
1151+
DROPTABLE fk_notpartitioned_pk, fk_partitioned_fk;
1152+
11421153
-- Test some other exotic foreign key features: MATCH SIMPLE, ON UPDATE/DELETE
11431154
-- actions
11441155
CREATETABLEfk_notpartitioned_pk (aint, bint,primary key (a, b));

0 commit comments

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