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

Commitbf5fe7b

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 parent81f92a5 commitbf5fe7b

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
@@ -7718,6 +7718,24 @@ ATExecDropConstraint(Relation rel, const char *constrName,
77187718

77197719
is_no_inherit_constraint=con->connoinherit;
77207720

7721+
/*
7722+
* If it's a foreign-key constraint, we'd better lock the referenced
7723+
* table and check that that's not in use, just as we've already done
7724+
* for the constrained table (else we might, eg, be dropping a trigger
7725+
* that has unfired events). But we can/must skip that in the
7726+
* self-referential case.
7727+
*/
7728+
if (con->contype==CONSTRAINT_FOREIGN&&
7729+
con->confrelid!=RelationGetRelid(rel))
7730+
{
7731+
Relationfrel;
7732+
7733+
/* Must match lock taken by RemoveTriggerById: */
7734+
frel=heap_open(con->confrelid,AccessExclusiveLock);
7735+
CheckTableNotInUse(frel,"ALTER TABLE");
7736+
heap_close(frel,NoLock);
7737+
}
7738+
77217739
/*
77227740
* Perform the actual constraint deletion
77237741
*/

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

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1401,4 +1401,17 @@ rollback to x;
14011401
commit; -- fail
14021402
ERROR: insert or update on table "fktable2" violates foreign key constraint "fktable2_f1_fkey"
14031403
DETAIL: Key (f1)=(2) is not present in table "pktable2".
1404+
--
1405+
-- Test that we prevent dropping FK constraint with pending trigger events
1406+
--
1407+
begin;
1408+
insert into fktable2 values(2);
1409+
alter table fktable2 drop constraint fktable2_f1_fkey;
1410+
ERROR: cannot ALTER TABLE "fktable2" because it has pending trigger events
1411+
commit;
1412+
begin;
1413+
delete from pktable2 where f1 = 1;
1414+
alter table fktable2 drop constraint fktable2_f1_fkey;
1415+
ERROR: cannot ALTER TABLE "pktable2" because it has pending trigger events
1416+
commit;
14041417
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
@@ -1041,4 +1041,17 @@ delete from fktable2;
10411041
rollback to x;
10421042
commit;-- fail
10431043

1044+
--
1045+
-- Test that we prevent dropping FK constraint with pending trigger events
1046+
--
1047+
begin;
1048+
insert into fktable2values(2);
1049+
altertable fktable2 dropconstraint fktable2_f1_fkey;
1050+
commit;
1051+
1052+
begin;
1053+
deletefrom pktable2where f1=1;
1054+
altertable fktable2 dropconstraint fktable2_f1_fkey;
1055+
commit;
1056+
10441057
droptable pktable2, fktable2;

0 commit comments

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