To: Michael Fuhr <mike@fuhr.org>
Cc: Bruno Wolff III <bruno@wolff.to>, Martin Lesser <ml-pgsql@bettercom.de>,
	Stephan Szabo <sszabo@megazone.bigpanda.com>,
	pgsql-performance@postgresql.org
Subject: Re: Effects of cascading references in foreign keys 
In-reply-to: <20051029160527.GA80135@winnie.fuhr.org> 
References: <87acgszihk.fsf@nb-aspire.bettercom.de>
	<20051029142432.GA79557@winnie.fuhr.org>
	<20051029144947.GB21068@wolff.to>
	<20051029160527.GA80135@winnie.fuhr.org>
Comments: In-reply-to Michael Fuhr <mike@fuhr.org>
	message dated "Sat, 29 Oct 2005 10:05:27 -0600"
Date: Sat, 29 Oct 2005 14:01:58 -0400
Message-ID: <11904.1130608918@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Michael Fuhr <mike@fuhr.org> writes:
> On Sat, Oct 29, 2005 at 09:49:47AM -0500, Bruno Wolff III wrote:
>> It looks like this feature was added last May, so I think it only applies
>> to 8.1.

> Earlier versions appear to have at least some kind of optimization.

Yeah.  IIRC, for quite some time we've had tests inside the FK update
triggers to not bother to search the other table if the key value hasn't
changed.  What we did in 8.1 was to push that test further upstream, so
that the trigger event isn't even queued if the key value hasn't
changed.  (This is why you don't see the trigger shown as being called
even once.)

Looking at this, I wonder if there isn't a bug or at least an
inefficiency in 8.1.  The KeysEqual short circuit tests are still there
in ri_triggers.c; aren't they now redundant with the test in triggers.c?
And don't they need to account for the special case mentioned in the
comment in triggers.c, that the RI check must still be done if we are
looking at a row updated by the same transaction that created it?

			regards, tom lane