Re: [PATCH] SQL assertions prototype

On 15 November 2013 03:30, Peter Eisentraut <peter_e@gmx.net> wrote:

> A constraint trigger performs the actual checking.

Good, that is consistent with other constraints.

> This is not a performance feature.  It's for things like, this table
> should have at most 10 rows, or all the values in this table must be
> bigger than all the values in that other table.  It's a bit esoteric,
> but it comes up again and again.

While I accept it may never perform well, it needs to perform reasonably well.

The key use cases for this are

* enforcing "one and only one" relationships
* enforcing quantified relationships like we do in XML: minoccurs and maxoccurs
* enforcing only one sub-type across multiple sub-type tables
etc

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.

-- Simon Riggs                   http://www.2ndQuadrant.com/PostgreSQL Development, 24x7 Support, Training & Services