Skip site navigation (1) Skip section navigation (2)

Re: how to continue a transaction after an error?

From: Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com>
To: Philip Warner <pjw(at)rhyme(dot)com(dot)au>
Cc: "Ross J(dot) Reedstrom" <reedstrm(at)rice(dot)edu>, Cristi Petrescu-Prahova <cristipp(at)lasting(dot)ro>, pgsql-sql(at)postgresql(dot)org
Subject: Re: how to continue a transaction after an error?
Date: 2000-11-14 06:23:57
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-sql
> >When you start a transaction,
> >you're telling the backend "treat all of these statements as one, big,
> >all or nothing event." 
> This is actually contrary to the standard. Statements are atomic, and a
> failed statement should not abort the TX:
>     The execution of all SQL-statements other than SQL-control 
>     statements is atomic with respect to recovery. Such an 
>     SQL-statement is called an atomic SQL-statement.
>     ...
>     An SQL-transaction cannot be explicitly terminated within an 
>     atomic execution context. If the execution of an atomic 
>     SQL-statement is unsuccessful, then the changes to SQL-data or schemas
>     made by the SQL-statement are canceled.

This I agree with in general.  You can almost defend the current behavior
by saying all errors cause an "unrecoverable error" (since I don't see a
definition of unreverable errors), but we're doing that wrong too since
that should initiate a rollback as opposed to our current behavior.
Admittedly, having an SQLSTATE style error code would help once we had
that so you could actually figure out what the error was.

> >If you want (need, if you're using large objects) transactions, you
> >really need to think about your transaction boundries. Don't just wrap
> >your whole frontend in one big, long lived transaction
> Totally agree; transactions will keep locks. Release them as soon as the
> business rules and application design says that you can. Note that
> commit-time constraints may make the commit fail; in this case PG will
> force a rollback, but it *should* allow corrective action and another
> attempt at a commit.

This I disagree with for commit time constraints unless stuff was changed
between the draft I have and final wording:
	"When a <commit statement> is executed,
         all constraints are effectively checked and, if any constraint
         is not satisfied, then an exception condition is raised and the
         transaction is terminated by an implicit <rollback statement>."

Other places they are a little less explicit about failed commits, but it
certainly allows a cancelation of changes:
	"If an SQL-transaction is
         terminated by a <rollback statement> or unsuccessful execution of
         a <commit statement>, then all changes made to SQL-data or schemas
         by that SQL-transaction are canceled. Committed changes cannot be
         canceled. If execution of a <commit statement> is attempted, but
         certain exception conditions are raised, it is unknown whether or
         not the changes made to SQL-data or schemas by that
         SQL-transaction are canceled or made persistent.

And I think this makes sense.  If you're committing then you're saying
you're done and that you want the transaction to go away.  If you just
want to check deferred constraints, there's set constraints mode.  I could
almost see certain recoverable internal state things being worth not doing
a rollback for, but not constraints.

In response to

pgsql-sql by date

Next:From: Philip WarnerDate: 2000-11-14 06:43:21
Subject: Re: how to continue a transaction after an error?
Previous:From: Philip WarnerDate: 2000-11-14 05:30:35
Subject: Re: how to continue a transaction after an error?

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group