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

Commitcd203f3

Browse files
Jan WieckJan Wieck
Jan Wieck
authored and
Jan Wieck
committed
Avoid primary key lookup (and lock) if foreign key does not change
on UPDATE.This get's rid of the long standing annoyance that updating a rowthat has foreign keys locks all the referenced rows even if theforeign key values do not change.The trick is to actually do a check identical to NO ACTION after aneventually done UPDATE in the SET DEFAULT case. Since a SET DEFAULToperation should have moved referencing rows to a new "home", a followingNO ACTION check can only fail if the column defaults of the referencingtable resulted in the key we actually deleted. Thanks to Stephan.Jan
1 parentafe1185 commitcd203f3

File tree

2 files changed

+34
-8
lines changed

2 files changed

+34
-8
lines changed

‎src/backend/utils/adt/ri_triggers.c

Lines changed: 33 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -17,7 +17,7 @@
1717
*
1818
* Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group
1919
*
20-
* $Header: /cvsroot/pgsql/src/backend/utils/adt/ri_triggers.c,v 1.48 2003/03/27 19:25:40 tgl Exp $
20+
* $Header: /cvsroot/pgsql/src/backend/utils/adt/ri_triggers.c,v 1.49 2003/04/07 20:30:38 wieck Exp $
2121
*
2222
* ----------
2323
*/
@@ -395,13 +395,19 @@ RI_FKey_check(PG_FUNCTION_ARGS)
395395
}
396396

397397
/*
398-
* Note: We cannot avoid the check on UPDATE, even if old and new key
399-
* are the same. Otherwise, someone could DELETE the PK that consists
400-
* of the DEFAULT values, and if there are any references, a ON DELETE
401-
* SET DEFAULT action would update the references to exactly these
402-
* values but we wouldn't see that weird case (this is the only place
403-
* to see it).
398+
* No need to check anything if old and new references are the
399+
* same on UPDATE.
404400
*/
401+
if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
402+
{
403+
if (ri_KeysEqual(fk_rel,old_row,new_row,&qkey,
404+
RI_KEYPAIR_FK_IDX))
405+
{
406+
heap_close(pk_rel,RowShareLock);
407+
returnPointerGetDatum(NULL);
408+
}
409+
}
410+
405411
if (SPI_connect()!=SPI_OK_CONNECT)
406412
elog(ERROR,"SPI_connect() failed in RI_FKey_check()");
407413

@@ -2397,6 +2403,16 @@ RI_FKey_setdefault_del(PG_FUNCTION_ARGS)
23972403

23982404
heap_close(fk_rel,RowExclusiveLock);
23992405

2406+
/*
2407+
* In the case we delete the row who's key is equal to the
2408+
* default values AND a referencing row in the foreign key
2409+
* table exists, we would just have updated it to the same
2410+
* values. We need to do another lookup now and in case a
2411+
* reference exists, abort the operation. That is already
2412+
* implemented in the NO ACTION trigger.
2413+
*/
2414+
RI_FKey_noaction_del(fcinfo);
2415+
24002416
returnPointerGetDatum(NULL);
24012417

24022418
/*
@@ -2635,6 +2651,16 @@ RI_FKey_setdefault_upd(PG_FUNCTION_ARGS)
26352651

26362652
heap_close(fk_rel,RowExclusiveLock);
26372653

2654+
/*
2655+
* In the case we updated the row who's key was equal to the
2656+
* default values AND a referencing row in the foreign key
2657+
* table exists, we would just have updated it to the same
2658+
* values. We need to do another lookup now and in case a
2659+
* reference exists, abort the operation. That is already
2660+
* implemented in the NO ACTION trigger.
2661+
*/
2662+
RI_FKey_noaction_upd(fcinfo);
2663+
26382664
returnPointerGetDatum(NULL);
26392665

26402666
/*

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

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -882,7 +882,7 @@ delete from pktable where base1=2;
882882
ERROR: $1 referential integrity violation - key (base1,ptest1)=(2,2) in pktable still referenced from pktable
883883
-- fails (1,1) is being referenced (twice)
884884
update pktable set base1=3 where base1=1;
885-
ERROR: $1 referential integrity violation - key (base2,ptest2)=(1,1)referenced frompktablenot found in pktable
885+
ERROR: $1 referential integrity violation - key (base1,ptest1)=(1,1)inpktablestill referenced from pktable
886886
-- this sequence of two deletes will work, since after the first there will be no (2,*) references
887887
delete from pktable where base2=2;
888888
delete from pktable where base1=2;

0 commit comments

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