Jeremy,
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
>
