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
