Re: A bad behavior under autocommit off mode

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(at)candle(dot)pha(dot)pa(dot)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(at)postgresql(dot)org
>