Thread: JDBC behaviour

>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

--

Andreas Joseph Krogh

CTO / Partner - Visena AS

Mobile: +47 909 56 963

andreas@visena.com

www.visena.com

> 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

--

Andreas Joseph Krogh

CTO / Partner - Visena AS

Mobile: +47 909 56 963

andreas@visena.com

www.visena.com

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

--

Andreas Joseph Krogh

CTO / Partner - Visena AS

Mobile: +47 909 56 963

andreas@visena.com

www.visena.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

--

Andreas Joseph Krogh

CTO / Partner - Visena AS

Mobile: +47 909 56 963

andreas@visena.com

www.visena.com

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

>still, this is bug
It is as per specification.

Vladimir

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

--

Andreas Joseph Krogh

CTO / Partner - Visena AS

Mobile: +47 909 56 963

andreas@visena.com

www.visena.com

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

The code/framework is written to handle batch inserts, which is common for all databases

 

I feel, PostgreSQL JDBC may need to modify setAutoCommit(false) code to "implicit savepoint - on error - rollback to savepoint"

--

Andreas Joseph Krogh

CTO / Partner - Visena AS

Mobile: +47 909 56 963

andreas@visena.com

www.visena.com

På torsdag 18. februar 2016 kl. 11:43:36, skrev Sridhar N Bamandlapally <sridhar.bn1@gmail.com>:

The code/framework is written to handle batch inserts, which is common for all databases

 

I feel, PostgreSQL JDBC may need to modify setAutoCommit(false) code to "implicit savepoint - on error - rollback to savepoint"

 

You simply cannot have batch-inserts in the same transaction and expecting the batch not to fail if one of the statements in the batch fails.

--

Andreas Joseph Krogh

CTO / Partner - Visena AS

Mobile: +47 909 56 963

andreas@visena.com

www.visena.com

On Thu, 18 Feb 2016 11:59:50 +0100 (CET), Andreas Joseph Krogh
<andreas@visena.com> wrote:
> På torsdag 18. februar 2016 kl. 11:43:36, skrev Sridhar N Bamandlapally
<
> sridhar.bn1@gmail.com <mailto:sridhar.bn1@gmail.com>>:
> The code/framework is written to handle batch inserts, which is common
for
> all
> databases 
> I feel, PostgreSQL JDBC may need to modify setAutoCommit(false) code to
> "implicit savepoint - on error - rollback to savepoint"
>
>  
> You simply cannot have batch-inserts in the same transaction and
expecting
> the
> batch not to fail if one of the statements in the batch fails.

On a lot of other database systems, that is exactly how it works. If a
statement fails, that one statement is backed out (rolled back), and it is
still up to the user to decide if he wants to commit or rollback the
statements that did succeed.

Mark

On Thu, 18 Feb 2016 07:15:11 -0500, Dave Cramer <pg@fastcrypt.com> wrote:
> On 18 February 2016 at 07:09, Mark Rotteveel <mark@lawinegevaar.nl>
wrote:
>
>> On Thu, 18 Feb 2016 11:59:50 +0100 (CET), Andreas Joseph Krogh
>> <andreas@visena.com> wrote:

>> > You simply cannot have batch-inserts in the same transaction and
>> expecting
>> > the
>> > batch not to fail if one of the statements in the batch fails.
>>
>> On a lot of other database systems, that is exactly how it works. If a
>> statement fails, that one statement is backed out (rolled back), and it
>> is
>> still up to the user to decide if he wants to commit or rollback the
>> statements that did succeed.
>>
>
> This behaviour is an artifact of PostgreSQL. If you want to change the
> transaction semantics of PostgreSQL then pgsql-hackers is the place to
take
> this up.
>
> JDBC is just an interface. We aren't going to rewrite the backend
semantics
> to meet everyones needs/wants.

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

On Thu, 18 Feb 2016 07:15:11 -0500, Dave Cramer <pg@fastcrypt.com> wrote:
> On 18 February 2016 at 07:09, Mark Rotteveel <mark@lawinegevaar.nl>
wrote:
>
>> On Thu, 18 Feb 2016 11:59:50 +0100 (CET), Andreas Joseph Krogh
>> <andreas@visena.com> wrote:

