JDBC behaviour

Hi

We are facing issue with PostgreSQL JDBC behaviour

in Java, under autocommit false mode,

1. In between if any transaction then for next transaction, throws
exception saying "current transaction is aborted, commands ignored until
end of transaction block"

2. Even if exception is suppressed with try-catch then too for next
transaction, throws exception saying "current transaction is aborted,
commands ignored until end of transaction block"

3. The same is not happening with Oracle or SQL-Server, in this with-out
any exception handling it works

Is it a bug or do we have other any alternate way to handle this ?

Please I need some help in this

Thanks
Sridhar

Is it a bug or do we have other any alternate way to handle this ?

PostgreSQL is strongly against "partial commits to the database". If
you think a bit about it, it is not that bad.
You got an error, what is the business case to commit the partial
transaction then?

Exceptions should not be used for a "control flow", should they?

If you want to shoot yourself in a foot for fun and profit, you can
try https://github.com/pgjdbc/pgjdbc/pull/477.
What it does, it creates savepoints before each statement, then it
rollbacks to that savepoint in case of failure.

Vladimir

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

If we want transactions in "begin-end" then its fine,

but in this case all these transactions are independent with autocommit off,

user choice to continue with commit or rollback

Thanks
Sridhar

On Thu, Feb 18, 2016 at 1:43 PM, Vladimir Sitnikov <
sitnikov.vladimir@gmail.com> wrote:

På torsdag 18. februar 2016 kl. 09:26:59, skrev Sridhar N Bamandlapally <
sridhar.bn1@gmail.com <mailto:sridhar.bn1@gmail.com>>:
If we want transactions in "begin-end" then its fine,   
but in this case all these transactions are independent with autocommit off,
 
user choice to continue with commit or rollback

 
Look in you serverlogs, "something" is issuing a BEGIN. The transaction
becomes invalid when an error occurs, you have to issue a COMMIT or ROLLBACK,
and in this case both will ROLLBACK.
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com&gt;
<https://www.visena.com&gt;

but in this case all these transactions are independent with autocommit off,

At database level, there is no "autocommit=off".
There's just "begin-end".

It is database who forbids .commit, not the JDBC driver.
Vladimir

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

Ok, let me put this way

in JDBC we have *setAutoCommit( false ) *, and all dmls are independent
transactions

and when any transaction fails then the session not allowing next
transactions

in Java when we do setAutoCommit( false ) its behaving like all
transactions in BEGIN-END block, this is not expected behavior

i guess this is bug

On Thu, Feb 18, 2016 at 2:00 PM, Vladimir Sitnikov <
sitnikov.vladimir@gmail.com> wrote:

På torsdag 18. februar 2016 kl. 09:51:47, skrev Sridhar N Bamandlapally <
sridhar.bn1@gmail.com <mailto:sridhar.bn1@gmail.com>>:
Ok, let me put this way  
in JDBC we have setAutoCommit( false ) , and all dmls are independent
transactions
 
and when any transaction fails then the session not allowing next transactions
 
in Java when we do setAutoCommit( false ) its behaving like all transactions
in BEGIN-END block, this is not expected behavior
 
i guess this is bug

 
No, you got it backwards. With autocommit=false all statements are NOT
independent transactions.
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com&gt;
<https://www.visena.com&gt;

setAutoCommit(false), it should not be treating all next transactions as
single set, simple, this is what expected behavior

On Thu, Feb 18, 2016 at 2:34 PM, Andreas Joseph Krogh <andreas@visena.com>
wrote:

Sridhar,

Please refer to documentation (see [1]https://docs.oracle.com/javase/7/docs/api/java/sql/Connection.html#setAutoCommit(boolean) ​ Vladimir).

Specification>Otherwise, its SQL statements are grouped into transactions
that are terminated by
Specification>a call to either the method commit or the method rollback

Sridhar> it should not be treating all next transactions as single set

What do you mean by "all next transactions"?

[1]: https://docs.oracle.com/javase/7/docs/api/java/sql/Connection.html#setAutoCommit(boolean) ​ Vladimir
https://docs.oracle.com/javase/7/docs/api/java/sql/Connection.html#setAutoCommit(boolean)
​
Vladimir

På torsdag 18. februar 2016 kl. 10:20:51, skrev Sridhar N Bamandlapally <
sridhar.bn1@gmail.com <mailto:sridhar.bn1@gmail.com>>:
setAutoCommit(false), it should not be treating all next transactions as
single set, simple, this is what expected behavior
 
The point is that all subsequent statements (after an exception) are part of
the same transaction (in autocommit=false mode). So you have to issue an
explicit ROLLBACK before any new statement can give meaningful results.
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com&gt;
<https://www.visena.com&gt;

On 2/18/2016 12:26 AM, Sridhar N Bamandlapally wrote:

If we want transactions in "begin-end" then its fine,

but in this case all these transactions are independent with
autocommit off,

with autocommit OFF, when you issue the first query, jdbc generates a
postgresql BEGIN; this starts a postgresql transaction. To end the
transaction, you have to explicitly .commit() or .rollback() ....

with autocommit ON, then jdbc lets each query execute standalone, this
is postgresql's default behavior if you don't use BEGIN...

--
john r pierce, recycling bits in santa cruz

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

let me put this way

table employee ( id PrimaryKey, name )

In Java ( just little pseudo-code way )

try {
conn.setAutoCommit(false);
try { executeUpdate("insert into employee(id,name) values(1, 'K1')");
} catch ...
try { executeUpdate("insert into employee(id,name) values(1, 'K1')");
} catch ...
try { executeUpdate("insert into employee(id,name) values(1, 'K2')");
} catch ...
conn.commit();
} catch ...

throws error
1. duplicate key value violates unique constraint "employee_pkey"
2. current transaction is aborted, commands ignored until end of
transaction block

In PL/SQL ( similar error thrown when used BEGIN-END )

postgres=# begin;
BEGIN
postgres=# insert into employee values (1,'aa');
INSERT 0 1
postgres=# insert into employee values (2,'bb');
INSERT 0 1
postgres=# insert into employee values (3,'cc');
INSERT 0 1
postgres=# insert into employee values (1,'aa');
ERROR: duplicate key value violates unique constraint "employee_pkey"
DETAIL: Key (eid)=(1) already exists.
postgres=# insert into employee values (4,'dd');
ERROR: current transaction is aborted, commands ignored until end of
transaction block

my question Java setAutoCommit (false) is behaving like PL/SQL BEGIN-END

On Thu, Feb 18, 2016 at 2:54 PM, Andreas Joseph Krogh <andreas@visena.com>
wrote:

yes John,

but why?, this is not expected behavior

autocommit is just autocommit, its not responsible to treat
multi-transactions as single or independent

On Thu, Feb 18, 2016 at 3:08 PM, Sridhar N Bamandlapally <
sridhar.bn1@gmail.com> wrote:

På torsdag 18. februar 2016 kl. 10:38:23, skrev Sridhar N Bamandlapally <
sridhar.bn1@gmail.com <mailto:sridhar.bn1@gmail.com>>:
let me put this way  
table employee ( id PrimaryKey, name )
 
In Java ( just little pseudo-code way )
 
try {
conn.setAutoCommit(false);
     try { executeUpdate("insert into employee(id,name) values(1, 'K1')"); }
catch ...
     try { executeUpdate("insert into employee(id,name) values(1, 'K1')"); }
catch ...
     try { executeUpdate("insert into employee(id,name) values(1, 'K2')"); }
catch ...
conn.commit();
} catch ...
 
throws error 
1. duplicate key value violates unique constraint "employee_pkey"
2. current transaction is aborted, commands ignored until end of transaction
block
 
 
In PL/SQL ( similar error thrown when used BEGIN-END )
 
postgres=# begin;
BEGIN
postgres=# insert into employee values (1,'aa');
INSERT 0 1
postgres=# insert into employee values (2,'bb');
INSERT 0 1
postgres=# insert into employee values (3,'cc');
INSERT 0 1
postgres=# insert into employee values (1,'aa');
ERROR:  duplicate key value violates unique constraint "employee_pkey"
DETAIL:  Key (eid)=(1) already exists.
postgres=# insert into employee values (4,'dd');
ERROR:  current transaction is aborted, commands ignored until end of
transaction block

 
 
my question Java setAutoCommit (false) is behaving like PL/SQL BEGIN-END

 
You are free to ignore what we tell you about the transaction being invalid
after an exception, and try all you want to issue new statements. But it will
all result in errors like the above. Youhave to issue a ROLLBACK to proceed,
and PG knows this so it refuses to do anything until you do.
 
I other words; There is no way to issue a statement without getting an error
in a transaction marked as invalid. This is what transactions are for,
guaranteeing consistent results.
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com&gt;
<https://www.visena.com&gt;

On 2/18/2016 1:42 AM, Sridhar N Bamandlapally wrote:

but why?, this is not expected behavior

autocommit is just autocommit, its not responsible to treat
multi-transactions as single or independent

its exactly how jdbc is defined to work, fit into postgres's
transaction model.

if autocommit is ON, then every statement is a standalone query.

if autocommit is OFF, then its in transaction mode. JDBC has an
implicit transaction start when you execute the first query, and you
have to commit or rollback the transaction.

if you want each INSERT to run indepedently, then use autocommit ON.

--
john r pierce, recycling bits in santa cruz

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

Andreas, ROLLBACK doesn't work as it do for all previous statements,

autocommit ON is not option as it controlled by connection pool,

with this the simple "statement" with need to replace with "try-catch",
"savepoint", "statement" and "rollback to savepoint", this looks lot of
changes in code to make it app/java-database compatible,

still, this is bug

On Thu, Feb 18, 2016 at 3:22 PM, John R Pierce <pierce@hogranch.com> wrote:

still, this is bug

It is as per specification.

Vladimir

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

På torsdag 18. februar 2016 kl. 11:02:20, skrev Sridhar N Bamandlapally <
sridhar.bn1@gmail.com <mailto:sridhar.bn1@gmail.com>>:
Andreas, ROLLBACK doesn't work as it do for all previous statements,   
autocommit ON is not option as it controlled by connection pool,   
with this the simple "statement" with need to replace with "try-catch",
"savepoint", "statement" and "rollback to savepoint", this looks lot of changes
in code to make it app/java-database compatible, 
 
still, this is bug

 
ROLLBACK is not a per-statement command, but a per-transaction.
I think you have to re-read the answers given in this thread.
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com&gt;
<https://www.visena.com&gt;

if you want each insert to work indepedently yet stay with the
transaction model, then each insert should be its own transaction...

conn.setAutoCommit(false);
executeUpdate("insert into employee(id,name) values(1, 'K1')");
conn.commit();
executeUpdate("insert into employee(id,name) values(1, 'K1')");
conn.commit();
executeUpdate("insert into employee(id,name) values(1, 'K2')");
conn.commit();

otherwise the way you wrote it, its a single transaction. all three
inserts have to succeed, otherwise, all three are rolledback. why is
that so hard to understand?

--
john r pierce, recycling bits in santa cruz

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