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=# 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:
postgresql-9.2-1002.jdbc4.jar

Java code:

Connection conn = ...
PreparedStatement ps = null;
try {
ps = 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 = ps.executeUpdate();
conn.commit();
return true; // commit OK

} catch (SQLException e) {
int code = e.getErrorCode();
if (code == 20000 // Derby
|| code == 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 != null) ps.close();
} catch (Exception e) { // ignore
}
}
return false;

> - Heikki