Re: BUG #7865: Unexpected error code on insert of duplicate to composite primary key

On 02/11/2013 02:11 PM, Heikki Linnakangas wrote:
> Works for me:
>
> postgres=3D# do $$
> begin
>   insert into example values ('2001-01-01', 'foo', 'bar');
>   insert into example values ('2001-01-01', 'foo', 'bar');
> exception
>   when others then raise notice 'caught %', sqlstate;
> end;
> $$;
> NOTICE:  caught 23505
> DO
>
> How exactly are you seeing the wrong status code? What client are you
> using?

I am calling PostgreSQL JDBC driver through Tomcat 7 Pool manager..
Which could of course scramble the status code report (unlikely, but
possible..)

The driver binary I was using is:  postgresql-9.0-801.jdbc3.jar

Switching to jdbc4 driver binary of otherwise same version makes no
difference.
Neither switching to latest version makes any difference:=20
postgresql-9.2-1002.jdbc4.jar


Java code:

  Connection conn =3D ...
  PreparedStatement ps =3D null;
  try {
     ps =3D conn.prepareStatement("INSERT INTO example(a,b,c)VALUES(?,?,?=
)");
     ps.setTimestamp(1, new Timestamp(1360596352000L));  // fixed value
for demo
     ps.setString(2, "x");
     ps.setString(3, "y");
     int rc =3D ps.executeUpdate();
     conn.commit();
     return true; // commit OK

  } catch (SQLException e) {
      int code =3D e.getErrorCode();
      if (code =3D=3D 20000 // Derby
         || code =3D=3D 23505) {// PostgreSQL, Oracle, ...
         System.out.println("Expected SQL duplicate insert indication
status code: "+code)
     } else {
         System.out.println("Insert into example at "+this.jdbcUrl+
                    " resulted unexpected SQL Exception code: "+
                    code + " " + e.getMessage());
     }
  } finally {
      try {
         if (ps !=3D null)  ps.close();
      } catch (Exception e) { // ignore
      }
  }
  return false;


> - Heikki