Re: JDBC behaviour

On 21 February 2016 at 10:20, Sridhar N Bamandlapally <sridhar(dot)bn1(at)gmail(dot)com
> wrote:

> 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 ?
>

Not with PgJDBC at the moment.

Dave pointed you to the patch that you'd need if you want this behaviour.
It might get integrated into PgJDBC. You could help by improving the patch
to add a configuration option to turn the functionality on/off (default
off) and by testing it.

That's really the only way you're going to get a robust version of what you
want. The ways others have outlined aren't going to work. ON CONFLICT only
handles unique violations and won't help with data that's not valid input
for a datatype, nulls where no nulls are permitted, etc.

The closest way supported well by PgJDBC and PostgreSQL way is to set
synchronous_commit = off . You can then do a series of autocommit
statements, followed at the end by setting synchronous_commit = on and
committing the final statement. This will have the performance benefits of
avoiding so many WAL flushes while ignoring errors and preserving
successful statements only.

set synchronous_commit = off;
insert into employee values(1, 'K1');
insert into employee values(1, 'K1');
insert into employee values(2, 'K2');
set synchronous_commit = on;
/* now do something that writes to the database that will NOT fail to make
sure everything commits */

Of course this isn't portable to other DBMSes. This isn't exactly the same
as what you want because you cannot rollback(). But it's close.

I strongly advise you to look into pull #477 above. Build a patched version
of the driver and test it to see if it meets your needs. Follow up with
feedback and test results here. Review the code. If you help solve your
problem you've got way more chance of seeing a good result.

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