AFTER UPDATE trigger updating other records

From: Ian Harding <harding(dot)ian(at)gmail(dot)com>
To: General PostgreSQL List <pgsql-general(at)postgresql(dot)org>
Subject: AFTER UPDATE trigger updating other records
Date: 2018-01-25 05:45:54
Message-ID: CAMR4UwHzaEWt=A0Mn6+VxX1c__yGNvC8My5PzrDrtFyJ-hON-Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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;

-- 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;

--------->8-----

I know this is simple, but I can't see it.

Thank you!

- Ian

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Thomas Munro 2018-01-25 07:26:00 Re: [PATCH] Logical decoding of TRUNCATE
Previous Message Rob Sargent 2018-01-25 04:58:05 Re: CannotAcquireResourceException in Junit