Re: [GENERAL] in failed sql transaction
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, i'd appreciate it. Best regards, Ralf. -- Ralf Wiebicke Software Engineer exedio GmbH Am Fiebig 14 01561 Thiendorf Deutschland Telefon +49 (35248) 80-118 Fax +49 (35248) 80-199 [EMAIL PROTECTED] www.exedio.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] in failed sql transaction
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 JDBC for me. Best regards, Ralf. -- Ralf Wiebicke Software Engineer exedio GmbH Am Fiebig 14 01561 Thiendorf Deutschland Telefon +49 (35248) 80-118 Fax +49 (35248) 80-199 [EMAIL PROTECTED] www.exedio.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] in failed sql transaction
Thanks a lot for the pointer This is exactly what I have been looking for.from_docsThe 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. /from_docsOn 9/26/06, Tom Lane [EMAIL PROTECTED] wrote: 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)---TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match-- [EMAIL PROTECTED][EMAIL PROTECTED] gmail | hotmail | yahoo }.com
Re: [GENERAL] in failed sql transaction
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 transaction intact.Which databases behave that way?Does COMMIT succeed even if somestatements failed?Oracle, for one, behaves that way... Yes, COMMIT does succeed even if some statement(s) threw errors. This is intended behavior.You can use savepoints to roll backpart of a transaction so the transaction can continue after an error.Probably, the 'other' DBs have implemented that by an implicit savepoint just before a command, and rollong back to it automatically, if the transaction fails.This is quite a desirable feature... -- [EMAIL PROTECTED][EMAIL PROTECTED] gmail | hotmail | yahoo }.com
Re: [GENERAL] in failed sql transaction
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? Oracle, for one, behaves that way... Yes, COMMIT does succeed even if some statement(s) threw errors. Probably, the 'other' DBs have implemented that by an implicit savepoint just before a command, and rollong back to it automatically, if the transaction fails. This is quite a desirable feature... Why bother with transactions at all if autocommit is enabled ?? Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] in failed sql transaction
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... Yes, COMMIT does succeed even if some statement(s) threw errors. Actually, Oracle implicitly COMMIT's all open transactions if someone performs a DDL statement on the table (or even the same schema?). What other databases do is not necessarily correct[1]. 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... [1] I'm not trying to imply that what PostgreSQL does is (in general). -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] in failed sql transaction
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 what PostgreSQL does is (in general).--In an automated/programmatic access to the database, this might be desirable; but when there's someone manually doing some activity, it sure does get to one's nerves if the transaction till now was a long one. Instead, the operator would love to edit just that one query and fire again! Also, in automated/programmatic access, the programs are supposed to catch the error and rollback/correct on their own. I sure like PG's following of the standards, but usability should not be lost sight of. Best regards,-- [EMAIL PROTECTED][EMAIL PROTECTED] gmail | hotmail | yahoo }.com
Re: [GENERAL] in failed sql transaction
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 an automated/programmatic access to the database, this might be desirable; but when there's someone manually doing some activity, it sure does get to one's nerves if the transaction till now was a long one. Instead, the operator would love to edit just that one query and fire again! Well, psql does it just that way. It implements auto-commit on behalf of the user unless a transaction is explicitely started. Also, in automated/programmatic access, the programs are supposed to catch the error and rollback/correct on their own. Sure but that of course does not relieve the database of aborting the transacation on its own as soon as something goes wrong. And for sake of efficiency the transaction should be aborted right there and then and subsequent queries can be ignored until the end of transaction. This is easier on CPU cycles and memory consumption. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] in failed sql transaction
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 error. All other databases I used up to now just ignore the statement violating the constraint, but leave the transaction intact. Is this intended behaviour or rather a bug? Or is there any way to switch on the behaviour I'd like to see? Normal behaviour. Have you read up on savepoints? http://www.postgresql.org/docs/8.1/interactive/sql-savepoint.html It allows you to set a point to rollback to should an error occur. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] in failed sql transaction
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 usable. A -- Andrew Sullivan | [EMAIL PROTECTED] A certain description of men are for getting out of debt, yet are against all taxes for raising money to pay it off. --Alexander Hamilton ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] in failed sql transaction
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. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] in failed sql transaction
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 after violating the constraint. I wouldn't hold MySQL as the standard of the right way of doing things. But I do take your point. Having grown up with PostgreSQL, I much prefer the all or nothing approach with explicit save pointing to make you do it right. It's especially nice when you're trying to to an import. With oracle, you HAVE to have sqlldr to get things done. With pgsql, you can just try an import, and if one row is bad, the whole thing aborts, no half finished import without knowing what did or didn't go in. There's the right way, and the easy way, and sadly, seldom are they the same. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] in failed sql transaction
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)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] in failed sql transaction
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 statement violating the constraint, but leave the transaction intact. Is this intended behaviour or rather a bug? Or is there any way to switch on the behaviour I'd like to see? Best regards, Ralf. -- Ralf Wiebicke Softwareengineer exedio GmbH Am Fiebig 14 01561 Thiendorf Deutschland Telefon +49 (35248) 80-118 Fax +49 (35248) 80-199 [EMAIL PROTECTED] www.exedio.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] in failed sql transaction
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. Transactions are all-or-nothing: all statements must succeed or the transaction fails (but see below regarding savepoints). 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? Is this intended behaviour or rather a bug? Or is there any way to switch on the behaviour I'd like to see? This is intended behavior. You can use savepoints to roll back part of a transaction so the transaction can continue after an error. -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
