Thread: After delete trigger problem

After delete trigger problem

From
"Teemu Juntunen"
Date:
Hello,
 
I have a child table with
 
CONSTRAINT fkey FOREIGN KEY (x)  REFERENCES master (x) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE.
 
and
 
CREATE TRIGGER td_y  AFTER DELETE ON chlid  FOR EACH ROW EXECUTE PROCEDURE fn_td_y();
and this trigger refers to the master table...
 
CREATE OR REPLACE FUNCTION fn_td_y() RETURNS trigger AS
$BODY$
DECLARE
  fi integer;
BEGIN
    SELECT i INTO fi FROM master  WHERE x = old.x;
...
 
It seems that SELECT results to null, so the master has already deleted the row. Is this intended and how can I solve this?
 
Regards,
Teemu Juntunen
 

Re: After delete trigger problem

From
Tom Lane
Date:
"Teemu Juntunen" <teemu.juntunen@e-ngine.fi> writes:
> CREATE TRIGGER td_y  AFTER DELETE ON chlid  FOR EACH ROW EXECUTE PROCEDURE fn_td_y();

> It seems that SELECT results to null, so the master has already deleted the row. Is this intended and how can I solve
this?

Your trigger is firing after the RI triggers.  If you want it to fire
before, give it a name that is before them (in ASCII order).

            regards, tom lane

Re: After delete trigger problem

From
Erik Jones
Date:
On Nov 7, 2008, at 10:57 AM, Teemu Juntunen wrote:

> Hello,
>
> I have a child table with
>
> CONSTRAINT fkey FOREIGN KEY (x)  REFERENCES master (x) MATCH SIMPLE
> ON UPDATE CASCADE ON DELETE CASCADE.
>
> and
>
> CREATE TRIGGER td_y  AFTER DELETE ON chlid  FOR EACH ROW EXECUTE
> PROCEDURE fn_td_y();
> and this trigger refers to the master table...
>
> CREATE OR REPLACE FUNCTION fn_td_y() RETURNS trigger AS
> $BODY$
> DECLARE
>   fi integer;
> BEGIN
>     SELECT i INTO fi FROM master  WHERE x = old.x;
> ...
>
> It seems that SELECT results to null, so the master has already
> deleted the row. Is this intended and how can I solve this?

Yes,, that is intended.  An AFTER DELETE statement runs after the
triggering statement has completed and your FOREIGN KEY constraint is
set to ON DELETE CASCADE so by the time the statement completes and
the trigger fires the DELETE has already CASCADEd to the master
table.  As far as how to get around it we'd need to know a little more
about what the trigger is actually supposed to do.

Erik Jones, Database Administrator
Engine Yard
Support, Scalability, Reliability
866.518.9273 x 260
Location: US/Pacific
IRC: mage2k






Re: After delete trigger problem

From
Erik Jones
Date:
On Nov 7, 2008, at 11:24 AM, Erik Jones wrote:

>
> On Nov 7, 2008, at 10:57 AM, Teemu Juntunen wrote:
>
>> Hello,
>>
>> I have a child table with
>>
>> CONSTRAINT fkey FOREIGN KEY (x)  REFERENCES master (x) MATCH SIMPLE
>> ON UPDATE CASCADE ON DELETE CASCADE.
>>
>> and
>>
>> CREATE TRIGGER td_y  AFTER DELETE ON chlid  FOR EACH ROW EXECUTE
>> PROCEDURE fn_td_y();
>> and this trigger refers to the master table...
>>
>> CREATE OR REPLACE FUNCTION fn_td_y() RETURNS trigger AS
>> $BODY$
>> DECLARE
>>  fi integer;
>> BEGIN
>>    SELECT i INTO fi FROM master  WHERE x = old.x;
>> ...
>>
>> It seems that SELECT results to null, so the master has already
>> deleted the row. Is this intended and how can I solve this?
>
> Yes,, that is intended.  An AFTER DELETE statement runs after the
> triggering statement has completed and your FOREIGN KEY constraint
> is set to ON DELETE CASCADE so by the time the statement completes
> and the trigger fires the DELETE has already CASCADEd to the master
> table.  As far as how to get around it we'd need to know a little
> more about what the trigger is actually supposed to do.

Ah, nevermind this.  Tom has just reponded in another reply and
educated me to the fact that fkeys fire in sequence with triggers, I
thought they were separate.

Erik Jones, Database Administrator
Engine Yard
Support, Scalability, Reliability
866.518.9273 x 260
Location: US/Pacific
IRC: mage2k






Re: After delete trigger problem

From
"Teemu Juntunen"
Date:
Hi,

thanks for answers!

I tried to rename the function

ALTER TRIGGER td_y ON child RENAME TO "BTD_Y";

with no help.

Also according to the manual BEFORE DELETE trigger should launch before
casading delete, so I changed the trigger

CREATE TRIGGER "BTD_Y" BEFORE DELETE ON chlid  FOR EACH ROW EXECUTE
PROCEDURE fn_td_y();

with no help. Any other ideas?

Regards,
Teemu Juntunen

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Teemu Juntunen" <teemu.juntunen@e-ngine.fi>
Cc: "PostgreSQL" <pgsql-general@postgresql.org>
Sent: Friday, November 07, 2008 9:23 PM
Subject: Re: [GENERAL] After delete trigger problem


> "Teemu Juntunen" <teemu.juntunen@e-ngine.fi> writes:
>> CREATE TRIGGER td_y  AFTER DELETE ON chlid  FOR EACH ROW EXECUTE
>> PROCEDURE fn_td_y();
>
>> It seems that SELECT results to null, so the master has already deleted
>> the row. Is this intended and how can I solve this?
>
> Your trigger is firing after the RI triggers.  If you want it to fire
> before, give it a name that is before them (in ASCII order).
>
> regards, tom lane


Re: After delete trigger problem

From
Tom Lane
Date:
"Teemu Juntunen" <teemu.juntunen@e-ngine.fi> writes:
> Also according to the manual BEFORE DELETE trigger should launch before
> casading delete, so I changed the trigger

> CREATE TRIGGER "BTD_Y" BEFORE DELETE ON chlid  FOR EACH ROW EXECUTE
> PROCEDURE fn_td_y();

> with no help.

In that case your problem is not about whether you are firing before the
RI action happens; you've got some other bug instead.  It's hard to see
what from the limited details you provided, though.  Can you put
together a complete example?

            regards, tom lane

Re: After delete trigger problem

From
"Teemu Juntunen"
Date:
Hi,

here is a complete example. With my Windows PostgreSQL 8.3.3 installation
this example leads to exception, because master has been deleted before the
child.

Teemu


--DROP TABLE master;
--DROP TABLE child;
--DROP FUNCTION fn_checkmaster()

-- The master table
CREATE TABLE master
(
  foo smallint NOT NULL DEFAULT 0,
  CONSTRAINT master_pkey PRIMARY KEY (foo)
)
WITH (OIDS=FALSE);
ALTER TABLE master OWNER TO postgres;

-- A child table to the master
CREATE TABLE child
(
  foo smallint NOT NULL DEFAULT 0,
  hoo smallint NOT NULL DEFAULT 0,
  CONSTRAINT child_pkey PRIMARY KEY (foo,hoo),
  CONSTRAINT child_foo_fkey FOREIGN KEY (foo)
      REFERENCES master (foo) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE
)
WITH (OIDS=FALSE);
ALTER TABLE tilitysraha OWNER TO postgres;

-- Function which checks the master table
CREATE OR REPLACE FUNCTION fn_checkmaster() RETURNS trigger AS
$BODY$
DECLARE
  fcount integer;
BEGIN
  -- Want to check something from the master table
  SELECT count(*) INTO fcount FROM master WHERE master.foo = old.foo;
  -- Nothing found
  IF fcount = 0 THEN
    RAISE EXCEPTION 'Master not found anymore!';
  END IF;

  RETURN old;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE COST 100;
ALTER FUNCTION fn_checkmaster() OWNER TO postgres;

-- Trigger at the child table
CREATE TRIGGER "AFTER_DELETE_CHILD"
  AFTER DELETE
  ON child
  FOR EACH ROW
  EXECUTE PROCEDURE fn_checkmaster();

-- This example leads to an exception
INSERT INTO master (foo) VALUES (1);
INSERT INTO child (foo,hoo) VALUES (1,1);
DELETE FROM master WHERE foo=1;

----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Teemu Juntunen" <teemu.juntunen@e-ngine.fi>
Cc: "PostgreSQL" <pgsql-general@postgresql.org>
Sent: Saturday, November 08, 2008 7:01 AM
Subject: Re: [GENERAL] After delete trigger problem


> "Teemu Juntunen" <teemu.juntunen@e-ngine.fi> writes:
>> Also according to the manual BEFORE DELETE trigger should launch before
>> casading delete, so I changed the trigger
>
>> CREATE TRIGGER "BTD_Y" BEFORE DELETE ON chlid  FOR EACH ROW EXECUTE
>> PROCEDURE fn_td_y();
>
>> with no help.
>
> In that case your problem is not about whether you are firing before the
> RI action happens; you've got some other bug instead.  It's hard to see
> what from the limited details you provided, though.  Can you put
> together a complete example?
>
> regards, tom lane
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general


Re: After delete trigger problem

From
Tom Lane
Date:
"Teemu Juntunen" <teemu.juntunen@e-ngine.fi> writes:
> -- Trigger at the child table
> CREATE TRIGGER "AFTER_DELETE_CHILD"
>   AFTER DELETE
>   ON child
>   FOR EACH ROW
>   EXECUTE PROCEDURE fn_checkmaster();

> -- This example leads to an exception
> INSERT INTO master (foo) VALUES (1);
> INSERT INTO child (foo,hoo) VALUES (1,1);
> DELETE FROM master WHERE foo=1;

Oh, I see the problem: the trigger's on the wrong table.  What you've
got here is:

* delete a master row

* after that, the FK trigger on the master fires and issues a DELETE
against affected rows of the child table

* this deletes a child row

* after that, your trigger fires

Basically there's no way for a trigger on the child to see the master
row still there, because it's already gone before any action is taken
against the child.  Even a BEFORE DELETE trigger would run too late.

You might be able to do something with a delete trigger on the
master table ...

            regards, tom lane