Re: [PATCH] SQL assertions prototype

On 12/18/2013 02:59 AM, Josh Berkus wrote:
> On 12/17/2013 01:42 PM, Kevin Grittner wrote:
>> 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.
>
> Serializable or not, *what* do we lock for assertions?  It's not rows.
> Tables?  Which tables?  What if the assertion is an interpreted language
> function?  Does the SSI reference counter really take care of all of this?

Here's another idea that doesn't involve SSI:

At COMMIT, take a new snapshot and check that the assertion still passes 
with that snapshot. Now, there's a race condition, if another 
transaction is committing at the same time, and performs the same check 
concurrently. That race condition can be eliminated by holding an 
exclusive lock while running the assertion, until commit, effectively 
allowing the assertion to be checked by only one transaction at a time.

I think that would work, and would be simple, although it wouldn't scale 
too well.

- Heikki