From: Stephen Frost <sfrost@snowman.net>
To: Ferrell, Denise CTR NSWCDD, Z11 <denise.ferrell.ctr@navy.mil>
Cc: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: PostgreSQL Tool
Date: Fri, 3 Oct 2014 14:10:59 -0400
Message-ID: <20141003181059.GP28859@tamriel.snowman.net> (raw)
In-Reply-To: <2BC18916DE4BF141AC1EFC55CE2CAE7D5817F3@NAEANRFKXM01V.nadsusea.nads.navy.mil>
References: <2BC18916DE4BF141AC1EFC55CE2CAE7D5817F3@NAEANRFKXM01V.nadsusea.nads.navy.mil>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
Denise,
* Ferrell, Denise CTR NSWCDD, Z11 (denise.ferrell.ctr@navy.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
Attachments:
signature.asc (application/pgp-signature, 818B) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: sfrost@snowman.net, denise.ferrell.ctr@navy.mil
Subject: Re: PostgreSQL Tool
In-Reply-To: <20141003181059.GP28859@tamriel.snowman.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox