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
commit

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 http://archives.postgresql.org/pgsql-jdbc/2006-04/msg00004.php 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 FacebookGoogle+Tweet about this on TwitterShare on LinkedIn

Leave a Reply