Re: Foreign key reference counting strategy? - Mailing list pgsql-sql

From Michael Fuhr
Subject Re: Foreign key reference counting strategy?
Date
Msg-id 20061014234844.GA20342@winnie.fuhr.org
Whole thread Raw
In response to Re: Foreign key reference counting strategy?  (Markus Schaber <schabi@logix-tt.com>)
Responses Re: Foreign key reference counting strategy?  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-sql
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


pgsql-sql by date:

Previous
From: Tom Lane
Date:
Subject: Re: Assigning a timestamp without timezone to a timestamp
Next
From: Tom Lane
Date:
Subject: Re: Foreign key reference counting strategy?