Re: Implementing SQL ASSERTION

On Sat, May 02, 2015 at 10:42:24PM +0100, Joe Wildish wrote:
>
> > 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.

I suspect that you would get a lot further with a PoC patch including
the needed documentation. Remember to include how this would work at
all the transaction isolation levels and combinations of same that we
support. Recall also to include the lock strength needed. Just about
anything can be done with a database-wide lock :)

Cheers,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com

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