JDBC behaviour
Hi
We are facing issue with PostgreSQL JDBC behaviour
in Java, under autocommit false mode,
1.in between if any transaction
Hi
We are facing issue with PostgreSQL JDBC behaviour
in Java, under autocommit false mode,
1. In between if any transaction then for next transaction, throws
exception saying "current transaction is aborted, commands ignored until
end of transaction block"
2. Even if exception is suppressed with try-catch then too for next
transaction, throws exception saying "current transaction is aborted,
commands ignored until end of transaction block"
3. The same is not happening with Oracle or SQL-Server, in this with-out
any exception handling it works
Is it a bug or do we have other any alternate way to handle this ?
Please I need some help in this
Thanks
Sridhar
Is it a bug or do we have other any alternate way to handle this ?
PostgreSQL is strongly against "partial commits to the database". If
you think a bit about it, it is not that bad.
You got an error, what is the business case to commit the partial
transaction then?
Exceptions should not be used for a "control flow", should they?
If you want to shoot yourself in a foot for fun and profit, you can
try https://github.com/pgjdbc/pgjdbc/pull/477.
What it does, it creates savepoints before each statement, then it
rollbacks to that savepoint in case of failure.
Vladimir
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
If we want transactions in "begin-end" then its fine,
but in this case all these transactions are independent with autocommit off,
user choice to continue with commit or rollback
Thanks
Sridhar
On Thu, Feb 18, 2016 at 1:43 PM, Vladimir Sitnikov <
sitnikov.vladimir@gmail.com> wrote:
Show quoted text
Is it a bug or do we have other any alternate way to handle this ?
PostgreSQL is strongly against "partial commits to the database". If
you think a bit about it, it is not that bad.
You got an error, what is the business case to commit the partial
transaction then?Exceptions should not be used for a "control flow", should they?
If you want to shoot yourself in a foot for fun and profit, you can
try https://github.com/pgjdbc/pgjdbc/pull/477.
What it does, it creates savepoints before each statement, then it
rollbacks to that savepoint in case of failure.Vladimir
På torsdag 18. februar 2016 kl. 09:26:59, skrev Sridhar N Bamandlapally <
sridhar.bn1@gmail.com <mailto:sridhar.bn1@gmail.com>>:
If we want transactions in "begin-end" then its fine,
but in this case all these transactions are independent with autocommit off,
user choice to continue with commit or rollback
Look in you serverlogs, "something" is issuing a BEGIN. The transaction
becomes invalid when an error occurs, you have to issue a COMMIT or ROLLBACK,
and in this case both will ROLLBACK.
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com>
<https://www.visena.com>
but in this case all these transactions are independent with autocommit off,
At database level, there is no "autocommit=off".
There's just "begin-end".
It is database who forbids .commit, not the JDBC driver.
Vladimir
--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
Ok, let me put this way
in JDBC we have *setAutoCommit( false ) *, and all dmls are independent
transactions
and when any transaction fails then the session not allowing next
transactions
in Java when we do setAutoCommit( false ) its behaving like all
transactions in BEGIN-END block, this is not expected behavior
i guess this is bug
On Thu, Feb 18, 2016 at 2:00 PM, Vladimir Sitnikov <
sitnikov.vladimir@gmail.com> wrote:
Show quoted text
but in this case all these transactions are independent with autocommit
off,
At database level, there is no "autocommit=off".
There's just "begin-end".It is database who forbids .commit, not the JDBC driver.
Vladimir
På torsdag 18. februar 2016 kl. 09:51:47, skrev Sridhar N Bamandlapally <
sridhar.bn1@gmail.com <mailto:sridhar.bn1@gmail.com>>:
Ok, let me put this way
in JDBC we have setAutoCommit( false ) , and all dmls are independent
transactions
and when any transaction fails then the session not allowing next transactions
in Java when we do setAutoCommit( false ) its behaving like all transactions
in BEGIN-END block, this is not expected behavior
i guess this is bug
No, you got it backwards. With autocommit=false all statements are NOT
independent transactions.
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com>
<https://www.visena.com>
setAutoCommit(false), it should not be treating all next transactions as
single set, simple, this is what expected behavior
On Thu, Feb 18, 2016 at 2:34 PM, Andreas Joseph Krogh <andreas@visena.com>
wrote:
Show quoted text
På torsdag 18. februar 2016 kl. 09:51:47, skrev Sridhar N Bamandlapally <
sridhar.bn1@gmail.com>:Ok, let me put this way
in JDBC we have *setAutoCommit( false ) *, and all dmls are independent
transactionsand when any transaction fails then the session not allowing next
transactionsin Java when we do setAutoCommit( false ) its behaving like all
transactions in BEGIN-END block, this is not expected behaviori guess this is bug
No, you got it backwards. With autocommit=false all statements are NOT
independent transactions.--
*Andreas Joseph Krogh*
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas@visena.com
www.visena.com
<https://www.visena.com>
Sridhar,
Please refer to documentation (see [1]https://docs.oracle.com/javase/7/docs/api/java/sql/Connection.html#setAutoCommit(boolean) Vladimir).
Specification>Otherwise, its SQL statements are grouped into transactions
that are terminated by
Specification>a call to either the method commit or the method rollback
Sridhar> it should not be treating all next transactions as single set
What do you mean by "all next transactions"?
[1]: https://docs.oracle.com/javase/7/docs/api/java/sql/Connection.html#setAutoCommit(boolean) Vladimir
https://docs.oracle.com/javase/7/docs/api/java/sql/Connection.html#setAutoCommit(boolean)
Vladimir
På torsdag 18. februar 2016 kl. 10:20:51, skrev Sridhar N Bamandlapally <
sridhar.bn1@gmail.com <mailto:sridhar.bn1@gmail.com>>:
setAutoCommit(false), it should not be treating all next transactions as
single set, simple, this is what expected behavior
The point is that all subsequent statements (after an exception) are part of
the same transaction (in autocommit=false mode). So you have to issue an
explicit ROLLBACK before any new statement can give meaningful results.
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com>
<https://www.visena.com>
On 2/18/2016 12:26 AM, Sridhar N Bamandlapally wrote:
If we want transactions in "begin-end" then its fine,
but in this case all these transactions are independent with
autocommit off,
with autocommit OFF, when you issue the first query, jdbc generates a
postgresql BEGIN; this starts a postgresql transaction. To end the
transaction, you have to explicitly .commit() or .rollback() ....
with autocommit ON, then jdbc lets each query execute standalone, this
is postgresql's default behavior if you don't use BEGIN...
--
john r pierce, recycling bits in santa cruz
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
let me put this way
table employee ( id PrimaryKey, name )
In Java ( just little pseudo-code way )
try {
conn.setAutoCommit(false);
try { executeUpdate("insert into employee(id,name) values(1, 'K1')");
} catch ...
try { executeUpdate("insert into employee(id,name) values(1, 'K1')");
} catch ...
try { executeUpdate("insert into employee(id,name) values(1, 'K2')");
} catch ...
conn.commit();
} catch ...
throws error
1. duplicate key value violates unique constraint "employee_pkey"
2. current transaction is aborted, commands ignored until end of
transaction block
In PL/SQL ( similar error thrown when used BEGIN-END )
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
my question Java setAutoCommit (false) is behaving like PL/SQL BEGIN-END
On Thu, Feb 18, 2016 at 2:54 PM, Andreas Joseph Krogh <andreas@visena.com>
wrote:
Show quoted text
På torsdag 18. februar 2016 kl. 10:20:51, skrev Sridhar N Bamandlapally <
sridhar.bn1@gmail.com>:setAutoCommit(false), it should not be treating all next transactions as
single set, simple, this is what expected behaviorThe point is that all subsequent statements (after an exception) are part
of *the same* transaction (in autocommit=false mode). So you have to
issue an explicit ROLLBACK before any new statement can give meaningful
results.--
*Andreas Joseph Krogh*
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas@visena.com
www.visena.com
<https://www.visena.com>
yes John,
but why?, this is not expected behavior
autocommit is just autocommit, its not responsible to treat
multi-transactions as single or independent
On Thu, Feb 18, 2016 at 3:08 PM, Sridhar N Bamandlapally <
sridhar.bn1@gmail.com> wrote:
Show quoted text
let me put this way
table employee ( id PrimaryKey, name )
In Java ( just little pseudo-code way )
try {
conn.setAutoCommit(false);
try { executeUpdate("insert into employee(id,name) values(1, 'K1')");
} catch ...
try { executeUpdate("insert into employee(id,name) values(1, 'K1')");
} catch ...
try { executeUpdate("insert into employee(id,name) values(1, 'K2')");
} catch ...
conn.commit();
} catch ...throws error
1. duplicate key value violates unique constraint "employee_pkey"
2. current transaction is aborted, commands ignored until end of
transaction blockIn PL/SQL ( similar error thrown when used BEGIN-END )
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 blockmy question Java setAutoCommit (false) is behaving like PL/SQL BEGIN-END
On Thu, Feb 18, 2016 at 2:54 PM, Andreas Joseph Krogh <andreas@visena.com>
wrote:På torsdag 18. februar 2016 kl. 10:20:51, skrev Sridhar N Bamandlapally <
sridhar.bn1@gmail.com>:setAutoCommit(false), it should not be treating all next transactions as
single set, simple, this is what expected behaviorThe point is that all subsequent statements (after an exception) are part
of *the same* transaction (in autocommit=false mode). So you have to
issue an explicit ROLLBACK before any new statement can give meaningful
results.--
*Andreas Joseph Krogh*
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas@visena.com
www.visena.com
<https://www.visena.com>
På torsdag 18. februar 2016 kl. 10:38:23, skrev Sridhar N Bamandlapally <
sridhar.bn1@gmail.com <mailto:sridhar.bn1@gmail.com>>:
let me put this way
table employee ( id PrimaryKey, name )
In Java ( just little pseudo-code way )
try {
conn.setAutoCommit(false);
try { executeUpdate("insert into employee(id,name) values(1, 'K1')"); }
catch ...
try { executeUpdate("insert into employee(id,name) values(1, 'K1')"); }
catch ...
try { executeUpdate("insert into employee(id,name) values(1, 'K2')"); }
catch ...
conn.commit();
} catch ...
throws error
1. duplicate key value violates unique constraint "employee_pkey"
2. current transaction is aborted, commands ignored until end of transaction
block
In PL/SQL ( similar error thrown when used BEGIN-END )
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
my question Java setAutoCommit (false) is behaving like PL/SQL BEGIN-END
You are free to ignore what we tell you about the transaction being invalid
after an exception, and try all you want to issue new statements. But it will
all result in errors like the above. Youhave to issue a ROLLBACK to proceed,
and PG knows this so it refuses to do anything until you do.
I other words; There is no way to issue a statement without getting an error
in a transaction marked as invalid. This is what transactions are for,
guaranteeing consistent results.
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com>
<https://www.visena.com>
On 2/18/2016 1:42 AM, Sridhar N Bamandlapally wrote:
but why?, this is not expected behavior
autocommit is just autocommit, its not responsible to treat
multi-transactions as single or independent
its exactly how jdbc is defined to work, fit into postgres's
transaction model.
if autocommit is ON, then every statement is a standalone query.
if autocommit is OFF, then its in transaction mode. JDBC has an
implicit transaction start when you execute the first query, and you
have to commit or rollback the transaction.
if you want each INSERT to run indepedently, then use autocommit ON.
--
john r pierce, recycling bits in santa cruz
--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
Andreas, ROLLBACK doesn't work as it do for all previous statements,
autocommit ON is not option as it controlled by connection pool,
with this the simple "statement" with need to replace with "try-catch",
"savepoint", "statement" and "rollback to savepoint", this looks lot of
changes in code to make it app/java-database compatible,
still, this is bug
On Thu, Feb 18, 2016 at 3:22 PM, John R Pierce <pierce@hogranch.com> wrote:
Show quoted text
On 2/18/2016 1:42 AM, Sridhar N Bamandlapally wrote:
but why?, this is not expected behavior
autocommit is just autocommit, its not responsible to treat
multi-transactions as single or independentits exactly how jdbc is defined to work, fit into postgres's transaction
model.if autocommit is ON, then every statement is a standalone query.
if autocommit is OFF, then its in transaction mode. JDBC has an
implicit transaction start when you execute the first query, and you have
to commit or rollback the transaction.if you want each INSERT to run indepedently, then use autocommit ON.
--
john r pierce, recycling bits in santa cruz--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
still, this is bug
It is as per specification.
Vladimir
--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
På torsdag 18. februar 2016 kl. 11:02:20, skrev Sridhar N Bamandlapally <
sridhar.bn1@gmail.com <mailto:sridhar.bn1@gmail.com>>:
Andreas, ROLLBACK doesn't work as it do for all previous statements,
autocommit ON is not option as it controlled by connection pool,
with this the simple "statement" with need to replace with "try-catch",
"savepoint", "statement" and "rollback to savepoint", this looks lot of changes
in code to make it app/java-database compatible,
still, this is bug
ROLLBACK is not a per-statement command, but a per-transaction.
I think you have to re-read the answers given in this thread.
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com>
<https://www.visena.com>
if you want each insert to work indepedently yet stay with the
transaction model, then each insert should be its own transaction...
conn.setAutoCommit(false);
executeUpdate("insert into employee(id,name) values(1, 'K1')");
conn.commit();
executeUpdate("insert into employee(id,name) values(1, 'K1')");
conn.commit();
executeUpdate("insert into employee(id,name) values(1, 'K2')");
conn.commit();
otherwise the way you wrote it, its a single transaction. all three
inserts have to succeed, otherwise, all three are rolledback. why is
that so hard to understand?
--
john r pierce, recycling bits in santa cruz
--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
The code/framework is written to handle batch inserts, which is common for
all databases
I feel, PostgreSQL JDBC may need to modify setAutoCommit(false) code to
"implicit savepoint - on error - rollback to savepoint"
On Thu, Feb 18, 2016 at 3:59 PM, John R Pierce <pierce@hogranch.com> wrote:
Show quoted text
if you want each insert to work indepedently yet stay with the transaction
model, then each insert should be its own transaction...conn.setAutoCommit(false);
executeUpdate("insert into employee(id,name) values(1, 'K1')");
conn.commit();
executeUpdate("insert into employee(id,name) values(1, 'K1')");
conn.commit();
executeUpdate("insert into employee(id,name) values(1, 'K2')");
conn.commit();otherwise the way you wrote it, its a single transaction. all three
inserts have to succeed, otherwise, all three are rolledback. why is
that so hard to understand?--
john r pierce, recycling bits in santa cruz--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
On 18 February 2016 at 05:43, Sridhar N Bamandlapally <sridhar.bn1@gmail.com
wrote:
The code/framework is written to handle batch inserts, which is common for
all databasesI feel, PostgreSQL JDBC may need to modify setAutoCommit(false) code to
"implicit savepoint - on error - rollback to savepoint"
This would completely nullify the performance advantage of batch inserts.
Imagine that instead of sending N inserts we now had to do a savepoint
between each one.
PostgreSQL semantics around transaction processing are such that if one
fails they all fail. Unless this can be changed in the backend the driver
is unlikely to support this.
Dave Cramer
davec@postgresintl.com
www.postgresintl.com
På torsdag 18. februar 2016 kl. 11:43:36, skrev Sridhar N Bamandlapally <
sridhar.bn1@gmail.com <mailto:sridhar.bn1@gmail.com>>:
The code/framework is written to handle batch inserts, which is common for all
databases
I feel, PostgreSQL JDBC may need to modify setAutoCommit(false) code to
"implicit savepoint - on error - rollback to savepoint"
You simply cannot have batch-inserts in the same transaction and expecting the
batch not to fail if one of the statements in the batch fails.
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com>
<https://www.visena.com>
På torsdag 18. februar 2016 kl. 11:59:50, skrev Andreas Joseph Krogh <
andreas@visena.com <mailto:andreas@visena.com>>:
På torsdag 18. februar 2016 kl. 11:43:36, skrev Sridhar N Bamandlapally <
sridhar.bn1@gmail.com <mailto:sridhar.bn1@gmail.com>>:
The code/framework is written to handle batch inserts, which is common for all
databases
I feel, PostgreSQL JDBC may need to modify setAutoCommit(false) code to
"implicit savepoint - on error - rollback to savepoint"
You simply cannot have batch-inserts in the same transaction and expecting the
batch not to fail if one of the statements in the batch fails.
Note that it's perfectly doable to have a connection-pool configured with
autocommit=false and do the transaction-management your self. Then you can do
whatever you want when one statement fails. You would want to rollback that
statement...
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com>
<https://www.visena.com>
our scenario is to commit successful transaction without worry of failed
transactions ( these will be logged for verification into error-log-table )
however, the only method for us in java/framework is to put
postgresql-checkpoint and do "try-catch, savepoint, statement, on-error
rollback to savepoint"
On Thu, Feb 18, 2016 at 4:32 PM, Andreas Joseph Krogh <andreas@visena.com>
wrote:
Show quoted text
På torsdag 18. februar 2016 kl. 11:59:50, skrev Andreas Joseph Krogh <
andreas@visena.com>:På torsdag 18. februar 2016 kl. 11:43:36, skrev Sridhar N Bamandlapally <
sridhar.bn1@gmail.com>:The code/framework is written to handle batch inserts, which is common for
all databasesI feel, PostgreSQL JDBC may need to modify setAutoCommit(false) code to
"implicit savepoint - on error - rollback to savepoint"You simply cannot have batch-inserts in the same transaction and expecting
the batch not to fail if one of the statements in the batch fails.Note that it's perfectly doable to have a connection-pool configured with
autocommit=false and do the transaction-management your self. Then you can
do whatever you want when one statement fails. You would want to rollback
that statement...--
*Andreas Joseph Krogh*
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas@visena.com
www.visena.com
<https://www.visena.com>
my point is functionality is first priority and then comes performance,
no meaning of performance without functionality
autocommit off, should do just autocommit off only, nothing else
On Thu, Feb 18, 2016 at 4:41 PM, Sridhar N Bamandlapally <
sridhar.bn1@gmail.com> wrote:
Show quoted text
our scenario is to commit successful transaction without worry of failed
transactions ( these will be logged for verification into error-log-table )however, the only method for us in java/framework is to put
postgresql-checkpoint and do "try-catch, savepoint, statement, on-error
rollback to savepoint"On Thu, Feb 18, 2016 at 4:32 PM, Andreas Joseph Krogh <andreas@visena.com>
wrote:På torsdag 18. februar 2016 kl. 11:59:50, skrev Andreas Joseph Krogh <
andreas@visena.com>:På torsdag 18. februar 2016 kl. 11:43:36, skrev Sridhar N Bamandlapally <
sridhar.bn1@gmail.com>:The code/framework is written to handle batch inserts, which is common
for all databasesI feel, PostgreSQL JDBC may need to modify setAutoCommit(false) code to
"implicit savepoint - on error - rollback to savepoint"You simply cannot have batch-inserts in the same transaction and
expecting the batch not to fail if one of the statements in the batch fails.Note that it's perfectly doable to have a connection-pool configured with
autocommit=false and do the transaction-management your self. Then you can
do whatever you want when one statement fails. You would want to rollback
that statement...--
*Andreas Joseph Krogh*
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas@visena.com
www.visena.com
<https://www.visena.com>
my point is functionality is first
Please, go ahead and RTFM on "setAutoCommit".
You definitely misunderstand how it is supposed to behave.
Vladimir
On Thu, 18 Feb 2016 11:59:50 +0100 (CET), Andreas Joseph Krogh
<andreas@visena.com> wrote:
På torsdag 18. februar 2016 kl. 11:43:36, skrev Sridhar N Bamandlapally
<
sridhar.bn1@gmail.com <mailto:sridhar.bn1@gmail.com>>:
The code/framework is written to handle batch inserts, which is common
for
all
databases
I feel, PostgreSQL JDBC may need to modify setAutoCommit(false) code to
"implicit savepoint - on error - rollback to savepoint"
You simply cannot have batch-inserts in the same transaction and
expecting
the
batch not to fail if one of the statements in the batch fails.
On a lot of other database systems, that is exactly how it works. If a
statement fails, that one statement is backed out (rolled back), and it is
still up to the user to decide if he wants to commit or rollback the
statements that did succeed.
Mark
--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
On 18 February 2016 at 07:09, Mark Rotteveel <mark@lawinegevaar.nl> wrote:
On Thu, 18 Feb 2016 11:59:50 +0100 (CET), Andreas Joseph Krogh
<andreas@visena.com> wrote:På torsdag 18. februar 2016 kl. 11:43:36, skrev Sridhar N Bamandlapally
<
sridhar.bn1@gmail.com <mailto:sridhar.bn1@gmail.com>>:
The code/framework is written to handle batch inserts, which is commonfor
all
databases
I feel, PostgreSQL JDBC may need to modify setAutoCommit(false) code to
"implicit savepoint - on error - rollback to savepoint"You simply cannot have batch-inserts in the same transaction and
expecting
the
batch not to fail if one of the statements in the batch fails.On a lot of other database systems, that is exactly how it works. If a
statement fails, that one statement is backed out (rolled back), and it is
still up to the user to decide if he wants to commit or rollback the
statements that did succeed.
This behaviour is an artifact of PostgreSQL. If you want to change the
transaction semantics of PostgreSQL then pgsql-hackers is the place to take
this up.
JDBC is just an interface. We aren't going to rewrite the backend semantics
to meet everyones needs/wants.
Dave Cramer
davec@postgresintl.com
www.postgresintl.com
On Thu, 18 Feb 2016 07:15:11 -0500, Dave Cramer <pg@fastcrypt.com> wrote:
On 18 February 2016 at 07:09, Mark Rotteveel <mark@lawinegevaar.nl>
wrote:
On Thu, 18 Feb 2016 11:59:50 +0100 (CET), Andreas Joseph Krogh
<andreas@visena.com> wrote:
You simply cannot have batch-inserts in the same transaction and
expecting
the
batch not to fail if one of the statements in the batch fails.On a lot of other database systems, that is exactly how it works. If a
statement fails, that one statement is backed out (rolled back), and it
is
still up to the user to decide if he wants to commit or rollback the
statements that did succeed.This behaviour is an artifact of PostgreSQL. If you want to change the
transaction semantics of PostgreSQL then pgsql-hackers is the place to
take
this up.
JDBC is just an interface. We aren't going to rewrite the backend
semantics
to meet everyones needs/wants.
I understand that and indeed this isn't something that should be handled
by the driver, however some of the response in this thread seem to think it
is an absurd expectation from the OP that failure of one statement should
still allow a commit. Which it isn't if you look at what other database
systems do.
Mark
--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
På torsdag 18. februar 2016 kl. 13:22:15, skrev Mark Rotteveel <
mark@lawinegevaar.nl <mailto:mark@lawinegevaar.nl>>:
On Thu, 18 Feb 2016 07:15:11 -0500, Dave Cramer <pg@fastcrypt.com> wrote:
On 18 February 2016 at 07:09, Mark Rotteveel <mark@lawinegevaar.nl>
wrote:
On Thu, 18 Feb 2016 11:59:50 +0100 (CET), Andreas Joseph Krogh
<andreas@visena.com> wrote:
You simply cannot have batch-inserts in the same transaction and
expecting
the
batch not to fail if one of the statements in the batch fails.On a lot of other database systems, that is exactly how it works. If a
statement fails, that one statement is backed out (rolled back), and it
is
still up to the user to decide if he wants to commit or rollback the
statements that did succeed.This behaviour is an artifact of PostgreSQL. If you want to change the
transaction semantics of PostgreSQL then pgsql-hackers is the place to
take
this up.
JDBC is just an interface. We aren't going to rewrite the backend
semantics
to meet everyones needs/wants.
I understand that and indeed this isn't something that should be handled
by the driver, however some of the response in this thread seem to think it
is an absurd expectation from the OP that failure of one statement should
still allow a commit. Which it isn't if you look at what other database
systems do.
Mark
If that one failed statement doesn't raise an exception, how does the client
(code) know that it failed? If it does raise an exception, then what standard
specifies that that specific exceptions is to be treated as "don't rollback for
this type of error"?
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
andreas@visena.com <mailto:andreas@visena.com>
www.visena.com <https://www.visena.com>
<https://www.visena.com>
Unfortunately, OP seems to ignore all the inputs, thus no "advanced"
recommendations.
Mark>I understand that and indeed this isn't something that should be handled
Mark>by the driver
psql has special "transparent auto-rollback-to-auto-savepoint", thus
there's a certain trend to workaround database strictness.
As I posted earlier, there is a work-in-progress implementation of
exactly that functionality at the driver level:
https://github.com/pgjdbc/pgjdbc/pull/477
Of course it won't be able to "rollback just a single row of a whole
batch", however batch DML is often split to sub-batches. The
savepoints can be in between those sub-batches.
Just one more time:
1) If the application is developed via PostgreSQL from the ground up,
then "transacted aborted" is more likely to be a feature.
2) If the application is migrated from other databases, then excessive
strictness might be a "bug". That is, the application should be
eventually be adapted not to throw exceptions, however, "transaction
aborted" might easily be a blocker to migration to PG. Frankly
speaking, "autosavepoint" is somewhat that might sense. It is not
something crazy like returning 0 from 0/0.
Vladimir
--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
On Thu, 18 Feb 2016 13:48:04 +0100 (CET), Andreas Joseph Krogh
<andreas@visena.com> wrote:
I understand that and indeed this isn't something that should be
handled
by the driver, however some of the response in this thread seem to
think
it
is an absurd expectation from the OP that failure of one statement
should
still allow a commit. Which it isn't if you look at what other database
systems do.Mark
If that one failed statement doesn't raise an exception, how does the
client
(code) know that it failed? If it does raise an exception, then what
standard
specifies that that specific exceptions is to be treated as "don't
rollback for
this type of error"?
Of course an exception is raised, but the exact handling could then be
left to the client. For example the client could catch the exception,
decide based on the specific error to execute another statement to "fix"
the error condition and then commit. Think of INSERT, duplicate key, then
UPDATE before the existence of 'UPSERT'-like statements; if the occurrence
of duplicate key is rare it can be cheaper to do than to first SELECT to
check for existence and then INSERT or UPDATE, or to UPDATE, INSERT when
update count = 0. Another situation could be where the failure is not
important (eg it was only a log entry that is considered supporting, not
required), so the exception is ignored and the transaction as a whole is
committed.
Sure, in most cases it is abusing exceptions for flow control and likely
an example of bad design, but the point is that it is not outlandish to
allow execution of other statements and eventually a commit of a
transaction even if one or more statements failed in that transaction; as
demonstrated by systems that do allow this (for SQL Server you need to set
XACT_ABORT mode on to get similar behavior as PostgreSQL).
As to standards, for batch execution, JDBC expects that a driver either
process up to the first failure and raise a BatchUpdateException with the
update counts of the successfully executed statements, or continue
processing after failure(s) and only raise the exception after processing
the remainder of the batch (where the exception contains a mix of update
counts + failure indications). In both cases a commit for the statements
that were processed successfully would still be possible if the client so
wishes (see section 14.1.3 "Handling Failures during Execution" of JDBC
4.2).
Mark
--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
There are many reasons why this is required,
1. Postgres migrated client percentage is high,
2. For application developers this looks like bug in Postgres, as it throw
exception for next transaction even when current exception
suppressed/handled,
3. Most of non-financial application or data-ware-house application have
batch transaction process where successful transaction goes into
data-tables and failed transactions goes into error-log-tables,
this is most generic requirement
cannot effort any reason if client think about rollback to old database or
feel not meeting requirements -- please ignore
On Thu, Feb 18, 2016 at 7:06 PM, Mark Rotteveel <mark@lawinegevaar.nl>
wrote:
Show quoted text
On Thu, 18 Feb 2016 13:48:04 +0100 (CET), Andreas Joseph Krogh
<andreas@visena.com> wrote:I understand that and indeed this isn't something that should be
handled
by the driver, however some of the response in this thread seem to
think
it
is an absurd expectation from the OP that failure of one statementshould
still allow a commit. Which it isn't if you look at what other database
systems do.Mark
If that one failed statement doesn't raise an exception, how does the
client
(code) know that it failed? If it does raise an exception, then what
standard
specifies that that specific exceptions is to be treated as "don't
rollback for
this type of error"?Of course an exception is raised, but the exact handling could then be
left to the client. For example the client could catch the exception,
decide based on the specific error to execute another statement to "fix"
the error condition and then commit. Think of INSERT, duplicate key, then
UPDATE before the existence of 'UPSERT'-like statements; if the occurrence
of duplicate key is rare it can be cheaper to do than to first SELECT to
check for existence and then INSERT or UPDATE, or to UPDATE, INSERT when
update count = 0. Another situation could be where the failure is not
important (eg it was only a log entry that is considered supporting, not
required), so the exception is ignored and the transaction as a whole is
committed.Sure, in most cases it is abusing exceptions for flow control and likely
an example of bad design, but the point is that it is not outlandish to
allow execution of other statements and eventually a commit of a
transaction even if one or more statements failed in that transaction; as
demonstrated by systems that do allow this (for SQL Server you need to set
XACT_ABORT mode on to get similar behavior as PostgreSQL).As to standards, for batch execution, JDBC expects that a driver either
process up to the first failure and raise a BatchUpdateException with the
update counts of the successfully executed statements, or continue
processing after failure(s) and only raise the exception after processing
the remainder of the batch (where the exception contains a mix of update
counts + failure indications). In both cases a commit for the statements
that were processed successfully would still be possible if the client so
wishes (see section 14.1.3 "Handling Failures during Execution" of JDBC
4.2).Mark
--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
On 18 February 2016 at 11:57, Sridhar N Bamandlapally <sridhar.bn1@gmail.com
wrote:
There are many reasons why this is required,
1. Postgres migrated client percentage is high,
2. For application developers this looks like bug in Postgres, as it throw
exception for next transaction even when current exception
suppressed/handled,3. Most of non-financial application or data-ware-house application have
batch transaction process where successful transaction goes into
data-tables and failed transactions goes into error-log-tables,this is most generic requirement
cannot effort any reason if client think about rollback to old database or
feel not meeting requirements -- please ignore
Please take this up with pgsql-hackers..
This is not something JDBC can solve
Dave Cramer
davec@postgresintl.com
www.postgresintl.com
Show quoted text
On Thu, Feb 18, 2016 at 7:06 PM, Mark Rotteveel <mark@lawinegevaar.nl>
wrote:On Thu, 18 Feb 2016 13:48:04 +0100 (CET), Andreas Joseph Krogh
<andreas@visena.com> wrote:I understand that and indeed this isn't something that should be
handled
by the driver, however some of the response in this thread seem to
think
it
is an absurd expectation from the OP that failure of one statementshould
still allow a commit. Which it isn't if you look at what other database
systems do.Mark
If that one failed statement doesn't raise an exception, how does the
client
(code) know that it failed? If it does raise an exception, then what
standard
specifies that that specific exceptions is to be treated as "don't
rollback for
this type of error"?Of course an exception is raised, but the exact handling could then be
left to the client. For example the client could catch the exception,
decide based on the specific error to execute another statement to "fix"
the error condition and then commit. Think of INSERT, duplicate key, then
UPDATE before the existence of 'UPSERT'-like statements; if the occurrence
of duplicate key is rare it can be cheaper to do than to first SELECT to
check for existence and then INSERT or UPDATE, or to UPDATE, INSERT when
update count = 0. Another situation could be where the failure is not
important (eg it was only a log entry that is considered supporting, not
required), so the exception is ignored and the transaction as a whole is
committed.Sure, in most cases it is abusing exceptions for flow control and likely
an example of bad design, but the point is that it is not outlandish to
allow execution of other statements and eventually a commit of a
transaction even if one or more statements failed in that transaction; as
demonstrated by systems that do allow this (for SQL Server you need to set
XACT_ABORT mode on to get similar behavior as PostgreSQL).As to standards, for batch execution, JDBC expects that a driver either
process up to the first failure and raise a BatchUpdateException with the
update counts of the successfully executed statements, or continue
processing after failure(s) and only raise the exception after processing
the remainder of the batch (where the exception contains a mix of update
counts + failure indications). In both cases a commit for the statements
that were processed successfully would still be possible if the client so
wishes (see section 14.1.3 "Handling Failures during Execution" of JDBC
4.2).Mark
--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
2. as it throw exception for next transaction even when current exception suppressed/handled,
You are plain wrong here.
Next **transaction** would be perfectly fine.
You are somehow ignoring the fact that to start that *next*
transaction you need a rollback/commit call.
next statement != next transaction.
Vladimir
--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
Dave Cramer <pg@fastcrypt.com> writes:
Please take this up with pgsql-hackers..
This is not something JDBC can solve
The reception on -hackers won't be any more positive. The performance and
backwards-compatibility problems associated with changing these semantics
mean that it won't happen. You need to perform an explicit savepoint
anywhere you want to be able to roll back to.
regards, tom lane
--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
Using ‘psql’ executing your example would yield the same result, a command error would cause a required rollback before proceeding. This tells you that this is how PostgreSQL, the database, is designed to work. It has nothing to do with the Java driver implementation.
You are asking the creators of a client driver implementation to change a fundamental behavior of the database. Repeatedly people have suggested you take this up with those creating the actual database (that’s the request to move this to the ‘-hackers’ list); yet you persist.
I’m only chiming in because it’s getting quite annoying to have you keep this thread alive when the situation has been made quite clear to you.
Show quoted text
On Feb 18, 2016, at 9:57 AM, Sridhar N Bamandlapally <sridhar.bn1@gmail.com> wrote:
There are many reasons why this is required,
1. Postgres migrated client percentage is high,
2. For application developers this looks like bug in Postgres, as it throw exception for next transaction even when current exception suppressed/handled,
3. Most of non-financial application or data-ware-house application have batch transaction process where successful transaction goes into data-tables and failed transactions goes into error-log-tables,
this is most generic requirement
cannot effort any reason if client think about rollback to old database or feel not meeting requirements -- please ignore
On Thu, Feb 18, 2016 at 7:06 PM, Mark Rotteveel <mark@lawinegevaar.nl <mailto:mark@lawinegevaar.nl>> wrote:
On Thu, 18 Feb 2016 13:48:04 +0100 (CET), Andreas Joseph Krogh
<andreas@visena.com <mailto:andreas@visena.com>> wrote:I understand that and indeed this isn't something that should be
handled
by the driver, however some of the response in this thread seem to
think
it
is an absurd expectation from the OP that failure of one statementshould
still allow a commit. Which it isn't if you look at what other database
systems do.Mark
If that one failed statement doesn't raise an exception, how does the
client
(code) know that it failed? If it does raise an exception, then what
standard
specifies that that specific exceptions is to be treated as "don't
rollback for
this type of error"?Of course an exception is raised, but the exact handling could then be
left to the client. For example the client could catch the exception,
decide based on the specific error to execute another statement to "fix"
the error condition and then commit. Think of INSERT, duplicate key, then
UPDATE before the existence of 'UPSERT'-like statements; if the occurrence
of duplicate key is rare it can be cheaper to do than to first SELECT to
check for existence and then INSERT or UPDATE, or to UPDATE, INSERT when
update count = 0. Another situation could be where the failure is not
important (eg it was only a log entry that is considered supporting, not
required), so the exception is ignored and the transaction as a whole is
committed.Sure, in most cases it is abusing exceptions for flow control and likely
an example of bad design, but the point is that it is not outlandish to
allow execution of other statements and eventually a commit of a
transaction even if one or more statements failed in that transaction; as
demonstrated by systems that do allow this (for SQL Server you need to set
XACT_ABORT mode on to get similar behavior as PostgreSQL).As to standards, for batch execution, JDBC expects that a driver either
process up to the first failure and raise a BatchUpdateException with the
update counts of the successfully executed statements, or continue
processing after failure(s) and only raise the exception after processing
the remainder of the batch (where the exception contains a mix of update
counts + failure indications). In both cases a commit for the statements
that were processed successfully would still be possible if the client so
wishes (see section 14.1.3 "Handling Failures during Execution" of JDBC
4.2).Mark
--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org <mailto:pgsql-jdbc@postgresql.org>)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc <http://www.postgresql.org/mailpref/pgsql-jdbc>
Hi All
I understand your point,
may be I didn't understand everyone or everyone didn't understand me
one feature of PostgreSQL is implemented into another feature of Java ( i
say subject PostgreSQL::autocommit Vs JDBC::setAutoCommit ),
i.e PostgreSQL::"set autocommit to FALSE" is implemented as
JDBC::"BEGIN-<statements>-END"
currently PostgreSQL::"set autocommit to FALSE ( not supported )
say in future, if PostgreSQL come with proper fix/support for "set
autocommit to FALSE" then will JDBC-team change the to code to JDBC::"set
autocommit to FALSE" ?, then what about existing behaviors dependency
applications ?
this could have handled in different way in blogs saying to add "BEGIN-END"
from JDBC-connection-query with warning
simple, if PostgreSQL DB is not support then same with PostgreSQL JDBC too,
if still JDBC want to support then need to support with expected behavior
way only, how come other feature is added to this ?
basically, decision/review seems to be wrong, may be bug in the decision
and why for this we are continuing/forcing the loop is, because
1. "every/entire application developers expected behavior are matching,
only PostgreSQL::JDBC-team is not in sync"
2. "every organisation want there applications to be multi-database
compatible, only PostgreSQL::JDBC-team <don't know what to say>"
however, looping hackers and ending the loop
sorry, for using hard words(if any), but as open-source we need to complete
transparent
Thanks
Sridhar
On Thu, Feb 18, 2016 at 11:03 PM, Kevin Wooten <kdubb@me.com> wrote:
Show quoted text
Using ‘psql’ executing your example would yield the same result, a command
error would cause a required rollback before proceeding. This tells you
that this is how PostgreSQL, the database, is designed to work. It has
nothing to do with the Java driver implementation.You are asking the creators of a client driver implementation to change a
fundamental behavior of the database. Repeatedly people have suggested you
take this up with those creating the actual database (that’s the request to
move this to the ‘-hackers’ list); yet you persist.I’m only chiming in because it’s getting quite annoying to have you keep
this thread alive when the situation has been made quite clear to you.On Feb 18, 2016, at 9:57 AM, Sridhar N Bamandlapally <
sridhar.bn1@gmail.com> wrote:There are many reasons why this is required,
1. Postgres migrated client percentage is high,
2. For application developers this looks like bug in Postgres, as it throw
exception for next transaction even when current exception
suppressed/handled,3. Most of non-financial application or data-ware-house application have
batch transaction process where successful transaction goes into
data-tables and failed transactions goes into error-log-tables,this is most generic requirement
cannot effort any reason if client think about rollback to old database or
feel not meeting requirements -- please ignoreOn Thu, Feb 18, 2016 at 7:06 PM, Mark Rotteveel <mark@lawinegevaar.nl>
wrote:On Thu, 18 Feb 2016 13:48:04 +0100 (CET), Andreas Joseph Krogh
<andreas@visena.com> wrote:I understand that and indeed this isn't something that should be
handled
by the driver, however some of the response in this thread seem to
think
it
is an absurd expectation from the OP that failure of one statementshould
still allow a commit. Which it isn't if you look at what other database
systems do.Mark
If that one failed statement doesn't raise an exception, how does the
client
(code) know that it failed? If it does raise an exception, then what
standard
specifies that that specific exceptions is to be treated as "don't
rollback for
this type of error"?Of course an exception is raised, but the exact handling could then be
left to the client. For example the client could catch the exception,
decide based on the specific error to execute another statement to "fix"
the error condition and then commit. Think of INSERT, duplicate key, then
UPDATE before the existence of 'UPSERT'-like statements; if the occurrence
of duplicate key is rare it can be cheaper to do than to first SELECT to
check for existence and then INSERT or UPDATE, or to UPDATE, INSERT when
update count = 0. Another situation could be where the failure is not
important (eg it was only a log entry that is considered supporting, not
required), so the exception is ignored and the transaction as a whole is
committed.Sure, in most cases it is abusing exceptions for flow control and likely
an example of bad design, but the point is that it is not outlandish to
allow execution of other statements and eventually a commit of a
transaction even if one or more statements failed in that transaction; as
demonstrated by systems that do allow this (for SQL Server you need to set
XACT_ABORT mode on to get similar behavior as PostgreSQL).As to standards, for batch execution, JDBC expects that a driver either
process up to the first failure and raise a BatchUpdateException with the
update counts of the successfully executed statements, or continue
processing after failure(s) and only raise the exception after processing
the remainder of the batch (where the exception contains a mix of update
counts + failure indications). In both cases a commit for the statements
that were processed successfully would still be possible if the client so
wishes (see section 14.1.3 "Handling Failures during Execution" of JDBC
4.2).Mark
--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
On 2/19/2016 8:40 PM, Sridhar N Bamandlapally wrote:
one feature of PostgreSQL is implemented into another feature of Java
( i say subject PostgreSQL::autocommit Vs JDBC::setAutoCommit ),
i.e PostgreSQL::"set autocommit to FALSE" is implemented as
JDBC::"BEGIN-<statements>-END"currently PostgreSQL::"set autocommit to FALSE ( not supported )
say in future, if PostgreSQL come with proper fix/support for "set
autocommit to FALSE" then will JDBC-team change the to code to
JDBC::"set autocommit to FALSE" ?, then what about existing behaviors
dependency applications ?
in autocommit 'off' mode, most SQL databases start a transaction when
you execute any modifying query, these transactions are by default
atomic, and nothing is written til you commit. any errors cause the
transaction to be put in failure mode, and the whole thing has to be
rolled back. there are differing levels of transaction isolation
depending on database capabilities and settings, but most relational
transactional databases adhere to these basic semantics, and
transactions are all or nothing.
postgres differs only in that it is always in autocommit ON mode, unless
you begin a transaction explicitly with BEGIN. the net effect is
exactly the same.
--
john r pierce, recycling bits in santa cruz
--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
On 20 February 2016 at 12:40, Sridhar N Bamandlapally <sridhar.bn1@gmail.com
wrote:
Hi All
I understand your point,
may be I didn't understand everyone or everyone didn't understand me
Sounds like it.
one feature of PostgreSQL is implemented into another feature of Java ( i
say subject PostgreSQL::autocommit Vs JDBC::setAutoCommit ),
There's no JDBC::setAutoCommit . If you're going to discuss behavour please
be very specific. Do you mean java.sql.Connection.setAutoCommit(boolean) ?
i.e PostgreSQL::"set autocommit to FALSE" is implemented as
JDBC::"BEGIN-<statements>-END"
This does not make any sense.
All setAutoCommit(false) does is tells the drive to begin a transaction
when the next statement is run and not commit it automatically. It doesn't
actually do anything its self.
It certainly doesn't run any block of statements.
By the way, "END" is kind of confusing. I presume you mean "COMMIT", which
is the more usual way to say that? PostgreSQL does support "END" as an
alias for COMMIT, but it's a pretty weird way to write it.
If you are going to discuss the behaviour of the driver please be specific
and accurate. Use the actual commands/queries/functions that the driver
uses or the specification describes, don't make up vague descriptions that
don't reflect what actually happens.
currently PostgreSQL::"set autocommit to FALSE ( not supported )
This also does not make any sense.
PgJDBC does support turning autocommit off. So I don't know in what way
it's "not supported".
say in future, if PostgreSQL come with proper fix/support for "set
autocommit to FALSE"
It already supports it.
The only behaviour change that might be contemplated is a change for spec
compliance where we delay commit of a statement in autocommit mode until
the ResultSet and/or Statement are closed. Right now we commit immediately,
which is what most users expect, but apparently conflicts with how the JDBC
spec expects things to work when it comes to the duration of locks being
held etc.
There was a prior discussion thread on this.
That's a (fairly) minor detail, though it could have a significant impact
on apps. It does not change the fact that PgJDBC supports autocommit on or
off and will continue to do so.
then will JDBC-team change the to code to JDBC::"set autocommit to FALSE"
?, then what about existing behaviors dependency applications ?
What behavour exactly are you talking about changing?
It already supports turning autocommit off.
this could have handled in different way in blogs saying to add
"BEGIN-END" from JDBC-connection-query with warning
I don't understand what you're trying to say here.
simple, if PostgreSQL DB is not support then same with PostgreSQL JDBC
too, if still JDBC want to support then need to support with expected
behavior way only, how come other feature is added to this ?
I don't understand this.
1. "every/entire application developers expected behavior are matching,
only PostgreSQL::JDBC-team is not in sync"
Please provide a complete, compileable, self-contained example
demonstrating behaviour that causes a failure or problem in PgJDBC but
works correctly with at least most of:
- MS SQL
- Oracle
- DB2
- Sybase
- MySQL
including test run output demonstrating the details of what exactly the
behaviour of each other implementation is.
Please show where in the JDBC specification the behaviour is described.
2. "every organisation want there applications to be multi-database
compatible, only PostgreSQL::JDBC-team <don't know what to say>"
Well, nobody's truly "multi-database compatible" because the SQL spec is in
some areas vague and hard to interpret. Every DBMS has extensions and
quirks. Oracle thinks that "" = NULL is TRUE, for example. JDBC
implementations vary too.
Of course it's desirable to be more consistent and compatible where that's
practical, but you need to actually show clear evidence that other DBMSes
all do it one way and we do it a different way. With real, detailed,
complete code examples and test output.
Hand-waving about how we're doing it wrong won't get you anywhere.
--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
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
On 18 February 2016 at 16:13, Vladimir Sitnikov <sitnikov.vladimir@gmail.com
wrote:
If you want to shoot yourself in a foot for fun and profit, you can
I think this should be incorporated, once it's ready, as a non-default
connection option. It's handy for porting applications.
I think PostgreSQL's behaviour is the most correct, but if people are
porting apps and want to wear the performance impact of all those
savepoints and have written their code to handle partially-failed xacts,
then sure, they can have that.
I'm constantly astounded by how many people are willing to simply ignore
errors and carry on with the transaction without even properly verifying
that the error was the exact one they expected though. Seriously bad
application development and it *will* bite them. The best, most correct
thing to do remains to retry the whole transaction.
--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
On 2/20/2016 2:44 AM, Craig Ringer wrote:
Hand-waving about how we're doing it wrong won't get you anywhere.
near as I can tell, the OP has used some sort of SQL (unspecified) where
multiple inserts within a transaction are individually inserted,
regardless of one failing.
to me this seems to break the rules of transaction semantics, but it
matches the behavior with autocommit=on ...
--
john r pierce, recycling bits in santa cruz
--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
On 20 February 2016 at 19:05, John R Pierce <pierce@hogranch.com> wrote:
to me this seems to break the rules of transaction semantics, but it
matches the behavior with autocommit=on ...
... which doesn't matter, because if you wanted that you'd just set
autocommit=on.
I think all this discussion of autocommit is a pointless side-track.
Reading between the lines, the real complaint appears to be that we abort
the xact if a statement ERRORs, and by default other DBMS vendors don't. OP
wants something like psqlODBC's "statement" error-rollback mode where
savepoints are done automatically before each statement.
Of course they're contending that PgJDBC's behaviour is wrong because it
doesn't do exactly what they expect, with no reference to the specs and
standards concerned, which doesn't help. They're also using a bunch of
incomplete pseudocode and vague references to make the argument, which
means it's all really rather hard to follow. But I'm pretty sure the above
is what they actually mean, like in
https://github.com/pgjdbc/pgjdbc/issues/423 .
--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
John R Pierce schrieb am 20.02.2016 um 12:05:
near as I can tell, the OP has used some sort of SQL (unspecified) where multiple inserts
within a transaction are individually inserted, regardless of one failing.
At least Oracle does it this way (and I think DB2 as well).
Oracle gets really slow if you do a row-by-row commit with large inserts. That's why
most people don't use auto-commit and just ignore any errors during inserts for batch loads.
to me this seems to break the rules of transaction semantics
I agree, the expected behaviour from the OP does violate the A in the ACID principle,
but apparently it's popular enough that people think the correct behaviour is a bug:
http://dba.stackexchange.com/a/129822/1822
--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
Craig Ringer schrieb am 20.02.2016 um 11:44:
Please provide a complete, compileable, self-contained example demonstrating behaviour that
causes a failure or problem in PgJDBC but works correctly with at least most of:- MS SQL
- Oracle
- DB2
- Sybase
- MySQLincluding test run output demonstrating the details of what exactly the behaviour of each other implementation is.
Disclaimer: I do agree with you that Postgres behaviour is correct. A transaction either succeeds completely or not at all.
Having said that: Postgres' behaviour *is* unique regarding this.
Consider the following table:
create table x (id integer not null primary key);
The following code will run with Oracle, DB2, SQL Sever, MySQL and Firebird:
---- code start ----
public class TestInsert
{
public static void main(String args[])
throws Exception
{
Connection con = DriverManager.getConnection("...", "...", "...");
con.setAutoCommit(false);
PreparedStatement pstmt = con.prepareStatement("insert into x (id) values (?)");
pstmt.setInt(1, 1);
pstmt.executeUpdate();
try
{
pstmt.setInt(1, 1);
pstmt.executeUpdate();
}
catch (Exception ex)
{
System.out.println("***** Error: " + ex.getMessage());
}
System.out.println("trying second row");
pstmt.setInt(1, 2);
pstmt.executeUpdate();
con.commit();
ResultSet rs = con.createStatement().executeQuery("select count(*) from x");
if (rs.next())
{
int rows = rs.getInt(1);
System.out.println("rows: " + rows);
}
con.close();
}
}
---- code end ----
With Oracle DB2, SQL Server, MySQL and Firebird you'll get something like this
(this is from Oracle, the error message will of course differ for the others)
***** Error: ORA-00001: Unique Constraint (THOMAS.SYS_C0021623) violated
trying second row
rows: 2
With Postgres you get:
***** Error: ERROR: duplicate key value violates unique constraint "x_pkey"
Detail: Key (id)=(1) already exists.
trying second row
Exception in thread "main" org.postgresql.util.PSQLException: ERROR: current transaction is aborted, commands ignored until end of transaction block
But again: in contrast to the OP I do not consider this a bug, it's just that Postgres behaves differently.
But I understand that it can be a problem when migrating applications that have a sloppy way of dealing with transactions.
I was part in the migration of several projects from Oracle and MySQL to Postgres in the last two years, none of them had problems regarding that.
Thomas
--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
This is really just a case of:
OP did not fully understand the transaction semantics of PostgreSQL.
OP wrote a bunch of code under the assumption that the transaction
semantics worked the way he thought it would work
OP considered this a bug
As you can see from the spec below the behaviour is not specified, however
we are compliant. We do not continue processing after the first failed
execution and we return an empty array.
14.1.3 Handling Failures during Execution A JDBC driver may or may not
continue processing the remaining commands in a batch once execution of a
command fails. However, a JDBC driver must always provide the same behavior
with a particular data source. For example, a driver cannot continue
processing after a failure for one batch and not continue processing for
another batch. If a driver stops processing after the first failure, the
array returned by the method BatchUpdateException.getUpdateCounts will
always contain fewer entries than there were statements in the batch. Since
statements are executed in the order that they are added to the batch, if
the array contains N elements, this means that the first N elements in the
batch were processed successfully when executeBatch was called. When a
driver continues processing in the presence of failures, the number of
elements in the array returned by the method
BatchUpdateException.getUpdateCounts always equals the number of commands
in the batch. When a BatchUpdateException object is thrown and the driver
continues processing after a failure, the array of update counts will
contain the following BatchUpdateException constant:
JDBC 4.1 Specification • July 2011 ■ Statement.EXECUTE_FAILED — the command
failed to execute successfully. This value is also returned for commands
that could not be processed for some reason—such commands fail implicitly.
JDBC drivers that do not continue processing after a failure never return
Statement.EXECUTE_FAILED in an update count array. Drivers of this type
simply return a status array containing an entry for each command that was
processed successfully. A JDBC technology-based application can distinguish
a JDBC driver that continues processing after a failure from one that does
not by examining the size of the array returned by
BatchUpdateException.getUpdateCounts. A JDBC driver that continues
processing always returns an array containing one entry for each element in
the batch. A JDBC driver that does not continue processing after a failure
will always return an array whose number of entries is less than the number
of commands in the batch.
Dave Cramer
davec@postgresintl.com
www.postgresintl.com
On 20 February 2016 at 09:51, Thomas Kellerer <spam_eater@gmx.net> wrote:
Show quoted text
Craig Ringer schrieb am 20.02.2016 um 11:44:
Please provide a complete, compileable, self-contained example
demonstrating behaviour that
causes a failure or problem in PgJDBC but works correctly with at least
most of:- MS SQL
- Oracle
- DB2
- Sybase
- MySQLincluding test run output demonstrating the details of what exactly the
behaviour of each other implementation is.Disclaimer: I do agree with you that Postgres behaviour is correct. A
transaction either succeeds completely or not at all.Having said that: Postgres' behaviour *is* unique regarding this.
Consider the following table:
create table x (id integer not null primary key);
The following code will run with Oracle, DB2, SQL Sever, MySQL and
Firebird:---- code start ----
public class TestInsert
{
public static void main(String args[])
throws Exception
{
Connection con = DriverManager.getConnection("...", "...", "...");
con.setAutoCommit(false);PreparedStatement pstmt = con.prepareStatement("insert into x (id)
values (?)");pstmt.setInt(1, 1);
pstmt.executeUpdate();try
{
pstmt.setInt(1, 1);
pstmt.executeUpdate();
}
catch (Exception ex)
{
System.out.println("***** Error: " + ex.getMessage());
}System.out.println("trying second row");
pstmt.setInt(1, 2);
pstmt.executeUpdate();con.commit();
ResultSet rs = con.createStatement().executeQuery("select count(*)
from x");
if (rs.next())
{
int rows = rs.getInt(1);
System.out.println("rows: " + rows);
}
con.close();
}
}---- code end ----
With Oracle DB2, SQL Server, MySQL and Firebird you'll get something like
this
(this is from Oracle, the error message will of course differ for the
others)***** Error: ORA-00001: Unique Constraint (THOMAS.SYS_C0021623) violated
trying second row
rows: 2With Postgres you get:
***** Error: ERROR: duplicate key value violates unique constraint
"x_pkey"
Detail: Key (id)=(1) already exists.
trying second row
Exception in thread "main" org.postgresql.util.PSQLException: ERROR:
current transaction is aborted, commands ignored until end of transaction
blockBut again: in contrast to the OP I do not consider this a bug, it's just
that Postgres behaves differently.But I understand that it can be a problem when migrating applications that
have a sloppy way of dealing with transactions.I was part in the migration of several projects from Oracle and MySQL to
Postgres in the last two years, none of them had problems regarding that.Thomas
--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
On Saturday, February 20, 2016, Craig Ringer <craig@2ndquadrant.com> wrote:
On 20 February 2016 at 19:05, John R Pierce <pierce@hogranch.com
<javascript:_e(%7B%7D,'cvml','pierce@hogranch.com');>> wrote:to me this seems to break the rules of transaction semantics, but it
matches the behavior with autocommit=on ...... which doesn't matter, because if you wanted that you'd just set
autocommit=on.I think all this discussion of autocommit is a pointless side-track.
Reading between the lines, the real complaint appears to be that we abort
the xact if a statement ERRORs, and by default other DBMS vendors don't. OP
wants something like psqlODBC's "statement" error-rollback mode where
savepoints are done automatically before each statement.
So basically it's a means to obtain some I/O savings by not forcing a WAL
flush on every statement while still retaining the semantics of autocommit.
If the final commit fails (or never happens) none of the successful rows
appear but otherwise you get some improvement in I/O which is mitigated but
the cost of savepoint handling.
David J.
Well, it OT here and belongs to -hackers, but as for me main use case here
is ETL or ELT process getting a lot of unvalidated external data.
And a good option to solve this problem is not to change transaction
semantics or slow down processing by adding tons of savepoints, but add "on
error" clause to insert/copy statement.
This clause should allow to save records that can't fit into destination
table because of type, check of referential constaints into error table.
Oracle has similar functionality and we are using it in our project. No
error is generated - no transaction rollback, batch abort or similar.
As for me it would cover 90% of use cases and would be really usefull. The
one problem I can see is with inserting into partition parent.
Best regards, Vitalii Tymchyshyn
On Sat, 20 Feb 2016 16:01:04 +0000
Vitalii Tymchyshyn <vit@tym.im> wrote:
Well, it OT here and belongs to -hackers, but as for me main use case here
is ETL or ELT process getting a lot of unvalidated external data.
And a good option to solve this problem is not to change transaction
semantics or slow down processing by adding tons of savepoints, but add "on
error" clause to insert/copy statement.This clause should allow to save records that can't fit into destination
table because of type, check of referential constaints into error table.
Oracle has similar functionality and we are using it in our project. No
error is generated - no transaction rollback, batch abort or similar.As for me it would cover 90% of use cases and would be really usefull. The
one problem I can see is with inserting into partition parent.
PL/PGSQL provides this functionality. It requires the creation of a server
side function and using that function to insert data, but it can do exactly
what you're describing.
While adding other mechanisms to make it "easier" or "more like some other
software" might be valuable; the simple fact is that Postgres _does_ support
what you want. The fact that you're not aware of it doesn't change that.
--
Bill Moran
--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
Well, I suppose replacing simple copy with procedural per-row function
would give huge performance hit. Also what method do you propose to use in
the code? Savepoints? I'd say this would also add a more slowdown.
Also quite a bit of boilerplate code would be needed. It's similar to merge
statement. Yes, it can be achieved in pure SQL, but having clean merge
statement saves you a lot of headache and is usually much more efficient.
Basically, it's not that what OP needs is not doable at all, it's that
other RDBMs often has this functionality in much more convenient and
performance optimized way.
Best regards, Vitalii Tymchyshyn
Сб, 20 лют. 2016 11:16 Bill Moran <wmoran@potentialtech.com> пише:
Show quoted text
On Sat, 20 Feb 2016 16:01:04 +0000
Vitalii Tymchyshyn <vit@tym.im> wrote:Well, it OT here and belongs to -hackers, but as for me main use case
here
is ETL or ELT process getting a lot of unvalidated external data.
And a good option to solve this problem is not to change transaction
semantics or slow down processing by adding tons of savepoints, but add"on
error" clause to insert/copy statement.
This clause should allow to save records that can't fit into destination
table because of type, check of referential constaints into error table.
Oracle has similar functionality and we are using it in our project. No
error is generated - no transaction rollback, batch abort or similar.As for me it would cover 90% of use cases and would be really usefull.
The
one problem I can see is with inserting into partition parent.
PL/PGSQL provides this functionality. It requires the creation of a server
side function and using that function to insert data, but it can do exactly
what you're describing.While adding other mechanisms to make it "easier" or "more like some other
software" might be valuable; the simple fact is that Postgres _does_
support
what you want. The fact that you're not aware of it doesn't change that.--
Bill Moran
On Sat, 20 Feb 2016 16:29:09 +0000
Vitalii Tymchyshyn <vit@tym.im> wrote:
Well, I suppose replacing simple copy with procedural per-row function
would give huge performance hit. Also what method do you propose to use in
the code? Savepoints?
Not at all. PL/PGSQL's ON ERROR handling can manage this without needing
savepoints.
I'd say this would also add a more slowdown.
What? The savepoints? Well, you don't need them. The stored
procedure is going to incur a bit of a hit, though.
Also quite a bit of boilerplate code would be needed. It's similar to merge
statement. Yes, it can be achieved in pure SQL, but having clean merge
statement saves you a lot of headache and is usually much more efficient.
Basically, it's not that what OP needs is not doable at all, it's that
other RDBMs often has this functionality in much more convenient and
performance optimized way.
True. I don't think "clean up my crappy data" has ever been a
priority for PostgreSQL. Although, "allow the user to build whatever
is needed" has been.
I find it curious that those of us who become stewards of other people's
data find ourselves bending over backwards to try to clean up their
garbage data. It's an interesting social commentary on how software
design has changed since the term GIGO was in common use.
Best regards, Vitalii Tymchyshyn
??, 20 ???. 2016 11:16 Bill Moran <wmoran@potentialtech.com> ????:
On Sat, 20 Feb 2016 16:01:04 +0000
Vitalii Tymchyshyn <vit@tym.im> wrote:Well, it OT here and belongs to -hackers, but as for me main use case
here
is ETL or ELT process getting a lot of unvalidated external data.
And a good option to solve this problem is not to change transaction
semantics or slow down processing by adding tons of savepoints, but add"on
error" clause to insert/copy statement.
This clause should allow to save records that can't fit into destination
table because of type, check of referential constaints into error table.
Oracle has similar functionality and we are using it in our project. No
error is generated - no transaction rollback, batch abort or similar.As for me it would cover 90% of use cases and would be really usefull.
The
one problem I can see is with inserting into partition parent.
PL/PGSQL provides this functionality. It requires the creation of a server
side function and using that function to insert data, but it can do exactly
what you're describing.While adding other mechanisms to make it "easier" or "more like some other
software" might be valuable; the simple fact is that Postgres _does_
support
what you want. The fact that you're not aware of it doesn't change that.--
Bill Moran
--
Bill Moran
--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
Bill Moran <wmoran@potentialtech.com> writes:
On Sat, 20 Feb 2016 16:29:09 +0000
Vitalii Tymchyshyn <vit@tym.im> wrote:Well, I suppose replacing simple copy with procedural per-row function
would give huge performance hit. Also what method do you propose to use in
the code? Savepoints?
Not at all. PL/PGSQL's ON ERROR handling can manage this without needing
savepoints.
Actually, plpgsql's exception blocks *are* savepoints under the hood.
The backend engine does not have any way of recovering from errors other
than a (sub)transaction abort, which means you can't do this without a
savepoint or equivalent.
regards, tom lane
--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
Well, often in ETL it's not MY crappy data. It's something received from
upstream system and my task is to perform a load, not stop on the first
error and produce at least meaninful report on data crappyness statistics.
Extended cases may involve something like manual data fixing for error rows.
Just a real world example: in financial transactions sometimes you can
receive a transaction on a financial instrument that is brand new and is
not in your db yet. You don't want to fail the whole batch.
And yes, globally it's a move from "getting data" to "filtering this
[crappy] data ocean" going on.
Best regards, Vitalii Tymchyshyn
Сб, 20 лют. 2016 12:09 Bill Moran <wmoran@potentialtech.com> пише:
Show quoted text
On Sat, 20 Feb 2016 16:29:09 +0000
Vitalii Tymchyshyn <vit@tym.im> wrote:Well, I suppose replacing simple copy with procedural per-row function
would give huge performance hit. Also what method do you propose to usein
the code? Savepoints?
Not at all. PL/PGSQL's ON ERROR handling can manage this without needing
savepoints.I'd say this would also add a more slowdown.
What? The savepoints? Well, you don't need them. The stored
procedure is going to incur a bit of a hit, though.Also quite a bit of boilerplate code would be needed. It's similar to
merge
statement. Yes, it can be achieved in pure SQL, but having clean merge
statement saves you a lot of headache and is usually much more efficient.
Basically, it's not that what OP needs is not doable at all, it's that
other RDBMs often has this functionality in much more convenient and
performance optimized way.True. I don't think "clean up my crappy data" has ever been a
priority for PostgreSQL. Although, "allow the user to build whatever
is needed" has been.I find it curious that those of us who become stewards of other people's
data find ourselves bending over backwards to try to clean up their
garbage data. It's an interesting social commentary on how software
design has changed since the term GIGO was in common use.Best regards, Vitalii Tymchyshyn
??, 20 ???. 2016 11:16 Bill Moran <wmoran@potentialtech.com> ????:
On Sat, 20 Feb 2016 16:01:04 +0000
Vitalii Tymchyshyn <vit@tym.im> wrote:Well, it OT here and belongs to -hackers, but as for me main use case
here
is ETL or ELT process getting a lot of unvalidated external data.
And a good option to solve this problem is not to change transaction
semantics or slow down processing by adding tons of savepoints, butadd
"on
error" clause to insert/copy statement.
This clause should allow to save records that can't fit into
destination
table because of type, check of referential constaints into error
table.
Oracle has similar functionality and we are using it in our project.
No
error is generated - no transaction rollback, batch abort or similar.
As for me it would cover 90% of use cases and would be really
usefull.
The
one problem I can see is with inserting into partition parent.
PL/PGSQL provides this functionality. It requires the creation of a
server
side function and using that function to insert data, but it can do
exactly
what you're describing.
While adding other mechanisms to make it "easier" or "more like some
other
software" might be valuable; the simple fact is that Postgres _does_
support
what you want. The fact that you're not aware of it doesn't changethat.
--
Bill Moran--
Bill Moran
Vitalii Tymchyshyn schrieb am 20.02.2016 um 17:01:
Well, it OT here and belongs to -hackers, but as for me main use case
here is ETL or ELT process getting a lot of unvalidated external
data. And a good option to solve this problem is not to change
transaction semantics or slow down processing by adding tons of
savepoints, but add "on error" clause to insert/copy statement.
You can use INSERT .. ON CONFLICT ... to ignore the erroneous rows
for unique constraint violations.
It's not the same as Oracle's ON ERROR clause, but you don't need savepoints
and you can use JDBC batching with that as well.
For all other constraint violations I usually put the import data into
a staging table anyway - even with Oracle.
--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
We do have a patch https://github.com/pgjdbc/pgjdbc/pull/477 that will
address this but not in batch update.
In my mind batches are used primarily for performance. Adding the overhead
of savepoints makes them much slower, which is something we are unlikely to
accommodate
Dave
Dave Cramer
davec@postgresintl.com
www.postgresintl.com
On 20 February 2016 at 12:34, Vitalii Tymchyshyn <vit@tym.im> wrote:
Show quoted text
Well, often in ETL it's not MY crappy data. It's something received from
upstream system and my task is to perform a load, not stop on the first
error and produce at least meaninful report on data crappyness statistics.
Extended cases may involve something like manual data fixing for error rows.Just a real world example: in financial transactions sometimes you can
receive a transaction on a financial instrument that is brand new and is
not in your db yet. You don't want to fail the whole batch.And yes, globally it's a move from "getting data" to "filtering this
[crappy] data ocean" going on.Best regards, Vitalii Tymchyshyn
Сб, 20 лют. 2016 12:09 Bill Moran <wmoran@potentialtech.com> пише:
On Sat, 20 Feb 2016 16:29:09 +0000
Vitalii Tymchyshyn <vit@tym.im> wrote:Well, I suppose replacing simple copy with procedural per-row function
would give huge performance hit. Also what method do you propose to usein
the code? Savepoints?
Not at all. PL/PGSQL's ON ERROR handling can manage this without needing
savepoints.I'd say this would also add a more slowdown.
What? The savepoints? Well, you don't need them. The stored
procedure is going to incur a bit of a hit, though.Also quite a bit of boilerplate code would be needed. It's similar to
merge
statement. Yes, it can be achieved in pure SQL, but having clean merge
statement saves you a lot of headache and is usually much moreefficient.
Basically, it's not that what OP needs is not doable at all, it's that
other RDBMs often has this functionality in much more convenient and
performance optimized way.True. I don't think "clean up my crappy data" has ever been a
priority for PostgreSQL. Although, "allow the user to build whatever
is needed" has been.I find it curious that those of us who become stewards of other people's
data find ourselves bending over backwards to try to clean up their
garbage data. It's an interesting social commentary on how software
design has changed since the term GIGO was in common use.Best regards, Vitalii Tymchyshyn
??, 20 ???. 2016 11:16 Bill Moran <wmoran@potentialtech.com> ????:
On Sat, 20 Feb 2016 16:01:04 +0000
Vitalii Tymchyshyn <vit@tym.im> wrote:Well, it OT here and belongs to -hackers, but as for me main use
case
here
is ETL or ELT process getting a lot of unvalidated external data.
And a good option to solve this problem is not to change transaction
semantics or slow down processing by adding tons of savepoints, butadd
"on
error" clause to insert/copy statement.
This clause should allow to save records that can't fit into
destination
table because of type, check of referential constaints into error
table.
Oracle has similar functionality and we are using it in our
project. No
error is generated - no transaction rollback, batch abort or
similar.
As for me it would cover 90% of use cases and would be really
usefull.
The
one problem I can see is with inserting into partition parent.
PL/PGSQL provides this functionality. It requires the creation of a
server
side function and using that function to insert data, but it can do
exactly
what you're describing.
While adding other mechanisms to make it "easier" or "more like some
other
software" might be valuable; the simple fact is that Postgres _does_
support
what you want. The fact that you're not aware of it doesn't changethat.
--
Bill Moran--
Bill Moran
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 ?
Thanks
Sridhar
On Sat, Feb 20, 2016 at 11:12 PM, Dave Cramer <pg@fastcrypt.com> wrote:
Show quoted text
We do have a patch https://github.com/pgjdbc/pgjdbc/pull/477 that will
address this but not in batch update.In my mind batches are used primarily for performance. Adding the overhead
of savepoints makes them much slower, which is something we are unlikely to
accommodateDave
Dave Cramer
davec@postgresintl.com
www.postgresintl.comOn 20 February 2016 at 12:34, Vitalii Tymchyshyn <vit@tym.im> wrote:
Well, often in ETL it's not MY crappy data. It's something received from
upstream system and my task is to perform a load, not stop on the first
error and produce at least meaninful report on data crappyness statistics.
Extended cases may involve something like manual data fixing for error rows.Just a real world example: in financial transactions sometimes you can
receive a transaction on a financial instrument that is brand new and is
not in your db yet. You don't want to fail the whole batch.And yes, globally it's a move from "getting data" to "filtering this
[crappy] data ocean" going on.Best regards, Vitalii Tymchyshyn
Сб, 20 лют. 2016 12:09 Bill Moran <wmoran@potentialtech.com> пише:
On Sat, 20 Feb 2016 16:29:09 +0000
Vitalii Tymchyshyn <vit@tym.im> wrote:Well, I suppose replacing simple copy with procedural per-row function
would give huge performance hit. Also what method do you propose touse in
the code? Savepoints?
Not at all. PL/PGSQL's ON ERROR handling can manage this without needing
savepoints.I'd say this would also add a more slowdown.
What? The savepoints? Well, you don't need them. The stored
procedure is going to incur a bit of a hit, though.Also quite a bit of boilerplate code would be needed. It's similar to
merge
statement. Yes, it can be achieved in pure SQL, but having clean merge
statement saves you a lot of headache and is usually much moreefficient.
Basically, it's not that what OP needs is not doable at all, it's that
other RDBMs often has this functionality in much more convenient and
performance optimized way.True. I don't think "clean up my crappy data" has ever been a
priority for PostgreSQL. Although, "allow the user to build whatever
is needed" has been.I find it curious that those of us who become stewards of other people's
data find ourselves bending over backwards to try to clean up their
garbage data. It's an interesting social commentary on how software
design has changed since the term GIGO was in common use.Best regards, Vitalii Tymchyshyn
??, 20 ???. 2016 11:16 Bill Moran <wmoran@potentialtech.com> ????:
On Sat, 20 Feb 2016 16:01:04 +0000
Vitalii Tymchyshyn <vit@tym.im> wrote:Well, it OT here and belongs to -hackers, but as for me main use
case
here
is ETL or ELT process getting a lot of unvalidated external data.
And a good option to solve this problem is not to changetransaction
semantics or slow down processing by adding tons of savepoints,
but add
"on
error" clause to insert/copy statement.
This clause should allow to save records that can't fit into
destination
table because of type, check of referential constaints into error
table.
Oracle has similar functionality and we are using it in our
project. No
error is generated - no transaction rollback, batch abort or
similar.
As for me it would cover 90% of use cases and would be really
usefull.
The
one problem I can see is with inserting into partition parent.
PL/PGSQL provides this functionality. It requires the creation of a
server
side function and using that function to insert data, but it can do
exactly
what you're describing.
While adding other mechanisms to make it "easier" or "more like some
other
software" might be valuable; the simple fact is that Postgres _does_
support
what you want. The fact that you're not aware of it doesn't changethat.
--
Bill Moran--
Bill Moran
On 2/20/2016 6:20 PM, Sridhar N Bamandlapally wrote:
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 rowsis there any way is possible ?
turn on autocommit if you want each INSERT to be run atomically
independent of the other inserts.
this is going in circles, we've explained a dozens how postgres and jdbc
works.
--
john r pierce, recycling bits in santa cruz
--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
Please see http://www.postgresql.org/docs/9.1/static/sql-createtrigger.html
You would need something like an "instead of trigger" for insert doing what
you want. If you are not forced to use "insert", you can directly call the
procedure that handles errors.
It has nothing to do with JDBC, it's how PostgreSQL transactions works.
Best regards, Vitalii Tymchyshyn
Сб, 20 лют. 2016 о 21:20 Sridhar N Bamandlapally <sridhar.bn1@gmail.com>
пише:
Show quoted text
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 rowsis there any way is possible ?
Thanks
SridharOn Sat, Feb 20, 2016 at 11:12 PM, Dave Cramer <pg@fastcrypt.com> wrote:
We do have a patch https://github.com/pgjdbc/pgjdbc/pull/477 that will
address this but not in batch update.In my mind batches are used primarily for performance. Adding the
overhead of savepoints makes them much slower, which is something we are
unlikely to accommodateDave
Dave Cramer
davec@postgresintl.com
www.postgresintl.comOn 20 February 2016 at 12:34, Vitalii Tymchyshyn <vit@tym.im> wrote:
Well, often in ETL it's not MY crappy data. It's something received from
upstream system and my task is to perform a load, not stop on the first
error and produce at least meaninful report on data crappyness statistics.
Extended cases may involve something like manual data fixing for error rows.Just a real world example: in financial transactions sometimes you can
receive a transaction on a financial instrument that is brand new and is
not in your db yet. You don't want to fail the whole batch.And yes, globally it's a move from "getting data" to "filtering this
[crappy] data ocean" going on.Best regards, Vitalii Tymchyshyn
Сб, 20 лют. 2016 12:09 Bill Moran <wmoran@potentialtech.com> пише:
On Sat, 20 Feb 2016 16:29:09 +0000
Vitalii Tymchyshyn <vit@tym.im> wrote:Well, I suppose replacing simple copy with procedural per-row function
would give huge performance hit. Also what method do you propose touse in
the code? Savepoints?
Not at all. PL/PGSQL's ON ERROR handling can manage this without needing
savepoints.I'd say this would also add a more slowdown.
What? The savepoints? Well, you don't need them. The stored
procedure is going to incur a bit of a hit, though.Also quite a bit of boilerplate code would be needed. It's similar to
merge
statement. Yes, it can be achieved in pure SQL, but having clean merge
statement saves you a lot of headache and is usually much moreefficient.
Basically, it's not that what OP needs is not doable at all, it's that
other RDBMs often has this functionality in much more convenient and
performance optimized way.True. I don't think "clean up my crappy data" has ever been a
priority for PostgreSQL. Although, "allow the user to build whatever
is needed" has been.I find it curious that those of us who become stewards of other people's
data find ourselves bending over backwards to try to clean up their
garbage data. It's an interesting social commentary on how software
design has changed since the term GIGO was in common use.Best regards, Vitalii Tymchyshyn
??, 20 ???. 2016 11:16 Bill Moran <wmoran@potentialtech.com> ????:
On Sat, 20 Feb 2016 16:01:04 +0000
Vitalii Tymchyshyn <vit@tym.im> wrote:Well, it OT here and belongs to -hackers, but as for me main use
case
here
is ETL or ELT process getting a lot of unvalidated external data.
And a good option to solve this problem is not to changetransaction
semantics or slow down processing by adding tons of savepoints,
but add
"on
error" clause to insert/copy statement.
This clause should allow to save records that can't fit into
destination
table because of type, check of referential constaints into error
table.
Oracle has similar functionality and we are using it in our
project. No
error is generated - no transaction rollback, batch abort or
similar.
As for me it would cover 90% of use cases and would be really
usefull.
The
one problem I can see is with inserting into partition parent.
PL/PGSQL provides this functionality. It requires the creation of a
server
side function and using that function to insert data, but it can do
exactly
what you're describing.
While adding other mechanisms to make it "easier" or "more like
some other
software" might be valuable; the simple fact is that Postgres _does_
support
what you want. The fact that you're not aware of it doesn't changethat.
--
Bill Moran--
Bill Moran
Sridhar N Bamandlapally schrieb am 21.02.2016 um 03:20:
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 rowsis there any way is possible ?
Use on conflict do nothing:
insert into employee values(1, 'K1') on conflict (empid) do nothing;
insert into employee values(1, 'K1') on conflict (empid) do nothing;
insert into employee values(2, 'K2') on conflict (empid) do nothing;
This can be combined with a PreparedStatement and batching if needed.
--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
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 rowsis 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
--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
On 21 February 2016 at 10:20, Sridhar N Bamandlapally <sridhar.bn1@gmail.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
I may be wrong, please correct if,
can we do function overloading to add functionality with savepoint option,
i.e. with this both will be available and its app developers to chose
On Sat, Feb 20, 2016 at 11:01 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Show quoted text
Bill Moran <wmoran@potentialtech.com> writes:
On Sat, 20 Feb 2016 16:29:09 +0000
Vitalii Tymchyshyn <vit@tym.im> wrote:Well, I suppose replacing simple copy with procedural per-row function
would give huge performance hit. Also what method do you propose to usein
the code? Savepoints?
Not at all. PL/PGSQL's ON ERROR handling can manage this without needing
savepoints.Actually, plpgsql's exception blocks *are* savepoints under the hood.
The backend engine does not have any way of recovering from errors other
than a (sub)transaction abort, which means you can't do this without a
savepoint or equivalent.regards, tom lane
--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
On 2/21/2016 9:35 PM, Sridhar N Bamandlapally wrote:
I may be wrong, please correct if,
can we do function overloading to add functionality with savepoint
option, i.e. with this both will be available and its app developers
to chose
if you want every operation saved, then use autocommit, and let it
execute each insert atomically.
--
john r pierce, recycling bits in santa cruz
--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
On 22 February 2016 at 00:35, Sridhar N Bamandlapally <sridhar.bn1@gmail.com
wrote:
I may be wrong, please correct if,
can we do function overloading to add functionality with savepoint option,
i.e. with this both will be available and its app developers to chose
Can you be explicit in what you are asking for please ?
As John points out you can do this now by checking every commit.
Dave Cramer
davec@postgresintl.com
www.postgresintl.com
Show quoted text
On Sat, Feb 20, 2016 at 11:01 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Bill Moran <wmoran@potentialtech.com> writes:
On Sat, 20 Feb 2016 16:29:09 +0000
Vitalii Tymchyshyn <vit@tym.im> wrote:Well, I suppose replacing simple copy with procedural per-row function
would give huge performance hit. Also what method do you propose touse in
the code? Savepoints?
Not at all. PL/PGSQL's ON ERROR handling can manage this without needing
savepoints.Actually, plpgsql's exception blocks *are* savepoints under the hood.
The backend engine does not have any way of recovering from errors other
than a (sub)transaction abort, which means you can't do this without a
savepoint or equivalent.regards, tom lane
--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
I mean, we will not change existing functionality/behavior/code as there
may be dependency applications with same behavior
i.e. currently conn.setAutoCommit (false) is using "BEGIN;"
and the new functionality can be like conn.setAutoCommit(false,
<new-parameter> ), where new-parameter can be Boolean or flag which does
following way for statements
try
{
conn.savepoint(SP);
SQL-statement;
}
catch(Exception exp)
{
conn.rollback(SP);
throw exp;
}
autocommit is not option as end-user need control/decision to commit or
rollback on successful transactions
our applications build with Oracle, SQL-Sever compatible ( i.e. using
setAutCommit FALSE at every transaction ),
now are migrating applications compatible with PostgreSQL on cloud,
Thanks
Sridhar
On Mon, Feb 22, 2016 at 3:56 PM, Dave Cramer <pg@fastcrypt.com> wrote:
Show quoted text
On 22 February 2016 at 00:35, Sridhar N Bamandlapally <
sridhar.bn1@gmail.com> wrote:I may be wrong, please correct if,
can we do function overloading to add functionality with savepoint
option, i.e. with this both will be available and its app developers to
choseCan you be explicit in what you are asking for please ?
As John points out you can do this now by checking every commit.
Dave Cramer
davec@postgresintl.com
www.postgresintl.comOn Sat, Feb 20, 2016 at 11:01 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Bill Moran <wmoran@potentialtech.com> writes:
On Sat, 20 Feb 2016 16:29:09 +0000
Vitalii Tymchyshyn <vit@tym.im> wrote:Well, I suppose replacing simple copy with procedural per-row function
would give huge performance hit. Also what method do you propose touse in
the code? Savepoints?
Not at all. PL/PGSQL's ON ERROR handling can manage this without
needing
savepoints.
Actually, plpgsql's exception blocks *are* savepoints under the hood.
The backend engine does not have any way of recovering from errors other
than a (sub)transaction abort, which means you can't do this without a
savepoint or equivalent.regards, tom lane
--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
On 22 February 2016 at 23:06, Sridhar N Bamandlapally <sridhar.bn1@gmail.com
wrote:
I mean, we will not change existing functionality/behavior/code as there
may be dependency applications with same behaviori.e. currently conn.setAutoCommit (false) is using "BEGIN;"
Yes, this is the exact definition of what setAutoCommit(false) is.
and the new functionality can be like conn.setAutoCommit(false,
<new-parameter> ), where new-parameter can be Boolean or flag which does
following way for statements
This is completely incompatible with the spec. You can't just add
parameters to methods, and expect it to be compatible.
This below is exactly what PR477 is meant to do. If you want to be
constructive test this https://github.com/pgjdbc/pgjdbc/pull/477 and
provide feed back
try
{
conn.savepoint(SP);
SQL-statement;
}
catch(Exception exp)
{
conn.rollback(SP);
throw exp;
}
Dave
Show quoted text
On 22 February 2016 at 00:35, Sridhar N Bamandlapally <
sridhar.bn1@gmail.com> wrote:I may be wrong, please correct if,
can we do function overloading to add functionality with savepoint
option, i.e. with this both will be available and its app developers to
choseCan you be explicit in what you are asking for please ?
As John points out you can do this now by checking every commit.
Dave Cramer
davec@postgresintl.com
www.postgresintl.comOn Sat, Feb 20, 2016 at 11:01 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Bill Moran <wmoran@potentialtech.com> writes:
On Sat, 20 Feb 2016 16:29:09 +0000
Vitalii Tymchyshyn <vit@tym.im> wrote:Well, I suppose replacing simple copy with procedural per-row
function
would give huge performance hit. Also what method do you propose to
use in
the code? Savepoints?
Not at all. PL/PGSQL's ON ERROR handling can manage this without
needing
savepoints.
Actually, plpgsql's exception blocks *are* savepoints under the hood.
The backend engine does not have any way of recovering from errors other
than a (sub)transaction abort, which means you can't do this without a
savepoint or equivalent.regards, tom lane
--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
On Sat, Feb 20, 2016 at 4:14 PM, Craig Ringer <craig@2ndquadrant.com> wrote:
currently PostgreSQL::"set autocommit to FALSE ( not supported )
This also does not make any sense.
PgJDBC does support turning autocommit off. So I don't know in what way it's
"not supported".
I believe Sridhar is imagining that someday "set autocommit to false"
might be a command that the server would understand.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
On 23 February 2016 at 12:06, Sridhar N Bamandlapally <sridhar.bn1@gmail.com
wrote:
I mean, we will not change existing functionality/behavior/code as there
may be dependency applications with same behavior
What you need to do is help test and review the patch that you've already
been sent links to. It will add a connection option that you can use to
control whether or not the driver does automatic savepoints. So you won't
have to change application code so long as the application gives you a way
to specify options in the JDBC URL or Properties dictionary. At least, not
because of that.
Go look at https://github.com/pgjdbc/pgjdbc/pull/477 . If you really want
to make progress, take that patch and add support for a JDBC URL connection
option then send a new pull request. With unit tests.
I am confident that automatic SAVEPOINTs are the *only* way you will get
the behaviour you want with PostgreSQL and PgJDBC.
--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
On 23 February 2016 at 21:34, Robert Haas <robertmhaas@gmail.com> wrote:
On Sat, Feb 20, 2016 at 4:14 PM, Craig Ringer <craig@2ndquadrant.com>
wrote:currently PostgreSQL::"set autocommit to FALSE ( not supported )
This also does not make any sense.
PgJDBC does support turning autocommit off. So I don't know in what way
it's
"not supported".
I believe Sridhar is imagining that someday "set autocommit to false"
might be a command that the server would understand.
... I guess. Yeah.
Oracle's SQL*Plus has the concept of turning autocommit off, but I suspect
that's client-side behaviour.
http://docs.oracle.com/cd/B19306_01/server.102/b14357/ch12040.htm
I can't really imagine how it'd make sense on the server side, given how
the protocol works etc. Nor is it necessary since the desired behaviour is
entirely controlled on the client side.
We could have a server mode that did silent, automatic savepoints and
rolled back to a savepoint automatically on ERROR. That wouldn't be the
same as autocommit, but appears to be what Sridhar actually needs. There's
even the remotest chance someone could come up with a patch that might be
acceptable, but I don't know of anyone who'd want to do it when it can be
done well enough client side.
I think Sridhar is confusing autocommit with other DBMSes behaviour of
automatically rolling back failed statements without affecting the rest of
the transaction. These are not the same thing.
--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
Craig Ringer <craig@2ndquadrant.com> writes:
On 23 February 2016 at 21:34, Robert Haas <robertmhaas@gmail.com> wrote:
I believe Sridhar is imagining that someday "set autocommit to false"
might be a command that the server would understand.
... I guess. Yeah.
We've been there, we've done that. We're not doing it again.
Cf commits 26993b291, f85f43dfb, 525a48991, as well as a whole
bunch of thrashing in between the first two (grep the git logs
for "autocommit" to find most of it). It's a bit harder to locate
relevant email threads, because searching for just "autocommit"
yields too many hits; but here's one long thread from when we were
starting to realize that it wasn't working very well:
/messages/by-id/3E54526A.121EBEE5@tpf.co.jp
In all, this was one of the more searing experiences contributing
to what's now received project wisdom that GUCs that change
fundamental semantics are a bad idea.
Oracle's SQL*Plus has the concept of turning autocommit off, but I suspect
that's client-side behaviour.
The conclusion we came to back in 2002-2003 was that client-side
autocommit was the only behavior we could sanely support. I see
no reason to think that a fresh experiment in the same direction
would produce a different result.
regards, tom lane
--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
On 23 February 2016 at 22:46, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Craig Ringer <craig@2ndquadrant.com> writes:
On 23 February 2016 at 21:34, Robert Haas <robertmhaas@gmail.com> wrote:
I believe Sridhar is imagining that someday "set autocommit to false"
might be a command that the server would understand.... I guess. Yeah.
We've been there, we've done that. We're not doing it again.
Thanks for the pointer to the history.
I had zero enthusiasm for going that way anyway and was mostly trying to
figure out what Sridhar was talking about. It's useful to know it's already
been explored though.
I think we know where we need to go from here - updating that PgJDBC patch
to add a connection option, making sure it doesn't add round-trips, adding
tests and merging it. At this point it's up to Sridhar to start putting
time and development effort into it to push it forward if desired.
--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services