Re: autocommit trouble with jdbc on Postgres 7.4

PostgreSQL 7.4 no longer supports server side SET AUTOCOMMIT TO OFF,
thanks to Tom Lane. Problem is my java application server now breaks.
When I try to save a record in the database, I get
"java.sql.SQLException: ERROR: SET AUTOCOMMIT TO OFF is no longer
supported". The code that sends this is jdbc compliant and works with
about a dozen other rdbms including postgresql7.3. Isn't he whole point
of jdbc to make it so clients don't need to be rewritten each
database??? Please don't tell me to change my code code - it isn't mine
and I can't.

Questions:

1) Does this mean that the jdbc driver that ships with pg7.4 isn't up
to speed with the new autocommit change? What about the 7.4 development
version of the driver? Does it properly handle the SQL for the new 7.4
change? I tried it and it also failed, but maybe I installed it
improperly. I simply replaced the existing postgresql.jar file with the
devpgjdbc3-1.jar (renamed as postgresql.jar) and restarted. Didn't
work.

2) is there a version of the jdbc driver that won't send "SET
AUTOCOMMIT TO OFF" to the postgresql7.4 server when I use
Connection.setAutoCommit(false)

3) If there is a driver that works, do I need to do something other
than just replacing the existing postgresql.jar file? I'd assume the
classpath etc would be the same it that case, but I've tried it
building 7.4 --with-java from scratch and also tried the SuSE 7.4 RPM
in addition to replacing the jar file as mentioned. Each fails.

Any clues appreciated. Thanks in advance!

Bob

Bob,

This sounds like a bug, do you have test code you can send me?

Dave
On Sun, 2003-11-30 at 01:13, Bob Cart wrote:
> PostgreSQL 7.4 no longer supports server side SET AUTOCOMMIT TO OFF,
> thanks to Tom Lane. Problem is my java application server now breaks.
> When I try to save a record in the database, I get
> "java.sql.SQLException: ERROR: SET AUTOCOMMIT TO OFF is no longer
> supported". <fontfamily><param>Verdana</param>The code that sends
> this is jdbc compliant and works with about a dozen other rdbms
> including postgresql7.3. Isn't he whole point of jdbc to make it so
> clients don't need to be rewritten each database??? Please don't tell
> me to change my code code - it isn't mine and I can't.</fontfamily>
>
>
> Questions:
>
>
> 1) Does this mean that the jdbc driver that ships with pg7.4 isn't up
> to speed with the new autocommit change? What about the 7.4
> development version of the driver? Does it properly handle the SQL for
> the new 7.4 change? I tried it and it also failed, but maybe I
> installed it improperly. I simply replaced the existing postgresql.jar
> file with the <fontfamily><param>Lucida Grande</param>devpgjdbc3-1.jar
> (renamed as</fontfamily> postgresql.jar) and restarted. Didn't work.
>
>
> 2) is there a version of the jdbc driver that won't send "SET
> AUTOCOMMIT TO OFF" to the postgresql7.4 server when I use
> <fontfamily><param>Verdana</param>Connection.setAutoCommit(false)
>
>
> 3) If there is a driver that works, do I need to do something other
> than just replacing the existing </fontfamily>postgresql.jar file? I'd
> assume the classpath etc would be the same it that case, but I've
> tried it building 7.4 --with-java from scratch and also tried the SuSE
> 7.4 RPM in addition to replacing the jar file as mentioned. Each fails.
>
>
> Any clues appreciated. Thanks in advance!
>
>
> Bob

Thanks for the quick reply. Unfortunately, I don't have a nice package
of test code for you. I'm using a system called Servoy. It is a
java-based GUI forms type development environment. You can download it
from:

http://developer.servoy.com/generic.jsp?taxonomy_id=251

It has the firebird database included so to test with pg, you go to the
edit menu, then preferences, then DB servers to set up the new
connection with the typical jdbc:postgresql://<server>/<database>.

You can then go to the file menu and select 'new solution', give it a
name then select the database you just set up for the form you are
creating. Add a table and columns/fields and you are ready to test. To
get the error condition, just click the add new record button, enter
some data into the field you created, then click add new record again.
That should throw the exception.

Thanks for taking a look. Let me know if there is anything I can do to
assist.

Bob

On Nov 29, 2003, at 10:13 PM, Bob Cart wrote:

