Date: Fri, 3 Oct 2014 14:10:59 -0400
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
Message-ID: <20141003181059.GP28859@tamriel.snowman.net>
References: 
 <2BC18916DE4BF141AC1EFC55CE2CAE7D5817F3@NAEANRFKXM01V.nadsusea.nads.navy.mil>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
 protocol="application/pgp-signature"; boundary="3duvV/J7KUqO/slB"
Content-Disposition: inline
In-Reply-To: 
 <2BC18916DE4BF141AC1EFC55CE2CAE7D5817F3@NAEANRFKXM01V.nadsusea.nads.navy.mil>
User-Agent: Mutt/1.5.21 (2010-09-15)
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org


--3duvV/J7KUqO/slB
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

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

--3duvV/J7KUqO/slB
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: Digital signature

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1

iQIcBAEBAgAGBQJULuayAAoJEO1sijiDR2RVH0EQAIL5ROxIk0JGMHBcbveyTJ5K
oxWqIEJ8HCwd81IFvMJriUMl+aP8lycJ81B1AjAbnQbPiaztooiu/R4PaoneCqRZ
lBUnGwVDZdy2C59Y9swLbF3gTNDoiYLMeseXKkgk2h3/Y8QwZdiOO9OxJTtTbKzB
bm5DPeWKoA17xk06YvmMSGkQ21FSnfr96l724O5MmyondPByNY0YlXel0FJOPawk
j1NAUy50wPvRG/EMNbosRgYYjNA9StfwsKbDOoDpakPFFvl+OLXOimeEp5itKwRM
1R5UxHIh1dxTVNOH0Fs71rIdrrA6x6DAhqmnFEA7Yw+IKa5Ku/obP0ju6I3Q3TcD
c4OPCJZMJOwbZN6cwttWQm0qFGf7/N9IMNMFdOEyVrFj1gCyFwDwcOimml7nmp6g
5mXp/dbPjfEm6vOLrXcLtPlOSn24IllqmWw+ZslrinQP+uAtB7E/jFr4PZj3gvoZ
bO5vi9fIEP0+uzBGK5s2imExszIWW5LZDWy1of9Z8y13lAneMhVAWTNVsdbxKEZA
a+zzMfuLKlHHarLXyyuZxKziIn1c3AdTt+HDa766higHHer5ypGL71RS0pQ6TEYA
gSpxWC++M28dnLHxJnbBJldXMLtZQEe/7RsQukFWTedA0X4/drnlnzXIfwn98/XF
R/JAhRj4LpSvWImN2rVy
=fEyC
-----END PGP SIGNATURE-----

--3duvV/J7KUqO/slB--