ndeed, any sort of constraint that invokes a function call which looks at other tables could later be invalidated if those other tables change, and postgres would be none the smarter. the same goes for trigger based checks.
Yes. I could imagine a new kind of "multi-referential trigger" that would specify all relations it touches and the function to fire when each of them is updated. While you'd still have to write the functions correctly it would at least allow one to explicitly model the multi-table dynamic in pg_catalog. Lacking that CHECK is no worse than TRIGGER and we've decided to say "use triggers".
at $job, the policy is, AVOID ALL TRIGGERS AND FANCY CONSTRAINTS :)
they don't even like using foreign key references, and rely on code logic to do most joins in the performance-critical OLTP side of things.
