Re: Problems with protocol V3 after migration to latest driver

On Fri, 22 Oct 2004, Alexey Yudichev wrote:

> I have recently tried to migrate to pgdev.307.jdbc3 driver and got
> several problems.
>
> =========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.

Using the V3 protocol means using server prepared statements. When
planning such a statement a 7.4 server does not use the given
parameter values and uses generic selectivity estimates that can
generate bad plans. 8.0 servers use the given values to generate
an identical plan as running without a server prepared statemnt.
The fact that you claim this was not fixed by using the V2 protocol
with the new driver makes the above explanation invalid, so I
really couldn't tell you what is causing the high load.

> =========Problem 2.
> The following statement
>
> PreparedStatement st = c.prepareStatement("SELECT count(*) FROM
> XXX WHERE ? IS NULL OR animated=?");
> st.setNull(1, Types.BOOLEAN);
> java.sql.SQLException: ERROR: could not determine data type of parameter $1

OK, I see what's going on here. Temporarily using Types.BIT will fix
this, but I'll fix this in the driver.

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

I don't know what the INTERVAL syntax is, but this works for me if you
leave it off and just use AT TIME ZONE ?.

>
> =========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)

Will fix.

Thanks for the testing and the report.

Kris Jurka