From: Tom Lane <tgl@sss.pgh.pa.us>
To: Dirk Jagdmann <jagdmann@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: foreign keys with on delete cascade and triggers
Date: Wed, 11 Jan 2006 22:02:21 -0500
Message-ID: <331.1137034941@sss.pgh.pa.us> (raw)
In-Reply-To: <5d0f60990601111456m136aff2se77e354354a1c6aa@mail.gmail.com>
References: <5d0f60990601111456m136aff2se77e354354a1c6aa@mail.gmail.com>
Dirk Jagdmann <jagdmann@gmail.com> writes:
> The idea behind the sample commands below is, that the whole deletion
> should be denied, because a trigger in a cascaded table blocked the
> deletion. The trigger works as expected and prevents rows with a value
> of "5" being deleted from table "b". However if the deletion was
> triggered via the cascaded foreign key (trigger), the deletion in
> table "a" is not rolled back, thus the row with "5" in "a" is lost.
> This of course leaves the database in an inconsistant state, because
> the foreign key in table "b" can no longer be referenced in "a".
> Now I'd like to know if this is a bug in the current form of cascaded
> deletions; or if this is desired behaviour and the oppression of
> deletions via triggers is undefined behaviour in the cascaded case; or
> if this issue just hasn't been addressed yet; or something completly
> differnt.
This is a bug in your trigger design. The database is doing what it's
supposed to do, ie, trying to delete the dependent row. If you turn
that into a no-op, it's your own fault if you don't like the resulting
state.
If you want the whole transaction rolled back, raise an error instead
of returning NULL.
(This has been discussed before ... see the archives.)
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, jagdmann@gmail.com
Subject: Re: foreign keys with on delete cascade and triggers
In-Reply-To: <331.1137034941@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