Re: Problems with protocol V3 after migration to latest driver

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