Re: Trouble with recursive trigger - Mailing list pgsql-general

Justin Hawkins <justin@hawkins.id.au> writes:
> I'm having trouble with the DELETE. When deleting a row three things
> need to happen:

> o recursively DELETE all children posts to preserve data integrity
> o decrement the number of replies of the parent post (if it exists)
> o delete itself

This has a couple of problems:

1. You can't delete a post's children before deleting the post itself,
because of the REFERENCES constraint.  I'm not entirely sure why your
original formulation of the trigger didn't hit that failure, but I sure
hit it while experimenting with alternatives.

2. The reason the UPDATE causes a problem is that it creates row
versions that are newer than the versions the outer DELETE can see.
(Any database changes caused by a function invoked by a query are by
definition later than that query.)  This means that if the outer
DELETE hasn't yet zapped a row that the UPDATE touches, it will fail to
delete that row when it does come to it.

The easiest way to fix #2 is to do the UPDATEs in an AFTER trigger
instead of a BEFORE trigger, and the easiest way to fix #1 is to let the
system do it for you, by using ON DELETE CASCADE instead of a
handwritten trigger.  I got reasonable behavior with this:

---------

CREATE TABLE post (
  id         SERIAL NOT NULL PRIMARY KEY,
  parent     INT REFERENCES post(id) ON DELETE CASCADE,
  replies    INT NOT NULL DEFAULT 0
);

CREATE OR REPLACE FUNCTION post_update_replies() RETURNS TRIGGER AS $function$
  DECLARE iv integer;
  BEGIN
    IF (TG_OP = 'DELETE') THEN
      -- now update the parents replies, if they have any
      IF (OLD.parent IS NOT NULL) THEN
        RAISE NOTICE 'decrementing replies of parent % because of delete of %', OLD.parent, OLD.id;
        UPDATE post SET replies = replies - 1 WHERE id = OLD.parent;
    GET DIAGNOSTICS iv = ROW_COUNT;
    RAISE NOTICE 'decremented % parent rows of %', iv, OLD.id;
      END IF;
      RETURN OLD;
    END IF;
  END;
$function$ LANGUAGE plpgsql;

CREATE TRIGGER post_update_replies AFTER DELETE ON post
    FOR EACH ROW EXECUTE PROCEDURE post_update_replies();

COPY post FROM stdin WITH CSV;
3000,,0
3001,3000,0
3002,3001,0
3003,3002,0
3004,3003,0
3005,3004,0
3006,3005,0
\.

---------

to wit:

regression=# DELETE FROM post WHERE id = 3002;
NOTICE:  decrementing replies of parent 3005 because of delete of 3006
CONTEXT:  SQL statement "DELETE FROM ONLY "public"."post" WHERE "parent" = $1"
SQL statement "DELETE FROM ONLY "public"."post" WHERE "parent" = $1"
SQL statement "DELETE FROM ONLY "public"."post" WHERE "parent" = $1"
SQL statement "DELETE FROM ONLY "public"."post" WHERE "parent" = $1"
NOTICE:  decremented 0 parent rows of 3006
CONTEXT:  SQL statement "DELETE FROM ONLY "public"."post" WHERE "parent" = $1"
SQL statement "DELETE FROM ONLY "public"."post" WHERE "parent" = $1"
SQL statement "DELETE FROM ONLY "public"."post" WHERE "parent" = $1"
SQL statement "DELETE FROM ONLY "public"."post" WHERE "parent" = $1"
NOTICE:  decrementing replies of parent 3004 because of delete of 3005
CONTEXT:  SQL statement "DELETE FROM ONLY "public"."post" WHERE "parent" = $1"
SQL statement "DELETE FROM ONLY "public"."post" WHERE "parent" = $1"
SQL statement "DELETE FROM ONLY "public"."post" WHERE "parent" = $1"
NOTICE:  decremented 0 parent rows of 3005
CONTEXT:  SQL statement "DELETE FROM ONLY "public"."post" WHERE "parent" = $1"
SQL statement "DELETE FROM ONLY "public"."post" WHERE "parent" = $1"
SQL statement "DELETE FROM ONLY "public"."post" WHERE "parent" = $1"
NOTICE:  decrementing replies of parent 3003 because of delete of 3004
CONTEXT:  SQL statement "DELETE FROM ONLY "public"."post" WHERE "parent" = $1"
SQL statement "DELETE FROM ONLY "public"."post" WHERE "parent" = $1"
NOTICE:  decremented 0 parent rows of 3004
CONTEXT:  SQL statement "DELETE FROM ONLY "public"."post" WHERE "parent" = $1"
SQL statement "DELETE FROM ONLY "public"."post" WHERE "parent" = $1"
NOTICE:  decrementing replies of parent 3002 because of delete of 3003
CONTEXT:  SQL statement "DELETE FROM ONLY "public"."post" WHERE "parent" = $1"
NOTICE:  decremented 0 parent rows of 3003
CONTEXT:  SQL statement "DELETE FROM ONLY "public"."post" WHERE "parent" = $1"
NOTICE:  decrementing replies of parent 3001 because of delete of 3002
NOTICE:  decremented 1 parent rows of 3002
DELETE 1
regression=# select * from post;
  id  | parent | replies
------+--------+---------
 3000 |        |       0
 3001 |   3000 |      -1
(2 rows)

regression=#

Notice that most of the UPDATEs report not doing anything, because the
parent row they would need to hit is already gone by the time the AFTER
trigger runs.

            regards, tom lane