Re: PostgreSQL Tool

Denise,

* Ferrell, Denise CTR NSWCDD, Z11 (denise(dot)ferrell(dot)ctr(at)navy(dot)mil) wrote:
> Is there a known tool or a way to retrieve dependencies and/or track changes that will impact other items? For example, if a column is removed from a table is there a way to determine easily that this change will impact functions that use the deleted column?

There is the pg_depend catalog table, but that won't help with columns
which are referenced inside of functions as those are not considered
permanent dependencies and, in general, core PostgreSQL does not know or
really understand the contents of a stored procedure- only the language
handler itself does. There is a 'check' function provided by language
handlers generally, but that only does syntax checking and won't verify
that columns referenced in queries exist.

Also, with all the languages that I'm aware of, including plpgsql,
queries are only planned when they're actually reached- you could have a
query under a conditional (eg: IF) which is very rarely reached and you
won't realize that the query references a removed column until that code
path is followed.

If you have a large collection of plpgsql to review, this can certainly
be difficult to prove. One approach is to have a large set of
regression tests which go through most, if not all, of the code paths in
the plpgsql code. If the column and/or table names are sufficiently
distinct then it might be possible to search for them in the function
bodies and manually review each hit.

Thanks!

Stephen