This is part of The Pile, a partial archive of some open source mailing lists and newsgroups.
To: rmesser@intellisurvey.com
From: Joern Reder <joern@dimedis.de>
Subject: Re: DBD::Pg aborts Transaction if an error occured
Date: Mon, 3 Dec 2001 11:01:23 +0100
Rob Messer wrote:
> This is really how transactions are supposed to work -- to maintain
> database integrity, in a transaction either everything should go through
> or nothing. It would work the same way in Oracle and other databases
> with transaction control.
Here I fully contradict. Yes, in a transaction either everything should
go through or nothing, but it should be my decision what "everything"
is. The example I wrote works inside our application with Oracle,
Informix, Sybase Enterprise Server and Sybase SQL Anywhere without any
problems since years.
> One solution would seem be to do checking
> before the insert to make sure you are not inserting duplicate keys.
But if it would work, as it should (in my opinion and like it does in
any other dbms I know), I can write the following code, which is much
more elegant and more effective than explicite checking of conditions:
begin work
update table y
insert into table z
try
insert into table x
commit
catch pk_constraint_violation
update table x
commit
catch the rest
rollback
print error message
And this contradicts not the idea of a transaction. It's the programmers
choice, when the transaction is committed and when it is rollbacked. And
using exception handling this way leads to very nice and readable code,
whereas using nested if() conditions is bad code in my eyes.
> Or you could perhaps break the transaction into smaller parts and then just
> ignore the errors if you don't care about them. In any event PostgreSQL
> seems to be doing what it should be doing here.
Ok, to use your words ;) Is it possible to trick PostgreSQL into doing
what it not should do: not aborting the transcation?
===
To: dbi-users@perl.org
From: "Peter Haworth" <pmh@edison.ioppublishing.com>
Subject: Re: DBD::Pg aborts Transaction if an error occured
Date: Mon, 3 Dec 2001 12:12:30 +0000
On Mon, 3 Dec 2001 11:01:23 +0100, Joern Reder wrote:
> But if it would work, as it should (in my opinion and like it does in
> any other dbms I know), I can write the following code, which is much
> more elegant and more effective than explicite checking of conditions:
>
> begin work
>
> update table y
> insert into table z
>
> try
> insert into table x
> commit
>
> catch pk_constraint_violation
> update table x
> commit
>
> catch the rest
> rollback
> print error message
You can do it with one check:
try
if( ! update table x )
insert into table x
commit
catch all
rollback
print error messge
Surely your application ought to know whether it should be doing an insert
or an update anyway?
===
To: <dbi-users@perl.org>
From: "Arguile" <arguile@lucentstudios.com>
Subject: RE: DBD::Pg aborts Transaction if an error occured
Date: Mon, 3 Dec 2001 04:34:31 -0800
Joern wrote:
>
> > This is really how transactions are supposed to work -- to maintain
> > database integrity, in a transaction either everything should go through
> > or nothing. It would work the same way in Oracle and other databases
> > with transaction control.
>
> Here I fully contradict. Yes, in a transaction either everything should
> go through or nothing, but it should be my decision what "everything"
> is. The example I wrote works inside our application with Oracle,
> Informix, Sybase Enterprise Server and Sybase SQL Anywhere without any
> problems since years.
>
PostgreSQL doesn't support nested transactions nor can it distinguish
between errors. Any error in the transaction block causes an automatic
rollback. It's definitely convenient to be able to gracefully recover, but
don't expect this anytime soon in Pg.
(I learned this after losing ten minutes of hand typing to a typo ;).
Tom Lane wrote on pgadmin-hackers:
TL> No, the real problem is that we have only one mechanism for recovering
TL> to a valid state after an error, and that is transaction abort.
TL>
TL> Distinguishing statement abort from transaction abort will require
TL> a huge amount of work --- every transaction-or-longer-lifetime data
TL> structure in the backend will need to be looked at, for example, to see
TL> how it can be rolled back to the proper state after a statement abort.
TL> It'll probably get done someday, but don't hold your breath ...
The thread can be found here:
http://archives2.us.postgresql.org/pgsql-general/2001-07/msg01255.php
===
To: pmh@edison.ioppublishing.com
From: Joern Reder <joern@dimedis.de>
Subject: Re: DBD::Pg aborts Transaction if an error occured
Date: Mon, 3 Dec 2001 13:43:58 +0100
"Peter Haworth" wrote:
> You can do it with one check:
>
> try
> if( ! update table x )
> insert into table x
> commit
> catch all
> rollback
> print error messge
That's correct for the actual example. The problem is: we have many,
many constructions like this in our application, and want to prevent
their adaption for PostgreSQL. Further on we use this in lower level
libraries, which can't make assupmtions about the transactional state or
commit/rollback the actual transaction.
> Surely your application ought to know whether it should be doing an insert
> or an update anyway?
This can easy be done by setting flags, with or without usage of
exception handling. A common case for my example is an import program,
which transparently inserts new or updates existing rows.
Besides this it is much faster to assume, that the row can be inserted,
and only seldomly do special things if this fails, as always execute a
SQL command for checking on existence, which normally always will fail,
thus I have to regularly execute two statements instead of one.
It works with every database I know, but not with PostgreSQL. Whose
transaction model is right?
===
To: Joern Reder <joern@dimedis.de>
From: Rob Messer <rmesser@intellisurvey.com>
Subject: Re: DBD::Pg aborts Transaction if an error occured
Date: Mon, 03 Dec 2001 10:31:11 -0800
Joern Reder wrote:
> Rob Messer wrote:
>
> > This is really how transactions are supposed to work -- to maintain
> > database integrity, in a transaction either everything should go through
> > or nothing. It would work the same way in Oracle and other databases
> > with transaction control.
>
> Here I fully contradict. Yes, in a transaction either everything should
> go through or nothing, but it should be my decision what "everything"
> is. The example I wrote works inside our application with Oracle,
> Informix, Sybase Enterprise Server and Sybase SQL Anywhere without any
> problems since years.
>
> > One solution would seem be to do checking
> > before the insert to make sure you are not inserting duplicate keys.
>
> But if it would work, as it should (in my opinion and like it does in
> any other dbms I know), I can write the following code, which is much
> more elegant and more effective than explicite checking of conditions:
>
> begin work
>
> update table y
> insert into table z
>
> try
> insert into table x
> commit
>
> catch pk_constraint_violation
> update table x
> commit
>
> catch the rest
> rollback
> print error message
>
> And this contradicts not the idea of a transaction. It's the programmers
> choice, when the transaction is committed and when it is rollbacked. And
> using exception handling this way leads to very nice and readable code,
> whereas using nested if() conditions is bad code in my eyes.
>
> > Or you could perhaps break the transaction into smaller parts and then just
> > ignore the errors if you don't care about them. In any event PostgreSQL
> > seems to be doing what it should be doing here.
>
> Ok, to use your words ;) Is it possible to trick PostgreSQL into doing
> what it not should do: not aborting the transcation?
Perhaps more detail on your original example would be
useful. I know from working with Oracle that if you feed it
an insert with a primary key violation in the middle of a
transaction, it will abort the transaction and rollback,
just like PostgreSQL. As your original example was
provided, it didn't really seem to be a PostgreSQL issue.
In your new example, if you put everything in an eval loop
and have a commit after each insert then it should work ok:
eval {
insert...
commit
};
if ($@) {
check for type of error
do update if appropriate
}
But again this doesn't seem to be really different in
PostgreSQL and other DBs with transaction control. That is,
it isn't a matter of "tricking" PostgreSQL, because Oracle
and other DBs will also rollback a transaction when they hit
SQL that has a problem. However, maybe there is more going
on in your code than I can see from your example -- perhaps
if you provide a small version of the exact code that was
working in Oracle and not in PostgreSQL, maybe somebody will
be able to suggest something. Good luck in any event,
===