Thread: Using ON_ERROR_ROLLBACK functionality in JDBC
Hi all, I have seen psql has implemented a variable called ON_ERROR_ROLLBACK. "When on, if a statement in a transaction block generates an error, the error is ignored and the transaction continues." (from the documentation). I would need the same functionality using JDBC. So I could continue with my batch insert even if one or more statements fail. Right now I'm committing the transaction after each insert. I have thought of the following: 1. Checking constraints before calling insert. (to slow) 2. Creating a user defined savepoint before each insert, and rolling back to this savepoint if the insert fails. (not tried yet) What would you recommend? Is it possible to set ON_ERROR_ROLLBACK for JDBC transactions? AFAIK other DBMS do let you continue with you transaction if there was in error. Thanks, Chris ****************************************** The information contained in, or attached to, this e-mail, may contain confidential information and is intended solely forthe use of the individual or entity to whom they are addressed and may be subject to legal privilege. If you have receivedthis e-mail in error you should notify the sender immediately by reply e-mail, delete the message from your systemand notify your system manager. Please do not copy it for any purpose, or disclose its contents to any other person. The views or opinions presented in this e-mail are solely those of the author and do not necessarily represent thoseof the company. The recipient should check this e-mail and any attachments for the presence of viruses. The companyaccepts no liability for any damage caused, directly or indirectly, by any virus transmitted in this email. ******************************************
On Fri, 28 Jul 2006, Koth, Christian (DWBI) wrote: > I would need the same functionality using JDBC. So I could continue with > my batch insert even if one or more statements fail. Right now I'm > committing the transaction after each insert. > > I have thought of the following: > > 1. Checking constraints before calling insert. (to slow) > 2. Creating a user defined savepoint before each insert, > and rolling back to this savepoint if the insert fails. (not tried yet) > > What would you recommend? Is it possible to set ON_ERROR_ROLLBACK for > JDBC transactions? The JDBC driver does not currently support this behavior and if it did it would implement it using 2) behind the scenes, so that's a good approach. Also it's not clear what you mean by a batch, but the use of savepoints will not allow things like Statement.executeBatch() to commit parts of a batch. It's all or nothing for these. Kris Jurka
Kris, thanks for your answer. Find my comments below. > > I would need the same functionality using JDBC. So I could > continue with > > my batch insert even if one or more statements fail. Right now I'm > > committing the transaction after each insert. > > > > I have thought of the following: > > > > 1. Checking constraints before calling insert. (to slow) > > 2. Creating a user defined savepoint before each insert, > > and rolling back to this savepoint if the insert fails. > (not tried yet) > > The JDBC driver does not currently support this behaviour and > if it did it would implement it using 2) behind the scenes, so that's a > good approach. Do you think its planned to do a thing like this? Also I am wondering if this would really speed up applications. Is creating savepoints expensive? Will have to do some tests. I think of doing something like: SAVEPOINT insert_savepoint; INSERT (... without error): SAVEPOINT insert_savepoint; (previous sp will be replaced?) (... with error): ROLLBACK TO SAVEPOINT insert_savepoint; INSERT SAVEPOINT insert_savepoint; ... > Also it's not clear what you mean by a batch, but the use of > savepoints will not allow things like Statement.executeBatch() to commit > parts of a batch. It's all or nothing for these. By saying "my batch insert" I meant doing inserts without committing them each time. You are right that with using Statement.executeBatch() all statement within this batch will fail. regards, Chris ****************************************** The information contained in, or attached to, this e-mail, may contain confidential information and is intended solely forthe use of the individual or entity to whom they are addressed and may be subject to legal privilege. If you have receivedthis e-mail in error you should notify the sender immediately by reply e-mail, delete the message from your systemand notify your system manager. Please do not copy it for any purpose, or disclose its contents to any other person. The views or opinions presented in this e-mail are solely those of the author and do not necessarily represent thoseof the company. The recipient should check this e-mail and any attachments for the presence of viruses. The companyaccepts no liability for any damage caused, directly or indirectly, by any virus transmitted in this email. ******************************************
Does the current savepoint implementation in the driver require a separate round-trip? -- Mark Lewis On Fri, 2006-07-28 at 03:57 -0500, Kris Jurka wrote: > > On Fri, 28 Jul 2006, Koth, Christian (DWBI) wrote: > > > I would need the same functionality using JDBC. So I could continue with > > my batch insert even if one or more statements fail. Right now I'm > > committing the transaction after each insert. > > > > I have thought of the following: > > > > 1. Checking constraints before calling insert. (to slow) > > 2. Creating a user defined savepoint before each insert, > > and rolling back to this savepoint if the insert fails. (not tried yet) > > > > What would you recommend? Is it possible to set ON_ERROR_ROLLBACK for > > JDBC transactions? > > The JDBC driver does not currently support this behavior and if it did it > would implement it using 2) behind the scenes, so that's a good approach. > > Also it's not clear what you mean by a batch, but the use of savepoints > will not allow things like Statement.executeBatch() to commit parts of a > batch. It's all or nothing for these. > > Kris Jurka > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly
On Fri, 28 Jul 2006, Koth, Christian (DWBI) wrote: > Do you think its planned to do a thing like this? It is on the TODO list, but driver development has currently stalled. > Also I am wondering if this would really speed up applications. Is > creating savepoints expensive? Will have to do some tests. I think of > doing something like: > Savepoints aren't cheap, but they are cheaper than committing a whole transaction. There was a thread on this on -performance just yesterday. http://archives.postgresql.org/pgsql-performance/2006-07/threads.php#00245 Kris Jurka
On Fri, 28 Jul 2006, Mark Lewis wrote: > Does the current savepoint implementation in the driver require a > separate round-trip? Yes. Creating, rolling back to, or releasing a savepoint each currently make a server round-trip. If you're suggesting queueing these up so that it sends these with queries instead of alone, I think that's something the driver could do if it was creating savepoints itself, but would be tougher to do for user savepoints. For user savepoints I think you need to get feedback to the caller immediately upon command execution rather than with a later query. Consider something like trying to release an invalid savepoint. You need to tell the user immediately. Kris Jurka