Re: Expression indexes and dependecies

On Tue, Jul 23, 2013 at 4:51 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Andres Freund <andres(at)2ndquadrant(dot)com> writes:
> > On 2013-07-22 17:04:06 -0400, Alvaro Herrera wrote:
> >> One way to attack this would be registering dependencies of a new kind
> >> on functions used by index expressions. Then CREATE OR REPLACE function
> >> could reject alteration for such functions. I don't know if we care
> >> enough about this case.
>
> > I think changing the results of a immutable function violates the
> > contract enough to make this the user's fault. Also the other solutions
> > seem hard to achieve ;)
>
> Yeah. Prohibiting any change at all would be a cure worse than the
> disease, likely, but we don't have the tools to analyze more finely than
> that. And what if the index uses function A which calls function B,
> and you change function B?
>

Right. I was gonna suggest that if can mark the index invalid if a
dependent immutable function is being changed, but that clearly does not
solve the case of nested function calls and we don't have any mechanism to
track that either.

>
> I'd be in favor of adding a note to the CREATE INDEX man page pointing
> out that if you change the behavior of an immutable function, any bad
> consequences for indexes are on your own head, and a REINDEX would be
> advisable.
>
>
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 ?

Thanks,
Pavan

--
Pavan Deolasee
http://www.linkedin.com/in/pavandeolasee