pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedForeign key reference counting strategy?
8+ messages / 4 participants
[nested] [flat]
* Foreign key reference counting strategy?
@ 2006-10-14 12:25 Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2006-10-14 13:52 ` Re: Foreign key reference counting strategy? Markus Schaber <schabi@logix-tt.com>
0 siblings, 1 reply; 8+ messages in thread
From: Joost Kraaijeveld @ 2006-10-14 12:25 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Foreign key reference counting strategy?
2006-10-14 12:25 Foreign key reference counting strategy? Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
@ 2006-10-14 13:52 ` Markus Schaber <schabi@logix-tt.com>
2006-10-14 23:48 ` Re: Foreign key reference counting strategy? Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 8+ messages in thread
From: Markus Schaber @ 2006-10-14 13:52 UTC (permalink / raw)
To: pgsql-sql; +Cc: Joost Kraaijeveld <J.Kraaijeveld@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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Foreign key reference counting strategy?
2006-10-14 12:25 Foreign key reference counting strategy? Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2006-10-14 13:52 ` Re: Foreign key reference counting strategy? Markus Schaber <schabi@logix-tt.com>
@ 2006-10-14 23:48 ` Michael Fuhr <mike@fuhr.org>
2006-10-14 23:58 ` Re: Foreign key reference counting strategy? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 8+ messages in thread
From: Michael Fuhr @ 2006-10-14 23:48 UTC (permalink / raw)
To: pgsql-sql; +Cc: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Foreign key reference counting strategy?
2006-10-14 12:25 Foreign key reference counting strategy? Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2006-10-14 13:52 ` Re: Foreign key reference counting strategy? Markus Schaber <schabi@logix-tt.com>
2006-10-14 23:48 ` Re: Foreign key reference counting strategy? Michael Fuhr <mike@fuhr.org>
@ 2006-10-14 23:58 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-10-15 00:11 ` Re: Foreign key reference counting strategy? Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 8+ messages in thread
From: Tom Lane @ 2006-10-14 23:58 UTC (permalink / raw)
To: Michael Fuhr <mike@fuhr.org>; +Cc: pgsql-sql; Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Foreign key reference counting strategy?
2006-10-14 12:25 Foreign key reference counting strategy? Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2006-10-14 13:52 ` Re: Foreign key reference counting strategy? Markus Schaber <schabi@logix-tt.com>
2006-10-14 23:48 ` Re: Foreign key reference counting strategy? Michael Fuhr <mike@fuhr.org>
2006-10-14 23:58 ` Re: Foreign key reference counting strategy? Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-10-15 00:11 ` Michael Fuhr <mike@fuhr.org>
2006-10-15 00:20 ` Re: Foreign key reference counting strategy? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 8+ messages in thread
From: Michael Fuhr @ 2006-10-15 00:11 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql; Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Foreign key reference counting strategy?
2006-10-14 12:25 Foreign key reference counting strategy? Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2006-10-14 13:52 ` Re: Foreign key reference counting strategy? Markus Schaber <schabi@logix-tt.com>
2006-10-14 23:48 ` Re: Foreign key reference counting strategy? Michael Fuhr <mike@fuhr.org>
2006-10-14 23:58 ` Re: Foreign key reference counting strategy? Tom Lane <tgl@sss.pgh.pa.us>
2006-10-15 00:11 ` Re: Foreign key reference counting strategy? Michael Fuhr <mike@fuhr.org>
@ 2006-10-15 00:20 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-10-15 01:07 ` Re: Foreign key reference counting strategy? Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 8+ messages in thread
From: Tom Lane @ 2006-10-15 00:20 UTC (permalink / raw)
To: Michael Fuhr <mike@fuhr.org>; +Cc: pgsql-sql; Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Foreign key reference counting strategy?
2006-10-14 12:25 Foreign key reference counting strategy? Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2006-10-14 13:52 ` Re: Foreign key reference counting strategy? Markus Schaber <schabi@logix-tt.com>
2006-10-14 23:48 ` Re: Foreign key reference counting strategy? Michael Fuhr <mike@fuhr.org>
2006-10-14 23:58 ` Re: Foreign key reference counting strategy? Tom Lane <tgl@sss.pgh.pa.us>
2006-10-15 00:11 ` Re: Foreign key reference counting strategy? Michael Fuhr <mike@fuhr.org>
2006-10-15 00:20 ` Re: Foreign key reference counting strategy? Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-10-15 01:07 ` Michael Fuhr <mike@fuhr.org>
2006-10-15 02:10 ` Re: Foreign key reference counting strategy? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 8+ messages in thread
From: Michael Fuhr @ 2006-10-15 01:07 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql; Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Foreign key reference counting strategy?
2006-10-14 12:25 Foreign key reference counting strategy? Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2006-10-14 13:52 ` Re: Foreign key reference counting strategy? Markus Schaber <schabi@logix-tt.com>
2006-10-14 23:48 ` Re: Foreign key reference counting strategy? Michael Fuhr <mike@fuhr.org>
2006-10-14 23:58 ` Re: Foreign key reference counting strategy? Tom Lane <tgl@sss.pgh.pa.us>
2006-10-15 00:11 ` Re: Foreign key reference counting strategy? Michael Fuhr <mike@fuhr.org>
2006-10-15 00:20 ` Re: Foreign key reference counting strategy? Tom Lane <tgl@sss.pgh.pa.us>
2006-10-15 01:07 ` Re: Foreign key reference counting strategy? Michael Fuhr <mike@fuhr.org>
@ 2006-10-15 02:10 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 8+ messages in thread
From: Tom Lane @ 2006-10-15 02:10 UTC (permalink / raw)
To: Michael Fuhr <mike@fuhr.org>; +Cc: pgsql-sql; Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
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
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2006-10-15 02:10 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-10-14 12:25 Foreign key reference counting strategy? Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
2006-10-14 13:52 ` Markus Schaber <schabi@logix-tt.com>
2006-10-14 23:48 ` Michael Fuhr <mike@fuhr.org>
2006-10-14 23:58 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-10-15 00:11 ` Michael Fuhr <mike@fuhr.org>
2006-10-15 00:20 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-10-15 01:07 ` Michael Fuhr <mike@fuhr.org>
2006-10-15 02:10 ` Tom Lane <tgl@sss.pgh.pa.us>
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