PostgreSQL 视图的依赖关系 - Redrock Postgres

link管理

链接快照平台

输入网页链接，自动生成快照
标签化管理网页链接

为什么需要了解它？

假设您想修改一个表，例如将列的数据类型从 integer 更改为 bigint ，因为您意识到您需要存储更大的数字。但是，如果存在使用该列的视图，则无法执行此操作。首先必须删除这些视图，然后更改列，然后运行所有的 CREATE VIEW 语句以再次创建视图。

如示例所示，如果存在较深的视图层次结构，则编辑表可能是一个相当大的挑战，因为您必须按正确的顺序创建视图。除非视图所需的所有对象都存在，否则无法创建视图。

视图的最佳实践

在我向您展示如何理清混乱之前，我想告诉您在数据库设计中使用视图时要避免哪些错误。

视图有两点好处：

它们允许您在一个位置具有重复的 SQL 查询或表达式，以便于重用。

它们可以用作从实际的表定义抽象出接口，以便您可以重新组织表，而无需修改接口。

这些应用程序都不要求您“堆叠”视图，即在视图上定义视图。

有两种使用视图的模式往往有问题，它们都源于一种错误的想法，即视图的工作方式与表完全相同，只是因为它看起来像一个表：

定义多层视图，以便最终的查询看起来很简单。但是，当您尝试解开视图时，例如通过查看执行计划，查询变得非常复杂，几乎不可能理解真正发生的事情以及如何改进它。

定义一个非规范化的“全局视图”，它只是所有数据表的连接，并将其用于所有查询。这样做的人往往会感到惊讶，使用某些


    WHERE

条件会工作的很好，但其他的条件却需要很长时间。

永远不要忘记，视图只是一个“提炼”的 SQL 语句，并在执行查询时被其定义替换。

视图如何存储在 PostgreSQL 中？

PostgreSQL 中的视图与表没有什么不同：它是一个“关系”，即“有列的东西”。所有这些对象都存储在 pg_class 系统表中。

如文档所述，视图几乎与表相同，但有一些例外：

它没有数据文件（因为它没有数据）

它的


    relkind

是“

”而不是“

”

它有一个名为“


    _RETURN

”的


    ON SELECT

模式的规则

此“查询重写规则”包含视图的定义，并存储在 pg_rewrite 系统表的 ev_action 列中。

请注意，视图定义不是存储为字符串，而是以“查询解析树”的形式存储。视图在创建时被解析，这会产生以下几个结果：

对象名称在


    CREATE VIEW

期间解析，因此 应用的是当前的 search_path 设置 。

对象是以内部不可变的“对象 ID”而不是其名称引用的。因此， 重命名视图定义中使用的对象或列是没有问题的 。

PostgreSQL 确切地知道视图定义中使用了哪些对象，因此它可以添加对它们的依赖关系。

请注意，PostgreSQL 处理视图的方式与 PostgreSQL 处理函数的方式完全不同：函数体存储为字符串，在创建时不解析。因此，PostgreSQL 无法知道给定函数依赖于哪些对象。

依赖关系是如何存储的？

所有的依赖关系（“实例共享对象”上的依赖关系除外）都存储在 pg_depend 系统表中：


    classid

存储包含依赖对象的系统表的对象 ID


    objid

存储依赖对象的 ID

如果依赖项是针对列的，则用


    objsubid

存储列号


    refclassid

、


    refobjid

和


    refobjsubid

类似于上面的三列，但描述的是引用对象


    deptype

描述依赖关系的类型

重要的是要注意，视图对它使用的对象没有直接依赖关系：依赖对象实际上是视图的重写规则。这又增加了一层间接性。

一个简单的例子

在下文中，我将使用此关系结构来测试我的查询：

CREATE TABLE t1 (
   id integer PRIMARY KEY,
   val text NOT NULL
INSERT INTO t1 VALUES
   (1, 'one'),
   (2, 'two'),
   (3, 'three');
CREATE FUNCTION f() RETURNS text
   LANGUAGE sql AS 'SELECT ''suffix''';
CREATE VIEW v1 AS
SELECT max(id) AS id
FROM t1;
CREATE VIEW v2 AS
SELECT t1.val
FROM t1 JOIN v1 USING (id);
CREATE VIEW v3 AS
SELECT val || f()
FROM t1;

我抛出了一个函数，只是为了表明视图可以依赖于表以外的对象。

在下文中，我将重点介绍表和列，但如果将包含表的 pg_class 系统表替换为包含函数的 pg_proc 系统表，则查询也适用于函数。

查找表上的直接依赖视图

要找出哪些视图直接依赖于表 t1 ，您可以像这样查询：

SELECT v.oid::regclass AS view
FROM pg_depend AS d      -- objects that depend on the table
   JOIN pg_rewrite AS r  -- rules depending on the table
      ON r.oid = d.objid
   JOIN pg_class AS v    -- views for the rules
      ON v.oid = r.ev_class
WHERE v.relkind = 'v'    -- only interested in views
  -- dependency must be a rule depending on a relation
  AND d.classid = 'pg_rewrite'::regclass
  AND d.refclassid = 'pg_class'::regclass
  AND d.deptype = 'n'    -- normal dependency
  AND d.refobjid = 't1'::regclass;
------
(4 rows)

要查找直接依赖于函数 f 的视图，只需将 “ d.refclassid = 'pg_class'::regclass ” 替换为 “ d.refclassid = 'pg_proc'::regclass ”，并将 “ refobjid = 't1'::regclass ” 替换为 “ refobjid = 'f'::regproc ”。

实际上，视图通常不依赖于表本身，而是依赖于表的列（例外情况是在视图中使用所谓的“整行引用”）。这就是视图 v2 在上面的列表中显示两次的原因。您可以使用 DISTINCT 删除这些重复项。

查找对表列的直接依赖关系

我们可以稍微修改上面的查询以查找那些依赖于某个表列的视图，如果您计划删除列（向基表添加列从来都不是问题），这会很有用。

以下查询查找依赖于表 t1 中的列 val 的视图：

SELECT v.oid::regclass AS view
FROM pg_attribute AS a   -- columns for the table
   JOIN pg_depend AS d   -- objects that depend on the column
      ON d.refobjsubid = a.attnum AND d.refobjid = a.attrelid
   JOIN pg_rewrite AS r  -- rules depending on the column
      ON r.oid = d.objid
   JOIN pg_class AS v    -- views for the rules
      ON v.oid = r.ev_class
WHERE v.relkind = 'v'    -- only interested in views
  -- dependency must be a rule depending on a relation
  AND d.classid = 'pg_rewrite'::regclass
  AND d.refclassid = 'pg_class'::regclass 
  AND d.deptype = 'n'    -- normal dependency
  AND a.attrelid = 't1'::regclass
  AND a.attname = 'val';
------
(2 rows)

递归查找所有依赖视图

现在，如果您没有听从我上面给出的建议，并且您继续定义了一个复杂的视图层次结构，那就不只是需要关注直接的依赖关系了。相反，您需要递归地遍历整个层次结构。

例如，假设您想要从我们的示例中 DROP 并重新创建表 t1 ，而且您需要在完成后执行 CREATE VIEW 语句重新创建视图（如果您使用 DROP TABLE t1 CASCADE ，删除它们不会有问题）。

那么，您需要在递归的 “公共表表达式”（CTE）中使用上述查询。CTE 用于跟踪递归的视图依赖关系，可以重用于所有此类需求；唯一的区别在于主查询。

WITH RECURSIVE views AS (
   -- get the directly depending views
   SELECT v.oid::regclass AS view,
          v.relkind = 'm' AS is_materialized,
          1 AS level
   FROM pg_depend AS d
      JOIN pg_rewrite AS r
         ON r.oid = d.objid
      JOIN pg_class AS v
         ON v.oid = r.ev_class
   WHERE v.relkind IN ('v', 'm')
     AND d.classid = 'pg_rewrite'::regclass
     AND d.refclassid = 'pg_class'::regclass
     AND d.deptype = 'n'
     AND d.refobjid = 't1'::regclass
UNION
   -- add the views that depend on these
   SELECT v.oid::regclass,
          v.relkind = 'm',
          views.level + 1
   FROM views
      JOIN pg_depend AS d
         ON d.refobjid = views.view
      JOIN pg_rewrite AS r  
         ON r.oid = d.objid
      JOIN pg_class AS v
         ON v.oid = r.ev_class
   WHERE v.relkind IN ('v', 'm')
     AND d.classid = 'pg_rewrite'::regclass
     AND d.refclassid = 'pg_class'::regclass
     AND d.deptype = 'n'
     AND v.oid <> views.view  -- avoid loop
SELECT format('CREATE%s VIEW %s AS%s',
              CASE WHEN is_materialized
                   THEN ' MATERIALIZED'
                   ELSE ''
              END,
              view,
              pg_get_viewdef(view))
FROM views
GROUP BY view, is_materialized
ORDER BY max(level);
                  format                   
-------------------------------------------
 CREATE VIEW v3 AS SELECT (t1.val || f()) +
    FROM t1;
 CREATE VIEW v1 AS SELECT max(t1.id) AS id+
    FROM t1;
 CREATE VIEW v2 AS SELECT t1.val          +
    FROM (t1                              +
      JOIN v1 USING (id));
(3 rows)

我们需要 GROUP BY 因为视图可能以多种方式依赖于一个对象：在我们的示例中， v2 依赖于 t1 两次：一次是直接的，一次通过 v1 。