How can I trigger an error on transaction commit?

All we need is an easy explanation of the problem, so here it is.

I develop a library that implements transactions in Postgresql, and at my job we had a weird bug in production with it, when transaction failed not on one of the queries itself, but on COMMIT, because of something failing in the triggers. Apparently, my library didn’t handle this situation the right way.

Obviously, I wanted to replicate this bug in my test suit for the library, but I don’t want to bring in all the complicated setup. All the behaviour I need, is that Postgresql has to return an error not to any of the queries inside transaction, but when you try to COMMIT it. What’s the minimal and stable way to achieve it?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

One way is to use deferred constraints.

CREATE TABLE tst(x int CONSTRAINT test_me UNIQUE DEFERRABLE INITIALLY DEFERRED);

begin;

insert into tst values(1);

insert into tst values(1);  -- Note: no error at this point

commit;

The COMMIT fails with:

ERROR:  duplicate key value violates unique constraint "test_me"
DÉTAIL : Key (x)=(1) already exists.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply