Re: Implementing SQL ASSERTION

Hi Fabien,

>> * certain combinations of aggregates with comparison operations cannot be invalidating.
>>
>> As an example of the last point, the expression "CHECK (10 > (SELECT COUNT(*) FROM t))" cannot be invalidated by a delete or an update but can be invalidated by an insert.
>
> 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?

Yes, there was quite a bit of discussion in the original thread about concurrency. See here:

https://www.postgresql.org/message-id/flat/1384486216(dot)5008(dot)17(dot)camel%40vanquo(dot)pezone(dot)net#1384486216(dot)5008(dot)17(dot)camel(at)vanquo(dot)pezone(dot)net <https://www.postgresql.org/message-id/flat/1384486216(dot)5008(dot)17(dot)camel(at)vanquo(dot)pezone(dot)net#1384486216(dot)5008(dot)17(dot)camel@vanquo.pezone.net>

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).

> Maybe if the check was deferred, but this is not currently possible with pg (eg the select can simply be put in a function), and I there might be race conditions. ISTM that such a check would imply non trivial locking to be okay, it is not just a matter of deciding whether to invoke the check or not.

I traverse into SQL functions so that the analysis can capture invalidating operations from the expression inside the function. Only internal and SQL functions are considered legal. Other languages are rejected.

-Joe