Re: Foreign key reference counting strategy?

From: Michael Fuhr <mike(at)fuhr(dot)org>
To: PostgreSQL SQL List <pgsql-sql(at)postgresql(dot)org>
Cc: Joost Kraaijeveld <J(dot)Kraaijeveld(at)Askesis(dot)nl>
Subject: Re: Foreign key reference counting strategy?
Date: 2006-10-14 23:48:44
Message-ID: 20061014234844.GA20342@winnie.fuhr.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

In response to

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Tom Lane 2006-10-14 23:58:06 Re: Foreign key reference counting strategy?
Previous Message Tom Lane 2006-10-14 23:26:32 Re: Assigning a timestamp without timezone to a timestamp