Re: Problems with protocol V3 after migration to latest driver
От | Alexey Yudichev |
---|---|
Тема | Re: Problems with protocol V3 after migration to latest driver |
Дата | |
Msg-id | 8BCBF9DB739F034B87FE7C7D30EAE55C026AC202@hqex2k.francoudi.com обсуждение исходный текст |
Ответ на | 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 |
So is "SELECT count(*) FROM XXX WHERE ? IS NULL OR animated=?" allowed in the spec or not? is "SELECT date_trunc('day', m.created AT TIME ZONE INTERVAL ?) FROM message m" allowed or not? Both work perfectly with V2 but not with V3. > You are stating in the setparameter that the oid type is varbinary, > when it is actually an oid. So what is the correct JDBC type for oid? NUMERIC? I am using JBoss and java.lang.Object is mapped to VARBINARY and it workswell with protocolVersion=V2 and compatible=7.1, jboss uses ResultSet.setBinaryStream()/getBinaryStream() for that.If I configure the mapping as java.lang.Object->JDBC NUMERIC, then setNull() inside JBoss will probably work fine, butJBoss will not be able to store array of bytes as NUMERIC. Any known workaround for that, apart of using V2 and usingbytea? As for server logs from the problem 1, they are not enabled on production server. Once I have some spare time, I'll try tosimulate it on development server. -----Original Message----- From: Dave Cramer [mailto:pg@fastcrypt.com] Sent: Friday, October 22, 2004 2:53 PM To: pg@fastcrypt.com Cc: Alexey Yudichev; pgsql-jdbc@postgresql.org Subject: Re: [JDBC] 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
В списке pgsql-jdbc по дате отправления:
Предыдущее
От: Dave CramerДата:
Сообщение: Re: Problems with protocol V3 after migration to latest driver
Следующее
От: Dave CramerДата:
Сообщение: Re: Problems with protocol V3 after migration to latest driver