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.