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 22:10:13 -0400
Message-ID: <14374.1160878213@sss.pgh.pa.us> (raw)
In-Reply-To: <20061015010725.GA21149@winnie.fuhr.org>
References: <1160828741.28838.9.camel@panoramix>
<4530EB84.4050904@logix-tt.com>
<20061014234844.GA20342@winnie.fuhr.org>
<13525.1160870286@sss.pgh.pa.us>
<20061015001126.GA20713@winnie.fuhr.org>
<13667.1160871610@sss.pgh.pa.us>
<20061015010725.GA21149@winnie.fuhr.org>
Michael Fuhr <mike@fuhr.org> writes:
> My intent wasn't to assert that IF EXISTS adds a new way for the
> DELETE to fail. I was just pointing out that the test "if no
> referencing rows exist then delete the referenced row" isn't
> foolproof, viz., the DELETE can fail even though IF EXISTS said
> there were no referencing rows.
Actually, it's me that was confused --- I was thinking in terms of
the original DELETE being done on the PK row, but of course that's
not the case here --- the original DELETE would be on some FK row.
It might be possible to make it work by adding a SELECT FOR UPDATE
that locks the PK row in the trigger before doing the IF EXISTS test,
but I think that that could create deadlock failures. You'd basically
be making the transaction upgrade a share row lock to an exclusive
lock, and that's generally unsafe. If you could ensure that your
trigger runs before the RI integrity trigger, maybe it would work...
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: <14374.1160878213@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