Re: ROLLBACK automatically - Mailing list pgsql-general

"On Error Resume Next" is also a common programming "pattern".  It doesn't
make it right.  I believe Mr. Allie misspoke when he said that the problem
with using error handling to direct normal program flow was in the
"additional side-effects".  The problem is not with additional
side-effects, but rather with alternative causes.  That is, any number of
things can cause an UPDATE to fail (including your fat-fingering the SQL
statement).  When you assume that an error is caused by a particular event
and deal with it "elegantly" you move the error out of the realm of
functional errors (which are detectable) and into the realm of logic errors
(which are not detectable).  That is why relying on error handling to
direct normal program flow ranks up there with reusing variables on the
list programming don'ts.

Mr. Allie is correct, the *proper* way to address this issue is with a
preliminary SELECT.

(Am I too dogmatic?)
David Boerwinkle

-----Original Message-----
From: Chris Bitmead <chrisb@nimrod.itg.telstra.com.au>
To: Billy G. Allie <bga@mug.org>
Cc: Alex Bolenok <abolen@chat.ru>; Kshipra <kshipra@mahindrabt.com>;
pgsql-general <pgsql-general@postgresql.org>
Date: Monday, July 24, 2000 11:31 PM
Subject: Re: [GENERAL] ROLLBACK automatically


"Billy G. Allie" wrote:
>
> Chris Bitmead wrote:
> >
> > And what if I didn't want the commit to fail? What if I half expected
the insert
> > to fail and then want to do an update instead? That's a pretty common
pattern - try
> > to insert, if fail - do an update instead.
> >
> > Is this behaviour some kind of standard? Can it be changed?
>
> Hmmmmm.....  where to begin.
>
> You use a transaction when you have a series of related insert/updates
that
> must all
> succeed or all fail.  For example, in an accounting system you make a
debit to
> one account and a credit to a different account.  If done outside a
> transaction and one fails, your books are out of balance.  If done in a
> transaction, if one fails, then a rollback is done so that neither
apply --
> your books remain balanced.  If your updates are not related in such a
way
> that failed insert/update does not require the previous updates to be
rolled
> back, perform them in seperate transactions, or outside of a transaction
> (using the autocommit feature of PostgreSQL).
>
> As for your common pattern -- it's a poor one.  The reason it's a poor
one is
> that you are relying on an error condition to determine the coarse of
action,

That is normally considered a very good course of action because it has
much
better performance than your solution. Actually, usually one will want
to try
an UPDATE first,  which will in many applications succeed in 99% of
cases, and
then do an INSERT on failure. In other words, only one query instead of
two
for most cases. This is a common programming pattern - try the typical
case
first and fall-back to a back-up solution on error. The alternative,
which is
trying to determine whether it will succeed has worse performance.

> but the error condition has additional side effects

As you say, it doesn't work because of the side effect. But why must we
have
this side effect? Isn't the side effect wrong? Shouldn't the application
programmer decide whether a particular error should or shouldn't cause a
rollback?

> (an aborted transaction)
> that prevent easy recovery.  A better pattern would be to do a select
instead
> of an insert.  If no rows are returned, do an insert.  If row is
returned, do
> an update.  A select that returns 0 rows is not an error, the transaction
is
> not aborted, and you can continue using the transaction until you are
ready to
> commit (or rollback) it.