Postgres Pro
Downloads
Home   >   mailing lists

Re: JDBC behaviour - Mailing list pgsql-jdbc

From Craig Ringer
Subject Re: JDBC behaviour
Date
Msg-id CAMsr+YH1qX3Opj597aSHsBUNkREDL0kRuDWser_owmzRfJqSSg@mail.gmail.com
Whole thread Raw
In response to Re: JDBC behaviour  (Sridhar N Bamandlapally <sridhar.bn1@gmail.com>)
List pgsql-jdbc
Tree view
On 18 February 2016 at 17:38, Sridhar N Bamandlapally <sridhar.bn1@gmail.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

pgsql-jdbc by date:

Previous
From: Thomas Kellerer
Date:
Subject: Re: JDBC behaviour
Next
From: John R Pierce
Date:
Subject: Re: JDBC behaviour