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

Commit4e026b3

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 parent8afb811 commit4e026b3

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

77237723
is_no_inherit_constraint=con->connoinherit;
77247724

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

‎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