Postgresql catch rollback when function fails on one of multiple inserts but trigger was executed

Question

I've got plpgsql function that does multiple inserts to multiple tables. I've also trigger that do some stuff f.g notifies some service.

The process looks like this:

Insert into table A
Trigger executed on table A
Function fails on insert into table B

And then transaction is rollbacked and I've got no data in table A but trigger has executed. Furthemore there is no any DELETE on table A.

My trigger and function:

CREATE OR REPLACE FUNCTION some_function() RETURNS trigger AS
$$
BEGIN
    PERFORM pg_notify('pgchannel1', row_to_json(new)::text);
    RETURN NULL;
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER some_trigger
    AFTER INSERT
    ON some_table
    FOR EACH ROW
EXECUTE PROCEDURE some_function();

I would like to have this process like that:

Insert into table A
Insert into table B
Trigger executed on table A / Trigger executed when function is done

How could I catch that transaction was rollbacked and not executing trigger? Is there any way to trigger function status?

Laurenz Albe · Accepted Answer · 2019-07-02 14:17:55Z

1

Yes, you can do that with constraint triggers:

CREATE CONSTRAINT TRIGGER some_trigger
   AFTER INSERT ON some_table
   DEFERRABLE INITIALLY DEFERRED FOR EACH ROW
EXECUTE PROCEDURE some_function();

Such a trigger will fire at the end of the transaction rather than immediately.

answered Jul 2, 2019 at 14:17

Laurenz Albe

257k22 gold badges314 silver badges390 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

MrNetroful Over a year ago

Thats it. There is another question. Is it possible to spy function state or trigger after its execution (done).

Laurenz Albe Over a year ago

I am not sure what you mean.

MrNetroful Over a year ago

I've got function that is executing multiple inserts on multiple tables. Is there any way to trigger function when it finish?

Laurenz Albe Over a year ago

No, a trigger is always defined on one table.

Collectives™ on Stack Overflow

Postgresql catch rollback when function fails on one of multiple inserts but trigger was executed

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related