Postgres Surprise

I was working with Steve recently on a Postgres-related bug in our test JDBC driver. Alec gave us a Postgres factoid that helped us figure out what was going on. However, I refused to believe him (sorry Alec) until we had written a test to confirm what he was claiming.

The factoid was that when a statement causes an exception (e.g., failing a constraint), then the connection will not process any more commands until you roll back or commit.

We tried a little program to demonstrate the difference between Oracle and Postgres. In pseudocode:

get a connection
set autocommit to false
attempt a statement that will fail
attempt a statement that will pass

Turns out Alec was right. In the Postgres version of the program, the second statement results in:

current transaction is aborted, commands ignored until end of transaction block

In the Oracle version, the second statement succeeds.

I think there are arguments in favour of either behaviour, I was just surprised that there was a difference. (And not that this means anything, but MySQL behaves like Oracle… at least in this respect).

This post says to use a savepoint and to roll back to the savepoint if you want to attempt statements that might fail without losing the entire transaction.

It's only fair to share...
Share on Facebook
Tweet about this on Twitter
Share on LinkedIn

Leave a Reply