Re: [HACKERS] [JDBC] JDBC behaviour

[Craig Ringer]

On 23 February 2016 at 22:46, Tom Lane  wrote:

> Craig Ringer  writes:
> > On 23 February 2016 at 21:34, Robert Haas  wrote:
> >> I believe Sridhar is imagining that someday "set autocommit to false"
> >> might be a command that the server would understand.
>
> > ... I guess. Yeah.
>
> We've been there, we've done that.  We're not doing it again.
>

Thanks for the pointer to the history.

I had zero enthusiasm for going that way anyway and was mostly trying to
figure out what Sridhar was talking about. It's useful to know it's already
been explored though.

I think we know where we need to go from here - updating that PgJDBC patch
to add a connection option, making sure it doesn't add round-trips, adding
tests and merging it. At this point it's up to Sridhar to start putting
time and development effort into it to push it forward if desired.

-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: [HACKERS] [JDBC] JDBC behaviour

[Tom Lane]

Craig Ringer  writes:
> On 23 February 2016 at 21:34, Robert Haas  wrote:
>> I believe Sridhar is imagining that someday "set autocommit to false"
>> might be a command that the server would understand.

> ... I guess. Yeah.

We've been there, we've done that.  We're not doing it again.
Cf commits 26993b291, f85f43dfb, 525a48991, as well as a whole
bunch of thrashing in between the first two (grep the git logs
for "autocommit" to find most of it).  It's a bit harder to locate
relevant email threads, because searching for just "autocommit"
yields too many hits; but here's one long thread from when we were
starting to realize that it wasn't working very well:
http://www.postgresql.org/message-id/flat/3e54526a.121eb...@tpf.co.jp

In all, this was one of the more searing experiences contributing
to what's now received project wisdom that GUCs that change
fundamental semantics are a bad idea.

> Oracle's SQL*Plus has the concept of turning autocommit off, but I suspect
> that's client-side behaviour.

The conclusion we came to back in 2002-2003 was that client-side
autocommit was the only behavior we could sanely support.  I see
no reason to think that a fresh experiment in the same direction
would produce a different result.

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [JDBC] JDBC behaviour

[Craig Ringer]

On 23 February 2016 at 21:34, Robert Haas  wrote:

> On Sat, Feb 20, 2016 at 4:14 PM, Craig Ringer 
> wrote:
> >> currently PostgreSQL::"set autocommit to FALSE ( not supported )
> >
> > This also does not make any sense.
> >
> > PgJDBC does support turning autocommit off. So I don't know in what way
> it's
> > "not supported".
>
> I believe Sridhar is imagining that someday "set autocommit to false"
> might be a command that the server would understand.
>

... I guess. Yeah.

Oracle's SQL*Plus has the concept of turning autocommit off, but I suspect
that's client-side behaviour.

http://docs.oracle.com/cd/B19306_01/server.102/b14357/ch12040.htm

I can't really imagine how it'd make sense on the server side, given how
the protocol works etc. Nor is it necessary since the desired behaviour is
entirely controlled on the client side.

We could have a server mode that did silent, automatic savepoints and
rolled back to a savepoint automatically on ERROR. That wouldn't be the
same as autocommit, but appears to be what Sridhar actually needs. There's
even the remotest chance someone could come up with a patch that might be
acceptable, but I don't know of anyone who'd want to do it when it can be
done well enough client side.

I think Sridhar is confusing autocommit with other DBMSes behaviour of
automatically rolling back failed statements without affecting the rest of
the transaction. These are not the same thing.

-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: [HACKERS] [JDBC] JDBC behaviour

[Robert Haas]

On Sat, Feb 20, 2016 at 4:14 PM, Craig Ringer  wrote:
>> currently PostgreSQL::"set autocommit to FALSE ( not supported )
>
> This also does not make any sense.
>
> PgJDBC does support turning autocommit off. So I don't know in what way it's
> "not supported".

I believe Sridhar is imagining that someday "set autocommit to false"
might be a command that the server would understand.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [JDBC] JDBC behaviour

[Craig Ringer]

On 20 February 2016 at 12:40, Sridhar N Bamandlapally  wrote:

> Hi All
>
> I understand your point,
>
> may be I didn't understand everyone or everyone didn't understand me
>

Sounds like it.


> one feature of PostgreSQL is implemented into another feature of Java ( i
> say subject PostgreSQL::autocommit Vs JDBC::setAutoCommit ),
>

There's no JDBC::setAutoCommit . If you're going to discuss behavour please
be very specific. Do you mean java.sql.Connection.setAutoCommit(boolean) ?


i.e PostgreSQL::"set autocommit to FALSE" is implemented as
> JDBC::"BEGIN--END"
>

This does not make any sense.

All setAutoCommit(false) does is tells the drive to begin a transaction
when the next statement is run and not commit it automatically. It doesn't
actually do anything its self.

It certainly doesn't run any block of statements.

By the way, "END" is kind of confusing. I presume you mean "COMMIT", which
is the more usual way to say that? PostgreSQL does support "END" as an
alias for COMMIT, but it's a pretty weird way to write it.

If you are going to discuss the behaviour of the driver please be specific
and accurate. Use the actual commands/queries/functions that the driver
uses or the specification describes, don't make up vague descriptions that
don't reflect what actually happens.


> currently PostgreSQL::"set autocommit to FALSE ( not supported )
>

This also does not make any sense.

PgJDBC does support turning autocommit off. So I don't know in what way
it's "not supported".


> say in future, if PostgreSQL come with proper fix/support for "set
> autocommit to FALSE"
>

It already supports it.

The only behaviour change that might be contemplated is a change for spec
compliance where we delay commit of a statement in autocommit mode until
the ResultSet and/or Statement are closed. Right now we commit immediately,
which is what most users expect, but apparently conflicts with how the JDBC
spec expects things to work when it comes to the duration of locks being
held etc.

There was a prior discussion thread on this.

That's a (fairly) minor detail, though it could have a significant impact
on apps. It does not change the fact that PgJDBC supports autocommit on or
off and will continue to do so.


> then will JDBC-team change the to code to JDBC::"set autocommit to FALSE"
> ?, then what about existing behaviors dependency applications ?
>

What behavour exactly are you talking about changing?

It already supports turning autocommit off.


> this could have handled in different way in blogs saying to add
> "BEGIN-END" from JDBC-connection-query with warning
>

I don't understand what you're trying to say here.


> simple, if PostgreSQL DB is not support then same with PostgreSQL JDBC
> too, if still JDBC want to support then need to support with expected
> behavior way only, how come other feature is added to this ?
>

I don't understand this.


> 1. "every/entire application developers expected behavior are matching,
> only PostgreSQL::JDBC-team is not in sync"
>

Please provide a complete, compileable, self-contained example
demonstrating behaviour that causes a failure or problem in PgJDBC but
works correctly with at least most of:

- MS SQL
- Oracle
- DB2
- Sybase
- MySQL

including test run output demonstrating the details of what exactly the
behaviour of each other implementation is.

Please show where in the JDBC specification the behaviour is described.


> 2. "every organisation want there applications to be multi-database
> compatible, only PostgreSQL::JDBC-team "
>

Well, nobody's truly "multi-database compatible" because the SQL spec is in
some areas vague and hard to interpret. Every DBMS has extensions and
quirks. Oracle thinks that "" = NULL is TRUE, for example. JDBC
implementations vary too.

Of course it's desirable to be more consistent and compatible where that's
practical, but you need to actually show clear evidence that other DBMSes
all do it one way and we do it a different way. With real, detailed,
complete code examples and test output.

Hand-waving about how we're doing it wrong won't get you anywhere.

-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Re: [HACKERS] [JDBC] JDBC behaviour

[Sridhar N Bamandlapally]

Hi All

I understand your point,

may be I didn't understand everyone or everyone didn't understand me

one feature of PostgreSQL is implemented into another feature of Java ( i
say subject PostgreSQL::autocommit Vs JDBC::setAutoCommit ),
i.e PostgreSQL::"set autocommit to FALSE" is implemented as
JDBC::"BEGIN--END"

currently PostgreSQL::"set autocommit to FALSE ( not supported )

say in future, if PostgreSQL come with proper fix/support for "set
autocommit to FALSE" then will JDBC-team change the to code to JDBC::"set
autocommit to FALSE" ?, then what about existing behaviors dependency
applications ?

this could have handled in different way in blogs saying to add "BEGIN-END"
from JDBC-connection-query with warning

simple, if PostgreSQL DB is not support then same with PostgreSQL JDBC too,
if still JDBC want to support then need to support with expected behavior
way only, how come other feature is added to this ?

basically, decision/review seems to be wrong, may be bug in the decision

and why for this we are continuing/forcing the loop is, because

1. "every/entire application developers expected behavior are matching,
only PostgreSQL::JDBC-team is not in sync"
2. "every organisation want there applications to be multi-database
compatible, only PostgreSQL::JDBC-team "

however, looping hackers and ending the loop

sorry, for using hard words(if any), but as open-source we need to complete
transparent


Thanks
Sridhar





On Thu, Feb 18, 2016 at 11:03 PM, Kevin Wooten  wrote:

> Using ‘psql’ executing your example would yield the same result, a command
> error would cause a required rollback before proceeding.  This tells you
> that this is how PostgreSQL, the database, is designed to work. It has
> nothing to do with the Java driver implementation.
>
> You are asking the creators of a client driver implementation to change a
> fundamental behavior of the database.  Repeatedly people have suggested you
> take this up with those creating the actual database (that’s the request to
> move this to the ‘-hackers’ list); yet you persist.
>
> I’m only chiming in because it’s getting quite annoying to have you keep
> this thread alive when the situation has been made quite clear to you.
>
> On Feb 18, 2016, at 9:57 AM, Sridhar N Bamandlapally <
> sridhar@gmail.com> wrote:
>
> There are many reasons why this is required,
>
> 1. Postgres migrated client percentage is high,
>
> 2. For application developers this looks like bug in Postgres, as it throw
> exception for next transaction even when current exception
> suppressed/handled,
>
> 3. Most of non-financial application or data-ware-house application have
> batch transaction process where successful transaction goes into
> data-tables and failed transactions goes into error-log-tables,
>
> this is most generic requirement
>
> cannot effort any reason if client think about rollback to old database or
> feel not meeting requirements  -- please ignore
>
>
>
> On Thu, Feb 18, 2016 at 7:06 PM, Mark Rotteveel 
> wrote:
>
>> On Thu, 18 Feb 2016 13:48:04 +0100 (CET), Andreas Joseph Krogh
>>  wrote:
>> >  I understand that and indeed this isn't something that should be
>> handled
>> >  by the driver, however some of the response in this thread seem to
>> think
>> >  it
>> >  is an absurd expectation from the OP that failure of one statement
>> should
>> >  still allow a commit. Which it isn't if you look at what other database
>> >  systems do.
>> >
>> >  Mark
>> >
>> > If that one failed statement doesn't raise an exception, how does the
>> > client
>> > (code) know that it failed? If it does raise an exception, then what
>> > standard
>> > specifies that that specific exceptions is to be treated as "don't
>> > rollback for
>> > this type of error"?
>>
>> Of course an exception is raised, but the exact handling could then be
>> left to the client. For example the client could catch the exception,
>> decide based on the specific error to execute another statement to "fix"
>> the error condition and then commit. Think of INSERT, duplicate key, then
>> UPDATE before the existence of 'UPSERT'-like statements; if the occurrence
>> of duplicate key is rare it can be cheaper to do than to first SELECT to
>> check for existence and then INSERT or UPDATE, or to UPDATE, INSERT when
>> update count = 0. Another situation could be where the failure is not
>> important (eg it was only a log entry that is considered supporting, not
>> required), so the exception is ignored and the transaction as a whole is
>> committed.
>>
>> Sure, in most cases it is abusing exceptions for flow control and likely
>> an example of bad design, but the point is that it is not outlandish to
>> allow execution of other statements and eventually a commit of a
>> transaction even if one or more statements failed in that transaction; as
>> demonstrated by systems that do allow this (for SQL Server you need to set
>>