Re: Behaviour of setAutoCommit may not be completely

Just an FYI, I ran a quick compatibility test and Oracle's 10g driver
does NOT commit if autoCommit was false and is set to false again.

-- Mark Lewis

On Fri, 2006-03-31 at 08:39 -0500, Dave Cramer wrote:
> So the question is which document is right.
>
> The specs as Michael pointed out say if it is called, the specs as
> Joao pointed out suggest that if it is changed. Joao, where did you
> read this ?
>
> Dave
> On 31-Mar-06, at 5:02 AM, João Paulo Ribeiro wrote:
>
> > Hello.
> >
> > I found a situation that is causing db locks using enhydra dods
> > with postgresql due to somethinf that look like a bad behaviour of
> > postgresql.
> > Since version 6.0, i think, dods do all the db queries with
> > setAutoCommit(false). The expected behaviour of setAutocommit(...)
> > is that the current transaction is commited, allowing the db
> > backend to free resources previously allocated, and a new one is
> > created with the autocommit set to the value passed.
> >
> > From the specs: "If the value of auto-commit is changed in the
> > middle of a transaction, the current transaction is committed."
> >
> > The postgresql have a problem with setAutocommit(...): if the value
> > passed (false or true) is the same that was previously set, it just
> > does nothing.
> >
> > I looked to the postgresql driver and found this:
> >
> >     public void setAutoCommit(boolean autoCommit) throws SQLException
> >     {
> >         if (this.autoCommit == autoCommit)
> >             return ;
> >         if (autoCommit)
> >         {
> >                 execSQL("end");
> >         }
> >         else
> >         {
> >             if (haveMinimumServerVersion("7.1"))
> >             {
> >                 execSQL("begin;" + getIsolationLevelSQL());
> >             }
> >             else
> >             {
> >                 execSQL("begin");
> >                 execSQL(getIsolationLevelSQL());
> >             }
> >         }
> >         this.autoCommit = autoCommit;
> >     }
> >
> > The driver do nothing when the value passed (false or true) is the
> > same
> > that was previously set.
> > I fixed the driver and found that the behaviour was the same: it's
> > look
> > likes the backend is using the same logic and that explain why someone
> > at the driver JDBC decided to not pass this to the backend.
> > My tests were done with Postgresql 7.4.12.
> > Can someone confirm that the problem exist and it is in the driver
> > code
> > and the backend server?
> > Is this still happening with postgresql 8.1?
> >
> > Best regards
> > João Paulo Ribeiro
> >
> > --
> > João Paulo Ribeiro | Senior Software Engineer
> > jp@mobicomp.com
> >
> > PHONE: + 351 253 305 250
> > FAX  : + 351 253 305 250
> > www.mobicomp.com
> >
> > ________________________________________________________________
> >
> > About Solutions | Wireless World
> >
> > CONFIDENTIALITY NOTICE: This message, as well as existing attached
> > files, is confidential and intended exclusively for the individual
> > (s) named as addressees. If you are not the intended recipient, you
> > are kindly requested not to make any use whatsoever of its contents
> > and to proceed to the destruction of the message, thereby notifying
> > the sender.
> > DISCLAIMER: The sender of this message can not ensure the security
> > of its electronic transmission and consequently does not accept
> > liability for any fact which may interfere with the integrity of
> > its content.
> >
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 5: don't forget to increase your free space map settings
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly