From: Tom Lane <tgl@sss.pgh.pa.us>
To: Michael Fuhr <mike@fuhr.org>
Cc: 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 19:58:06 -0400
Message-ID: <13525.1160870286@sss.pgh.pa.us> (raw)
In-Reply-To: <20061014234844.GA20342@winnie.fuhr.org>
References: <1160828741.28838.9.camel@panoramix>
<4530EB84.4050904@logix-tt.com>
<20061014234844.GA20342@winnie.fuhr.org>
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
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: tgl@sss.pgh.pa.us, mike@fuhr.org, J.Kraaijeveld@Askesis.nl
Subject: Re: Foreign key reference counting strategy?
In-Reply-To: <13525.1160870286@sss.pgh.pa.us>
* 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