Thread: bug or not? Trigger preventing delete causes circumvention of FK

I wrote a trigger function with the intent of preventing the deletion of a
parent record when a referencing record would not allow it.  However, the
result is that the referencing record stays, but the referenced one is gone,
so that my foreign key constraint is not respected.  The behaviour can be
replicated with the following:

create table parent(id serial primary key);
create table dependent (id integer primary key references parent on delete
cascade);

create or replace function check_delete() returns trigger as $$
BEGIN
  if TG_OP = 'DELETE' then
      raise notice 'preventing delete';
      return null;
  else
      return OLD;
  end if;
END;
$$
language 'plpgsql';

CREATE TRIGGER trig_check_delete BEFORE DELETE ON dependent
FOR EACH ROW EXECUTE PROCEDURE check_delete();

insert into parent values(1);
insert into dependent values(1);
delete from parent;

The record in the dependent table is left behind, while the referenced parent
is gone.  Is this a bug?

I'm using PostgreSQL version 8.0.4 on Linux.

Luca

Luca Pireddu <luca@cs.ualberta.ca> writes:
> I wrote a trigger function with the intent of preventing the deletion of a
> parent record when a referencing record would not allow it.  However, the
> result is that the referencing record stays, but the referenced one is gone,
> so that my foreign key constraint is not respected.

[ shrug... ] So don't do that.  Triggers can break FK constraints in any
number of ways beside this one (eg, changing the data in the record).
If we tried to prevent that I think we'd either waste a lot of cycles or
cripple the trigger feature ... quite possibly both.

            regards, tom lane

On 12/8/2005 9:12 PM, Luca Pireddu wrote:

> I wrote a trigger function with the intent of preventing the deletion of a
> parent record when a referencing record would not allow it.  However, the
> result is that the referencing record stays, but the referenced one is gone,
> so that my foreign key constraint is not respected.  The behaviour can be
> replicated with the following:

You did something else than intended. You prevented deletion of the
referencing (dependent) record. That is where you defined the trigger,
and that is what renders the foreign key constraints DELETE operation
from dependent into a NOP.

I guess that counts more as a pilot error.


Jan

>
> create table parent(id serial primary key);
> create table dependent (id integer primary key references parent on delete
> cascade);
>
> create or replace function check_delete() returns trigger as $$
> BEGIN
>   if TG_OP = 'DELETE' then
>       raise notice 'preventing delete';
>       return null;
>   else
>       return OLD;
>   end if;
> END;
> $$
> language 'plpgsql';
>
> CREATE TRIGGER trig_check_delete BEFORE DELETE ON dependent
> FOR EACH ROW EXECUTE PROCEDURE check_delete();
>
> insert into parent values(1);
> insert into dependent values(1);
> delete from parent;
>
> The record in the dependent table is left behind, while the referenced parent
> is gone.  Is this a bug?
>
> I'm using PostgreSQL version 8.0.4 on Linux.
>
> Luca
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster


--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

On Friday 09 December 2005 09:22, Jan Wieck wrote:
> On 12/8/2005 9:12 PM, Luca Pireddu wrote:
> > I wrote a trigger function with the intent of preventing the deletion of
> > a parent record when a referencing record would not allow it.  However,
> > the result is that the referencing record stays, but the referenced one
> > is gone, so that my foreign key constraint is not respected.  The
> > behaviour can be replicated with the following:
>
> You did something else than intended. You prevented deletion of the
> referencing (dependent) record. That is where you defined the trigger,
> and that is what renders the foreign key constraints DELETE operation
> from dependent into a NOP.
>
> I guess that counts more as a pilot error.
>
>
> Jan
>

Thanks for the clarification.  I was working under the assumption that
preventing the deletion would cause an error to be raised by the FK check.
On a related note, I can achieve my intended behaviour by raising an
exception in the trigger, rather than returning null.

Luca