Postgresql – Are triggers and their results visible in same order as original operations

plpgsqlpostgresqltrigger

I've created simple trigger

CREATE TABLE data (
    id int,
    v1 text,
    v2 text
);

CREATE TABLE events (
    id serial primary key,
    v1 text
);

CREATE OR REPLACE FUNCTION emit_event_for_v1()
RETURNS trigger AS '
    BEGIN
        IF OLD.v1 != NEW.v1 THEN
            INSERT INTO events (v1)
            VALUES (NEW.v1);
        END IF;
        RETURN NULL;
    END
' LANGUAGE 'plpgsql';

CREATE TRIGGER event_v1 AFTER INSERT OR UPDATE ON data
FOR EACH ROW EXECUTE PROCEDURE emit_event_for_v1();

INSERT INTO data VALUES (1, '', '');

After that, I've launched many clients (25) doing many updates (500 each) on the
data.

UPDATE data SET v1 = $1 WHERE id = 1

with $1 being $WORKER_ID : $ITER_ID. In the end I've verified that last
inserted event (SELECT id, v1 FROM events ORDER BY id DESC) matches current
state of data (SELECT v1 FROM data WHERE id = 1).

I've also checked that id of last event is equal to number of updates
(25 * 500). And it is.

My question is, is this guaranteed to work? Are results of inserts in triggers
always visible in same order as the original update that triggered the trigger?
Or did I just get lucky during my testing (like 50 times in a row)?

Best Answer

Yes, that is guaranteed, because there is no chance that the trigger function for two updates of the same row will be executed at concurrently.

The UPDATE will get an exclusive lock on the row, and the trigger function will run after the update. The transaction (that holds the exclusive lock) will not complete until the trigger has run, so no concurrent UPDATE can get the lock and start running until the previous one is done.

An exception would be if you had defined the sequence with the CACHE clause, so that backends can cache sequence values, but you didn't do that.

Related Solutions

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

Postgresql trigger update whole table after delete

You can use this simpler / cheaper query for your view or CTE or simply a subquery:

SELECT ser_nr, meas_ser, meas_value
     , row_number() OVER (PARTITION BY ser_nr ORDER BY meas_ser DESC) = 1 AS last_meas
FROM   measurements;

SQL Fiddle.

There are no corner case problems with duplicate or NULL values here while you have that PK constraint you added in the fiddle:

ALTER TABLE measurements ADD CONSTRAINT measurements_pk PRIMARY KEY (ser_nr,meas_ser);

The associated index will also help to make it fast if you only need a small selection of ser_no from a big table.

And yes, this dynamic approach has so much less potential for headache (consistency in the face of concurrent write access!) than trying to keep rows in your base table current.

Best Answer

Related Solutions

How to commit transaction on an after update event trigger

Postgresql trigger update whole table after delete

Related Question