Re: Expression indexes and dependecies

Pavan Deolasee <pavan(dot)deolasee(at)gmail(dot)com> writes:
> On Thu, Jul 25, 2013 at 6:43 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> 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.

> But they are also prone to unexpected behaviour, no ? For example, a slight
> variation of the above example is:
> create ... last_update timestamptz check (last_update <= now() and
> last_update >= now() - '1 week'::interval) ...
> This constraint would most likely fail if someone was to restore the table
> from a dump.

Sure, but the reason for the failure would be entirely obvious. It
might be annoying, but it'd still be obvious --- and not too hard to
fix, either. The prohibition on mutable index functions is because you
might waste a great deal of time on diagnosing the reason for a problem.

Now, I grant that that argument could also be used to justify trying
harder than we do now to detect not-really-immutable index functions,
or for trying harder than we do now to prevent you from changing an
index function's behavior. I'm not opposed in principle to tightening
those checks more; I'm just doubtful that we can easily make things
better there.

regards, tom lane