Re: Problems with protocol V3 after migration to latest driver
От | Kris Jurka |
---|---|
Тема | Re: Problems with protocol V3 after migration to latest driver |
Дата | |
Msg-id | Pine.BSO.4.56.0410221455400.25275@leary.csoft.net обсуждение исходный текст |
Ответ на | Problems with protocol V3 after migration to latest driver ("Alexey Yudichev" <Alexey@francoudi.com>) |
Ответы |
Re: Problems with protocol V3 after migration to latest driver
|
Список | pgsql-jdbc |
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@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
В списке pgsql-jdbc по дате отправления: