使用 plpgsqlcheck 扩展检测 PostgreSQL 语义问题

关键要点

在从 Oracle 迁移到 PostgreSQL 的过程中，检测可能出现的语义问题是一个关键环节。Oracle 在代码编译期间进行语义和语法检查，而 PostgreSQL 仅针对语法进行检查，这样转换的代码库可能会存在潜在的语义差异。这可能会导致功能测试和应用运行时出现问题。为了解决这一问题，开发人员可利用 plpgsqlcheck 扩展检测 PL/pgSQL 代码中的潜在语义错误，从而提高代码的整体质量。

在从 Oracle 迁移到 PostgreSQL 的过程中，检测从 Oracle 特定代码转换到 PostgreSQL 时出现的语义问题是一个重要的方面。虽然 Oracle 在代码编译过程中会进行语义和语法检查，但 PostgreSQL 仅专注于语法检查，这就留出了在转换代码库中可能出现语义不一致的空间。这可能在功能测试和应用运行时造成问题。为了针对这个问题，针对使用 PL/pgSQL 代码进行开发的数据库开发人员可以使用 plpgsqlcheck 扩展。

在本文中，我们将说明如何在 PostgreSQL 中使用 plpgsqlcheck 扩展来捕捉潜在的语义错误并提升整体代码品质。

解决方案概述

plpgsqlcheck 扩展提供了一套工具来对您的 PL/pgSQL 代码进行语义检查。对于处理大型或复杂 SQL 代码库的开发人员来说，plpgsqlcheck 是非常有用的，因为它能帮助及早发现和解决问题。本扩展可识别范围广泛的问题，包括语法错误、未定义变量、未使用变量和其他潜在错误。它会提供所有识别出的问题的详细清单，以及该问题所在代码行的相关信息和明确的问题描述。

plpgsqlcheck 扩展有丰富的使用模式，具体内容可查阅 GitHub 存储库。本文深入探讨以下使用模式：

用于 PostgreSQL 语义检查的函数和过程用于 PostgreSQL 语义检查的触发器依赖列表性能警告

Amazon RDS for PostgreSQL 或 Amazon Aurora PostgreSQL 支持许多 PostgreSQL 数据库引擎的扩展。有关详细信息，请参考 Amazon RDS for PostgreSQL 扩展版本 和 Amazon Aurora PostgreSQL 扩展版本。

不过，该扩展目前尚不支持 Amazon RDS for PostgreSQL 或 Amazon Aurora PostgreSQL 兼容版本。在本文中，我们将使用在 Amazon Elastic Compute Cloud (Amazon EC2) 上安装的标准 PostgreSQL 并解释 plpgsqlcheck 如何帮助开发人员在运行代码之前识别和更正错误。如果您正在迁移到 Amazon RDS for PostgreSQL 或 Amazon Aurora PostgreSQL，可以运行 pgdump 操作并使用 pgrestore 将元数据传输到 EC2 实例。

前提条件

要开始使用本文中描述的解决方案，您应该具备以下前提条件：

一个活跃的 AWS 帐户在 Amazon EC2 上安装的 PostgreSQL 数据库根据 GitHub 存储库 中的说明安装的 plpgsqlcheck 扩展

PostgreSQL 语义检查的挑战

虽然 Oracle 在 PL/SQL 代码编译期间进行语义和语法检查，但 PostgreSQL 仅专注于语法检查。这种行为在从 Oracle 迁移到 PostgreSQL 时，可能导致在功能测试期间出现以下未预见的语义问题：

代码中存在语义问题，未在编译期间出现，但在运行 PL/pgSQL 代码时报告。当有多个语义问题时，未报告所有问题。即使运行函数时，也可能未检测到语义问题，因为错误条件可能不会出现。

让我们通过以下示例来理解这些问题。

示例 1

以下示例使用 PL/pgSQL 函数 calculatearea，该函数计算用户通过函数参数提供的正方形的面积：

sqlCREATE OR REPLACE FUNCTION calculatearea(plength NUMERIC)RETURNS INTEGER AS DECLARE vdt1 DATEBEGIN SELECT currentdate INTO vdt1 FROM DUAL / Bug1 表 DUAL 在 PostgreSQL 中不存在 /

IF plength lt= 0 THEN    RETURN 0END IFRETURN plength  pwidth    / Bug2  变量 pwidth 在函数范围内未被声明 /

END LANGUAGE plpgsql

我们得到以下输出：

sqlCREATE FUNCTION

显然，这个函数有两个错误：

DUAL 表在 PostgreSQL 中默认不存在变量 pwidth 在函数范围内未被声明，但却被访问

这些类型的语义问题在 PostgreSQL 中编译函数时不会出现。不过，它们可能会在运行函数时出现。因此，为了识别和解决 PL/pgSQL 代码中的这些语义问题，需要额外的精力来运行函数，并逐一修正错误：

sqlpostgres=# SELECT calculatearea(45)ERROR relation dual does not existLINE 1 SELECT currentdate FROM DUAL QUERY SELECT currentdate FROM DUALCONTEXT PL/pgSQL function calculatearea(numeric) line 5 at SQL statement

一旦您通过从语句 SELECT currentdate INTO vdt1 FROM DUAL 移除对 DUAL 的引用，将其替换为 SELECT currentdate INTO vdt1，便可以继续测试该函数以查找其他错误。

sqlpostgres=# SELECT calculatearea(45)ERROR column pwidth does not existLINE 1 plength pwidth QUERY plength pwidthCONTEXT PL/pgSQL function calculatearea(numeric) line 11 at RETURN

示例 2

有时，即使在运行函数时也可能未检测到错误，因为错误条件可能不会出现。为了更好地理解这一点，让我们考虑一个示例。

创建一个名为 t1 的表，拥有两个列：a 和 b：

sqlpostgres=# CREATE TABLE t1( a int b int)

我们得到以下输出：

sqlCREATE TABLE

现在让我们创建一个函数，该函数循环遍历表数据并进行某些处理：

sqlCREATE OR REPLACE FUNCTION f2()RETURNS voidLANGUAGE plpgsqlAS DECLARE rec recordBEGIN FOR rec IN SELECT FROM t1 LOOP RAISE NOTICE recc / Bug1 列 c 在表 t1 中不存在 / END LOOPEND

我们得到了以下输出：

sqlCREATE FUNCTION

现在运行这个函数：

sql/ 找不到语义问题，因为表 t1 是空的，控制不会进入循环 /postgres=# SELECT f2()f2

(1 row)

这个例子说明手动转换和测试函数可能会增加错误出现的可能性，从而降低整体代码质量。

plpgsqlcheck 可以识别的常见语义问题

plpgsqlcheck 扩展可以识别 PL/pgSQL 代码中可能引起问题的各类语义问题，例如：

未定义的变量 如果您引用了一个未被声明或超出范围的变量，plpgsqlcheck 会将其标为错误。未使用的变量 它可以突出显示在函数体内声明但未被使用的变量。类型不匹配 它有助于识别可能导致运行时错误的类型不匹配。例如，调用一个程序时，参数类型不一致。函数返回类型与声明的变量和预期类型不匹配。控制流程问题 它识别条件语句、循环或分支中的潜在逻辑错误：事务使用不当，例如 BEGIN 和 COMMIT 语句不匹配。当在同一块内部提升异常却未建立异常块时，异常使用不当。错误的函数调用 它检测到以错误数量或类型的参数调用的函数，或当依赖对象在数据库中不存在时。触发器相关问题 对于 PL/pgSQL 触发器，它帮助发现与 OLD/NEW 记录使用、触发器控制和数据类型不匹配的问题。SQL 语句使用不当 它识别 SELECT 语句和 INTO 子句在列数方面不匹配等问题。子查询使用不当 它检测子查询的不当使用，如在子查询中缺失或多余的列或缺失的别名。游标使用不当 它识别游标错误使用的情况，如未妥善打开或关闭游标。数组使用不当 它检测与数组使用相关的问题，例如数组元素与表列之间的数据类型不匹配。

重要的语义检查函数

plpgsqlcheck 扩展提供了一组函数，允许您检查 PL/pgSQL 代码的语法和语义。以下是主要函数：

plpgsqlcheckfunction 检查特定 PL/pgSQL 函数的语法和语义，并将结果返回为文本plpgsqlcheckfunctiontb 返回以可读的表格形式显示的 plpgsqlcheckfunction 的检查结果

让我们深入这些函数并通过示例说明它们的用法。

创建以下表以了解这两个函数如何在几个 PL/pgSQL 代码示例中运作：

sql/ 创建一个名为 bigtable 的表 /CREATE TABLE bigtable( id integer name text constraint bigtablepk primary key(id))

/ 向 bigtable 插入示例数据 /INSERT INTO bigtableSELECT i nameiFROM generateseries(1 1000) i

/ 一个示例函数，根据传入的 id 从 bigtable 读取数据 /CREATE OR REPLACE FUNCTION example01(id numeric)RETURNS numeric AS DECLARE r record s numeric DEFAULT 0BEGIN FOR r IN SELECT FROM bigtable WHERE id = id LOOP s = s rstudentname END LOOPEND LANGUAGE plpgsql

在此示例中，我们故意包含了两个错误：

对标识符 rstudentname 的错误引用 表 bigtable 中没有此列 studentname缺少 RETURN 语句

尽管有两个语义问题，前面的函数代码将仍然被编译成功，但在运行函数时可能会出现运行时错误。

第一次运行时，将值 1000 传递给函数：

sqlSELECT example01(1000)

命令失败，错误为：函数控制到达结束却没有 RETURN。这是因为值 1000 存在于 bigtable 中且该行被处理，但无法找到 RETURN 关键字来返回输出值。

免费国际服加速器

第二次运行时，将输入参数改为 1001：

sqlSELECT example01(1001)

命令失败，出现不同的错误： record r has no field called studentname。

现在让我们了解如何通过 plpgsqlcheckfunction 和 plpgsqlcheckfunctiontb 来发现这些错误，而无需运行代码。

使用 plpgsqlcheckfunction 进行语义检查

此函数分析单个 PL/pgSQL 函数。它以函数名称或函数 OID 作为参数，并返回发现问题的表格，包含行号、位置和消息等列，以识别每个问题的位置和性质。

使用 plpgsqlcheckfunction 检查上一节中讨论的函数的语法和语义：

sqlSELECT FROM plpgsqlcheckfunction(example01(numeric))

在这种情况下，错误消息指向错误发生的行。不过，需要注意的是，除非配置可选参数以获取所有错误，否则仅会检索到一个错误我们在随后的例子中会更详细地讨论这一点。

使用 plpgsqlcheckfunctiontb 进行语义检查

plpgsqlcheckfunctiontb 函数与 plpgsqlcheckfunction 类似，但它以表格格式返回结果，提供如函数中问题的行号、错误讯息和其他有用的信息。这使得开发人员能够快速理解错误并迅速修正。该函数特别适合用于创建问题列表的报告，提供许多信息，以便仔细检查和解决问题。请参见以下示例代码：

sqlSELECT FROM plpgsqlcheckfunctiontb(example01(numeric))

我们得到以下输出。

PostgreSQL 中的触发器语义检查

PostgreSQL 中的触发器与指定的表、视图或外部表以及名为 yourtriggerfunctionname 的函数相关联，该函数执行其内部定义的业务操作。触发器函数必须在创建触发器之前定义，并且必须声明为不带参数并返回类型为 trigger 的函数。每当触发器被任何触发事件激活时，该函数就会执行。