Re: A bad behavior under autocommit off mode - Mailing list pgsql-hackers

I have an idea.  It would be nice if you could ask the backend to report
the current autocommit value at the end of every query.

Tom, did you have any thought of adding the ability to ask for reports
on GUC variables on every query return?  Barry, that would solve your
problem, and I bet a few others.

In fact, we could add a SHOW tranaction_status readonly GUC variable,
and use that to return the transaction status for psql!

---------------------------------------------------------------------------

Barry Lind wrote:
> Tom,
> 
>  From the jdbc driver perspective I prefer the GUC variable approach, 
> but either can be used.  Each has limitations.
> 
> In 7.2 and earlier jdbc code the driver handled the transaction 
> symantics by adding begin/commit/rollback in appropriate places.  And 
> that code is still in the 7.3 driver to support older servers.
> 
> In 7.3 the driver uses the GUC variable to control the transaction 
> state.  In general this is easier since it is a set once and forget 
> about it operation.
> 
> As I mentioned earlier each method has limitations.  Let me list them.
> 
> The problem with managing the state on the client is that in order for 
> this to work the client needs to intercept all transaction ending events 
> in order to start the next transaction when running in non-autocommit 
> mode.  Thus each 'commit' becomes 'commit; begin;'.  Since the jdbc API 
> has a commit() and rollback() method there is an obvious place to insert 
> this logic.  However if the user directly issues a commit or rollback 
> sql call (instead of using the jdbc api) then the driver isn't in a 
> position to start the new transaction, unless the driver starts parsing 
> all SQL looking for commits or rollbacks which I am reluctant to do. 
> However the proposed FE/BE protocol change to tell the client the 
> transaction state would allow the driver to detect this.
> 
> The problem with using the GUC approach is that if the user in SQL 
> changed the GUC value the driver would have no way to know the state 
> change.  And thus the driver would think it was opperating in one mode 
> (the mode *it* set), but actually be running in a different mode (the 
> mode the *user* set through SQL).
> 
> Of these two limitations the first is more significant since users do 
> issue 'commit' statements directly sometimes, whereas users would likely 
> never change the GUC parameter in their SQL.  I like the simplicity of 
> the GUC parameter and that is the reason I converted the jdbc driver in 
> 7.3 to use this new method.
> 
> thanks,
> --Barry
> 
> Tom Lane wrote:
> > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > 
> >>I think our SET functionality is easy to understand and use.  I don't
> >>see pushing it into the client as greatly improving things, and could
> >>make things worse.  If we can't get it right in the backend, how many
> >>clients are going to do it wrong?
> > 
> > 
> > This argument overlooks the fact that most of the client libraries
> > already have notions of autocommit on/off semantics that they need to
> > adhere to.  libpq is too simple to have heard of the concept, but I
> > believe that JDBC, ODBC, and DBI/DBD all need to deal with it anyway.
> > I doubt that managing a server-side facility makes their lives any
> > easier ... especially not if its semantics don't quite match what
> > they need to do, which seems very possible.
> > 
> > But it'd be interesting to hear what the JDBC and ODBC maintainers
> > think about it.  Perhaps autocommit as a GUC variable is just what
> > they want.
> > 
> > Please recall that GUC-autocommit in its current form was my idea,
> > and I rushed it in there because I wanted us to be able to run the
> > NIST compliance tests easily.  In hindsight I am thinking it was a
> > bad move. 
> > 
> >             regards, tom lane
> > 
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> > 
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
> 

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