Re: how to continue a transaction after an error?

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