Re: Expression indexes and dependecies

Pavan Deolasee <pavan.deolasee@gmail.com> writes:
> Ok. I will write up something and submit a patch. Constraints probably also
> suffer from the same issue. Whats surprising is we don't mandate that the
> functions used in CHECK constraint are immutable (like we do for indexes).
> What that means is, even if a row was satisfying a constraint while
> insertion, it may not once its there. Is that intentional ?

Well, it's probably somewhat historical, but I doubt we'd want to
tighten it up now.  Here's an example of a sensible CHECK that's
only stable:
create ... last_update timestamptz check (last_update <= now()) ...

More generally, I think the argument was that the behavior of a
non-immutable CHECK would at least be easy to understand, assuming you
know that the check will only be applied at row insertion or update.
Non-immutable indexes could misbehave in much less obvious ways, for
instance causing the results of a query to differ depending on whether
the planner chose to use that index.
        regards, tom lane