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