> On 1 May 2015, at 19:51, Robert Haas <robertmhaas@gmail.com> wrote:
>
> On Thu, Apr 30, 2015 at 6:36 PM, Joe Wildish
> <joe-postgresql.com@elusive.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
DatabaseProfessionals” by Toon Koppelaars and Lex de Haan. I’ve gotten as far as execution model 3 and am now looking
atderiving 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
tryand 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
amore efficient manner than the last. This is achieved by performing static analysis of the assertion's expression to
determineunder 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
assertionexpression.
“Polarity" here means that one is able to (statically) determine if only INSERTS and not DELETES can affect an
expressionor vice-versa.
EMs 5 and 6 are further enhancements that make use of querying the “transition effect” data of what actually changed in
astatement, to determine if the assertion expression need be validated. I’ve not done as much reading around this topic
yetso 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
AM4DPbook 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