Skip site navigation (1) Skip section navigation (2)

Re: Using ON_ERROR_ROLLBACK functionality in JDBC

From: "Koth, Christian (DWBI)" <Christian(dot)Koth(at)smiths-heimann(dot)com>
To: "Kris Jurka" <books(at)ejurka(dot)com>
Cc: <pgsql-jdbc(at)postgresql(dot)org>
Subject: Re: Using ON_ERROR_ROLLBACK functionality in JDBC
Date: 2006-07-28 09:51:12
Message-ID: F5F602C218633C419C7BCA6CDDC6E90A0244572E@COSSMGMBX04.EMAIL.CORP.TLD (view raw or flat)
Thread:
Lists: pgsql-jdbc
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 for the use of the individual or entity to whom they are addressed and may be subject to legal privilege.  If you have received this e-mail in error you should notify the sender immediately by reply e-mail, delete the message from your system and 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 those of the company.  The recipient should check this e-mail and any attachments for the presence of viruses.  The company accepts no liability for any damage caused, directly or indirectly, by any virus transmitted in this email.
******************************************

Responses

pgsql-jdbc by date

Next:From: Mark LewisDate: 2006-07-28 15:58:01
Subject: Re: Using ON_ERROR_ROLLBACK functionality in JDBC
Previous:From: Kris JurkaDate: 2006-07-28 08:57:07
Subject: Re: Using ON_ERROR_ROLLBACK functionality in JDBC

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group