Re: AFTER UPDATE trigger updating other records

[Ian Harding <harding.ian@xxxxxxxxx>]

On Thu, Jan 25, 2018 at 2:33 PM, David G. Johnston <david.g.johnston@xxxxxxxxx> wrote:

On Thu, Jan 25, 2018 at 3:06 PM, Ian Harding <harding.ian@xxxxxxxxx> wrote:

      4 |        3 | Top.Bar.Blah
      5 |        4 | Top.Bar.Blah.Scooby

​​

 

barf$#             UPDATE area SET areapath = (select areapath from area a where areaid = area.parentid) || subpath(areapath, nlevel(areapath) - 1)
barf$#             WHERE OLD.areapath @> areapath;

barf=# update area set parentid = 2 where areaid = 4;

OLD.areapath = Top.Bar.Blah

​When its Top.Bar.Blah.Scooby 's turn to be updated its parent is 4 which has a value of "Top.Bar.Blah" and so nothing happens.  It doesn't matter whether row 4 or row 5 occurs first - when multiple rows are updated your subselect presents the exact same data to each row and it doesn't take into account updates applied to other rows made concurrently.

​Top.Bar.Blah 's parent was changed to 2 so it does go from "Top.Bar.Blah" to "Top.Foo.Blah"

When you then run your update manually row 5 sees the newly committed areapath value for row 4 and now affects the change to "Top.Foo.Blah.Scooby"​

IOW, cascading updates are not possible (regardless of whether you are doing them in a trigger or manually).  If you had a three-deep parent-child hierarchy to change in your example you would have seen that your example script would only have corrected the first two levels.

Probably your trigger will need to capture (SELECT areapath ​.. WHERE id = NEW.parentid) and then use that constant in your SET clause.

​David J.

​

For the record:

CREATE OR REPLACE FUNCTION trig_areapath_u() RETURNS trigger AS
$$
DECLARE
  ltree_parentpath ltree;
  ltree_mypath ltree;
  int_cnt int;
BEGIN
  IF TG_OP = 'UPDATE' THEN
      IF (COALESCE(OLD.parentid,0) != COALESCE(NEW.parentid,0)) THEN
            -- Get the new parent path and save it
            -- Get the old path for this item and save it
            -- Replace the first X elements of the path for this and all
            -- my children with the parent path

            SELECT areapath
                INTO ltree_parentpath
                FROM area
                WHERE areaid = NEW.parentid;

            ltree_mypath := OLD.areapath;

            UPDATE area SET areapath = ltree_parentpath ||
                subpath(areapath, nlevel(ltree_parentpath) )
            WHERE ltree_mypath @> areapath;

            GET DIAGNOSTICS int_cnt = ROW_COUNT;
            RAISE NOTICE 'Rows affected: %', int_cnt;
      END IF;
  END IF;

  RETURN NULL;
END
$$
LANGUAGE 'plpgsql' VOLATILE;