PostgreSQL 视图的依赖关系

由 Laurenz Albe 七月 5, 2023

摘要：在本文中，您将学习如何在 PostgreSQL 中管理有视图依赖关系的对象。

目录

介绍

我们都知道，在 PostgreSQL 中，如果对象上有视图依赖关系，我们就不能删除它：

CREATE TABLE t (id integer PRIMARY KEY);

CREATE VIEW v AS SELECT * FROM t;

DROP TABLE t;
ERROR:  cannot drop table t because other objects depend on it
DETAIL:  view v depends on table t
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

ALTER TABLE t DROP id;
ERROR:  cannot drop column id of table t because other objects depend on it
DETAIL:  view v depends on column id of table t
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

有些人喜欢它，因为它使数据库保持一致；有些人讨厌它，因为它使关系结构修改更加困难。但事实就是如此。

在本文中，我想探讨视图依赖关系背后的机制，并向您展示如何跟踪哪些视图依赖于某个 PostgreSQL 对象。

为什么需要了解它？

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

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

视图的最佳实践

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

视图有两点好处：

  • 它们允许您在一个位置具有重复的 SQL 查询或表达式，以便于重用。
  • 它们可以用作从实际的表定义抽象出接口，以便您可以重新组织表，而无需修改接口。

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

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

  • 定义多层视图，以便最终的查询看起来很简单。但是，当您尝试解开视图时，例如通过查看执行计划，查询变得非常复杂，几乎不可能理解真正发生的事情以及如何改进它。
  • 定义一个非规范化的“全局视图”，它只是所有数据表的连接，并将其用于所有查询。这样做的人往往会感到惊讶，使用某些WHERE条件会工作的很好，但其他的条件却需要很长时间。

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

视图如何存储在 PostgreSQL 中？

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

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

  • 它没有数据文件（因为它没有数据）
  • 它的relkind是“v”而不是“r”
  • 它有一个名为“_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;

 view
------
 v2
 v1
 v3
 v2
(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';
 
 view
------
 v3
 v2
(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。

搜索

分类

标签