Thread: 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
ralf.wiebicke@exedio.com
www.exedio.com

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

I too have been bothered about this behaviour in the past.

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...

--
gurjeet@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | yahoo }.com

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

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
alban@magproductions.nl

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 //

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,


--
gurjeet@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | yahoo }.com

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

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.

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  | ajs@crankycanuck.ca
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

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.

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.

Ralf Wiebicke <ralf.wiebicke@exedio.com> 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

> 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
ralf.wiebicke@exedio.com
www.exedio.com

Thanks a lot for the pointer.... This is exactly what I have been looking for.

<from_docs>
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.
</from_docs>






--
gurjeet@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | yahoo }.com

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
ralf.wiebicke@exedio.com
www.exedio.com