experiences with autocommit functionality in 7.3
I was spending some time investigating how to fix the jdbc driver to
deal with the autocommit functionality in 7.3. I am trying to come up
with a way of using 'set autocommit = on/off' as a way of implementing
the jdbc symantics for autocommit. The current code just inserts a
'begin' after every commit or rollback when autocommit is turned off in
jdbc.
I can continue to use the old way and just issue a 'set autocommit = on'
at connection initialization, but I wanted to investigate if using 'set
autocommit = off' would be a better implementation.
The problem I am having is finding a way to turn autocommit on or off
without generating warning messages, or without having the change
accidentally rolled back later.
Below is the current behavior (based on a fresh pull from cvs this morning):
Key: ACon = autocommit on
ACoff = autocommit off
NIT = not in transaction
IT = in transaction
IT* = in transaction where a rollback will change autocommit state
Current State Action End State
ACon and NIT set ACon ACon and NIT
set ACoff ACoff and IT*
ACon and IT set ACon ACon and IT
set ACoff ACoff and IT*
ACon and IT* set ACon ACon and IT*
set ACoff ACoff and IT
ACoff and NIT set ACon ACon and NIT
set ACoff ACoff and IT
ACoff and IT set ACon ACon and IT*
set ACoff ACoff and IT
ACoff and IT* set ACon ACon and IT
set ACoff ACoff and IT*
There are two conclusions I have drawn from this:
1) Without the ability for the client to know the current transaction
state it isn't feasible to use set autocommit = on/off in the client.
There will either end up being spurious warning messages about
transaction already in process or no transaction in process, or
situations where a subsequent rollback can undo the change. So I will
stay with the current functionality in the jdbc driver until the FE/BE
protocol provides access to the transaction status.
2) In one place the current functionality doesn't make sense (at least
to me).
ACon and NIT set ACoff ACoff and IT*
If I am running in autocommit mode and I issue a command I expect that
command to be committed. But that is not the case here. I would have
expected the result to be: ACoff and NIT
thanks,
--Barry
Barry Lind <barry@xythos.com> writes:
Below is the current behavior (based on a fresh pull from cvs this morning):
Current State Action End State
ACon and NIT set ACon ACon and NIT
set ACoff ACoff and IT*
Bruce was supposed to fix this. We agreed that a SET command would
never initiate a transaction block on its own. Looks like it's not
there yet --- but IMHO the behavior should be
ACon and NIT set ACon ACon and NIT
set ACoff ACoff and NIT
ACon and IT set ACon ACon and IT
set ACoff ACoff and IT*
ACon and IT* set ACon ACon and IT*
set ACoff ACoff and IT
ACoff and NIT set ACon ACon and NIT
set ACoff ACoff and NIT
ACoff and IT set ACon ACon and IT*
set ACoff ACoff and IT
ACoff and IT* set ACon ACon and IT
set ACoff ACoff and IT*
Will that resolve your concern?
regards, tom lane
I said:
Bruce was supposed to fix this. We agreed that a SET command would
never initiate a transaction block on its own. Looks like it's not
there yet ---
Now it is. Give it another try ...
regards, tom lane
Tom Lane wrote:
Barry Lind <barry@xythos.com> writes:
Below is the current behavior (based on a fresh pull from cvs this morning):
Current State Action End State
ACon and NIT set ACon ACon and NIT
set ACoff ACoff and IT*Bruce was supposed to fix this. We agreed that a SET command would
never initiate a transaction block on its own. Looks like it's not
there yet --- but IMHO the behavior should be
Well, I thought I did it, and it did work on my limited number of test
cases. Seems you got it fully working.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073Bruce Momjian <pgman@candle.pha.pa.us> writes:
Well, I thought I did it, and it did work on my limited number of test
cases. Seems you got it fully working.
Actually, it failed for me (and evidently for Barry) on exactly the test
case you posted along with the patch. You said
test=> set autocommit = off;
SET
test=> commit;
WARNING: COMMIT: no transaction in progress
COMMIT
but in fact I saw the COMMIT succeeding without complaint. I was
meaning to ask you just what code you'd tested, because this morning's
CVS tip did *not* behave as above.
regards, tom lane
Tom Lane wrote:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
Well, I thought I did it, and it did work on my limited number of test
cases. Seems you got it fully working.Actually, it failed for me (and evidently for Barry) on exactly the test
case you posted along with the patch. You saidtest=> set autocommit = off;
SET
test=> commit;
WARNING: COMMIT: no transaction in progress
COMMITbut in fact I saw the COMMIT succeeding without complaint. I was
meaning to ask you just what code you'd tested, because this morning's
CVS tip did *not* behave as above.
I am stumped myself as well. I still have the CVS of my old code, and
it fails just as you saw, but I know I tested it and copied that into
the email via cut/paste so my only guess is that I tweaked something
after I ran the test and if broke something else. If you got it all
working now, I won't research further.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073