Обсуждение: 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, Pennsylvania19073
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 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 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. 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, Pennsylvania19073