Обсуждение: JDBC behaviour
Hi
We are facing issue with PostgreSQL JDBC behaviour
in Java, under autocommit false mode,
1.in between if any transaction
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
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:
>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
> 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
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:
> 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
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
On 18 February 2016 at 16:13, Vladimir Sitnikov <sitnikov.vladimir@gmail.com> wrote:
If you want to shoot yourself in a foot for fun and profit, you can
try https://github.com/pgjdbc/pgjdbc/pull/477.
I think this should be incorporated, once it's ready, as a non-default connection option. It's handy for porting applications.
I think PostgreSQL's behaviour is the most correct, but if people are porting apps and want to wear the performance impact of all those savepoints and have written their code to handle partially-failed xacts, then sure, they can have that.
I'm constantly astounded by how many people are willing to simply ignore errors and carry on with the transaction without even properly verifying that the error was the exact one they expected though. Seriously bad application development and it *will* bite them. The best, most correct thing to do remains to retry the whole transaction.
On 18 February 2016 at 16:13, Vladimir Sitnikov <sitnikov.vladimir@gmail.com> wrote:
If you want to shoot yourself in a foot for fun and profit, you can
try https://github.com/pgjdbc/pgjdbc/pull/477.
I think this should be incorporated, once it's ready, as a non-default connection option. It's handy for porting applications.
I think PostgreSQL's behaviour is the most correct, but if people are porting apps and want to wear the performance impact of all those savepoints and have written their code to handle partially-failed xacts, then sure, they can have that.
I'm constantly astounded by how many people are willing to simply ignore errors and carry on with the transaction without even properly verifying that the error was the exact one they expected though. Seriously bad application development and it *will* bite them. The best, most correct thing to do remains to retry the whole transaction.