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

--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc