PostgreSQL 函数的依赖关系

John Doe 七月 4, 2023

摘要:使用 PostgreSQL 函数时,您需要注意不要引用非确定性的函数,因为函数之间的依赖关系在 PostgreSQL 系统表中没有得到很好的维护。

目录

介绍

以下示例显示了如何最终获得悬空函数:

CREATE OR REPLACE FUNCTION dep_func (INT)
RETURNS INT AS $$
BEGIN
  RETURN $1 + 2;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION test_func(INT)
RETURNS INT AS $$
BEGIN
  RETURN dep_func($1);
END;
$$LANGUAGE plpgsql;

DROP FUNCTION dep_func(int);

test_func函数悬空了。调用此函数时,将引发错误,如下所示:

SELECT test_func (5);
ERROR: function dep_func(integer) does not exist
LINE 1: SELECT dep_func($1)
HINT: No function matches ...

函数是如何存储在 PostgreSQL 中的?

由于函数在 PostgreSQL 中存储为字符串,并且对编写函数的语言没有限制,因此 PostgreSQL 无法分析代码以跟踪依赖关系。

在一般情况下,不可能找出函数的依赖对象;但是有限的(场景受限)解决方案是完全可行的,并且可能足以满足您的需求。

局限性

  • 如果将依赖对象的名称指定为带引号的标识符,则失败(漏报)。
  • 如果依赖对象的名称作为参数传递,则失败(漏报)。
  • 如果在运行时从关系中读取依赖对象的名称,则失败(漏报)。
  • 如果依赖对象的名称由标记组合而成,则失败(漏报)。
  • 如果依赖对象的名称与文本一样存在,则失败(误报)。
  • 如果多行注释中存在依赖对象的名称,则失败(误报)。
  • 不考虑函数重载。
  • 不考虑通过触发器调用的函数。
  • 不考虑每个查询重写规则调用的函数。
  • 不考虑查询重写规则的影响。
  • 无法对用非解释性过程语言(如 C)编写的函数进行分析。

查找函数的直接依赖关系

WITH routines AS (
  SELECT DISTINCT(lower(proname)) AS name,      --#0
    string_to_array(                            --#4
      regexp_replace(                           --#3
        regexp_replace(                         --#2
          lower(pg_get_functiondef(oid))        --#1
          , '--.*?\n', '', 'g')
        , '\W+', ' ', 'g')
      , ' ') AS objnames
  FROM pg_proc
  WHERE proowner = to_regrole(current_role)
    AND prokind != 'a'
), routine_names AS (
  SELECT DISTINCT(lower(proname)) AS name
    FROM pg_proc
    WHERE proowner = to_regrole(current_role)
    ORDER BY 1
), relation_names AS (
  SELECT DISTINCT(lower(relname)) AS name
    FROM pg_class
    WHERE relowner = to_regrole(current_role)
    ORDER BY 1
)
SELECT
  name AS "Your routine",
  array_remove(                                 --#8
    array(                                      --#7
      SELECT unnest(objnames)                   --#5
      INTERSECT                                 --#6
      SELECT name FROM routine_names
    ), name) AS "Dependent routines",
  array(
    SELECT unnest(objnames)
    INTERSECT
    SELECT name FROM relation_names
  ) AS "Dependent relations"
FROM routines;

实现原理

  • #0 收集所有可能是调用者的函数名称。无论如何,我们无法正确处理重载函数,所以为了省去后面处理的麻烦使用DISTINCT进行了去重;除了带引号的标识符之外,SQL 是不区分大小写的,所以为了简化以后的比较我们使用lower()统一转换为小写。
  • #1 通过pg_get_functiondef()函数获取 CREATE FUNCTIONCREATE PROCEDURE 命令的完整文本。同样,再次使用lower()转换为小写。
  • #2 去除单行注释。请注意使用的是惰性的(非贪婪)*?量词:通常的*量词,如果在这里使用,将删除第一个单行注释加上所有后续行!
  • #3 将除字母、数字和_以外的所有字符替换为空格。请注意+量词:它确保 2 个以上连续字符被删除并替换为 1 个空格符。
  • #4 按空格拆分为数组;此数组会包含 SQL 关键词、文本、数字和标识符(包括函数名称)。
  • #5 将数组转换为行集。
  • #6 将引用对象列表和函数列表取交集;结果将仅包含函数名称。
  • #7 将行集转换为数组。
  • #8 由于输入是CREATE FUNCTION f ...命令的完整文本,因此提取的函数名称显然会包含f自身;所以我们用array_remove()去除它。