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

Commit05bef7b

Browse files
committed
Check for pending trigger events on far end when dropping an FK constraint.
When dropping a foreign key constraint with ALTER TABLE DROP CONSTRAINT,we refuse the drop if there are any pending trigger events on the namedtable; this ensures that we won't remove the pg_trigger row that will beconsulted by those events. But we should make the same check for thereferenced relation, else we might remove a due-to-be-referenced pg_triggerrow for that relation too, resulting in "could not find trigger NNN" or"relation NNN has no triggers" errors at commit. Per bug #14431 fromBenjie Gillam. Back-patch to all supported branches.Report: <20161124114911.6530.31200@wrigleys.postgresql.org>
1 parent8f67a6c commit05bef7b

File tree

3 files changed

+44
-0
lines changed

3 files changed

+44
-0
lines changed

‎src/backend/commands/tablecmds.c

Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -7058,6 +7058,24 @@ ATExecDropConstraint(Relation rel, const char *constrName,
70587058

70597059
is_no_inherit_constraint=con->connoinherit;
70607060

7061+
/*
7062+
* If it's a foreign-key constraint, we'd better lock the referenced
7063+
* table and check that that's not in use, just as we've already done
7064+
* for the constrained table (else we might, eg, be dropping a trigger
7065+
* that has unfired events). But we can/must skip that in the
7066+
* self-referential case.
7067+
*/
7068+
if (con->contype==CONSTRAINT_FOREIGN&&
7069+
con->confrelid!=RelationGetRelid(rel))
7070+
{
7071+
Relationfrel;
7072+
7073+
/* Must match lock taken by RemoveTriggerById: */
7074+
frel=heap_open(con->confrelid,AccessExclusiveLock);
7075+
CheckTableNotInUse(frel,"ALTER TABLE");
7076+
heap_close(frel,NoLock);
7077+
}
7078+
70617079
/*
70627080
* Perform the actual constraint deletion
70637081
*/

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

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1355,4 +1355,17 @@ rollback to x;
13551355
commit; -- fail
13561356
ERROR: insert or update on table "fktable2" violates foreign key constraint "fktable2_f1_fkey"
13571357
DETAIL: Key (f1)=(2) is not present in table "pktable2".
1358+
--
1359+
-- Test that we prevent dropping FK constraint with pending trigger events
1360+
--
1361+
begin;
1362+
insert into fktable2 values(2);
1363+
alter table fktable2 drop constraint fktable2_f1_fkey;
1364+
ERROR: cannot ALTER TABLE "fktable2" because it has pending trigger events
1365+
commit;
1366+
begin;
1367+
delete from pktable2 where f1 = 1;
1368+
alter table fktable2 drop constraint fktable2_f1_fkey;
1369+
ERROR: cannot ALTER TABLE "pktable2" because it has pending trigger events
1370+
commit;
13581371
drop table pktable2, fktable2;

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

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1004,4 +1004,17 @@ delete from fktable2;
10041004
rollback to x;
10051005
commit;-- fail
10061006

1007+
--
1008+
-- Test that we prevent dropping FK constraint with pending trigger events
1009+
--
1010+
begin;
1011+
insert into fktable2values(2);
1012+
altertable fktable2 dropconstraint fktable2_f1_fkey;
1013+
commit;
1014+
1015+
begin;
1016+
deletefrom pktable2where f1=1;
1017+
altertable fktable2 dropconstraint fktable2_f1_fkey;
1018+
commit;
1019+
10071020
droptable pktable2, fktable2;

0 commit comments

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