> PostgreSQL 7.4 no longer supports server side SET AUTOCOMMIT TO OFF,
> thanks to Tom Lane. Problem is my java application server now breaks.
> When I try to save a record in the database, I get
> "java.sql.SQLException: ERROR: SET AUTOCOMMIT TO OFF is no longer
> supported". The code that sends this is jdbc compliant and works with
> about a dozen other rdbms including postgresql7.3. Isn't he whole
> point of jdbc to make it so clients don't need to be rewritten each
> database??? Please don't tell me to change my code code - it isn't
> mine and I can't.
>
> Questions:
>
> 1) Does this mean that the jdbc driver that ships with pg7.4 isn't up
> to speed with the new autocommit change? What about the 7.4
> development version of the driver? Does it properly handle the SQL for
> the new 7.4 change? I tried it and it also failed, but maybe I
> installed it improperly. I simply replaced the existing postgresql.jar
> file with the devpgjdbc3-1.jar (renamed as postgresql.jar) and
> restarted. Didn't work.
>
> 2) is there a version of the jdbc driver that won't send "SET
> AUTOCOMMIT TO OFF" to the postgresql7.4 server when I use
> Connection.setAutoCommit(false)
>
> 3) If there is a driver that works, do I need to do something other
> than just replacing the existing postgresql.jar file? I'd assume the
> classpath etc would be the same it that case, but I've tried it
> building 7.4 --with-java from scratch and also tried the SuSE 7.4 RPM
> in addition to replacing the jar file as mentioned. Each fails.
>
> Any clues appreciated. Thanks in advance!
>
> Bob

On Sat, Nov 29, 2003 at 10:13:10PM -0800, Bob Cart wrote:
> PostgreSQL 7.4 no longer supports server side SET AUTOCOMMIT TO OFF,
> thanks to Tom Lane. Problem is my java application server now breaks.
> When I try to save a record in the database, I get
> "java.sql.SQLException: ERROR: SET AUTOCOMMIT TO OFF is no longer
> supported". The code that sends this is jdbc compliant and works with
> about a dozen other rdbms including postgresql7.3. Isn't he whole point
> of jdbc to make it so clients don't need to be rewritten each
> database??? Please don't tell me to change my code code - it isn't mine
> and I can't.

AFAIK the driver never generates SET AUTOCOMMIT TO OFF; the only thing it
does with server-side autocommit is to turn it on if the backend defaults to
off (as is possible with a 7.3 server). So I'd hazard a guess that the
problematic query is actually being generated by your application code, not
the JDBC driver; there's not much the driver can do about that case.

Can you provide the full exception & stack trace that's generated, and
ideally a statement trace from the backend?

(I took a quick look at the download URL you mentioned in a later email, but
it's registration-required).

-O

On Nov 30, 2003, at 4:40 PM, Oliver Jowett wrote:
> AFAIK the driver never generates SET AUTOCOMMIT TO OFF; the only thing
> it
> does with server-side autocommit is to turn it on if the backend
> defaults to
> off (as is possible with a 7.3 server). So I'd hazard a guess that the
> problematic query is actually being generated by your application
> code, not
> the JDBC driver; there's not much the driver can do about that case.
>
> Can you provide the full exception & stack trace that's generated, and
> ideally a statement trace from the backend?
>
> (I took a quick look at the download URL you mentioned in a later
> email, but
> it's registration-required).
>
> -O

Right. The driver for 7.4 should not even be able to generate SET
AUTOCOMMIT TO OFF. That's one reason why I think this is a jdbc issue
and not an application issue. The other reason is that the app works
with so many other databases - including postgres 7.3. Also, the
logistics of the release don't make it likely that the driver has been
updated for all the changes that happened late in teh development cycle
of pg7.4. 7.4 was just released a couple of weeks ago. From looking at
various threads out on the net, Tom Lane removed the server side
autocommit pretty recently. Now that the most recent jdbc driver the
7.4 beta was posted back in september, I don't see how it could have
been patched up to support the autocommit change. Now if you are right
and the driver never was able to SET AUTOCOMMIT TO OFF anyway, well we
do have a tricky one. My guess is the application I'm using isn't smart
enough to come up with an error string that says, "Error: SET
AUTOCOMMIT TO OFF no longer supported". That's delivered via the jdbc
driver. Doesn't seem to me that an app should be able to make the drive
throw that even if it tried.

Some brave soul out there needs to dig into that part of the driver
code to see what's up. Go for it Oliver!

Bob

Bob Cart wrote:

> On Nov 30, 2003, at 4:40 PM, Oliver Jowett wrote:
>
>> AFAIK the driver never generates SET AUTOCOMMIT TO OFF; the only
>> thing it
>> does with server-side autocommit is to turn it on if the backend
>> defaults to
>> off (as is possible with a 7.3 server). So I'd hazard a guess that the
>> problematic query is actually being generated by your application
>> code, not
>> the JDBC driver; there's not much the driver can do about that case.
>>
>> Can you provide the full exception & stack trace that's generated, and
>> ideally a statement trace from the backend?
>>
>> (I took a quick look at the download URL you mentioned in a later
>> email, but
>> it's registration-required).
>>
>> -O
>
>
> Right. The driver for 7.4 should not even be able to generate SET
> AUTOCOMMIT TO OFF. That's one reason why I think this is a jdbc issue
> and not an application issue. The other reason is that the app works
> with so many other databases - including postgres 7.3. Also, the
> logistics of the release don't make it likely that the driver has been
> updated for all the changes that happened late in teh development
> cycle of pg7.4. 7.4 was just released a couple of weeks ago. From
> looking at various threads out on the net, Tom Lane removed the server
> side autocommit pretty recently. Now that the most recent jdbc driver
> the 7.4 beta was posted back in september, I don't see how it could
> have been patched up to support the autocommit change. Now if you are
> right and the driver never was able to SET AUTOCOMMIT TO OFF anyway,
> well we do have a tricky one. My guess is the application I'm using
> isn't smart enough to come up with an error string that says, "Error:
> SET AUTOCOMMIT TO OFF no longer supported". That's delivered via the
> jdbc driver. Doesn't seem to me that an app should be able to make the
> drive throw that even if it tried.
>
> Some brave soul out there needs to dig into that part of the driver
> code to see what's up. Go for it Oliver!

A stacktrace is really called for first before asking some soul to
review this. A very reasonable request.

The original report said that this was third party code. Any third
party code can have a statement.execute("SET AUTOCOMMIT OFF")
in their library because they naively implemented their transaction
model. And early postgres versions wouldn't have complained
(though they might not have generated the right results!).

I speak from experience since I was once new enough to JDBC to have done
just this and it ran for a long time before I fixed it.

So a stacktrace will clarify whether the third party code is doing this,
or whether postgresql is doing it,
and if so, where it's being done.

On Sun, Nov 30, 2003 at 06:53:24PM -0800, Bob Cart wrote:

[...]

> Some brave soul out there needs to dig into that part of the driver
> code to see what's up. Go for it Oliver!

Ok. After some digging, the postgresql driver shipped with the app is fairly
old (at least 6 months) and includes code that makes server-side autocommit
reflect jdbc-side autocommit. This doesn't work on a 7.4 server.

Using the 7.4 stable CVS driver everything appears to behave correctly.

So.. upgrade your driver and try again. Copying the new driver jar over and
restarting seemed to be sufficient for the app to pick it up.

-O

Okay, here's the stack trace:

java.sql.SQLException: no transaction in progress
java.sql.SQLException: ERROR: SET AUTOCOMMIT TO OFF is no longer
supported

com.servoy.j2db.persistence.RepositoryException: java.sql.SQLException:
ERROR: SET AUTOCOMMIT TO OFF is no longer supported
ERROR: SET AUTOCOMMIT TO OFF is no longer supported

What does that tell you?

On Nov 30, 2003, at 7:16 PM, Dave Tenny wrote:

> Bob Cart wrote:
>
>> On Nov 30, 2003, at 4:40 PM, Oliver Jowett wrote:
>>
>>> AFAIK the driver never generates SET AUTOCOMMIT TO OFF; the only
>>> thing it
>>> does with server-side autocommit is to turn it on if the backend
>>> defaults to
>>> off (as is possible with a 7.3 server). So I'd hazard a guess that
>>> the
>>> problematic query is actually being generated by your application
>>> code, not
>>> the JDBC driver; there's not much the driver can do about that case.
>>>
>>> Can you provide the full exception & stack trace that's generated,
>>> and
>>> ideally a statement trace from the backend?
>>>
>>> (I took a quick look at the download URL you mentioned in a later
>>> email, but
>>> it's registration-required).
>>>
>>> -O
>>
>>
>> Right. The driver for 7.4 should not even be able to generate SET
>> AUTOCOMMIT TO OFF. That's one reason why I think this is a jdbc issue
>> and not an application issue. The other reason is that the app works
>> with so many other databases - including postgres 7.3. Also, the
>> logistics of the release don't make it likely that the driver has
>> been updated for all the changes that happened late in teh
>> development cycle of pg7.4. 7.4 was just released a couple of weeks
>> ago. From looking at various threads out on the net, Tom Lane removed
>> the server side autocommit pretty recently. Now that the most recent
>> jdbc driver the 7.4 beta was posted back in september, I don't see
>> how it could have been patched up to support the autocommit change.
>> Now if you are right and the driver never was able to SET AUTOCOMMIT
>> TO OFF anyway, well we do have a tricky one. My guess is the
>> application I'm using isn't smart enough to come up with an error
>> string that says, "Error: SET AUTOCOMMIT TO OFF no longer
>> supported". That's delivered via the jdbc driver. Doesn't seem to me
>> that an app should be able to make the drive throw that even if it
>> tried.
>>
>> Some brave soul out there needs to dig into that part of the driver
>> code to see what's up. Go for it Oliver!
>
> A stacktrace is really called for first before asking some soul to
> review this. A very reasonable request.
>
> The original report said that this was third party code. Any third
> party code can have a statement.execute("SET AUTOCOMMIT OFF")
> in their library because they naively implemented their transaction
> model. And early postgres versions wouldn't have complained
> (though they might not have generated the right results!).
> I speak from experience since I was once new enough to JDBC to have
> done just this and it ran for a long time before I fixed it.
>
> So a stacktrace will clarify whether the third party code is doing
> this, or whether postgresql is doing it,
> and if so, where it's being done.
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if
> your
> joining column's datatypes do not match
>

Thank you, Oliver! That was all it took! I really really appreciate
your help. It's tireless folks like you that make learning and using
new technologies so much of a pleasure.

Best wishes,

Bob

On Nov 30, 2003, at 8:16 PM, Oliver Jowett wrote:

> On Sun, Nov 30, 2003 at 08:09:46PM -0800, Bob Cart wrote:
>> Thanks Oliver. I'm not using the driver that ships with the app - at
>> least I don't think I am. I installed the app on one box and built
>> PostgreSQL on another machine --with-java. My app connects to the
>> postgresql server on a seperate box using jdbc, i.e.
>>
>> jdbc:postgresql://server/database
>>
>> Doesn't that mean that the driver in question is the one on the
>> postgresql box and not the one that ships with the application?
>
> It's the .jar on the client (i.e. application) side that's important.
> So you
> need to take the postgresql.jar that was built on your postgresql box
> and
> copy it to the lib/ dir of your servoy install on the app box.
>
> -O
>

Oliver Jowett wrote:

> On Sun, Nov 30, 2003 at 06:53:24PM -0800, Bob Cart wrote:
>
> [...]
>
> > Some brave soul out there needs to dig into that part of the driver
> > code to see what's up. Go for it Oliver!
>
> Ok. After some digging, the postgresql driver shipped with the app is fairly
> old (at least 6 months) and includes code that makes server-side autocommit
> reflect jdbc-side autocommit. This doesn't work on a 7.4 server.
>
> Using the 7.4 stable CVS driver everything appears to behave correctly.
>

Exist any compiled version for this driver ready for download?It is realy
problem.
We also have many systems running old code.

regards,
ivan.

> So.. upgrade your driver and try again. Copying the new driver jar over and
> restarting seemed to be sufficient for the app to pick it up.
>
> -O
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)

Oliver Jowett wrote:

> On Tue, Dec 02, 2003 at 07:05:32AM +0100, pginfo wrote:
> >
> >
> > Oliver Jowett wrote:
> >
> > > On Sun, Nov 30, 2003 at 06:53:24PM -0800, Bob Cart wrote:
> > >
> > > [...]
> > >
> > > > Some brave soul out there needs to dig into that part of the driver
> > > > code to see what's up. Go for it Oliver!
> > >
> > > Ok. After some digging, the postgresql driver shipped with the app is fairly
> > > old (at least 6 months) and includes code that makes server-side autocommit
> > > reflect jdbc-side autocommit. This doesn't work on a 7.4 server.
> > >
> > > Using the 7.4 stable CVS driver everything appears to behave correctly.
> > >
> >
> > Exist any compiled version for this driver ready for download?It is realy
> > problem.
> > We also have many systems running old code.
>
> The 7.4 "development" drivers at http://jdbc.postgresql.org/ appear to be
> recent enough (although the 7.3 "stable" drivers aren't), or you can build
> from the code in the 7.4.0 release (./configure --with-java) if you want to
> build them yourself but not from CVS.
>

Thanks,it is not marked as stable, but I will try to use it.
regards,
ivan.

> -O

On Tue, Dec 02, 2003 at 07:05:32AM +0100, pginfo wrote:
>
>
> Oliver Jowett wrote:
>
> > On Sun, Nov 30, 2003 at 06:53:24PM -0800, Bob Cart wrote:
> >
> > [...]
> >
> > > Some brave soul out there needs to dig into that part of the driver
> > > code to see what's up. Go for it Oliver!
> >
> > Ok. After some digging, the postgresql driver shipped with the app is fairly
> > old (at least 6 months) and includes code that makes server-side autocommit
> > reflect jdbc-side autocommit. This doesn't work on a 7.4 server.
> >
> > Using the 7.4 stable CVS driver everything appears to behave correctly.
> >
>
> Exist any compiled version for this driver ready for download?It is realy
> problem.
> We also have many systems running old code.

The 7.4 "development" drivers at http://jdbc.postgresql.org/ appear to be
recent enough (although the 7.3 "stable" drivers aren't), or you can build
from the code in the 7.4.0 release (./configure --with-java) if you want to
build them yourself but not from CVS.

-O