Thread: Xn block abort exceptions: BUG+PATCH

I'm using the 7.0.3 release JDBC driver, and observed that when
Postgres aborts the transaction because of a problem within a
transaction block, SQLExceptions are not always thrown from the
driver on further queries within that block.

For example: say I have a table
create table x (pk int4 primary key);

I then start a transaction block and insert two duplicate keys
con.setAutoCommit(false);
Statement s = con.createStatement();
s.executeUpdate("insert into x values (1)"); // works fine
try {
  s.executeUpdate("insert into x values (1)"); // raises a duplicate key
SQLException
} catch (SQLException sqle) { sqle.printStackTrace(); }
s.executeUpdate("insert into x values(2)"); // no exception thrown

Looking into the code, it looked as though the *ABORT STATUS* message
in the protocol was being ignored in the driver. I've attached a patch
(against the 7.0.3 driver) of the changes that made it work better
for me.

Regards,
-kb
--- org/postgresql/Connection.java.orig Sun Feb 11 21:06:19 2001
+++ org/postgresql/Connection.java Sun Feb 11 21:06:29 2001
@@ -358,7 +358,6 @@
        break;
    case 'C': // Command Status
        recv_status = pg_stream.ReceiveString(8192);
-
     // Now handle the update count correctly.
     if(recv_status.startsWith("INSERT") ||
recv_status.startsWith("UPDATE")
|| recv_status.startsWith("DELETE")) {
      try {
@@ -374,6 +373,9 @@
          }
      }
     }
+                                else if (recv_status.startsWith("*ABORT
STATE*")) {
+                                    throw new
PSQLException("postgresql.con.abortedxn");
+                                }
        if (fields != null)
     hfr = true;
        else

--- org/postgresql/errors.properties.orig Tue Apr 25 22:39:32 2000
+++ org/postgresql/errors.properties Sun Feb 11 21:10:48 2001
@@ -20,6 +20,7 @@
 postgresql.con.tuple:Tuple received before MetaData.
 postgresql.con.type:Unknown Response Type {0}
 postgresql.con.user:The user property is missing. It is mandatory.
+postgresql.con.abortedxn:The current transaction has been aborted, no
more
queries are possible
 postgresql.fp.error:FastPath call returned {0}
 postgresql.fp.expint:Fastpath call {0} - No result was returned and we
expected an integer.
 postgresql.fp.protocol:FastPath protocol error: {0}

Quoting KB Sriram <kbs@snapfish.com>:

> I'm using the 7.0.3 release JDBC driver, and observed that when
> Postgres aborts the transaction because of a problem within a
> transaction block, SQLExceptions are not always thrown from the
> driver on further queries within that block.
>
> For example: say I have a table
> create table x (pk int4 primary key);
>
> I then start a transaction block and insert two duplicate keys
> con.setAutoCommit(false);
> Statement s = con.createStatement();
> s.executeUpdate("insert into x values (1)"); // works fine
> try {
>   s.executeUpdate("insert into x values (1)"); // raises a duplicate
> key
> SQLException
> } catch (SQLException sqle) { sqle.printStackTrace(); }
> s.executeUpdate("insert into x values(2)"); // no exception thrown
>
> Looking into the code, it looked as though the *ABORT STATUS* message
> in the protocol was being ignored in the driver. I've attached a patch
> (against the 7.0.3 driver) of the changes that made it work better
> for me.

I'm away from the source at the moment, but I'll check this one out. I'm not
sure but there may have been a fix for this already (can't be certain).

Peter

--
Peter Mount peter@retep.org.uk
PostgreSQL JDBC Driver: http://www.retep.org.uk/postgres/
RetepPDF PDF library for Java: http://www.retep.org.uk/pdf/