Re: Implementing SQL ASSERTION

On Mon, Jan 15, 2018 at 03:40:57PM +0100, Fabien COELHO wrote:
>
> >>I'm wondering about the effect of MVVC on this: if the check is
> >>performed when the INSERT is done, concurrent inserting transactions
> >>would count the current status which would be ok, but on commit all
> >>concurrent inserts would be there and the count could not be ok anymore?
>
> >The patch doesn’t attempt to address concurrency (beyond the obvious
> >benefit of reducing the circumstances under which the assertion is
> >checked). I am working under the assumption that we will find some
> >acceptable way for that to be resolved :-) And at the moment, working in
> >serialisable mode addresses this issue. I think that is suggested in the
> >thread actually (essentially, if you want to use assertions, you require
> >that transactions be performed at serialisable isolation level).
>
> Thanks for the pointers. The "serializable" isolation level restriction
> sounds reasonnable.

It sounds reasonable enough that I'd like to make a couple of Modest
Proposals™, to wit:

- We follow the SQL standard and make SERIALIZABLE the default
transaction isolation level, and

- We disallow writes at isolation levels other than SERIALIZABLE when
any ASSERTION could be in play.

That latter could range in implementation from crashingly unsubtle to
very precise.

Crashingly Unsubtle:

Disallow writes at any isolation level other than SERIALIZABLE.

Very Precise:

Disallow writes at any other isolation level when the ASSERTION
could come into play using the same machinery that enforces the
ASSERTION in the first place.

What say?

Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate