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