Postgresql – PostGIS trigger is fired without error, but the action seems not executed

postgispostgresqltrigger

Context

I have set up a trigger on a PostgreSQL (+Postgis) table containing observation points.

This trigger must compute a new geometry for each inserted or updated point. This new geometry must be calculated as the projection of the geometry of the currently inserted feature point onto the nearest line of another table containing line features.

What I've done

The trigger is as follow, it is correctly fired as the EXPLAIN ANALYZE shows after a new insertion:

no errors are thrown when I insert a new point, but the projected geometry column proj_geom stays empty:

CREATE OR REPLACE FUNCTION project_funct()
  RETURNS trigger AS
  $$
  BEGIN
    -- Here I wish I could update several fields (line_id, dist and proj_geom) at the same time
    -- but for the moment I only put the focus on the new geometry proj_geom:
    NEW.projected_geom := ( 
      SELECT sub.proj_geom
      FROM
        ( SELECT
             points.id AS point_id,
             schema.lines.id AS line_id,
             ST_Distance(schema.lines.geom, NEW.geom) AS dist,
             ST_ClosestPoint(schema.lines.geom, NEW.geom) AS proj_geom
          FROM schema.line, points
            -- Something is weird to me there, I should probably not have this WHERE statement here as the 
            -- trigger should be used on a single row at a time, hence with a single ID for each execution:
            WHERE points.id = NEW.id 
            ORDER BY dist
            LIMIT 1
        ) AS sub
    );
      RETURN NEW;
  END
  $$
LANGUAGE 'plpgsql';
CREATE TRIGGER proj_trigger
    -- Here, I don't really know if I must used BEFORE or AFTER, AFTER sounds better to me 
    -- but I may be totally wrong. I also wish I could keep the same code for both UPDATE and
    -- INSERT but this may not be a good idea?:
    BEFORE INSERT OR UPDATE ON points 
    FOR EACH ROW
    EXECUTE PROCEDURE project_funct();

As I discovered triggers yesterday evening, and even if I've done my best, my code is probably full of mistakes.
I also noticed some SQL scripts that I already have and which are actually working, … doesn't work anymore in this fancy(?) way of writing triggers (e.g. I don't know the meaning of the double dollar sign $$).

Notice:
If I take out the inner most SELECT statement, and replace the word NEW by the actual name of the points table, the query, when run directly in pgAdmin with a specified ID such as WHERE points.id = 41 returns the correct results:

In the trigger, my first comment relates to the fact I wish to be able to update at the same time the line_id, dist and proj_geom fields of the table points each time an observation is inserted.

Question

How could I fix my code, at least to be able to get the new geometry?
I'm pretty sure I'm missing some tiny details (probably where I left some comments in the code where I have that actual feeling) but I can't figure them out and I've done too much hit'n'try now with the trigger function (none of them worked as good as the one here above).

Environment

Ubuntu 18.04
PostgreSQL: 10.12
PostGIS: 2.4

Best Answer

In a BEFORE INSERT trigger, the new row has not yet been inserted into the table. So when the query searches for points.id with the new value, it will not find it.

And there is no need to read the points table because you want to access only the new row, and all those values are available in NEW:

      SELECT
         NEW.id AS point_id,
         schema.lines.id AS line_id,
         schema.lines.geom <-> NEW.geom AS dist,
         ST_ClosestPoint(schema.lines.geom, NEW.geom) AS proj_geom
      FROM schema.line
      ORDER BY dist
      LIMIT 1

You need to use a BEFORE trigger to be able to modify the row before it is written into the table.

For a nearest-neighbour search, better use the <-> operator, which is able to use a geometric index on lines.geom.

Related Solutions

Postgresql – How to ensure that in a insert trigger a column gets filled before other column

If I understood correctly, this query will return the streets and counts you are looking for (for all pipelines and streets):

SELECT x.id, y.name, count(x.id) AS cnt
FROM
    (
        SELECT x.id, y.name, ST_Distance(y.shape, x.shape) AS dist
        FROM 
            tableX x
            INNER JOIN tableY AS y ON ST_DWithin(r.shape, loc.shape, 20)
    ) t
GROUP BY x.id, y.name
ORDER BY cnt DESC
FETCH FIRST ROW ONLY
;

Now let's pack it in a trigger procedure:

CREATE or REPLACE function T_function RETURNS trigger AS 
$T_function$
BEGIN
    SELECT name
    INTO NEW.name
    FROM 
        (
            SELECT x.id, y.name, count(x.id) AS cnt
            FROM
                (
                    SELECT x.id, y.name, ST_Distance(y.shape, x.shape) AS dist
                    FROM 
                        tableX x
                        INNER JOIN tableY AS y ON ST_DWithin(r.shape, loc.shape, 20)
                    WHERE x.id = NEW.id -- we count the relevant pipeline only
                ) t
            GROUP BY x.id, y.name
            ORDER BY cnt DESC     -- we don't care the other
            FETCH FIRST ROW ONLY  -- streets in the list
        )
    ;

    RETURN NEW;
END;
$T_function$ 
LANGUAGE plpgsql
;

the line NEW.id := NEW.id did nothing useful
I flattened the innermost subquery into a simple JOIN (no need for the subquery)
to decrease the level of nested parentheses, I used SELECT name INTO NEW.name instead of NEW.id := (...)

How to commit transaction on an after update event trigger

Why do you expect B to be updated, when you inserted a row with a different id? Actually, your INSERT ALL does not insert anything into B.

The above works fine after fixing your trigger and sample data:

create or replace trigger updB  
after update on B  
for each row  
declare  
v varchar2(5);  
begin  
  v := :new.val;
end;  
/

This is how you reference the new val in the trigger, not with a select. Your original trigger would run into the infamous ORA-04091 "table is mutating" error.

SQL> insert all into A values ('a', 'false') into b values ('a', 'false') select * from dual;

2 rows created.

SQL> commit;

Commit complete.

SQL> update A set val = 'true';
Cek cek: false

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from a;

ID         VAL
---------- ----------
a          true

SQL> select * from b;

ID         VAL
---------- ----------
a          true

For the output:

Your updA trigger runs in an autonomous transaction. When you select val from A, your original update is still not finished (commited), you will see the old value. Fixing your trigger for A:

create or replace trigger updA  
after update on A  
for each row  
declare  
pragma autonomous_transaction;  
cek varchar2(5);  
begin  
commit;  
  cek := :new.val;  
dbms_output.put_line('Cek cek: ' || cek);  
update B set val = :new.val where id = :new.id;  
commit;  
end;  
/