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


Mon, 13 Jan 2003 11:35:41 -0800
Jeremy Buchmann <jeremy@wellsgaming.com>
Warning on transaction commit
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
> 




Mon, 13 Jan 2003 20:12:31 -0800
Barry Lind <blind@xythos.com>
Bug: Re: [JDBC] Warning on transaction commit
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
>


Tue, 14 Jan 2003 09:39:52 +0100
"Sziklai Gabor" <gsziklai@ikron.hu>
Re: Warning on transaction commit