Re: AFTER UPDATE trigger updating other records

[Ian Harding]

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

> On Thu, Jan 25, 2018 at 3:06 PM, Ian Harding <harding....@gmail.com>
> 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;