To: Michael Fuhr <mike@fuhr.org>
cc: PostgreSQL SQL List <pgsql-sql@postgresql.org>,
	Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
Subject: Re: Foreign key reference counting strategy? 
In-reply-to: <20061014234844.GA20342@winnie.fuhr.org> 
References: <1160828741.28838.9.camel@panoramix>
	<4530EB84.4050904@logix-tt.com>
	<20061014234844.GA20342@winnie.fuhr.org>
Comments: In-reply-to Michael Fuhr <mike@fuhr.org>
	message dated "Sat, 14 Oct 2006 17:48:44 -0600"
Date: Sat, 14 Oct 2006 19:58:06 -0400
Message-ID: <13525.1160870286@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Michael Fuhr <mike@fuhr.org> writes:
> On Sat, Oct 14, 2006 at 03:52:04PM +0200, Markus Schaber wrote:
>> Create an "after delete" trigger on the referencing table that checks
>> whether there still are records with the same key (IF EXISTS()), and
>> deletes the referenced row otherwise.

> In a concurrent environment that delete can fail with a foreign key
> constraint violation because IF EXISTS won't see uncommitted changes
> in other transactions.

No, I don't think so, because the DELETE will already be holding
exclusive lock on the doomed PK row, which any would-be inserters of
matching FK rows will be blocked on.  AFAICS the DELETE should go
through and then the inserters will fail.  Which is not necessarily
better of course ... but if you want logic like this, you probably
should have code to make the FK inserters deal with no-such-key
failures.

			regards, tom lane