Re: JDBC behaviour

Craig Ringer schrieb am 20.02.2016 um 11:44:
> Please provide a complete, compileable, self-contained example demonstrating behaviour that
>causes a failure or problem in PgJDBC but works correctly with at least most of:
>
> - MS SQL
> - Oracle
> - DB2
> - Sybase
> - MySQL
>
> including test run output demonstrating the details of what exactly the behaviour of each other implementation is.

Disclaimer: I do agree with you that Postgres behaviour is correct. A transaction either succeeds completely or not at all.

Having said that: Postgres' behaviour *is* unique regarding this.

Consider the following table:

create table x (id integer not null primary key);

The following code will run with Oracle, DB2, SQL Sever, MySQL and Firebird:

---- code start ----

public class TestInsert
{
public static void main(String args[])
throws Exception
{
Connection con = DriverManager.getConnection("...", "...", "...");
con.setAutoCommit(false);

PreparedStatement pstmt = con.prepareStatement("insert into x (id) values (?)");

pstmt.setInt(1, 1);
pstmt.executeUpdate();

try
{
pstmt.setInt(1, 1);
pstmt.executeUpdate();
}
catch (Exception ex)
{
System.out.println("***** Error: " + ex.getMessage());
}

System.out.println("trying second row");

pstmt.setInt(1, 2);
pstmt.executeUpdate();

con.commit();

ResultSet rs = con.createStatement().executeQuery("select count(*) from x");
if (rs.next())
{
int rows = rs.getInt(1);
System.out.println("rows: " + rows);
}
con.close();
}
}

---- code end ----

With Oracle DB2, SQL Server, MySQL and Firebird you'll get something like this
(this is from Oracle, the error message will of course differ for the others)

***** Error: ORA-00001: Unique Constraint (THOMAS.SYS_C0021623) violated
trying second row
rows: 2

With Postgres you get:

***** Error: ERROR: duplicate key value violates unique constraint "x_pkey"
Detail: Key (id)=(1) already exists.
trying second row
Exception in thread "main" org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block

But again: in contrast to the OP I do not consider this a bug, it's just that Postgres behaves differently.

But I understand that it can be a problem when migrating applications that have a sloppy way of dealing with transactions.

I was part in the migration of several projects from Oracle and MySQL to Postgres in the last two years, none of them had problems regarding that.

Thomas