Re: AFTER UPDATE trigger updating other records

On Thu, Jan 25, 2018 at 10:51 AM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

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

Yes. I expect it to modify 2 rows and when I include the GET DIAGNOSTICS
int_cnt = ROW_COUNT and raise it as a notice, it does reflect 2 rows
updated. In fact, I took out that filter and replaced it with WHERE
parentid IS NOT NULL all the rows (except the top of the tree) get updated,
just like running the update outside the trigger, but only one row is
actually changed after the trigger returns.

> --------->8-----
>
> I know this is simple, but I can't see it.
>
> Thank you!
>
> - Ian
>

--
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>