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