Re: Implementing SQL ASSERTION

On Tue, Sep 25, 2018 at 12:04:12AM +0100, Joe Wildish wrote:
> Hi Peter,
>
> > My feeling is that if we want to move forward on this topic, we need to
> > solve the concurrency question first. All these optimizations for when
> > we don't need to check the assertion are cool, but they are just
> > optimizations that we can apply later on, once we have solved the
> > critical problems.
>
> Having said all that: there are obviously going to be some expressions
> that cannot be proven to have no potential for invalidating the assertion
> truth. I guess this is the prime concern from a concurrency PoV? Example:
>
> CREATE TABLE t (
> b BOOLEAN NOT NULL,
> n INTEGER NOT NULL,
> PRIMARY KEY (b, n)
> );
>
> CREATE ASSERTION sum_per_b_less_than_10 CHECK
> (NOT EXISTS
> (SELECT FROM (SELECT b, SUM(n)
> FROM t
> GROUP BY b) AS v(b, sum_n)
> WHERE sum_n > 10));

>
> Invalidating operations are "INSERT(t) and UPDATE(t.b, t.n)".

So would DELETE(t), assuming n can be negative.

Is there some interesting and fairly easily documented subset of
ASSERTIONs that wouldn't have the "can't prove" property?

Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778

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