Getting the views dependency tree

When refactoring a certain view, such as adding a new column or changing the column type, one needs to refactor all the views that depend on this particular view. Unfortunately, PostgreSQL does not provide the means to create a logical dump of dependent object.

Getting ready

PostgreSQL provides pg_dump to dump a certain database or a specific set of objects in a database. Also, in development, it is recommended to keep the code in a GIT repository.

Unfortunately, often the SQL code for legacy applications is not maintained in the version control system. In this case, if there is requirement to change a certain view definition or even a column type, it is necessary to identify the affected views, dump them, and then restore them.

How to do it…

The first step is to identify the views to be dropped and restored. Depending on the task, one can write different scripts; a common pattern is to drop the views depending on a certain view, table, or table column. The base pg_catalog tables, pg_depend and pg_rewrite, store the dependency information and view rewriting rules; more human readable information can be found in information_schema.view_table_usage.

Let's assume that there are several views that depend on each other, as shown in the following figure, and the base view a needs to be refactored, which means dropped and created:

How to do it…

To generate this tree of dependency, one can execute the following queries:

CREATE VIEW a AS SELECT 1 FROM car;
CREATE VIEW b AS SELECT 1 FROM a;
CREATE VIEW c AS SELECT 1 FROM a;
CREATE VIEW d AS SELECT 1 FROM b,c;
CREATE VIEW e AS SELECT 1 FROM c;
CREATE VIEW f AS SELECT 1 FROM d,c;

To get the views and find out how they depend on each other in the preceding queries, the following query can be used:

SELECT view_schema,view_name parent, table_schema, table_name  FROM information_schema.view_table_usage WHERE view_name LIKE '_' order by view_name;
 view_schema | parent | table_schema | table_name
-------------+--------+--------------+------------
 public      | a      | public       | car
 public      | b      | public       | a
 public      | c      | public       | a
 public      | d      | public       | c
 public      | d      | public       | b
 public      | e      | public       | c
 public      | f      | public       | c
 public      | f      | public       | d
(8 rows)

Now, to solve the dependency tree, a recursive query will be used, as follows:

CREATE OR REPLACE FUNCTION get_dependency (schema_name text, view_name text) RETURNS TABLE (schema_name text, view_name text, level int) AS $$
WITH RECURSIVE view_tree(parent_schema, parent_view, child_schema, child_view, level) as
(
  SELECT
  parent.view_schema,
  parent.view_name ,
  parent.table_schema,
  parent.table_name,
  1
  FROM
  information_schema.view_table_usage parent
  WHERE
  parent.view_schema = $1 AND
  parent.view_name = $2
  UNION ALL
  SELECT
  child.view_schema,
  child.view_name,
  child.table_schema,
  child.table_name,
  parent.level + 1
  FROM
  view_tree parent JOIN information_schema.view_table_usage child ON child.table_schema = parent.parent_schema AND child.table_name = parent.parent_view
)
SELECT DISTINCT
  parent_schema,
  parent_view,
  level
FROM
  (SELECT
    parent_schema,
    parent_view,
    max (level) OVER (PARTITION BY parent_schema, parent_view) as max_level,
    level
  FROM   
    view_tree) AS FOO
WHERE level = max_level;
$$
LANGUAGE SQL;

In the preceding query, the inner part of the query is used to calculate dependency levels, while the outer part of the query is used to eliminate duplicates. The following shows the dependencies for view a in the right order:

car_portal=# SELECT * FROM get_dependency ('public', 'a') ORDER BY Level;
 schema_name | view_name | level
-------------+-----------+-------
 public      | a         |     1
 public      | b         |     2
 public      | c         |     2
 public      | d         |     3
 public      | e         |     3
 public      | f         |     4
(6 rows)

There's more…

To dump the view's definition, one can use pg_dump with the -t option, which is used to dump a certain relation. So, to dump the views in the previous example, one can use the following trick:

pg_dump –s $(psql -t car_portal -c "SELECT string_agg (' -t ' || quote_ident(schema_name)||'.'||quote_ident(view_name), ' ' ORDER BY level ) FROM get_dependency ('public'::text, 'a'::text)" ) -d  car_portal>/tmp/dump.sql

The psql uses the -t option to return tuples, and the string aggregate function is used to generate the list of views that need to be dumped based on the level order. So, the inner psql query gives the following output:

-t public.a  -t public.b  -t public.c  -t public.d  -t public.e  -t public.f
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset