Commitcd203f3

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 commitcd203f3Copy full SHA for cd203f3

File tree

2 files changed

+34

-8

lines changed

src
- backend/utils/adt
  - ri_triggers.c
- test/regress/expected
  - foreign_key.out

2 files changed

+34

-8

lines changed

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

Lines changed: 33 additions & 7 deletions

Original file line number	Diff line number	Diff line change
`@@ -17,7 +17,7 @@`
`17`	`17`	`*`
`18`	`18`	`* Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group`
`19`	`19`	`*`
`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 $`
`21`	`21`	`*`
`22`	`22`	`* ----------`
`23`	`23`	`*/`
`@@ -395,13 +395,19 @@ RI_FKey_check(PG_FUNCTION_ARGS)`
`395`	`395`	`}`
`396`	`396`
`397`	`397`	`/*`
`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.`
`404`	`400`	`*/`
	`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`	`+`
`405`	`411`	`if (SPI_connect()!=SPI_OK_CONNECT)`
`406`	`412`	`elog(ERROR,"SPI_connect() failed in RI_FKey_check()");`
`407`	`413`
`@@ -2397,6 +2403,16 @@ RI_FKey_setdefault_del(PG_FUNCTION_ARGS)`
`2397`	`2403`
`2398`	`2404`	`heap_close(fk_rel,RowExclusiveLock);`
`2399`	`2405`
	`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`	`+`
`2400`	`2416`	`returnPointerGetDatum(NULL);`
`2401`	`2417`
`2402`	`2418`	`/*`
`@@ -2635,6 +2651,16 @@ RI_FKey_setdefault_upd(PG_FUNCTION_ARGS)`
`2635`	`2651`
`2636`	`2652`	`heap_close(fk_rel,RowExclusiveLock);`
`2637`	`2653`
	`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`	`+`
`2638`	`2664`	`returnPointerGetDatum(NULL);`
`2639`	`2665`
`2640`	`2666`	`/*`

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

Lines changed: 1 addition & 1 deletion

Original file line number	Diff line number	Diff line change
`@@ -882,7 +882,7 @@ delete from pktable where base1=2;`
`882`	`882`	`ERROR: $1 referential integrity violation - key (base1,ptest1)=(2,2) in pktable still referenced from pktable`
`883`	`883`	`-- fails (1,1) is being referenced (twice)`
`884`	`884`	`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`
`886`	`886`	`-- this sequence of two deletes will work, since after the first there will be no (2,*) references`
`887`	`887`	`delete from pktable where base2=2;`
`888`	`888`	`delete from pktable where base1=2;`

0 commit comments

Comments

(0)

Movatterモバイル変換

Navigation Menu

Search code, repositories, users, issues, pull requests...

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Uh oh!

Commitcd203f3

File tree

2 files changed

2 files changed

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

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

0 commit comments