This appears to be a bug in the database. I have been able to
reproduce. It appears that the new 'autocommit' functionality in 7.3
has a problem.
The jdbc driver is essentially issuing the following sql in your example:
set autocommit = off; -- result of the setAutoCommit(false) call
delete ...
insert ...
commit;
select 1; commit; set autocommit = on; -- result of setAC(true) call
Note that the last one is one call to the server issuing three sql
statements together. (The reason for the select 1 and the commit is intended to ensure a transaction is in progress before committing it and then turning on autocommit)
If you do the exact same calls in psql it works. But the interesting
thing is that psql will take that last one and break it into three calls
to the server. So if you issue them as one call there is different
behavior than if you issue them in three calls.
So the bug is if a statement that starts a transaction is in the same set of statements as a commit or rollback the commit or rollback will not work correctly. In the example of the problem in the jdbc driver the warning can be ignored, however consider the following example which would be more of a problem:
set autocommit = off;
insert ...; commit;
rollback;
in this case even though the client application issued a commit the commit would be ignored and the insert would never be committed.
thanks,
--Barry
Jeremy Buchmann wrote:
Hi all,
I'm getting a warning message in my logs that looks like this:
WARNING: COMMIT: no transaction in progress
when I run this code:
dbh.setAutoCommit(false);
Statement doEvents = dbh.createStatement();
doEvents.executeUpdate("DELETE FROM ...");
doEvents.executeUpdate("INSERT INTO ...");
dbh.commit();
dbh.setAutoCommit(true);
The statements do what they're supposed to do, but I don't understand why I'm getting that warning message. Anyone know why?
I'm running PostgreSQL 7.3.1 and using the PostgreSQL 7.3 JDBC 2 driver that I just downloaded a couple days ago from jdbc.postgresql.org.
Thanks,
--Jeremy
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
