Re: Revisited: Transactions, insert unique.

Joachim Achtzehnter wrote:
>
> 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.

I found that pretty informative.  I dug up the previous conversation on
this, in which Tom Lane cited section 3.3.4.1 (of what std?).  Its emphasis
on *statements* as opposed to *transactions* suggests to me that aborting
the transaction is beyond the scope of what they had in mind, though I
admittedly don't fully understand the jargon here...

    The phrase "an exception condition is raised:", followed by the
    name of a condition, is used in General Rules and elsewhere to
    indicate that the execution of a statement is unsuccessful, ap-
    plication of General Rules, other than those of Subclause 12.3,
    "<procedure>", and Subclause 20.1, "<direct SQL statement>", may
    be terminated, diagnostic information is to be made available,
    and execution of the statement is to have no effect on SQL-data or
    schemas. The effect on <target specification>s and SQL descriptor
    areas of an SQL-statement that terminates with an exception condi-
    tion, unless explicitly defined by this International Standard, is
    implementation-dependent.

Regards,
Ed Loehr