Thread: Warning on transaction commit

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

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
>

Greetings,

As far as I know, the implementation of setAutoCommit( true ) is sending an
"END"
to the backend. Since you had just committed before, there is no transaction
taking place - hence the warning.

Setting autocommit to false entails that each commit() will send a
"COMMIT;BEGIN;" to the backend, while each rollback() will send a
"ROLLBACK;BEGIN;".

Regards,

Gabor

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