Обсуждение: Problems with protocol V3 after migration to latest driver
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
backthe 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.
=========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)
=========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)
Is there a workaround for Promlems 2-4 other than using protocol V2?
Any comments on Problem 1?
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
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
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
Alexey,
Well considering the javadoc uses it almost on the first page of
prepared statement I guess it's allowed.
Alexey Yudichev wrote:
>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?
>
>
>
Internally oid is an int4. Not sure how that helps you, can you create a
custom type in jboss ?
--dc--
>As for server logs from the problem 1, they are not enabled on production server. Once I have some spare time, I'll
tryto simulate 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
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
> On Fri, 22 Oct 2004, Alexey Yudichev wrote: > > > I have recently tried to migrate to pgdev.307.jdbc3 driver and got > > several problems. > > > > [ setNull(i, Types.BOOLEAN) doesn't work] Fixed. > > [ setNull(i, Types.VARBINARY) doesn't work with compatibleVersion=7.1] Fixed. > > [ AT TIME ZONE INTERVAL ? doesn't work] This looks like a server bug, I've reported it to the -bugs list. It does appear that using " AT TIME ZONE (?::interval) " is a reasonable workaround. I've put up newly built jar files if you'd like to give them a test: http://www.ejurka.com/pgsql/jars/alexey/ Kris Jurka
Thanks a lot for the fixes, will try new jars today, however, this planner problem will anyway prevent me from using
V3.I was wrong, it DOES fix the performance problem if I force V2. So I just added protocolVersion=2 to all of my pool
URLsfor now, will wait for 8.0 server release to use V3 protocol.
Over the weekend I have discovered another problem which only appears when using new 8.0 JDBC drivers, regardless of
protocolversion. The bad thing is that it only appears on ONE of my absolutely identical server installations (same
versionon both - postgresql-server-7.4.5-1PGDG).
The problem is:
Table "public.temp"
Column | Type | Modifiers
--------+------+-----------
c | oid |
Class.forName("org.postgresql.Driver");
Connection c = DriverManager.getConnection("jdbc:postgresql://host/db?compatible=7.1&user=xxx&password=yyy");
c.setAutoCommit(false);
PreparedStatement st = c.prepareStatement("INSERT INTO temp (c) values (?)");
st.setBytes(1, "yahoo".getBytes("UTF-8")); //exception here
st.executeUpdate();
[...]
results in
java.sql.SQLException: ERROR: large object 36376616 does not exist
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1187)
at org.postgresql.core.v3.QueryExecutorImpl.receiveFastpathResult(QueryExecutorImpl.java:409)
at org.postgresql.core.v3.QueryExecutorImpl.fastpathCall(QueryExecutorImpl.java:345)
at org.postgresql.fastpath.Fastpath.fastpath(Fastpath.java:67)
at org.postgresql.fastpath.Fastpath.fastpath(Fastpath.java:106)
at org.postgresql.fastpath.Fastpath.getInteger(Fastpath.java:118)
at org.postgresql.largeobject.LargeObject.<init>(LargeObject.java:89)
at org.postgresql.largeobject.LargeObjectManager.open(LargeObjectManager.java:150)
at org.postgresql.jdbc2.AbstractJdbc2Statement.setBytes(AbstractJdbc2Statement.java:1011)
[...]
I repeat, with 7.4 drivers it works OK and, surprisingly, it works OK even with newest drivers on one of two server
installationsI have.
-----Original Message-----
From: Kris Jurka [mailto:books@ejurka.com]
Sent: Saturday, October 23, 2004 1:12 AM
To: Alexey Yudichev
Cc: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] 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.
> >
> > [ setNull(i, Types.BOOLEAN) doesn't work]
Fixed.
> > [ setNull(i, Types.VARBINARY) doesn't work with compatibleVersion=7.1]
Fixed.
> > [ AT TIME ZONE INTERVAL ? doesn't work]
This looks like a server bug, I've reported it to the -bugs list. It does
appear that using " AT TIME ZONE (?::interval) " is a reasonable
workaround.
I've put up newly built jar files if you'd like to give them a test:
http://www.ejurka.com/pgsql/jars/alexey/
Kris Jurka
On Mon, 25 Oct 2004, Alexey Yudichev wrote:
> java.sql.SQLException: ERROR: large object 36376616 does not exist
> at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1187)
> at org.postgresql.core.v3.QueryExecutorImpl.receiveFastpathResult(QueryExecutorImpl.java:409)
> at org.postgresql.core.v3.QueryExecutorImpl.fastpathCall(QueryExecutorImpl.java:345)
> at org.postgresql.fastpath.Fastpath.fastpath(Fastpath.java:67)
> at org.postgresql.fastpath.Fastpath.fastpath(Fastpath.java:106)
> at org.postgresql.fastpath.Fastpath.getInteger(Fastpath.java:118)
> at org.postgresql.largeobject.LargeObject.<init>(LargeObject.java:89)
> at org.postgresql.largeobject.LargeObjectManager.open(LargeObjectManager.java:150)
> at org.postgresql.jdbc2.AbstractJdbc2Statement.setBytes(AbstractJdbc2Statement.java:1011)
> [...]
>
> I repeat, with 7.4 drivers it works OK and, surprisingly, it works OK
> even with newest drivers on one of two server installations I have.
I can generate a similar failure with the attached test case. The failure
only happens on the second time through and is caused by the fastpath API
not being aware of the transaction state. In the example I've shown the
first time through it does setAutoCommit(false), but that doesn't actually
start a transaction (as it does in the 7.4 series) instead it waits for
the first query to be issued to start the transaction. When using large
objects the large object needs to be created first before the INSERT query
is issued. The first time through it does not fail because the driver
needs to query the backend to get some setup information for large objects
which starts a transaction. The second time through it has this
information cached, so it proceeds to try and create a large object
without ever issuing a BEGIN.
I do notice that my stacktrace is different than yours, showing an error
on LargeObject.write while yours is in LargeObjectManager.open. Let me
fix the problem I found first because there's not much point in further
testing of a known broken area.
Kris Jurka
Exception in thread "main" java.sql.SQLException: ERROR: invalid
large-object descriptor: 0
at
org.postgresql.core.v2.QueryExecutorImpl.receiveErrorMessage(QueryExecutorImpl.java:418)
at
org.postgresql.core.v2.QueryExecutorImpl.receiveFastpathResult(QueryExecutorImpl.java:97)
at
org.postgresql.core.v2.QueryExecutorImpl.fastpathCall(QueryExecutorImpl.java:59)
at org.postgresql.fastpath.Fastpath.fastpath(Fastpath.java:67)
at org.postgresql.fastpath.Fastpath.fastpath(Fastpath.java:106)
at
org.postgresql.largeobject.LargeObject.write(LargeObject.java:192)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.setBytes(AbstractJdbc2Statement.java:1016)
at LOB.main(LOB.java:23)
Вложения
Kris Jurka wrote: > The first time through it does not fail because the driver > needs to query the backend to get some setup information for large objects > which starts a transaction. Sounds like that is (another) bug .. it should be using the QUERY_SUPPRESS_BEGIN flag for driver-generated queries to avoid starting a transaction accidentally. I fixed that in various other places but didn't think to check the LO code. Also, any thoughts on making the LO vs. bytea behaviour a separate option, rather than lumping it in with 7.1 compatibility? It seems quite possible that you might want to use LOs for get/setBytes() but use the most up to date driver behaviour elsewhere. -O
>Also, any thoughts on making the LO vs. bytea behaviour a separate
>option, rather than lumping it in with 7.1 compatibility? It seems quite
>possible that you might want to use LOs for get/setBytes() but use the
>most up to date driver behaviour elsewhere.
Are there any other changes in driver behaviour triggered by compatibility=7.1 other than LO-related ones? If yes, that
wouldbe a good idea to separate them from LO changes, because LOs are the only reason I use compatibility=7.1
parameter.
-----Original Message-----
From: Oliver Jowett [mailto:oliver@opencloud.com]
Sent: Monday, October 25, 2004 12:16 PM
To: Kris Jurka
Cc: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] Problems with protocol V3 after migration to latest driver
Kris Jurka wrote:
> The first time through it does not fail because the driver
> needs to query the backend to get some setup information for large objects
> which starts a transaction.
Sounds like that is (another) bug .. it should be using the
QUERY_SUPPRESS_BEGIN flag for driver-generated queries to avoid starting
a transaction accidentally. I fixed that in various other places but
didn't think to check the LO code.
Also, any thoughts on making the LO vs. bytea behaviour a separate
option, rather than lumping it in with 7.1 compatibility? It seems quite
possible that you might want to use LOs for get/setBytes() but use the
most up to date driver behaviour elsewhere.
-O
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
Alexey Yudichev wrote: >>Also, any thoughts on making the LO vs. bytea behaviour a separate >>option, rather than lumping it in with 7.1 compatibility? It seems quite >>possible that you might want to use LOs for get/setBytes() but use the >>most up to date driver behaviour elsewhere. > > > Are there any other changes in driver behaviour triggered by compatibility=7.1 other than LO-related ones? If yes, thatwould be a good idea to separate them from LO changes, because LOs are the only reason I use compatibility=7.1 parameter. Currently, the only thing affected by the compatible parameter is the LO-vs-bytea issue. But there could be behavioural changes in the future that could be controlled by that same parameter.. as I understand it, it's there to support existing applications during the transition period after some sort of behavioural change in the driver, not to support two different behaviours indefinitely. -O
On Mon, 25 Oct 2004, Kris Jurka wrote: > [ Fastpath code isn't aware of transaction state.] I've fixed the problem I was seeing. Could you try the updated jar files available from http://www.ejurka.com/pgsql/jars/alexey/ and see if the slightly different problem you reported has gone away. Kris Jurka
On Mon, 25 Oct 2004, Oliver Jowett wrote: > Sounds like that is (another) bug .. it should be using the > QUERY_SUPPRESS_BEGIN flag for driver-generated queries to avoid starting > a transaction accidentally. I fixed that in various other places but > didn't think to check the LO code. This might be a little tricky as the large object code uses just the public java.sql.* api. This situation is somewhat analogous to the DatabaseMetaData calls that we decided should start transactions. In any case I'm not sure it really matters. The only time the largeobject api is initialized when a query won't immediately follow is if they are using the LargeObjectManeger directly and not through the compatible=7.1 or getBlob. > Also, any thoughts on making the LO vs. bytea behaviour a separate > option, rather than lumping it in with 7.1 compatibility? It seems quite > possible that you might want to use LOs for get/setBytes() but use the > most up to date driver behaviour elsewhere. Seems like a good idea. Kris Jurka
Kris Jurka wrote: > > On Mon, 25 Oct 2004, Oliver Jowett wrote: > > >>Sounds like that is (another) bug .. it should be using the >>QUERY_SUPPRESS_BEGIN flag for driver-generated queries to avoid starting >>a transaction accidentally. I fixed that in various other places but >>didn't think to check the LO code. > > > This might be a little tricky as the large object code uses just the > public java.sql.* api. This situation is somewhat analogous to the > DatabaseMetaData calls that we decided should start transactions. In any > case I'm not sure it really matters. The only time the largeobject api > is initialized when a query won't immediately follow is if they are using > the LargeObjectManeger directly and not through the compatible=7.1 or > getBlob. I'm not entirely convinced. The DBMD queries are a direct result of an application call. It's reasonable to expect transactional isolation of metadata since you might be executing DDL that affects that metadata. We don't cache the results. In contrast, the LO stuff is one-off lazy initialization that is a side-effect of using the LO interface for the first time. The results are cached. We don't care about transactional isolation of the queries (or cache invalidation) as we are not expecting the LO function definitions to change under us. It seems like the same scenario as when we are filling the datatype<->OID caches in the main driver. That said, as you say it only makes a difference if you're using the LO manager directly, so maybe this isn't so important. -O
No, it is still there with pgdev.307ay2.jdbc3.jar:
java.sql.SQLException: ERROR: large object 36405664 does not exist
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1240)
at org.postgresql.core.v3.QueryExecutorImpl.receiveFastpathResult(QueryExecutorImpl.java:462)
at org.postgresql.core.v3.QueryExecutorImpl.fastpathCall(QueryExecutorImpl.java:398)
at org.postgresql.fastpath.Fastpath.fastpath(Fastpath.java:69)
at org.postgresql.fastpath.Fastpath.fastpath(Fastpath.java:108)
at org.postgresql.fastpath.Fastpath.getInteger(Fastpath.java:120)
at org.postgresql.largeobject.LargeObject.<init>(LargeObject.java:89)
at org.postgresql.largeobject.LargeObjectManager.open(LargeObjectManager.java:167)
at org.postgresql.largeobject.LargeObjectManager.open(LargeObjectManager.java:152)
at org.postgresql.jdbc2.AbstractJdbc2Statement.setBytes(AbstractJdbc2Statement.java:1017)
-----Original Message-----
From: Kris Jurka [mailto:books@ejurka.com]
Sent: Tuesday, October 26, 2004 12:01 AM
To: Alexey Yudichev
Cc: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] Problems with protocol V3 after migration to latest driver
On Mon, 25 Oct 2004, Kris Jurka wrote:
> [ Fastpath code isn't aware of transaction state.]
I've fixed the problem I was seeing. Could you try the updated jar files
available from http://www.ejurka.com/pgsql/jars/alexey/ and see if the
slightly different problem you reported has gone away.
Kris Jurka
On Tue, 26 Oct 2004, Alexey Yudichev wrote: > No, it is still there with pgdev.307ay2.jdbc3.jar: > > java.sql.SQLException: ERROR: large object 36405664 does not exist OK, I've tracked this one down. Some bit shifting code had a typo in it, that only affected sufficiently large oid values. This is why you only saw the error on one machine. Newly updated jar files available again from http://www.ejurka.com/pgsql/jars/alexey/ Kris Jurka
Works perfectly now, thanks. I have also run the full test suite of my largest project with these new drivers and V3 and 7.4.5 server and it showed noproblems. So the planner problem is the only one that remains with V3, is there a way to figure it out or 8.0 server isthe only solution? Is V3 protocol support in 7.4 server considered experimental or is it an issue of JDBC drivers? -----Original Message----- From: Kris Jurka [mailto:books@ejurka.com] Sent: Tuesday, October 26, 2004 10:41 AM To: Alexey Yudichev Cc: pgsql-jdbc@postgresql.org Subject: Re: [JDBC] Problems with protocol V3 after migration to latest driver On Tue, 26 Oct 2004, Alexey Yudichev wrote: > No, it is still there with pgdev.307ay2.jdbc3.jar: > > java.sql.SQLException: ERROR: large object 36405664 does not exist OK, I've tracked this one down. Some bit shifting code had a typo in it, that only affected sufficiently large oid values. This is why you only saw the error on one machine. Newly updated jar files available again from http://www.ejurka.com/pgsql/jars/alexey/ Kris Jurka
Ah, except that thing with "at time zone":
select now() at time zone ('-02:00'::interval);
works OK in psql console;
but with JDBC
select now() at time zone (?::interval)
with setString(1, '-02:00')
gives me
java.sql.SQLException: ERROR: cannot cast type character varying to interval
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1240)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1043)
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)
-----Original Message-----
From: Alexey Yudichev
Sent: Tuesday, October 26, 2004 1:00 PM
To: Kris Jurka
Cc: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] Problems with protocol V3 after migration to latest driver
Works perfectly now, thanks.
I have also run the full test suite of my largest project with these new drivers and V3 and 7.4.5 server and it showed
noproblems. So the planner problem is the only one that remains with V3, is there a way to figure it out or 8.0 server
isthe only solution? Is V3 protocol support in 7.4 server considered experimental or is it an issue of JDBC drivers?
-----Original Message-----
From: Kris Jurka [mailto:books@ejurka.com]
Sent: Tuesday, October 26, 2004 10:41 AM
To: Alexey Yudichev
Cc: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] Problems with protocol V3 after migration to latest driver
On Tue, 26 Oct 2004, Alexey Yudichev wrote:
> No, it is still there with pgdev.307ay2.jdbc3.jar:
>
> java.sql.SQLException: ERROR: large object 36405664 does not exist
OK, I've tracked this one down. Some bit shifting code had a typo in it,
that only affected sufficiently large oid values. This is why you only
saw the error on one machine.
Newly updated jar files available again from
http://www.ejurka.com/pgsql/jars/alexey/
Kris Jurka
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
On Tue, 26 Oct 2004, Alexey Yudichev wrote: > Works perfectly now, thanks. I have also run the full test suite of my > largest project with these new drivers and V3 and 7.4.5 server and it > showed no problems. So the planner problem is the only one that remains > with V3, is there a way to figure it out or 8.0 server is the only > solution? Is V3 protocol support in 7.4 server considered experimental > or is it an issue of JDBC drivers? An 8.0 server is the only solution. The V3 protocol was designed around prepared statements, but in JDBC the use of PreparedStatement isn't always for a true server prepared statements, it is often used just for portability/parameter usage. The 8.0 driver revealed this issue and a solution was put into the 8.0 server, but is not suitable for backpatching to 7.4. Kris Jurka
On Tue, 26 Oct 2004, Alexey Yudichev wrote:
> Ah, except that thing with "at time zone":
>
> select now() at time zone ('-02:00'::interval);
> works OK in psql console;
>
> but with JDBC
> select now() at time zone (?::interval)
> with setString(1, '-02:00')
Right, setString was recently changed from typing things as text to
varchar. This means you need (?::text::interval) now, at least for this
example on 7.4. Another solution would be to use the PGobject type
PGInterval via something like:
SELECT now() AT TIME ZONE ?
setObject(1, new org.postgresql.util.PGInterval("-02:00"));
I actually forget if this will work at the moment, but if not it will
eventually because of a patch from Oliver Jowett. This avoids the pg
specific cast in your sql code, but adds pg specific code at compile time.
I imagine it might be more future proof though.
Kris Jurka