Date: Sat, 14 Oct 2006 17:48:44 -0600
From: Michael Fuhr <mike@fuhr.org>
To: PostgreSQL SQL List <pgsql-sql@postgresql.org>
Cc: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
Subject: Re: Foreign key reference counting strategy?
Message-ID: <20061014234844.GA20342@winnie.fuhr.org>
References: <1160828741.28838.9.camel@panoramix>
	<4530EB84.4050904@logix-tt.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <4530EB84.4050904@logix-tt.com>

On Sat, Oct 14, 2006 at 03:52:04PM +0200, Markus Schaber wrote:
> Joost Kraaijeveld wrote:
> > Is there a strategy to implement reference counting for foreign keys so
> > that if the last reference to the key is deleted, the record is deleted
> > also?
> 
> 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.  If changes in another transaction reference
the same key then the delete will block until the other transaction
commits or rolls back; if the other transaction commits then the
delete will fail.  In PL/pgSQL you can trap that failure with an
EXCEPTION clause that catches foreign_key_violation.

-- 
Michael Fuhr