Thread: Find out what on what function depends an index

Salvete.

If I have an expression based index (a fucntion call) then the index
will become corrupt, if the function is changed. As the function
developer, I do not know, who uses the function for an index, and as the
user of the function in an index I do not know if the function is
changed. So the idea is: Have an event trigger ON ddl_command_end to do
a REINDEX; see the attached example.

The question is the todo in the script: Is there a way to find out what
indexes depends on what function?

thanks
Andreas

Andreas Ulbrich <andreas.ulbrich@matheversum.de> writes:
> The question is the todo in the script: Is there a way to find out what
> indexes depends on what function?

Direct dependencies would show up in pg_depend.  Indirect ones wouldn't,
since we don't analyze function bodies to see what they call (and if we
tried, there's the little matter of the halting problem).

A larger problem is that frequently the real issue with an unstable index
expression definition is that it depends on context, such as GUC settings,
rather than the function body per se.  So I'm not sure how much pain you'd
really be able to prevent with this approach.

            regards, tom lane

On 01.06.2015 16:15, Tom Lane wrote:
> Andreas Ulbrich <andreas.ulbrich@matheversum.de> writes:
>> The question is the todo in the script: Is there a way to find out what
>> indexes depends on what function?
> Direct dependencies would show up in pg_depend.  Indirect ones wouldn't,
> since we don't analyze function bodies to see what they call (and if we
> tried, there's the little matter of the halting problem).
>
> A larger problem is that frequently the real issue with an unstable index
> expression definition is that it depends on context, such as GUC settings,
> rather than the function body per se.  So I'm not sure how much pain you'd
> really be able to prevent with this approach.
>
>             regards, tom lane
Thanks. It works.

It's only for looking at the some features.

But there seems a "bug": I'm not a superuser but an
ALTER EVENT TRIGGER reindex_function_index DISABLE; -- ENABLE too
is possible, but documentation says: "You must be superuser to alter an
event trigger."

I'm running snapshot 5302760 (22. May)

regards, andreas