Horton
Menu

Thread

  1. experiences with autocommit functionality in 7.3

    Barry Lind <barry@xythos.com> — 2002-10-13T08:41:39Z

    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

  2. Re: experiences with autocommit functionality in 7.3

    Tom Lane <tgl@sss.pgh.pa.us> — 2002-10-13T14:51:44Z

    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

  3. Re: experiences with autocommit functionality in 7.3

    Tom Lane <tgl@sss.pgh.pa.us> — 2002-10-13T17:10:18Z

    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

  4. Re: experiences with autocommit functionality in 7.3

    Bruce Momjian <pgman@candle.pha.pa.us> — 2002-10-14T03:35:24Z

    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 19073

  5. Re: experiences with autocommit functionality in 7.3

    Tom Lane <tgl@sss.pgh.pa.us> — 2002-10-14T03:47:49Z

    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

  6. Re: experiences with autocommit functionality in 7.3

    Bruce Momjian <pgman@candle.pha.pa.us> — 2002-10-14T04:15:51Z

    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, Pennsylvania 19073