Re: assertions and constraint triggers

Marko Tiikkaja <marko(dot)tiikkaja(at)cs(dot)helsinki(dot)fi> writes:
> On 8/11/10 8:31 AM +0300, Peter Eisentraut wrote:
>> Thinking about SQL assertions (check constraints that are independent of
>> one particular table), do you think it would be reasonable to implement
>> those on top of constraint triggers? On creation you'd hook up a
>> trigger to each of the affected tables. And the trigger function runs
>> the respective check expression. Conceptually, this doesn't seem to be
>> very far away from foreign key constraints after all.

> I thought the point of ASSERTIONs was that you could write a thing such as:
> CREATE ASSERTION foo CHECK ((SELECT count(*) FROM tbl) = 4);
> Enforcing that kind of constraints without true serializability seems
> impractical.

Enforcing that kind of constraint seems impractical with or without
serializability. You need some optimization method that avoids the need
to do full-table scans after every update, or it's not going to be
useful for any real-world situation. Without a scheme that can do
incremental checking for some useful class of assertion expressions,
this isn't going to go far.

regards, tom lane