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?
Date: Sat, 14 Oct 2006 17:48:44 -0600
Message-ID: <20061014234844.GA20342@winnie.fuhr.org> (raw)
In-Reply-To: <4530EB84.4050904@logix-tt.com>
References: <1160828741.28838.9.camel@panoramix>
<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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mike@fuhr.org, J.Kraaijeveld@Askesis.nl
Subject: Re: Foreign key reference counting strategy?
In-Reply-To: <20061014234844.GA20342@winnie.fuhr.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox