Re: Implementing SQL ASSERTION - Mailing list pgsql-hackers

On Sat, May 02, 2015 at 10:42:24PM +0100, Joe Wildish wrote:
> 
> > 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
ina more efficient manner than the last. This is achieved by performing static analysis of the assertion's expression
todetermine 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
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
ina statement, to determine if the assertion expression need be validated. I’ve not done as much reading around this
topicyet 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.
TheAM4DP 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@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

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