Re: [GENERAL] in failed sql transaction

2006-09-27 Thread Ralf Wiebicke
Sorry, I was a bit impatient and posted the same question in a newsgroup a few days before. There is an answer now: http://groups.google.de/group/comp.databases.postgresql/browse_thread/thread/36e5c65dd15b0388/1e5ff9b7e2c6863e?hl=de#1e5ff9b7e2c6863e Of course, if anyone has an additional idea,

Re: [GENERAL] in failed sql transaction

2006-09-26 Thread Gurjeet Singh
Thanks a lot for the pointer This is exactly what I have been looking for.The on_error_rollback-on mode works by issuing an implicit SAVEPOINT for you, just before each command that is in a transaction block, and rolls back to the savepoint on error. On 9/26/06, Tom Lane

Re: [GENERAL] in failed sql transaction

2006-09-26 Thread Ralf Wiebicke
> Have you experimented with psql's ON_ERROR_ROLLBACK setting? Thanks for the hint. Seems to be exactly what I want. But is not yet available through JDBC, as far as I see: http://archives.postgresql.org/pgsql-jdbc/2006-07/msg00092.php I'm writing a java framework, so there is no way around JD

Re: [GENERAL] in failed sql transaction

2006-09-25 Thread Tom Lane
Ralf Wiebicke <[EMAIL PROTECTED]> writes: > I finally used savepoints to get what I want. > However I don't like this very much. Have you experimented with psql's ON_ERROR_ROLLBACK setting? regards, tom lane ---(end of broadcast)---

Re: [GENERAL] in failed sql transaction

2006-09-25 Thread Scott Marlowe
On Mon, 2006-09-25 at 16:20, Ralf Wiebicke wrote: > Hi! > > Thanks for all the help. > > I finally used savepoints to get what I want. > > However I don't like this very much. I tried a few other databases (hsqldb, > mysql/innodb and oracle), and none of them made the transaction unusable > af

Re: [GENERAL] in failed sql transaction

2006-09-25 Thread Ralf Wiebicke
Hi! Thanks for all the help. I finally used savepoints to get what I want. However I don't like this very much. I tried a few other databases (hsqldb, mysql/innodb and oracle), and none of them made the transaction unusable after violating the constraint. Best regards, Ralf. ---

Re: [GENERAL] in failed sql transaction

2006-09-25 Thread Andrew Sullivan
On Mon, Sep 25, 2006 at 05:40:56PM +0530, Gurjeet Singh wrote: >I sure like PG's following of the standards, but usability should not be > lost sight of. One man's meal is another man's poison. For me, with a small number of exceptions, the standards conformance _is_ what makes PostgreSQL so

Re: [GENERAL] in failed sql transaction

2006-09-25 Thread Scott Marlowe
On Sun, 2006-09-24 at 12:03 +0200, Ralf Wiebicke wrote: > Hi all! > > I just realized the following behaviour in postgresql: when I violate any > constraint (unique constraint in my case) then the transaction is not usable > anymore. Any other sql command returns a "in failed sql transaction" er

Re: [GENERAL] in failed sql transaction

2006-09-25 Thread Karsten Hilbert
On Mon, Sep 25, 2006 at 05:40:56PM +0530, Gurjeet Singh wrote: > >In this case > >PostgreSQL does the right thing; something went wrong, queries after the > >error may very well depend on that data - you can't rely on the current > >state. And it's what the SQL specs say too, of course... > > In a

Re: [GENERAL] in failed sql transaction

2006-09-25 Thread Gurjeet Singh
On 9/25/06, Alban Hertroys <[EMAIL PROTECTED]> wrote: In this casePostgreSQL does the right thing; something went wrong, queries after theerror may very well depend on that data - you can't rely on the currentstate. And it's what the SQL specs say too, of course... [1] I'm not trying to imply that

Re: [GENERAL] in failed sql transaction

2006-09-25 Thread Alban Hertroys
Gurjeet Singh wrote: > All other databases I used up to now just ignore the statement violating the > constraint, but leave the transaction intact. Which databases behave that way? Does COMMIT succeed even if some statements failed? Oracle, for one, behaves that way... Y

Re: [GENERAL] in failed sql transaction

2006-09-25 Thread Karsten Hilbert
On Mon, Sep 25, 2006 at 03:16:07PM +0530, Gurjeet Singh wrote: > >All other databases I used up to now just ignore the statement violating > >the > >> constraint, but leave the transaction intact. > > > >Which databases behave that way? Does COMMIT succeed even if some > >statements failed? > >

Re: [GENERAL] in failed sql transaction

2006-09-25 Thread Gurjeet Singh
I too have been bothered about this behaviour in the past.On 9/25/06, Michael Fuhr <[EMAIL PROTECTED]> wrote: Transactions are all-or-nothing: all statements must succeed or theCorrect. > All other databases I used up to now just ignore the statement violating the > constraint, but leave the trans

Re: [GENERAL] in failed sql transaction

2006-09-24 Thread Michael Fuhr
On Sun, Sep 24, 2006 at 12:03:59PM +0200, Ralf Wiebicke wrote: > I just realized the following behaviour in postgresql: when I violate any > constraint (unique constraint in my case) then the transaction is not usable > anymore. Any other sql command returns a "in failed sql transaction" error.

[GENERAL] in failed sql transaction

2006-09-24 Thread Ralf Wiebicke
Hi all! I just realized the following behaviour in postgresql: when I violate any constraint (unique constraint in my case) then the transaction is not usable anymore. Any other sql command returns a "in failed sql transaction" error. All other databases I used up to now just ignore the statem