Re: AFTER UPDATE trigger updating other records

On Thu, Jan 25, 2018 at 11:10 AM, David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:

> On Wednesday, January 24, 2018, Ian Harding <harding(dot)ian(at)gmail(dot)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.
>

I added a raise notice that indicated the number of rows affected and it is
the number I expect.

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

It should be updating 2 rows, and it does according to GET DIAGNOSTICS, but
only one row is in fact changed after the trigger is run.

>
> A self-contained example script would help you with isolation and us if
> you still cannot figure it out after writing one.
>
>
I thought I had... I will include the expected output.

> David J.
>
>
barf=# --8<----------
barf=#
barf=# create extension if not exists ltree;
CREATE EXTENSION
barf=#
barf=# create table area (
barf(# areaid serial primary key,
barf(# parentid int null references area (areaid),
barf(# areapath ltree not null unique);
CREATE TABLE
barf=#
barf=# insert into area (areapath) values ('Top');
INSERT 0 1
barf=# insert into area (parentid, areapath) values (1,'Top.Foo');
INSERT 0 1
barf=# insert into area (parentid, areapath) values (1,'Top.Bar');
INSERT 0 1
barf=# insert into area (parentid, areapath) values (3,'Top.Bar.Blah');
INSERT 0 1
barf=# insert into area (parentid, areapath) values
(4,'Top.Bar.Blah.Scooby');
INSERT 0 1
barf=#
barf=# select areaid, parentid, areapath from area order by areapath;
areaid | parentid | areapath
--------+----------+---------------------
1 | | Top
3 | 1 | Top.Bar
4 | 3 | Top.Bar.Blah
5 | 4 | Top.Bar.Blah.Scooby
2 | 1 | Top.Foo
(5 rows)

barf=#
barf=# CREATE OR REPLACE FUNCTION trig_areapath_u() RETURNS trigger AS
barf-# $$
barf$# BEGIN
barf$# IF TG_OP = 'UPDATE' THEN
barf$# IF (COALESCE(OLD.parentid,0) != COALESCE(NEW.parentid,0))
THEN
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$# END IF;
barf$# END IF;
barf$#
barf$# RETURN NULL;
barf$# END
barf$# $$
barf-# LANGUAGE 'plpgsql' VOLATILE;
CREATE FUNCTION
barf=#
barf=# CREATE TRIGGER trig01_update_area_node_path AFTER UPDATE OF parentid
ON area FOR EACH ROW
barf-# EXECUTE PROCEDURE trig_areapath_u();
CREATE TRIGGER
barf=#
barf=# update area set parentid = 2 where areaid = 4;
UPDATE 1
barf=#
barf=# select areaid, parentid, areapath from area order by areapath;
areaid | parentid | areapath
--------+----------+---------------------
1 | | Top
3 | 1 | Top.Bar
5 | 4 | Top.Bar.Blah.Scooby
2 | 1 | Top.Foo
4 | 2 | Top.Foo.Blah
(5 rows)

barf=#
barf=# -- This is not what I expect to see. I have even tried running the
update
barf=# -- unrestricted from within the trigger but I get the same result.
From
barf=# -- outside the trigger I run the update unrestricted...
barf=#
barf=# UPDATE area SET areapath = (select areapath from area a where areaid
= area.parentid) || subpath(areapath, nlevel(areapath) - 1) where parentid
is not null;
UPDATE 4
barf=#
barf=# -- And I see what I expected.
barf=#
barf=# select areaid, parentid, areapath from area order by areapath;
areaid | parentid | areapath
--------+----------+---------------------
1 | | Top
3 | 1 | Top.Bar
2 | 1 | Top.Foo
4 | 2 | Top.Foo.Blah
5 | 4 | Top.Foo.Blah.Scooby
(5 rows)

barf=#
barf=# --------->8-----