Skip site navigation (1) Skip section navigation (2)

Problems with protocol V3 after migration to latest driver

From: "Alexey Yudichev" <Alexey(at)francoudi(dot)com>
To: <pgsql-jdbc(at)postgresql(dot)org>
Subject: Problems with protocol V3 after migration to latest driver
Date: 2004-10-22 11:22:21
Message-ID: 8BCBF9DB739F034B87FE7C7D30EAE55C03B1E45A@hqex2k.francoudi.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
I have recently tried to migrate to pgdev.307.jdbc3 driver and got several problems. The main purpose of upgrade was to eliminate "idle in transaction" effect, because it caused the value of now() function to return values up to 30 minutes back the actual statement execution (due to connection being "idle in transaction" in the pool).
Server version is 7.4.5 and I am connecting with compatible=7.1 because I use OIDs.
Setting protocolVersion to 2 fixed all the problems 2-4, however I'd like to know what is wrong with V3.

=========Problem 1 (Most severe)
It may seem strange, but once I installed a new driver on production servers, I got database server overload: update showed 16.0 and more, simple one-row updates by primary key executed for several minutes!. If I switched to my second client node where the new driver was not yet installed, database load was becoming normal. It looks like somehow statements executed through the new driver caused a high database server CPU load.

=========Problem 2. 
The following statement  

      PreparedStatement st = c.prepareStatement("SELECT count(*) FROM XXX WHERE ? IS NULL OR animated=?");
      st.setNull(1, Types.BOOLEAN);
      st.setNull(2, Types.BOOLEAN);
      ResultSet rs = st.executeQuery();

fails with V3 throwing an exception 
java.sql.SQLException: ERROR: could not determine data type of parameter $1
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1187)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:990)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:138)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:347)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:294)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:209)

=========Problem 3.
trying to use expression date_trunc('day', m.created AT TIME ZONE INTERVAL ?)
gives ERROR: syntax error at or near "$1"

=========Problem 4
        Table "public.binaryobject"
         Column         | Type | Modifiers
------------------------+------+-----------
 id                     | text | not null
 data                   | oid  |
 sound_previewobject    | text |
 sound_srcobject        | text |
 mmsmessage_messagedata | text |
 object                 | oid  |

Executing SQL: INSERT INTO binaryobject (id, data, object) VALUES (?, ?, ?)
Set parameter: index=1, jdbcType=VARCHAR, value=bo2782808080808080808080808080DB80
Set parameter: index=2, jdbcType=VARBINARY, value=[B(at)4735a0
Set parameter: index=3, jdbcType=VARBINARY, value=NULL (here setNull(3, Types.VARBINARY) is invoked)
java.sql.SQLException: ERROR: column "object" is of type oid but expression is of type bytea
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1187)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:990)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:138)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:347)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:294)
	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:249)

Is there a workaround for Promlems 2-4 other than using protocol V2?
Any comments on Problem 1?

Responses

pgsql-jdbc by date

Next:From: Dave CramerDate: 2004-10-22 11:48:25
Subject: Re: Problems with protocol V3 after migration to latest driver
Previous:From: Kris JurkaDate: 2004-10-21 22:42:17
Subject: Re: [GENERAL] JDBC +CIDR (fwd)

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group