Tom Lane wrote:
> "Richard Broersma" <richard.broersma@gmail.com> writes:
> > I am curious if the motivation is still valid for intentionally
> > omitting check sub-queries. (what was the motivation to begin with?)
>
> > Since we can effectively work around this limitation by doing the same
> > thing with a function in a CHECK constraint, why would we want to
> > prevent anyone from using the standard syntax for achieving the same
> > effect?
>
> Because if we supported the standard syntax, we'd also have to support
> the standard semantics; which a function-in-CHECK does *not* give you.
>
> The standard says that the constraint is guaranteed not to be violated,
> which in the worst case means that any time you update the table(s)
> referenced in the subquery, you have to retest the CHECK expression
> at every row of the table having the constraint. Consider for instance
> CREATE TABLE t1 (x int CHECK (x < (SELECT sum(y) FROM t2)));
> If we change some value of t2.y, do all values of t1.x still satisfy
> their constraint?
>
> In some cases, with enough intelligence you could optimize this into
> something fast enough to be usable; but it's a research problem.
> (The cases that I can see how to optimize are pretty much equivalent to
> plain foreign key constraints, anyway.)
Is this a TODO? I assume it is not.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +