Re: Problems with protocol V3 after migration to latest driver

Alexey Yudichev wrote:

>I have recently tried to migrate to pgdev.307.jdbc3 driver and got several problems. The main purpose of upgrade was
toeliminate "idle in transaction" effect, because it caused the value of now() function to return values up to 30
minutesback 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
showed16.0 and more, simple one-row updates by primary key executed for several minutes!. If I switched to my second
clientnode where the new driver was not yet installed, database load was becoming normal. It looks like somehow
statementsexecuted 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