>> > You simply cannot have batch-inserts in the same transaction and
>> expecting
>> > the
>> > batch not to fail if one of the statements in the batch fails.
>>
>> On a lot of other database systems, that is exactly how it works. If a
>> statement fails, that one statement is backed out (rolled back), and it
>> is
>> still up to the user to decide if he wants to commit or rollback the
>> statements that did succeed.
>>
>
> This behaviour is an artifact of PostgreSQL. If you want to change the
> transaction semantics of PostgreSQL then pgsql-hackers is the place to
take
> this up.
>
> JDBC is just an interface. We aren't going to rewrite the backend
semantics
> to meet everyones needs/wants.

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

--

Andreas Joseph Krogh

CTO / Partner - Visena AS

Mobile: +47 909 56 963

andreas@visena.com

www.visena.com

Unfortunately, OP seems to ignore all the inputs, thus no "advanced"
recommendations.

Mark>I understand that and indeed this isn't something that should be handled
Mark>by the driver

psql has special "transparent auto-rollback-to-auto-savepoint", thus
there's a certain trend to workaround database strictness.

As I posted earlier, there is a work-in-progress implementation of
exactly that functionality at the driver level:
https://github.com/pgjdbc/pgjdbc/pull/477
Of course it won't be able to "rollback just a single row of a whole
batch", however batch DML is often split to sub-batches. The
savepoints can be in between those sub-batches.

Just one more time:
1) If the application is developed via PostgreSQL from the ground up,
then "transacted aborted" is more likely to be a feature.
2) If the application is migrated from other databases, then excessive
strictness might be a "bug". That is, the application should be
eventually be adapted not to throw exceptions, however, "transaction
aborted" might easily be a blocker to migration to PG. Frankly
speaking, "autosavepoint" is somewhat that might sense. It is not
something crazy like returning 0 from 0/0.

Vladimir

On Thu, 18 Feb 2016 13:48:04 +0100 (CET), Andreas Joseph Krogh
<andreas@visena.com> 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
XACT_ABORT mode on to get similar behavior as PostgreSQL).

As to standards, for batch execution, JDBC expects that a driver either
process up to the first failure and raise a BatchUpdateException with the
update counts of the successfully executed statements, or continue
processing after failure(s) and only raise the exception after processing
the remainder of the batch (where the exception contains a mix of update
counts + failure indications). In both cases a commit for the statements
that were processed successfully would still be possible if the client so
wishes (see section 14.1.3 "Handling Failures during Execution" of JDBC
4.2).

Mark

> 2. as it throw exception for next transaction even when current exception suppressed/handled,

You are plain wrong here.
Next **transaction** would be perfectly fine.
You are somehow ignoring the fact that to start that *next*
transaction you need a rollback/commit call.

next statement != next transaction.

Vladimir

Dave Cramer <pg@fastcrypt.com> writes:
> Please take this up with pgsql-hackers..
> This is not something JDBC can solve

The reception on -hackers won't be any more positive.  The performance and
backwards-compatibility problems associated with changing these semantics
mean that it won't happen.  You need to perform an explicit savepoint
anywhere you want to be able to roll back to.

            regards, tom lane

On 2/19/2016 8:40 PM, Sridhar N Bamandlapally wrote:
> 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-<statements>-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 ?

in autocommit 'off' mode, most SQL databases start a transaction when
you execute any modifying query, these transactions are by default
atomic, and nothing is written til you commit.   any errors cause the
transaction to be put in failure mode, and the whole thing has to be
rolled back.      there are differing levels of transaction isolation
depending on database capabilities and settings, but most relational
transactional databases adhere to these basic semantics, and
transactions are all or nothing.

postgres differs only in that it is always in autocommit ON mode, unless
you begin a transaction explicitly with BEGIN.      the net effect is
exactly the same.







--
john r pierce, recycling bits in santa cruz

On 2/19/2016 8:40 PM, Sridhar N Bamandlapally wrote:
> 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-<statements>-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 ?

in autocommit 'off' mode, most SQL databases start a transaction when
you execute any modifying query, these transactions are by default
atomic, and nothing is written til you commit.   any errors cause the
transaction to be put in failure mode, and the whole thing has to be
rolled back.      there are differing levels of transaction isolation
depending on database capabilities and settings, but most relational
transactional databases adhere to these basic semantics, and
transactions are all or nothing.

postgres differs only in that it is always in autocommit ON mode, unless
you begin a transaction explicitly with BEGIN.      the net effect is
exactly the same.







--
john r pierce, recycling bits in santa cruz

On 2/20/2016 2:44 AM, Craig Ringer wrote:
> Hand-waving about how we're doing it wrong won't get you anywhere.
>

near as I can tell, the OP has used some sort of SQL (unspecified) where
multiple inserts within a transaction are individually inserted,
regardless of one failing.

to me this seems to break the rules of transaction semantics, but it
matches the behavior with autocommit=on  ...





--
john r pierce, recycling bits in santa cruz

John R Pierce schrieb am 20.02.2016 um 12:05:
> near as I can tell, the OP has used some sort of SQL (unspecified) where multiple inserts
>within a transaction are individually inserted, regardless of one failing.

At least Oracle does it this way (and I think DB2 as well).

Oracle gets really slow if you do a row-by-row commit with large inserts. That's why
most people don't use auto-commit and just ignore any errors during inserts for batch loads.

> to me this seems to break the rules of transaction semantics

I agree, the expected behaviour from the OP does violate the A in the ACID principle,
but apparently it's popular enough that people think the correct behaviour is a bug:

http://dba.stackexchange.com/a/129822/1822

On 2/20/2016 2:44 AM, Craig Ringer wrote:
> Hand-waving about how we're doing it wrong won't get you anywhere.
>

near as I can tell, the OP has used some sort of SQL (unspecified) where
multiple inserts within a transaction are individually inserted,
regardless of one failing.

to me this seems to break the rules of transaction semantics, but it
matches the behavior with autocommit=on  ...





--
john r pierce, recycling bits in santa cruz

Craig Ringer schrieb am 20.02.2016 um 11:44:
> 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.

Disclaimer: I do agree with you that Postgres behaviour is correct. A transaction either succeeds completely or not at
all.

Having said that: Postgres' behaviour *is* unique regarding this.

Consider the following table:

   create table x (id integer not null primary key);

The following code will run with Oracle, DB2, SQL Sever, MySQL and Firebird:

---- code start ----

     public class TestInsert
     {
       public static void main(String args[])
         throws Exception
       {
         Connection con = DriverManager.getConnection("...", "...", "...");
         con.setAutoCommit(false);

         PreparedStatement pstmt = con.prepareStatement("insert into x (id) values (?)");

         pstmt.setInt(1, 1);
         pstmt.executeUpdate();

         try
         {
           pstmt.setInt(1, 1);
           pstmt.executeUpdate();
         }
         catch (Exception ex)
         {
           System.out.println("***** Error: " + ex.getMessage());
         }

         System.out.println("trying second row");

         pstmt.setInt(1, 2);
         pstmt.executeUpdate();

         con.commit();

         ResultSet rs = con.createStatement().executeQuery("select count(*) from x");
         if (rs.next())
         {
           int rows = rs.getInt(1);
           System.out.println("rows: " + rows);
         }
         con.close();
       }
     }

---- code end ----

With Oracle DB2, SQL Server, MySQL and Firebird you'll get something like this
(this is from Oracle, the error message will of course differ for the others)

   ***** Error: ORA-00001: Unique Constraint (THOMAS.SYS_C0021623) violated
   trying second row
   rows: 2


With Postgres you get:

   ***** Error: ERROR: duplicate key value violates unique constraint "x_pkey"
     Detail: Key (id)=(1) already exists.
   trying second row
   Exception in thread "main" org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands
ignoreduntil end of transaction block 


But again: in contrast to the OP I do not consider this a bug, it's just that Postgres behaves differently.

But I understand that it can be a problem when migrating applications that have a sloppy way of dealing with
transactions.

I was part in the migration of several projects from Oracle and MySQL to Postgres in the last two years, none of them
hadproblems regarding that. 

Thomas

On 20 February 2016 at 19:05, John R Pierce <pierce@hogranch.com> wrote:

 

to me this seems to break the rules of transaction semantics, but it matches the behavior with autocommit=on  ...

... which doesn't matter, because if you wanted that you'd just set autocommit=on.

I think all this discussion of autocommit is a pointless side-track. Reading between the lines, the real complaint appears to be that we abort the xact if a statement ERRORs, and by default other DBMS vendors don't. OP wants something like psqlODBC's "statement" error-rollback mode where savepoints are done automatically before each statement.

John R Pierce schrieb am 20.02.2016 um 12:05:
> near as I can tell, the OP has used some sort of SQL (unspecified) where multiple inserts
>within a transaction are individually inserted, regardless of one failing.

At least Oracle does it this way (and I think DB2 as well).

Oracle gets really slow if you do a row-by-row commit with large inserts. That's why
most people don't use auto-commit and just ignore any errors during inserts for batch loads.

> to me this seems to break the rules of transaction semantics

I agree, the expected behaviour from the OP does violate the A in the ACID principle,
but apparently it's popular enough that people think the correct behaviour is a bug:

http://dba.stackexchange.com/a/129822/1822

On Sat, 20 Feb 2016 16:01:04 +0000
Vitalii Tymchyshyn <vit@tym.im> wrote:

> Well, it OT here and belongs to -hackers, but as for me main use case here
> is ETL or ELT process getting a lot of unvalidated external data.
> And a good option to solve this problem is not to change transaction
> semantics or slow down processing by adding tons of savepoints, but add "on
> error" clause to insert/copy statement.
>
> This clause should allow to  save records that can't fit into destination
> table because of type, check of referential constaints into error table.
> Oracle has similar functionality and we are using it in our project. No
> error is generated - no transaction rollback, batch abort or similar.
>
> As for me it would cover 90% of use cases and would be really usefull. The
> one problem I can see is with inserting into partition parent.

PL/PGSQL provides this functionality. It requires the creation of a server
side function and using that function to insert data, but it can do exactly
what you're describing.

While adding other mechanisms to make it "easier" or "more like some other
software" might be valuable; the simple fact is that Postgres _does_ support
what you want. The fact that you're not aware of it doesn't change that.

--
Bill Moran

On Sat, 20 Feb 2016 16:29:09 +0000
Vitalii Tymchyshyn <vit@tym.im> wrote:

> Well, I suppose replacing simple copy with procedural per-row function
> would give huge performance hit. Also what method do you propose to use in
> the code? Savepoints?

Not at all. PL/PGSQL's ON ERROR handling can manage this without needing
savepoints.

> I'd say this would also add a more slowdown.

What? The savepoints? Well, you don't need them. The stored
procedure is going to incur a bit of a hit, though.

> Also quite a bit of boilerplate code would be needed. It's similar to merge
> statement. Yes, it can be achieved in pure SQL, but having clean merge
> statement saves you a lot of headache and is usually much more efficient.
> Basically, it's not that what OP needs is not doable at all, it's that
> other RDBMs often has this functionality in much more convenient and
> performance optimized way.

True. I don't think "clean up my crappy data" has ever been a
priority for PostgreSQL. Although, "allow the user to build whatever
is needed" has been.

I find it curious that those of us who become stewards of other people's
data find ourselves bending over backwards to try to clean up their
garbage data. It's an interesting social commentary on how software
design has changed since the term GIGO was in common use.

> Best regards, Vitalii Tymchyshyn
>
> ??, 20 ???. 2016 11:16 Bill Moran <wmoran@potentialtech.com> ????:
>
> > On Sat, 20 Feb 2016 16:01:04 +0000
> > Vitalii Tymchyshyn <vit@tym.im> wrote:
> >
> > > Well, it OT here and belongs to -hackers, but as for me main use case
> > here
> > > is ETL or ELT process getting a lot of unvalidated external data.
> > > And a good option to solve this problem is not to change transaction
> > > semantics or slow down processing by adding tons of savepoints, but add
> > "on
> > > error" clause to insert/copy statement.
> > >
> > > This clause should allow to  save records that can't fit into destination
> > > table because of type, check of referential constaints into error table.
> > > Oracle has similar functionality and we are using it in our project. No
> > > error is generated - no transaction rollback, batch abort or similar.
> > >
> > > As for me it would cover 90% of use cases and would be really usefull.
> > The
> > > one problem I can see is with inserting into partition parent.
> >
> > PL/PGSQL provides this functionality. It requires the creation of a server
> > side function and using that function to insert data, but it can do exactly
> > what you're describing.
> >
> > While adding other mechanisms to make it "easier" or "more like some other
> > software" might be valuable; the simple fact is that Postgres _does_
> > support
> > what you want. The fact that you're not aware of it doesn't change that.
> >
> > --
> > Bill Moran
> >


--
Bill Moran

Bill Moran <wmoran@potentialtech.com> writes:
> On Sat, 20 Feb 2016 16:29:09 +0000
> Vitalii Tymchyshyn <vit@tym.im> wrote:
>> Well, I suppose replacing simple copy with procedural per-row function
>> would give huge performance hit. Also what method do you propose to use in
>> the code? Savepoints?

> Not at all. PL/PGSQL's ON ERROR handling can manage this without needing
> savepoints.

Actually, plpgsql's exception blocks *are* savepoints under the hood.
The backend engine does not have any way of recovering from errors other
than a (sub)transaction abort, which means you can't do this without a
savepoint or equivalent.

            regards, tom lane

Well, often in ETL it's not MY crappy data. It's something received from upstream system and my task is to perform a load, not stop on the first error and produce at least meaninful report on data crappyness statistics. Extended cases may involve something like manual data fixing for error rows.

Just a real world example: in financial transactions sometimes you can receive a transaction on a financial instrument that is brand new and is not in your db yet. You don't want to fail the whole batch.

And yes, globally it's a move from "getting data" to "filtering this [crappy] data ocean" going on.

Best regards, Vitalii Tymchyshyn

Vitalii Tymchyshyn schrieb am 20.02.2016 um 17:01:
> Well, it OT here and belongs to -hackers, but as for me main use case
> here is ETL or ELT process getting a lot of unvalidated external
> data. And a good option to solve this problem is not to change
> transaction semantics or slow down processing by adding tons of
> savepoints, but add "on error" clause to insert/copy statement.
>

You can use INSERT .. ON CONFLICT ... to ignore the erroneous rows
for unique constraint violations.

It's not the same as Oracle's ON ERROR clause, but you don't need savepoints
and you can use JDBC batching with that as well.

For all other constraint violations I usually put the import data into
a staging table anyway - even with Oracle.

Craig Ringer schrieb am 20.02.2016 um 11:44:
> 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.

Disclaimer: I do agree with you that Postgres behaviour is correct. A transaction either succeeds completely or not at
all.

Having said that: Postgres' behaviour *is* unique regarding this.

Consider the following table:

   create table x (id integer not null primary key);

The following code will run with Oracle, DB2, SQL Sever, MySQL and Firebird:

---- code start ----

     public class TestInsert
     {
       public static void main(String args[])
         throws Exception
       {
         Connection con = DriverManager.getConnection("...", "...", "...");
         con.setAutoCommit(false);

         PreparedStatement pstmt = con.prepareStatement("insert into x (id) values (?)");

         pstmt.setInt(1, 1);
         pstmt.executeUpdate();

         try
         {
           pstmt.setInt(1, 1);
           pstmt.executeUpdate();
         }
         catch (Exception ex)
         {
           System.out.println("***** Error: " + ex.getMessage());
         }

         System.out.println("trying second row");

         pstmt.setInt(1, 2);
         pstmt.executeUpdate();

         con.commit();

         ResultSet rs = con.createStatement().executeQuery("select count(*) from x");
         if (rs.next())
         {
           int rows = rs.getInt(1);
           System.out.println("rows: " + rows);
         }
         con.close();
       }
     }

---- code end ----

With Oracle DB2, SQL Server, MySQL and Firebird you'll get something like this
(this is from Oracle, the error message will of course differ for the others)

   ***** Error: ORA-00001: Unique Constraint (THOMAS.SYS_C0021623) violated
   trying second row
   rows: 2


With Postgres you get:

   ***** Error: ERROR: duplicate key value violates unique constraint "x_pkey"
     Detail: Key (id)=(1) already exists.
   trying second row
   Exception in thread "main" org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands
ignoreduntil end of transaction block 


But again: in contrast to the OP I do not consider this a bug, it's just that Postgres behaves differently.

But I understand that it can be a problem when migrating applications that have a sloppy way of dealing with
transactions.

I was part in the migration of several projects from Oracle and MySQL to Postgres in the last two years, none of them
hadproblems regarding that. 

Thomas

On 20 February 2016 at 19:05, John R Pierce <pierce@hogranch.com> wrote:

 

to me this seems to break the rules of transaction semantics, but it matches the behavior with autocommit=on  ...

... which doesn't matter, because if you wanted that you'd just set autocommit=on.

I think all this discussion of autocommit is a pointless side-track. Reading between the lines, the real complaint appears to be that we abort the xact if a statement ERRORs, and by default other DBMS vendors don't. OP wants something like psqlODBC's "statement" error-rollback mode where savepoints are done automatically before each statement.

On Sat, 20 Feb 2016 16:01:04 +0000
Vitalii Tymchyshyn <vit@tym.im> wrote:

> Well, it OT here and belongs to -hackers, but as for me main use case here
> is ETL or ELT process getting a lot of unvalidated external data.
> And a good option to solve this problem is not to change transaction
> semantics or slow down processing by adding tons of savepoints, but add "on
> error" clause to insert/copy statement.
>
> This clause should allow to  save records that can't fit into destination
> table because of type, check of referential constaints into error table.
> Oracle has similar functionality and we are using it in our project. No
> error is generated - no transaction rollback, batch abort or similar.
>
> As for me it would cover 90% of use cases and would be really usefull. The
> one problem I can see is with inserting into partition parent.

PL/PGSQL provides this functionality. It requires the creation of a server
side function and using that function to insert data, but it can do exactly
what you're describing.

While adding other mechanisms to make it "easier" or "more like some other
software" might be valuable; the simple fact is that Postgres _does_ support
what you want. The fact that you're not aware of it doesn't change that.

--
Bill Moran

On Sat, 20 Feb 2016 16:29:09 +0000
Vitalii Tymchyshyn <vit@tym.im> wrote:

> Well, I suppose replacing simple copy with procedural per-row function
> would give huge performance hit. Also what method do you propose to use in
> the code? Savepoints?

Not at all. PL/PGSQL's ON ERROR handling can manage this without needing
savepoints.

> I'd say this would also add a more slowdown.

What? The savepoints? Well, you don't need them. The stored
procedure is going to incur a bit of a hit, though.

> Also quite a bit of boilerplate code would be needed. It's similar to merge
> statement. Yes, it can be achieved in pure SQL, but having clean merge
> statement saves you a lot of headache and is usually much more efficient.
> Basically, it's not that what OP needs is not doable at all, it's that
> other RDBMs often has this functionality in much more convenient and
> performance optimized way.

True. I don't think "clean up my crappy data" has ever been a
priority for PostgreSQL. Although, "allow the user to build whatever
is needed" has been.

I find it curious that those of us who become stewards of other people's
data find ourselves bending over backwards to try to clean up their
garbage data. It's an interesting social commentary on how software
design has changed since the term GIGO was in common use.

> Best regards, Vitalii Tymchyshyn
>
> ??, 20 ???. 2016 11:16 Bill Moran <wmoran@potentialtech.com> ????:
>
> > On Sat, 20 Feb 2016 16:01:04 +0000
> > Vitalii Tymchyshyn <vit@tym.im> wrote:
> >
> > > Well, it OT here and belongs to -hackers, but as for me main use case
> > here
> > > is ETL or ELT process getting a lot of unvalidated external data.
> > > And a good option to solve this problem is not to change transaction
> > > semantics or slow down processing by adding tons of savepoints, but add
> > "on
> > > error" clause to insert/copy statement.
> > >
> > > This clause should allow to  save records that can't fit into destination
> > > table because of type, check of referential constaints into error table.
> > > Oracle has similar functionality and we are using it in our project. No
> > > error is generated - no transaction rollback, batch abort or similar.
> > >
> > > As for me it would cover 90% of use cases and would be really usefull.
> > The
> > > one problem I can see is with inserting into partition parent.
> >
> > PL/PGSQL provides this functionality. It requires the creation of a server
> > side function and using that function to insert data, but it can do exactly
> > what you're describing.
> >
> > While adding other mechanisms to make it "easier" or "more like some other
> > software" might be valuable; the simple fact is that Postgres _does_
> > support
> > what you want. The fact that you're not aware of it doesn't change that.
> >
> > --
> > Bill Moran
> >


--
Bill Moran

Bill Moran <wmoran@potentialtech.com> writes:
> On Sat, 20 Feb 2016 16:29:09 +0000
> Vitalii Tymchyshyn <vit@tym.im> wrote:
>> Well, I suppose replacing simple copy with procedural per-row function
>> would give huge performance hit. Also what method do you propose to use in
>> the code? Savepoints?

> Not at all. PL/PGSQL's ON ERROR handling can manage this without needing
> savepoints.

Actually, plpgsql's exception blocks *are* savepoints under the hood.
The backend engine does not have any way of recovering from errors other
than a (sub)transaction abort, which means you can't do this without a
savepoint or equivalent.

            regards, tom lane

Well, often in ETL it's not MY crappy data. It's something received from upstream system and my task is to perform a load, not stop on the first error and produce at least meaninful report on data crappyness statistics. Extended cases may involve something like manual data fixing for error rows.

Just a real world example: in financial transactions sometimes you can receive a transaction on a financial instrument that is brand new and is not in your db yet. You don't want to fail the whole batch.

And yes, globally it's a move from "getting data" to "filtering this [crappy] data ocean" going on.

Best regards, Vitalii Tymchyshyn

Vitalii Tymchyshyn schrieb am 20.02.2016 um 17:01:
> Well, it OT here and belongs to -hackers, but as for me main use case
> here is ETL or ELT process getting a lot of unvalidated external
> data. And a good option to solve this problem is not to change
> transaction semantics or slow down processing by adding tons of
> savepoints, but add "on error" clause to insert/copy statement.
>

You can use INSERT .. ON CONFLICT ... to ignore the erroneous rows
for unique constraint violations.

It's not the same as Oracle's ON ERROR clause, but you don't need savepoints
and you can use JDBC batching with that as well.

For all other constraint violations I usually put the import data into
a staging table anyway - even with Oracle.

On 2/20/2016 6:20 PM, Sridhar N Bamandlapally wrote:
> from Java/jdbc code, conn.setAutoCommit(false)
>
> insert into employee values(1, 'K1');
> insert into employee values(1, 'K1');
> insert into employee values(2, 'K2');
>
> by looking at exceptions i may rollback or commit, i.e.
> conn.rollback() or conn.commit()
> if I rollback table should be empty,
> if I commit table should have 2 rows
>
> is there any way is possible ?



turn on autocommit if you want each INSERT to be run atomically
independent of the other inserts.

this is going in circles, we've explained a dozens how postgres and jdbc
works.


--
john r pierce, recycling bits in santa cruz

On 2/20/2016 6:20 PM, Sridhar N Bamandlapally wrote:
> from Java/jdbc code, conn.setAutoCommit(false)
>
> insert into employee values(1, 'K1');
> insert into employee values(1, 'K1');
> insert into employee values(2, 'K2');
>
> by looking at exceptions i may rollback or commit, i.e.
> conn.rollback() or conn.commit()
> if I rollback table should be empty,
> if I commit table should have 2 rows
>
> is there any way is possible ?



turn on autocommit if you want each INSERT to be run atomically
independent of the other inserts.

this is going in circles, we've explained a dozens how postgres and jdbc
works.


--
john r pierce, recycling bits in santa cruz

Sridhar N Bamandlapally schrieb am 21.02.2016 um 03:20:
> Hi
>
> My expectation is simple, please refer below
>
> create table employee(empid numeric(4) primary key, ename varchar(20));
>
> from Java/jdbc code, conn.setAutoCommit(false)
>
> insert into employee values(1, 'K1');
> insert into employee values(1, 'K1');
> insert into employee values(2, 'K2');
>
> by looking at exceptions i may rollback or commit, i.e. conn.rollback() or conn.commit()
> if I rollback table should be empty,
> if I commit table should have 2 rows
>
> is there any way is possible ?

Use on conflict do nothing:

    insert into employee values(1, 'K1') on conflict (empid) do nothing;
    insert into employee values(1, 'K1') on conflict (empid) do nothing;
    insert into employee values(2, 'K2') on conflict (empid) do nothing;

This can be combined with a PreparedStatement and batching if needed.

Sridhar N Bamandlapally schrieb am 21.02.2016 um 03:20:
> Hi
>
> My expectation is simple, please refer below
>
> create table employee(empid numeric(4) primary key, ename varchar(20));
>
> from Java/jdbc code, conn.setAutoCommit(false)
>
> insert into employee values(1, 'K1');
> insert into employee values(1, 'K1');
> insert into employee values(2, 'K2');
>
> by looking at exceptions i may rollback or commit, i.e. conn.rollback() or conn.commit()
> if I rollback table should be empty,
> if I commit table should have 2 rows
>
> is there any way is possible ?

Use on conflict do nothing:

    insert into employee values(1, 'K1') on conflict (empid) do nothing;
    insert into employee values(1, 'K1') on conflict (empid) do nothing;
    insert into employee values(2, 'K2') on conflict (empid) do nothing;

This can be combined with a PreparedStatement and batching if needed.

On Sun, 21 Feb 2016 07:50:19 +0530
Sridhar N Bamandlapally <sridhar.bn1@gmail.com> wrote:

> My expectation is simple, please refer below
>
> create table employee(empid numeric(4) primary key, ename varchar(20));
>
> from Java/jdbc code, conn.setAutoCommit(false)
>
> insert into employee values(1, 'K1');
> insert into employee values(1, 'K1');
> insert into employee values(2, 'K2');
>
> by looking at exceptions i may rollback or commit, i.e. conn.rollback() or
> conn.commit()
> if I rollback table should be empty,
> if I commit table should have 2 rows
>
> is there any way is possible ?

Two other responses to this email are incorrect: turning on autocommit will
not allow you rollback the entire transaction (which I believe you needed)
and an the ON CONFLICT statement won't catch errors other than the empid
conflict, which I believe was an example and not the sum total of possible
errors you want to avoid.

Of course, if I'm misunderstanding those points, then those actually are
viable solutions.

However, I think what you're really looking for are savepoints, which will
give you the flexibility to handle just about any situation:

BEGIN TRANSACTION;
SAVEPOINT sp;
insert into employee values(1, 'K1');
RELEASE SAVEPOINT sp;
SAVEPOINT sp;
insert into employee values(1, 'K1');
ROLLBACK TO SAVEPOINT sp;
SAVEPOINT sp;
insert into employee values(2, 'K2');
RELEASE SAVEPOINT sp;
COMMIT TRANSACTION;

After each INSERT you have the option to RELEASE the savepoint (allowing
the insert to succeed) or ROLLBACK the savepoint (which rolls back only
to where the savepoint was created). Once all inserts have been attempted
you have the option to either COMMIT or ROLLBACK the entire transaction.
This is a generic solution that will work with any types of errors the
INSERTs may have. It's also fairly easy to abstract into your Java code
so the pattern can easily be reused.

Read the docs and experiment some until you're comfortable with the
concept:
http://www.postgresql.org/docs/9.5/static/sql-savepoint.html

--
Bill Moran

On Sun, 21 Feb 2016 07:50:19 +0530
Sridhar N Bamandlapally <sridhar.bn1@gmail.com> wrote:

> My expectation is simple, please refer below
>
> create table employee(empid numeric(4) primary key, ename varchar(20));
>
> from Java/jdbc code, conn.setAutoCommit(false)
>
> insert into employee values(1, 'K1');
> insert into employee values(1, 'K1');
> insert into employee values(2, 'K2');
>
> by looking at exceptions i may rollback or commit, i.e. conn.rollback() or
> conn.commit()
> if I rollback table should be empty,
> if I commit table should have 2 rows
>
> is there any way is possible ?

Two other responses to this email are incorrect: turning on autocommit will
not allow you rollback the entire transaction (which I believe you needed)
and an the ON CONFLICT statement won't catch errors other than the empid
conflict, which I believe was an example and not the sum total of possible
errors you want to avoid.

Of course, if I'm misunderstanding those points, then those actually are
viable solutions.

However, I think what you're really looking for are savepoints, which will
give you the flexibility to handle just about any situation:

BEGIN TRANSACTION;
SAVEPOINT sp;
insert into employee values(1, 'K1');
RELEASE SAVEPOINT sp;
SAVEPOINT sp;
insert into employee values(1, 'K1');
ROLLBACK TO SAVEPOINT sp;
SAVEPOINT sp;
insert into employee values(2, 'K2');
RELEASE SAVEPOINT sp;
COMMIT TRANSACTION;

After each INSERT you have the option to RELEASE the savepoint (allowing
the insert to succeed) or ROLLBACK the savepoint (which rolls back only
to where the savepoint was created). Once all inserts have been attempted
you have the option to either COMMIT or ROLLBACK the entire transaction.
This is a generic solution that will work with any types of errors the
INSERTs may have. It's also fairly easy to abstract into your Java code
so the pattern can easily be reused.

Read the docs and experiment some until you're comfortable with the
concept:
http://www.postgresql.org/docs/9.5/static/sql-savepoint.html

--
Bill Moran

On 2/21/2016 9:35 PM, Sridhar N Bamandlapally wrote:
>
> I may be wrong, please correct if,
>
> can we do function overloading to add functionality with savepoint
> option, i.e. with this both will be available and its app developers
> to chose
>

if you want every operation saved, then use autocommit, and let it
execute each insert atomically.



--
john r pierce, recycling bits in santa cruz

On 2/21/2016 9:35 PM, Sridhar N Bamandlapally wrote:
>
> I may be wrong, please correct if,
>
> can we do function overloading to add functionality with savepoint
> option, i.e. with this both will be available and its app developers
> to chose
>

if you want every operation saved, then use autocommit, and let it
execute each insert atomically.



--
john r pierce, recycling bits in santa cruz

On Sat, Feb 20, 2016 at 4:14 PM, Craig Ringer <craig@2ndquadrant.com> 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

Craig Ringer <craig@2ndquadrant.com> writes:
> On 23 February 2016 at 21:34, Robert Haas <robertmhaas@gmail.com> 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.121EBEE5@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