dependency tracking

Hi,

I have a deployment routine to deal with PG’s requirement of having no dependencies in order to alter a table.

The routine basically navigates the dependency tree and saves off the ddl to a table and drops the dependent (always a view) along the way.

Once the alter table is completed,  the views are recreated and permissions regranted.

Unfortunately,  I’m missing some of the dependencies.

I’ve spent hours trying to understand how to do this, but considering I still don’t have it correct.  I thought I should ask the experts.

The views are complex views based on other views.    They do show up in the PGAdmin dependency list.

This is my sql

SELECT (nsc.nspname::text || '.'::text) || cl.relname::text AS refobj_name, (rwns.nspname::text || '.'::text) || rwcl.relname::text AS depobj_name

, pg_get_viewdef((rwns.nspname::text || '.'::text) || rwcl.relname::text, true) AS depobj_ddl

, array_to_string(rwcl.relacl, ','::text) AS depobj_acl

   FROM pg_namespace nsc

   JOIN pg_class cl ON cl.relnamespace = nsc.oid   --

   JOIN pg_depend dep ON dep.refobjid = cl.oid

   LEFT JOIN pg_rewrite rw ON dep.objid = rw.oid

   LEFT JOIN pg_class rwcl ON rwcl.oid = rw.ev_class

   LEFT JOIN pg_namespace rwns ON rwcl.relnamespace = rwns.oid

  WHERE rw.rulename = '_RETURN'::name

                AND ((nsc.nspname::text || '.'::text) || cl.relname::text) <> ((rwns.nspname::text || '.'::text) || rwcl.relname::text)

  GROUP BY (nsc.nspname::text || '.'::text) || cl.relname::text, (rwns.nspname::text || '.'::text)

                 || rwcl.relname::text, pg_get_viewdef((rwns.nspname::text   || '.'::text)

                 || rwcl.relname::text, true), array_to_string(rwcl.relacl, ','::text);

Thoughts?

I query with the name of the base table I’m trying to change.

Doug Little

Sr. Data Warehouse Architect | Enterprise Data Management | Orbitz Worldwide

500 W. Madison, Suite 1000  Chicago IL 60661| Office 312.260.2588 | Fax 312.894.5164 | Cell 847-997-5741

Douglas.Little@orbitz.com

   orbitz.com | ebookers.com | hotelclub.com | cheaptickets.com | ratestogo.com | asiahotels.com