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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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 Content-Type Size
fk_update_elide_check-4.patch text/x-patch 25.9 KB

Responses

Browse pgsql-patches by date

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