Re: Revisited: Transactions, insert unique.

Today, in a message to pgsql-general, Ross J. Reedstrom wrote:
>
> I've bent my brain around the SQL92 standards docs, and there's _no_
> requirement for this type of behavior on error.

Don't have access to the actual standard text, perhaps somebody who has
can confirm whether the following quotes from an earlier draft (identified
by the code X3H2-92-154/DBL CBR-002) are also in the final text.

In section 4.10.1 (Checking of constraints) I find this:

When a constraint is checked other than at the end of an SQL-
transaction, if it is not satisfied, then an exception condition
is raised and the SQL-statement that caused the constraint to be
checked has no effect other than entering the exception
information into the diagnostics area.

An automatic rollback of the whole transaction in response to a violated
primary key constraint is hardly consistent with the "no effect"
requirement expressed here.

The following passages from section 4.28 (SQL-transactions) also very
strongly imply that an automatic rollback should not occur except in
circumstances where there is no choice (serialization failure and
unrecoverable errors):

The execution of a <rollback statement> may be initiated implicitly
by an implementation when it detects the inability to guarantee the
serializability of two or more concurrent SQL-transactions. When
this error occurs, an exception condition is raised: transaction
rollback-serialization failure.

The execution of a <rollback statement> may be initiated implicitly
by an implementation when it detects unrecoverable errors. When
such an error occurs, an exception condition is raised: transaction
rollback with an implementation-defined subclass code.

The execution of an SQL-statement within an SQL-transaction has
no effect on SQL-data or schemas other than the effect stated in
the General Rules for that SQL-statement, in the General Rules
for Subclause 11.8, "<referential constraint definition>", and
in the General Rules for Subclause 12.3, "<procedure>".

Perhaps, you can make the argument that an automatic rollback in all error
situations is compliant by claiming that all errors are unrecoverable. In
my view this is definitely against the spirit of the standard. As you said
yourself, all big-name databases behave according to my interpretation,
hence it is understandable that the authors of the standard didn't see a
need to spell this out more explicitly.

Joachim

--
joachim(at)kraut(dot)bc(dot)ca (http://www.kraut.bc.ca)
joachim(at)mercury(dot)bc(dot)ca (http://www.mercury.bc.ca)