Thread: AFTER UPDATE trigger updating other records

On 01/24/2018 09:45 PM, Ian Harding wrote:

I have a conditional after update trigger on a table that issues an update statement on the same table.  The trigger does not fire recursively, and I know the rows are being updated, but the update is not happening in the same way the statement does when run from outside the trigger.

--8<----------

create extension if not exists ltree;

create table area (
         areaid serial primary key,
         parentid int null references area (areaid),
         areapath ltree not null unique);

insert into area (areapath) values ('Top');
insert into area (parentid, areapath) values (1,'Top.Foo');
insert into area (parentid, areapath) values (1,'Top.Bar');
insert into area (parentid, areapath) values (3,'Top.Bar.Blah');
insert into area (parentid, areapath) values (4,'Top.Bar.Blah.Scooby');

select areaid, parentid, areapath from area order by areapath;

CREATE OR REPLACE FUNCTION trig_areapath_u() RETURNS trigger AS
$$
BEGIN
   IF TG_OP = 'UPDATE' THEN
         IF (COALESCE(OLD.parentid,0) != COALESCE(NEW.parentid,0)) THEN
             UPDATE area SET areapath = (select areapath from area a where areaid = area.parentid) || subpath(areapath, nlevel(areapath) - 1)
             WHERE OLD.areapath @> areapath;
         END IF;
   END IF;

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

CREATE TRIGGER trig01_update_area_node_path AFTER UPDATE OF parentid ON area FOR EACH ROW
    EXECUTE PROCEDURE trig_areapath_u();

update area set parentid = 2 where areaid = 4;

select areaid, parentid, areapath from area order by areapath;

Was there supposed to be results shown for the above queries?

-- This is not what I expect to see.  I have even tried running the update
-- unrestricted from within the trigger but I get the same result.  From
-- outside the trigger I run the update unrestricted...

UPDATE area SET areapath = (select areapath from area a where areaid = area.parentid) || subpath(areapath, nlevel(areapath) - 1) where parentid is not null;

-- And I see what I expected.

select areaid, parentid, areapath from area order by areapath;

Have you verified that this condition:

WHERE OLD.areapath @> areapath;

is actually being met?

--
Adrian Klaver
adrian.klaver@aklaver.com

On Wednesday, January 24, 2018, Ian Harding <harding.ian@gmail.com> wrote:

-- This is not what I expect to see.  I have even tried running the update

-- unrestricted from within the trigger but I get the same result.  From

-- outside the trigger I run the update unrestricted...

UPDATE area SET areapath = (select areapath from area a where areaid = area.parentid) || subpath(areapath, nlevel(areapath) - 1) where parentid is not null;

-- And I see what I expected.

I'd start thinking that the function that the trigger is executing is not the one that I am editing.  Adding raise notice to the function should give you some confirmation as to what is firing.

Are rows beside the one your are updating not changing or not changing correctly.

A self-contained example script would help you with isolation and us if you still cannot figure it out after writing one.

David J.

      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;

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

​