SEO

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

Hi, João Paolo,

João Paulo Ribeiro wrote:

> 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 don't see this as bug, acutally setting the value to the same it had
previously is not "changing", strictly seen.



Markus

--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS

Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Markus Schaber write:
> João Paulo Ribeiro wrote:
>
> > 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 don't see this as bug, acutally setting the value to the same it had
> previously is not "changing", strictly seen.

The latest javadoc of the Connection interface at
http://java.sun.com/j2se/1.5.0/docs/...Commit(boolean)
has (same for 1.5.0 and 1.4.2):

setAutoCommit:
....
"NOTE: If this method is called during a transaction, the transaction is
committed."

This sentence is completely unambigously stating that calling the method
will commit a running transaction. If the postgresql jdbc driver is in
violation of this, it should be fixed. Otherwise it will break applications
that are written based on the specified semantics.

Best Regards,
Michael Paesold



---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

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

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Michael Paesold wrote:

> has (same for 1.5.0 and 1.4.2):
>
> setAutoCommit:
> ...
> "NOTE: If this method is called during a transaction, the transaction is
> committed."
>
> This sentence is completely unambigously stating that calling the method
> will commit a running transaction. If the postgresql jdbc driver is in
> violation of this, it should be fixed. Otherwise it will break
> applications that are written based on the specified semantics.

Note that this is new to the JDBC3 javadoc. The JDBC2 javadoc doesn't
have that note.

The main spec (e.g. the JDBC 3.0 PDF) document is unambiguous, too, but
describes different behaviour:

> The default is for auto-commit mode to be enabled when the Connection
> object is created. If the value of auto-commit is changed in the middle
> of a transaction, the current transaction is committed.

The specification behaviour is what the driver implements (it sounds
like Oracle did the same thing too).

Does the specification trump the javadoc, or vice versa?

-O

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Oliver Jowett <oliver@opencloud.com> writes:
> Does the specification trump the javadoc, or vice versa?

The spec certainly ought to win, but the fact that the change in the
javadoc is more recent is a pretty clear clue what Sun thinks.
You quote the spec text as

>> The default is for auto-commit mode to be enabled when the Connection
>> object is created. If the value of auto-commit is changed in the middle
>> of a transaction, the current transaction is committed.

I don't see that as unambiguous. If the autocommit setting is *changed*
then it's clear what must happen, but calling the method with the same
setting that already prevails could easily be considered not to cause
a "change" in the setting.

It looks to me like Sun is trying to clarify their reading of that
detail with the javadoc change. It's a crummy way to handle it; they
should have updated the spec. But I'd say the handwriting is on the
wall about what the next spec version will say.

Is it reasonable to provide an option about how to handle this case?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

On Sat, 1 Apr 2006, Tom Lane wrote:

> It looks to me like Sun is trying to clarify their reading of that
> detail with the javadoc change. It's a crummy way to handle it; they
> should have updated the spec. But I'd say the handwriting is on the
> wall about what the next spec version will say.
>

The JDBC 4.0 public draft spec says:

The default is for auto-commit mode to be enabled when the Connection
object is created. If the value of auto-commit is changed in the middle of
a transaction, the current transaction is committed. If setAutoCommit is
called and the value for auto-commit is not changed from its current
value, it is treated as a no-op.

The JDK1.6 beta2 javadoc confirms this:

NOTE: If this method is called during a transaction and the auto-commit
mode is changed, the transaction is committed. If setAutoCommit is called
and the auto-commit mode is not changed, the call is a no-op.

So I think we're fine.

Kris Jurka

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Ok. I believe you are right.

Looks like postgresql is doing the right thing. I'm gonna pass this
information to the dods developper team.

Many thanks.

Best regards.
João Paulo Ribeiro

Kris Jurka wrote:
>
>
> On Sat, 1 Apr 2006, Tom Lane wrote:
>
>> It looks to me like Sun is trying to clarify their reading of that
>> detail with the javadoc change. It's a crummy way to handle it; they
>> should have updated the spec. But I'd say the handwriting is on the
>> wall about what the next spec version will say.
>>
>
> The JDBC 4.0 public draft spec says:
>
> The default is for auto-commit mode to be enabled when the Connection
> object is created. If the value of auto-commit is changed in the
> middle of a transaction, the current transaction is committed. If
> setAutoCommit is called and the value for auto-commit is not changed
> from its current value, it is treated as a no-op.
>
> The JDK1.6 beta2 javadoc confirms this:
>
> NOTE: If this method is called during a transaction and the
> auto-commit mode is changed, the transaction is committed. If
> setAutoCommit is called and the auto-commit mode is not changed, the
> call is a no-op.
>
> So I think we're fine.
>
> Kris Jurka
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
>


--
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 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match