0

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?

5

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.
1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.