pg大版本升级兼容性检查——plpgsql_check扩展

在 PostgreSQL 大版本升级后，使用 plpgsql_check 工具可以高效检查 PL/pgSQL 函数和存储过程的兼容性。
限制与补充

点击查看代码

#plpgsql_check 的局限性：
无法检测非 PL/pgSQL 对象（如 SQL 函数、视图中的 SQL 语句）的兼容性问题。
#非 PL/pgSQL 对象的类型
##1. SQL 函数
SQL 函数是使用 SQL 语言编写的函数，它只能包含简单的 SQL 语句，不支持 PL/pgSQL 的过程化编程特性（如条件判断、循环等）。例如：
CREATE FUNCTION get_total_count()
RETURNS integer AS $$
    SELECT COUNT(*) FROM your_table;
$$ LANGUAGE sql;
这个函数使用 LANGUAGE sql 声明，它的主体是一个简单的 SQL 查询语句。
##2. 视图中的 SQL 语句
视图是虚拟表，它基于 SQL 查询定义。例如：
CREATE VIEW your_view AS
SELECT column1, column2
FROM your_table
WHERE condition;
视图中的 SQL 语句定义了视图的结构和数据来源。

#补充工具：
pg_upgrade --check：检查物理升级兼容性。
pgcopydb check-compatibility：逻辑复制兼容性验证。
pg_dump + pg_restore：验证数据结构迁移。

#

1. 安装与启用 plpgsql_check 扩展

点击查看代码

#安装扩展
--在目标 PostgreSQL 实例中安装（需超级用户权限）
sudo apt install postgresql-16-plpgsql-check  # Debian/Ubuntu
sudo yum install plpgsql_check                # CentOS/RHEL
#创建扩展
-- 在目标数据库中启用
CREATE EXTENSION plpgsql_check;

2. 检查函数的兼容性

点击查看代码

#检查所有函数
-- 检查当前数据库中所有 PL/pgSQL 函数
SELECT * FROM plpgsql_check_function_tb();
#检查特定函数
-- 检查函数 `my_function`
SELECT * FROM plpgsql_check_function('my_function()');
#检查所有函数并输出详细信息
SELECT
  funcid::regprocedure AS function_name,
  messages,
  error_context
FROM plpgsql_check_function_tb();

3. 常见兼容性问题的检测与修复示例

案例 1：使用废弃函数（如 pg_terminate_backend(pid)）

点击查看代码

#问题代码：
CREATE OR REPLACE FUNCTION kill_session(pid integer)
RETURNS void AS $$
BEGIN
  PERFORM pg_terminate_backend(pid);
END;
$$ LANGUAGE plpgsql;

#plpgsql_check 输出
WARNING: function pg_terminate_backend(integer) does not exist
HINT: Use pg_terminate_backend(integer, bigint) instead.

#修复方法：
-- 更新为 PG 16 支持的语法（添加超时参数）
CREATE OR REPLACE FUNCTION kill_session(pid integer)
RETURNS void AS $$
BEGIN
  PERFORM pg_terminate_backend(pid, 0); -- 0 表示立即终止
END;
$$ LANGUAGE plpgsql;

案例 2：隐式类型转换（如 text → xml）

点击查看代码

#问题代码
CREATE OR REPLACE FUNCTION parse_xml(data text)
RETURNS xml AS $$
BEGIN
  RETURN data::xml; -- 隐式转换
END;
$$ LANGUAGE plpgsql;

#plpgsql_check 输出：
ERROR: cannot cast type text to xml
CONTEXT: SQL statement "SELECT data::xml"

#修复方法
-- 使用显式转换函数
CREATE OR REPLACE FUNCTION parse_xml(data text)
RETURNS xml AS $$
BEGIN
  RETURN xmlparse(CONTENT data);
END;
$$ LANGUAGE plpgsql;

案例 3：使用废弃的 RAISE 语法

点击查看代码

#问题代码
CREATE OR REPLACE FUNCTION log_message(msg text)
RETURNS void AS $$
BEGIN
  RAISE NOTICE 'Message: %', msg;
END;
$$ LANGUAGE plpgsql;

#plpgsql_check 输出：
WARNING: RAISE option "NOTICE" is deprecated
HINT: Use RAISE NOTICE with USING clause.

#修复方法
-- 更新为新的 `USING` 语法
CREATE OR REPLACE FUNCTION log_message(msg text)
RETURNS void AS $$
BEGIN
  RAISE NOTICE USING MESSAGE = msg;
END;
$$ LANGUAGE plpgsql;