CODEWITHSUNDEEP
postgresqltestingtransactions
Tarion
Tarion

Reputation: 17134

Can committing an transaction in PostgreSQL fail?

If I execute some SQL inside a transaction successfully, can it happens that the commit will fail? And what are possible causes? Can it fail related to the executed queries, or just due to some DB side issues?

The question comes up because I need to judge if it makes sense to commit transactions inside tests or if it is "safe enough" to just rollback after each test case.

Upvotes: 6

Views: 4868

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324501

If I execute some SQL inside a transaction successfully, can it happens that the commit will fail?

Yes.

And what are possible causes?

  • DEFERRABLE constraints with SET CONSTRAINTS DEFERRED or in a one-statement autocommit transaction. (Can't happen unless you use DEFERRABLE constraints)
  • SERIALIZABLE transaction with serialization failure detected at commit time. (Can't happen unless you use SERIALIZABLE transactions)
  • Asynchronous commit where the DB crashes or is shut down. (Can't happen if synchronous_commit = on, the default)
  • Disk I/O error, filesystem error, etc
  • Out-of-memory error
  • Network error leading to session disconnect after you send the commit but before you get confirmation of success. In this case you don't know for sure if it committed or not.
  • ... probably more

Can it fail related to the executed queries, or just due to some DB side issues?

Either. A serialization failure, for example, is definitely related to the queries run.

If you're using READ COMMITTED isolation with no deferred constraints then commits are only likely to fail due to underlying system errors.

The question comes up because I need to judge if it makes sense to commit transactions inside tests or if it is "safe enough" to just rollback after each test case.

Any sensible test suite has to cover multiple concurrent transactions interacting, committing in different orders, etc.

If all you test is single standalone transactions you're not testing the real system.

So the question is IMO moot, because a decent suite of tests has to commit anyway.

Upvotes: 12

Related Questions