Re: JDBC behaviour

On 18 February 2016 at 17:38, Sridhar N Bamandlapally <sridhar(dot)bn1(at)gmail(dot)com
> wrote:

> let me put this way
>
> table employee ( id PrimaryKey, name )
>
> In Java ( just little pseudo-code way )
>

If you're discussing behaviour of the driver, pseudocode isn't good enough.
Show complete, compileable examples please. Otherwise I don't know if your
"..." is the same thing that I would expect to write there, so we might be
talking about totally different things.

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

catch ... what? exactly?

> 1. duplicate key value violates unique constraint "employee_pkey"
>

Well, of course.

> 2. current transaction is aborted, commands ignored until end of
> transaction block
>

Depends on what's in the catch {...} block.

Did you roll back the transaction?

Did you use a savepoint?

If I had to vaguely, wildly hand-wavily guess, I think what you might be
wanting here is for PgJDBC to do automatic savepoints before each statement
and automatically rollback to the last savepoint on error. So if a
statement fails it rolls back *just that statement*, automatically, without
requiring a manual "ROLLBACK".

There is precedent for this - psqlODBC supports it in the form of the
"statement" mode for its rollback on error setting.

PgJDBC could offer an option for that too, where it did automatic
savepoints and did a rollback automatically on an error. It would be slow
and inefficient, but there are certainly applications that would benefit
from it.

It would never be the default, but that's why we have JDBC connection
options - so you can customise driver behaviour for your application's
needs.

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

Um. That's not PL/SQL. PL/SQL is Oracle's procedural variant of SQL. What
you're showing below seems to be plain PostgreSQL-dialect SQL in the psql
command line client. Nothing to do with PL/SQL (or PL/PgSQL, PostgreSQL's
own procedural SQL variant).

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

Yes, exactly the same behaviour as under JDBC.

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

That's not a question, but if I assume you meant "why is it": because
PgJDBC is an interface to PostgreSQL, and that's what PostgreSQL does.

Now, if you want to argue that the JDBC specification requires us to do
something different to how PostgreSQL behaves by default, that's fine but
you'd better cite the parts of the spec that require that.

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