Skip site navigation (1) Skip section navigation (2)

skip FK trigger on UPDATE

From: Neil Conway <neilc(at)samurai(dot)com>
To: PostgreSQL-patches <pgsql-patches(at)postgresql(dot)org>
Subject: skip FK trigger on UPDATE
Date: 2005-05-29 13:27:33
Message-ID: 4299C345.6060705@samurai.com (view raw or flat)
Thread:
Lists: pgsql-patches
This patch implements an idea discussed on -hackers recently: if an 
UPDATE on a table with a foreign key does not modify any of the table's 
foreign key columns, we can avoid enqueueing the foreign queue check 
trigger.

I basically just moved the logic for the "are the keys equal?" test from 
the FK trigger itself into the code that enqueues the trigger. I then 
removed the keys-are-equal check from the FK trigger. I also had to 
change (somewhat awkwardly) RI_FKey_keyequal_upd() to work for updates 
on either the PK table or the FK table. I also removed the bogus 
TriggerData argument from RI_FKey_keyequal_upd(), since AFAICS it is no 
needed and merely adds confusion.

This patch does cause one change to the regression test output:

*** ./expected/foreign_key.out	Fri May 27 23:58:54 2005
--- ./results/foreign_key.out	Sat May 28 00:46:20 2005
***************
*** 911,918 ****
   DETAIL:  Key (base1,ptest1)=(2,2) is still referenced from table 
"pktable".
   -- fails (1,1) is being referenced (twice)
   update pktable set base1=3 where base1=1;
! ERROR:  insert or update on table "pktable" violates foreign key 
constraint "pktable_base2_fkey"
! DETAIL:  Key (base2,ptest2)=(1,1) is not present in table "pktable".
   -- this sequence of two deletes will work, since after the first 
there will be no (2,*) references
   delete from pktable where base2=2;
   delete from pktable where base1=2;
--- 911,918 ----
   DETAIL:  Key (base1,ptest1)=(2,2) is still referenced from table 
"pktable".
   -- fails (1,1) is being referenced (twice)
   update pktable set base1=3 where base1=1;
! ERROR:  update or delete on "pktable" violates foreign key constraint 
"pktable_base2_fkey" on "pktable"
! DETAIL:  Key (base1,ptest1)=(1,1) is still referenced from table 
"pktable".
   -- this sequence of two deletes will work, since after the first 
there will be no (2,*) references
   delete from pktable where base2=2;
   delete from pktable where base1=2;

I found this a bit strange: on the one hand I think the new error 
message is actually more sensible, but I'm not sure what caused the 
change in behavior. I'll have more of a think about this tomorrow.

-Neil

Attachment: fk_update_elide_check-4.patch
Description: text/x-patch (25.9 KB)

Responses

pgsql-patches by date

Next:From: Bruce MomjianDate: 2005-05-29 13:30:06
Subject: Re: Escape handling in COPY, strings, psql
Previous:From: Peter EisentrautDate: 2005-05-29 11:36:54
Subject: Re: Escape handling in COPY, strings, psql

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group