Thread: Foreign key reference counting strategy?
Hi, 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? TIA -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 web: www.askesis.nl
Hi, Joost, 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. It won't hurt to have an index on the referencing column, for speed reasons. HTH, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org
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
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
On Sat, Oct 14, 2006 at 07:58:06PM -0400, Tom Lane wrote: > 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. Unless the inserters got there first. I just tested both ways; if the insert acquires the lock first then the delete fails, but if the delete acquires the lock first then the insert fails. -- Michael Fuhr
Michael Fuhr <mike@fuhr.org> writes:
> On Sat, Oct 14, 2006 at 07:58:06PM -0400, Tom Lane wrote:
>> 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.
> Unless the inserters got there first. I just tested both ways; if
> the insert acquires the lock first then the delete fails, but if the
> delete acquires the lock first then the insert fails.
Well, if the inserters get a lock on the PK row before the DELETE does,
then of course. I was just disputing the assertion that doing IF EXISTS
in an after trigger would add a new way for the DELETE to fail. At that
point, any uncommitted inserts must be blocked waiting for the delete.
regards, tom lane
On Sat, Oct 14, 2006 at 08:20:10PM -0400, Tom Lane wrote: > Michael Fuhr <mike@fuhr.org> writes: > > Unless the inserters got there first. I just tested both ways; if > > the insert acquires the lock first then the delete fails, but if the > > delete acquires the lock first then the insert fails. > > Well, if the inserters get a lock on the PK row before the DELETE does, > then of course. I was just disputing the assertion that doing IF EXISTS > in an after trigger would add a new way for the DELETE to fail. 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. -- Michael Fuhr
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