Thread: BUG #1308: Bug with JDBC driver on duplicate

The following bug has been logged online:

Bug reference:      1308
Logged by:          Simon Lesage-Tremblay

Email address:      simonlt@thevco.com

PostgreSQL version: 7.4.2

Operating system:   Linux

Description:        Bug with JDBC driver on duplicate

Details:

I have the version 7.4.2 of Postgres and I use the JDBC driver version
pg74.215.jdbc3 and I run it on JBoss server.

My problem is when I insert a record that have a problem of duplicate key,
my request fall in a frozing mode.

I test my commands with pgadmin and I got a message of duplicate key. So I
supposed that is a problem with the driver.

This is the line that it's supposed to do :

insert INTO data_format_sps(dataformatid, datatype, signed, precision,
scale) VALUES (100,1,false,0,0);
insert INTO data_format_sps(dataformatid, datatype, signed, precision,
scale) VALUES (101,1,true,0,0);
insert INTO data_format_sps(dataformatid, datatype, signed, precision,
scale) VALUES (102,1,true,0,1);
insert INTO data_format_sps(dataformatid, datatype, signed, precision,
scale) VALUES (103,2,false,0,0);
insert INTO data_format_sps(dataformatid, datatype, signed, precision,
scale) VALUES (100,1,false,0,0);

Got this error ERROR:  duplicate key violates unique constraint
"data_format_sps_pkey" with pgadmin.

This is a part of the java code that froze :

try{
   String req = "INSERT INTO data_format_sps (dataformatid, datatype,
signed, precision, scale) VALUES (?,?,?,?,?)";
   PreparedStatement state = con.prepareStatement(req);
   state.setInt(1, in_DataFormatID);
   state.setInt(2, in_DataType);
   state.setBoolean(3, in_Signed);
   state.setInt(4, in_Precision);
   state.setInt(5, in_Scale);
   state.executeUpdate();
}catch(Exception e){
   System.out.println("Got an error " + e.getMessage());
}

This is the structure of my table :

CREATE TABLE public.data_format_sps
(
  dataformatid int4 NOT NULL,
  datatype int4 NOT NULL,
  signed bool NOT NULL,
  precision int4 NOT NULL,
  scale int4 NOT NULL,
  CONSTRAINT data_format_sps_pkey PRIMARY KEY (dataformatid)
) WITH OIDS;

Can you help me? I didn't see fix on that in newer version.

Thank you

Simon Lesage-Tremblay

On Tue, 9 Nov 2004, PostgreSQL Bugs List wrote:

>
> Bug reference:      1308
> Logged by:          Simon Lesage-Tremblay
> Email address:      simonlt@thevco.com
>
> Description:        Bug with JDBC driver on duplicate
>
> Details:
>
> My problem is when I insert a record that have a problem of duplicate key,
> my request fall in a frozing mode.
>
> I test my commands with pgadmin and I got a message of duplicate key. So I
> supposed that is a problem with the driver.
>

This is not a problem with the JDBC driver, but likely a problem of two
concurrent sessions issuing the same insert inside two separate
transactions.  Consider:

Session 1:

CREATE TABLE t(a int primary key);

BEGIN;

INSERT INTO t(a) VALUES(1);

Session 2:

BEGIN;

INSERT INTO t(a) VALUES(1);

Since Session 1 has not committed or rolled back Session 2 cannot tell if
the insert is a duplicate key error or is valid, so it must wait for
Session 1 to do something first.  This is likely the "frozing mode" you
are seeing as the insert waits.

Kris Jurka