Re: SQL deferred constraints (a bit O/T, I know)

[Quoted] Bob Badour wrote:

> Roy Hann wrote:
>
>> This is probably not the proper place to pose a question about how SQL
>> "should" work, but I don't know of a better one. Any suggestions?
>>
>> I am curious to know the moment to which a deferred constraint should be
>> understood to be deferred. I assume it should be after the last
>> update in a transaction (signalled by a COMMIT) but before the
>> transaction surrenders read consistency. But if that's the case,
>> one can construct a pair of concurrent transactions that
>> severally satisfy all constraints yet jointly leave the database
>> inconsistent. So what's the defined behaviour? (At this moment I'm
>> not interested in what we'd like it to be; I want to know what the
>> standards define it to be.)
>
> You assume the standard defines the behavior. You would have to read the
> standards document to see if it does.

Looking at section 4.10.1 of the standard left me none the wiser, but in section 4.35.4 we are told that integrity constraints are supposed to be processed using serializable isolation regardless of the isolation level of the transaction that triggered the check. (That is not the obvious section in which to secrete that vital detail.)

So now, after more that 12 hours, I have my answer. My gift to the Internet is to share it here.

-- 
Roy