Re: [PATCH] SQL assertions prototype

On 18/12/13 10:48, Andrew Dunstan wrote:
>
> On 12/17/2013 04:42 PM, Kevin Grittner wrote:
>> Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>>> On 11/15/2013 05:41 AM, Heikki Linnakangas wrote:
>>>> A fundamental problem with this is that it needs to handle isolation
>>>> reliable, so that the assertion cannot be violated when two concurrent
>>>> backends do things. Consider the example from the manual, which checks
>>>> that a table has at least one row. Now, if the table has two rows to
>>>> begin with, and in one backend you delete one row, and concurrently in
>>>> another backend you delete the other row, and then commit both
>>>> transactions, the assertion is violated.
>>>>
>>>> In other words, the assertions need to be checked in serializable
>>>> mode.
>>>> Now that we have a real serializable mode, I think that's actually
>>>> feasible.
>>> Going back over this patch, I haven't seen any further discussion of
>>> the
>>> point Heikki raises above, which seems like a bit of a showstopper.
>>>
>>> Heikki, did you have specific ideas on how to solve this? Right now my
>>> mind boggles.
>> It works fine as long as you set default_transaction_isolation =
>> 'serializable' and never override that. :-) Of course, it sure
>> would be nice to have a way to prohibit overrides, but that's
>> another issue.
>>
>> Otherwise it is hard to see how to make it work in a general way
>> without a mutually exclusive lock mode on the table for the
>> duration of any transaction which modifies the table.
>>
>
>
>
> Maybe the presence of an assertion should be enough to force
> serializable, i.e. turn it on and not allow it to be turned off.
>
> cheers
>
> andrew
>
>
Perhaps then it should be called an 'assurance', rather than an 'assertion?'

(Not being entirely facetious!)

Cheers,
Gavin