Re: Problems with protocol V3 after migration to latest driver - Mailing list pgsql-jdbc
From | Dave Cramer |
---|---|
Subject | Re: Problems with protocol V3 after migration to latest driver |
Date | |
Msg-id | 4178F4B4.90404@fastcrypt.com Whole thread Raw |
In response to | Re: Problems with protocol V3 after migration to latest driver (Dave Cramer <pg@fastcrypt.com>) |
List | pgsql-jdbc |
Actually, I mis-spoke, you can have parameters in the where clause. --dave Dave Cramer wrote: > > > Alexey Yudichev wrote: > >> 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. >> >> > > Can we see the server logs for this problem ? > >> =========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) >> >> >> >> > This is not allowed in the spec. > >> =========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@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) >> >> >> > You are stating in the setparameter that the oid type is varbinary, > when it is actually an oid. > >> Is there a workaround for Promlems 2-4 other than using protocol V2? >> Any comments on Problem 1? >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 7: don't forget to increase your free space map settings >> >> >> >> > -- Dave Cramer http://www.postgresintl.com 519 939 0336 ICQ#14675561
pgsql-jdbc by date: