> > I think some database constraints would need to be marked as "always
> > check on commit" because there's no clear trigger to invalidate them.
>
> You may, but I haven't found a case that convinces me yet.

So, if you wanted a rule such as "count(*) on table1 == count(*) on table2",
would you try to analyze the expression/procedural code that defines the
constraint, find its dependencies, and check the rule any time any of the objects
involved in the rule are touched? Or after every statement, no matter the action?

(In this case, you would need deferred constraints anyway, just to allow a
transaction to insert into both tables, then verify. But that's not the point --
the point is that database rules can touch a lot of tables, can be complex
queries, etc. and would be difficult to analyze properly ahead of time to avoid
useless validations, and would be inefficient to revalidate constantly.)

If you're looking at uncommitted data from other transactions, you have to run
the rule twice: once assuming the other transaction will commit (your data must
be able to stand next to upcoming data) and once assuming it will rollback (your
data must be able to stand on its own.) I'm guessing your logic would be:

after any change:
if (not okay locally) then reject change;
else if (not okay globally) then mark change as suspicious;
else accept change;

on commit:
for (every suspicious change)
if (not okay remotely) then reject commit;
accept commit;

(locally == within current transaction only)
(globally == within current + recently-committed + concurrent transactions)
(remotely == within current + recently-committed)

-Philip