Re: Implementing SQL ASSERTION

> On 1 May 2015, at 19:51, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>
> On Thu, Apr 30, 2015 at 6:36 PM, Joe Wildish
> <joe-postgresql(dot)com(at)elusive(dot)cx> wrote:
>> I’m wondering if there are other people out there working on implementing SQL ASSERTION functionality?
>>
>> I’ve recently spent a bit of time looking to implement the execution models described in “Applied Mathematics for Database Professionals” by Toon Koppelaars and Lex de Haan. I’ve gotten as far as execution model 3 and am now looking at deriving polarity of involved tables to do EM4 (described in some detail in “Deriving Production Rules for Constraint Maintenance”, Ceri & Widom, VLDB Conference 1990, p555-577). EM5 & EM6 look rather more difficult but I’m intending to try and implement those, too.
>>
>> If there are other people working on this stuff it would be great to collaborate.
>
> I don't know of anyone working on this. It sounds very difficult.

The book I mention details a series of execution models, where each successive model aims to validate the assertion in a more efficient manner than the last. This is achieved by performing static analysis of the assertion's expression to determine under what circumstances the assertion need be (re)checked. Briefly:

EM1: after all DML statements;
EM2: only after DML statements involving tables mentioned in the assertion expression;
EM3: only after DML statements involving the columns mentioned in the assertion expression;
EM4: only after DML statements involving the columns, plus if the statement has a “polarity” that may affect the assertion expression.

“Polarity" here means that one is able to (statically) determine if only INSERTS and not DELETES can affect an expression or vice-versa.

EMs 5 and 6 are further enhancements that make use of querying the “transition effect” data of what actually changed in a statement, to determine if the assertion expression need be validated. I’ve not done as much reading around this topic yet so am concentrating on EMs 1-4.

I agree it is a difficult problem but there are a fair number of published academic papers relating to this topic. The AM4DP book draws a lot of this research together and presents the executions models.

I may start writing up on a blog of where I get to, and then post further to this list, if there is interest.

Regards.
-Joe