Re: [PATCH] SQL assertions prototype

Andrew Tipton <andrew@kiwidrew.com> wrote:
> Simon Riggs <simon@2ndquadrant.com> wrote:
>> So we'd need to get access to the changed rows, rather than
>> re-executing a huge SQL command that re-checks every row of the
>> table.  That last point will make it unusable for sensible
>> amounts of data.
>
> That sounds very similar to handling incremental maintenance of
> materialized views, which Kevin is working on.

It does.

> Let's assume that the "huge SQL command that re-checks every row
> of the table" is actually a materialized view.  In that case, the
> CREATE ASSERTION trigger would merely need to scan the matview
> and raise an error if any rows were present.  That should be a
> very quick operation.

That would certainly be a viable way to implement this once we have
incremental maintenance for materialized views, although I make no
claims to having evaluated it versus the alternatives to be able to
assert what the *best* way is.

> No need to invent some sort of "get access to the changed
> rows" mechanism especially for CREATE ASSERTION.

As soon as we are out of this CF, I am planning to write code to
capture deltas and fire functions to process them "eagerly" (within
the creating transaction).  There has been suggestions that the
changeset mechanism should be used for that, which I will look
into; but my gut feel is that it will be better to build a
tuplestore of tids flagged with "old" or "new" around the point
that "after triggers" fire.  How close does that sound to what
CREATE ASSERTION (as currently envisioned) would need?  How viable
does it sound to turn an assertion expression into a matview which
is empty if there are no violations?

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company