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

The following bug has been logged on the website:

Bug reference:      7865
Logged by:          Matti Aarnio
Email address:      matti.aarnio@methics.fi
PostgreSQL version: 9.2.2
Operating system:   Fedora Linux 17/18
Description:        =


A table:

CREATE TABLE example (
   a   TIMESTAMP    NOT NULL,
   b   VARCHAR(256) NOT NULL,
   c   VARCHAR(256) NOT NULL,
   PRIMARY KEY(a,b,c) =

);

Inserting a duplicate record on this is returning an SQL Error, but the
status code is 0 instead of expected 23505.

This used to work fine in 8.x series, but is now causing trouble in 9.1.7,
and 9.2.3.

My application filters by the status code to detect if the issue is really
duplicate value, or some database service error.

In a few cases we want to see "it is duplicate!" in order to allow an
operation elsewhere.

matti.aarnio@methics.fi writes:
> CREATE TABLE example (
>    a   TIMESTAMP    NOT NULL,
>    b   VARCHAR(256) NOT NULL,
>    c   VARCHAR(256) NOT NULL,
>    PRIMARY KEY(a,b,c)
> );

> Inserting a duplicate record on this is returning an SQL Error, but the
> status code is 0 instead of expected 23505.

Works for me:

regression=# CREATE TABLE example (
regression(#    a   TIMESTAMP    NOT NULL,
regression(#    b   VARCHAR(256) NOT NULL,
regression(#    c   VARCHAR(256) NOT NULL,
regression(#    PRIMARY KEY(a,b,c)
regression(# );
CREATE TABLE
regression=# \set VERBOSITY verbose
regression=# insert into example values('today','today','foo');
INSERT 0 1
regression=# insert into example values('today','today','foo');
ERROR:  23505: duplicate key value violates unique constraint "example_pkey"
DETAIL:  Key (a, b, c)=(2013-02-10 00:00:00, today, foo) already exists.
SCHEMA NAME:  public
TABLE NAME:  example
CONSTRAINT NAME:  example_pkey
LOCATION:  _bt_check_unique, nbtinsert.c:398

I'm guessing you have a client-side problem, but since you've said
nothing about what the client-side software is, it's hard to venture
anything more detailed.

            regards, tom lane

On 09.02.2013 22:25, matti.aarnio@methics.fi wrote:
> CREATE TABLE example (
>     a   TIMESTAMP    NOT NULL,
>     b   VARCHAR(256) NOT NULL,
>     c   VARCHAR(256) NOT NULL,
>     PRIMARY KEY(a,b,c)
> );
>
> Inserting a duplicate record on this is returning an SQL Error, but the
> status code is 0 instead of expected 23505.
>
> This used to work fine in 8.x series, but is now causing trouble in 9.1.7,
> and 9.2.3.

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?

- Heikki

On 11.02.2013 17:34, Matti Aarnio wrote:
>    } 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());
>       }

Hmm, looking at the PSQLException source code, I don't think the driver
has ever set the vendor-specific error code that getErrorCode() returns.
I tested the snippet you posted with server 8,4 and 9.2, and with jdbc
driver 8.4 and 8.2, and saw no difference; getErrorCode() always returned 0.

You should be using getSQLState() instead. The "23505" sqlstate is
defined by the SQL standard, so if the other DBMS' you're supporting
follow the spec on that, you won't even need any vendor-specific code there.

- Heikki

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

On 02/11/2013 05:34 PM, Matti Aarnio wrote:
> 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


Catching the SQLException, and printing the backtrace shows:

2013-02-11 17:47:08,559 [http-bio-8080-exec-7] ERROR fi.methics.ExampleDa=
tabase - Insert into example at jdbc:kiuru:pool:example resulted unexpect=
ed SQL Exception code: 0 ERROR: duplicate key value violates unique const=
raint "replay_attack_detector_pkey"
  Detail: Key (a, b, c)=3D(a, b, 2013-02-11 17:47:08.163) already exists.=

org.postgresql.util.PSQLException: ERROR: duplicate key value violates un=
ique constraint "example_pkey"
  Detail: Key (a, b, c)=3D(a, b, 2013-02-11 17:47:08.163) already exists.=

        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(=
QueryExecutorImpl.java:2157)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryE=
xecutorImpl.java:1886)
        at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutor=
Impl.java:255)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJd=
bc2Statement.java:555)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(A=
bstractJdbc2Statement.java:417)
        at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(Abst=
ractJdbc2Statement.java:363)
        at sun.reflect.GeneratedMethodAccessor49.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMeth=
odAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:601)
        at org.apache.tomcat.jdbc.pool.interceptor.AbstractQueryReport$St=
atementProxy.invoke(AbstractQueryReport.java:235)
        at $Proxy7.executeUpdate(Unknown Source)
        at fi.methics.ExampleDatabase.exampleInsert(ExampleDatabase.java:=
123)
=2E...

On 02/11/2013 07:52 PM, Heikki Linnakangas wrote:
> On 11.02.2013 17:34, Matti Aarnio wrote:
>>    } 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());
>>       }
>
> Hmm, looking at the PSQLException source code, I don't think the driver=
 has ever set the vendor-specific error code that getErrorCode() returns.=
 I tested the snippet you posted with server 8,4 and 9.2, and with jdbc d=
river 8.4 and 8.2, and saw no difference; getErrorCode() always returned =
0.
>
> You should be using getSQLState() instead. The "23505" sqlstate is defi=
ned by the SQL standard, so if the other DBMS' you're supporting follow t=
he spec on that, you won't even need any vendor-specific code there.

Indeed..
Going over my codebase I see that this was the only one where the getErro=
rCode() was used in place of getSQLState().
It worked just fine, but was noisy in the logs, thus this bugreport.

[x] User error.

> - Heikki

BR, Matti Aarnio