Re: JDBC behaviour
| От | Craig Ringer |
|---|---|
| Тема | Re: JDBC behaviour |
| Дата | |
| Msg-id | CAMsr+YH1qX3Opj597aSHsBUNkREDL0kRuDWser_owmzRfJqSSg@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: JDBC behaviour (Sridhar N Bamandlapally <sridhar.bn1@gmail.com>) |
| Список | pgsql-jdbc |
On 18 February 2016 at 17:38, Sridhar N Bamandlapally <sridhar.bn1@gmail.com> wrote:
let me put this waytable 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;BEGINpostgres=# insert into employee values (1,'aa');INSERT 0 1postgres=# insert into employee values (2,'bb');INSERT 0 1postgres=# insert into employee values (3,'cc');INSERT 0 1postgres=# 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.
В списке pgsql-jdbc по дате отправления: