Обсуждение: commit errors

Hello,

Within an application, if I receive an error executing 'BEGIN',
'ROLLBACK', or 'COMMIT', what should I do?

What is the "official" recommendation, if any?
What do you (anyone) do?

I'm especially interested in serializable transactions, if that
makes a difference.

Yes, the documentation says I should either get 'COMMIT' back,
or 'WARNING: COMMIT: no transaciton in progress'. However,
what if, for example, the tcp/ip connection drops at that
point?  What should my application do?

Currently my code looks something like:

bool function_to_do_whatever() {
    while(1) {
        if (!begin_serializable_transaction())  {
            log("aborting: begin transaction failure");
            return false;
        }

        if (!execute( ... )) {
            if (!rollback_transaction()) {
                log("aborting: error rollback transaction");
                return false;
            }
            log("retrying: error executing ...");
            continue;
        }

        if (!commit_transaction())  {
            log("aborting: commit transaction failure");
            return false;
        }
        break;
    }

    log("database transaction commited.");
    return true;
}


--
Ryan VanderBijl                              rvbijl@vanderbijlfamily.com

Ryan VanderBijl <rvbijl-pgsql@vanderbijlfamily.com> writes:
> Within an application, if I receive an error executing 'BEGIN',
> 'ROLLBACK', or 'COMMIT', what should I do?

Well, BEGIN when not in a transaction, and ROLLBACK when in one, should
pretty much *always* succeed.  If they don't, I'd think I'd recommend
an application panic --- it's unlikely that any automatic recovery
procedure is going to improve the situation.

As far as COMMIT goes, you should *definitely* be prepared for failures
on commit.  A fairly obvious case is commit-time constraint checks (not
sure that we have any at the moment, but it'll likely be there someday).
Implementation-level problems could also surface (eg, no disk space to
write commit record).

> Yes, the documentation says I should either get 'COMMIT' back,
> or 'WARNING: COMMIT: no transaciton in progress'. However,
> what if, for example, the tcp/ip connection drops at that
> point?  What should my application do?

This is the classic catch-22: the backend may or may not have replied
COMMIT, but you don't know.  If you are concerned about automatic
recovery from this situation, you'd better design your application so
that you can tell after reconnecting whether your particular transaction
committed or not.

            regards, tom lane