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
ignoreduntil 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
hadproblems regarding that. 

Thomas