Postgres Pro
Downloads
Home   >   mailing lists

Re: AFTER UPDATE trigger updating other records - Mailing list pgsql-general

From Ian Harding
Subject Re: AFTER UPDATE trigger updating other records
Date
Msg-id CAMR4UwH4qVh=vON7rg+fns_erwwMgW0pp8Ls0M9U_eDnnK0Jog@mail.gmail.com
Whole thread Raw
In response to AFTER UPDATE trigger updating other records  ("David G. Johnston" <david.g.johnston@gmail.com>)
Responses Re: AFTER UPDATE trigger updating other records
List pgsql-general
Tree view
On Thu, Jan 25, 2018 at 11:10 AM, David G. Johnston <david.g.johnston@gmail.com> wrote:
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.

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

pgsql-general by date:

Previous
From: Ian Harding
Date:
Subject: Re: AFTER UPDATE trigger updating other records
Next
From: "David G. Johnston"
Date:
Subject: Re: AFTER UPDATE trigger updating other records