What is the difference between an after update and a before update in PostgreSQL

Question

What is the difference between an after update and a before update in PostgreSQL? I couldn't understand the difference between after update and before update because it looks like the function was always executed before update.

So I made the following example:

I made a function that updates a table when the status is typing but with a delay of 10 seconds.

CREATE OR REPLACE FUNCTION fai_prueba()
  RETURNS trigger AS
$BODY$
begin
    if new.status = 'Typing' then
        update image set status = 'ToTyping', path = 'Real path' from pg_sleep(5) where id = old.id;
    end if;
    return null;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION fai_prueba()
  OWNER TO postgres;

then I have the following trigger

create trigger tai 
after update on image 
for each row execute procedure fai_prueba();

but when I run an UPDATE the query does not end until the delay ends

UPDATE image 
    SET  path='fake path'
       , status= 'Typing' 
WHERE id=5;

>Query returned successfully: 0 rows affected, 10042 ms execution time.

So is it possible that the update query ends before the trigger?

Answer 1

There is one primary difference in a trigger that is AFTER from one that is BEFORE .. but both get executed regardless.

In a BEFORE trigger, the trigger is executed BEFORE the DML statement is executed.. So you have a chance to modify the :NEW (old :OLD in case of DELETE) row BEFORE it is inserted/updated/deleted... But anything you want to do you can do (like check data in a separate table and issue an update/insert/whatever in that table, etc). If, for some reason, the trigger causes an EXCEPTION, then execution ceases and the DML statement is never executed.

In an AFTER trigger, the trigger is executed AFTER the DML statement is executed. You have already lost any ability to modify the :NEW and :OLD records and have it actually mean anything. However, you can still do whatever you want in the trigger. If the trigger causes an exception, for PostgreSQL, this will cause a rollback of the original DML. I do not believe this is true for all RDBMSs - but am having a hard time finding an example right now.

A common use of a BEFORE trigger is to set a timestamp column to "now" before the data has been inserted.

A common use of an AFTER trigger is to populate an audit/history table with the changes.

In either case, you would usually want to issue a ROLLBACK if you find an exception has been raised.