这些 PL/pgSQL 运行时错误，你都碰到过吗-51CTO.COM

介绍

在 Postgres 的函数/过程中编写 PL/pgSQL 代码，并且不包含任何语法错误时，代码会创建成功。但是，这并不能保证代码没有不良或错误的语义，这可能会导致运行时错误。例如，函数中缺少 RETURN。

大多数的这些语义错误，是编写 PL/pgSQL 时的人为错误，或者由于模式更改破坏了其功能，而导致函数被弃用。在第二种情况下，数据库版本控制（DBVC）可以提供帮助。DBVC 能够在不同版本的模式之间进行互操作，并公开要使用的版本，并且还允许您在提交之前重现它，并检测与更改相关的错误。

在检测这类错误或告警方面，有一个推荐的 Postgres 工具，就是 plpgsql_check 扩展，它可以帮助您解决上面提到的那些潜在错误，并最终增强您在 Postgres 函数和存储过程上的开发体验。

plpgsql_check 扩展提供了如下功能：

• 检查嵌入式 SQL 中引用的数据库对象的字段和类型。

• 验证函数参数的输入。

• 标识未使用的变量和函数参数，未修改的 OUT 参数。

• 部分检测死代码（不合格的 RETURN 命令之后的代码）。

• 函数中缺少 RETURN 语句（常见于异常处理程序、复杂逻辑等之后）。

• 能够检测 EXECUTE 语句中潜在的 SQL 注入漏洞。

尽管有这个强大的扩展的帮助，但仍有一些与语义错误相关的错误，是您无法检测到的。在本教程中，我们将向您展示，在执行 PL/pgSQL 函数时，可能遇到的一些极端情况下的错误，以及如何避免它们。

未关闭游标

Postgres 提供了用CURSOR指向 SELECT 语句结果集的功能。使用 CURSOR 的原因之一是，通过在单个事务中一次批量获取几行，来避免在处理大型结果集时出现内存耗尽 - 请参阅游标操作。CLOSE 操作是一个重要的操作，它会关闭游标和释放资源，并回收游标变量。尽管它会在事务关闭时自动关闭，但一条黄金法则是，如果您已完成数据处理，应该始终关闭游标。如果事务尚未完成，并且您需要在同一事务中打开相同的游标，则会出现类似于 SQL Error [42P03]: ERROR: cursor "X" already in use 这样的错误。这是一个可重现的测试用例：

CREATE or REPLACE FUNCTION inc_quantity_by_date (p_orderdate date, p_quantity smallint)
RETURNS SETOF public.orderlines AS $$
DECLARE
  c1 CURSOR for SELECT * from public.orderlines  where orderdate = $1;
  res public.orderlines;
BEGIN
  OPEN c1;
  LOOP
    FETCH c1 INTO res;
    EXIT WHEN NOT found;
    UPDATE public.orderlines
      SET quantity = quantity - $2
      WHERE orderid = res.orderid AND prod_id = res.prod_id;
    RETURN NEXT res;
  END LOOP;
  -- never close the cursor c1;
  RETURN;
END;
$$ LANGUAGE plpgsql;

BEGIN;

SELECT * FROM inc_quantity_by_date('2004-01-27',1::smallint);

SELECT * FROM inc_quantity_by_date('2004-01-14',2::smallint);
ERROR:  cursor "c1" already in use
CONTEXT:  PL/pgSQL function inc_quantity_by_date(date,smallint) line 6 at OPEN

为避免此类错误，应当始终在函数内的RETURN之前，使用子句CLOSE c1关闭游标c1，请养成这样做的习惯。

删除的类型

Postgres 允许使用 CREATE DOMAIN 和 CREATE TYPE 等简单子句，来创建用户定义的数据类型，这对于自定义或扩展原始数据类型非常方便，当然，这些用户定义的数据类型可以在函数和存储过程中使用，例如：

CREATE TYPE public.price_type AS ENUM ('EXPENSIVE', 'CHEAP') ;

CREATE or REPLACE  FUNCTION type_example (pid int)
RETURNS text AS $$
DECLARE
  result public.price_type ;
BEGIN
  result:= (SELECT CASE WHEN p.price > 20 THEN 'EXPENSIVE' ELSE 'CHEAP' END
              FROM public.products p WHERE p.prod_id = $1);
  RETURN result;
END;
$$ LANGUAGE plpgsql;

在这种情况下，如果要删除数据类型public.price_type，Postgres 会允许操作完成，因为它不会存储代码中数据类型的使用与数据类型本身之间的依赖关系。而这种情况会导致执行时间的错误，例如：

select oid, typname from pg_type where typname='price_type';
  oid   |  typname
--------+------------
 120872 | price_type
(1 row)

drop type public.price_type ;

select type_example(81);
ERROR: cache lookup failed for type 120872

为防止删除自定义数据类型时引发的问题，请务必更新相应的函数，或根据需要重新创建数据类型。下一节将讨论与该场景相关的另一个示例。

带有 TOAST 中的值删除表

Postgres 使用超大属性存储技术（TOAST）来存储非常大的字段值。这意味着一个大字段会被拆分为多个物理行，存放到另一个特殊表中，这仅适用于那些可被 TOAST 的数据类型 - 有关更多详细信息，请查看文档。

所有这些活动都在幕后进行，它对用户是透明的。在函数代码中，在将存储在 TOAST 中的值分配给一个变量时，不会立即从 TOAST 中获取其内容，如果您不知道这一点，如果包含数据的表被删除了，则可能会引发运行时错误，例如：

CREATE OR REPLACE  FUNCTION fetch_toast_data_example()
RETURNS text as $$
DECLARE
  txt text;
BEGIN
  CREATE TABLE tab1 (i int, j text);
  -- To force to use store in toast without compresion:
  ALTER TABLE tab1 ALTER COLUMN j SET STORAGE EXTERNAL;
  INSERT INTO tab1
    SELECT i, repeat('textvalue ', 10000)
      FROM generate_series (1, 1000 ) AS i;

  RAISE NOTICE 'data about tables and toast: %',
        (SELECT 'Normal table ' || relname || ' -> OID=' || oid || ' - Toast table ' || 
          (SELECT relname FROM pg_class WHERE oid=c.reltoastrelid) || ' -> OID=' || 
          (SELECT oid FROM pg_class WHERE oid=c.reltoastrelid)
          FROM pg_class c WHERE relname = 'tab1' );

  -- Fetch the data from toast table:
  txt:= (SELECT j  FROM tab1 WHERE i = 20);

  -- Drop tab1 table before return data:
  DROP TABLE tab1;

  RETURN 'text: '||txt;
END;
$$ LANGUAGE plpgsql;

SELECT fetch_toast_data_example();
-- you will get error due to delete the table before return the result
NOTICE:  Info about tables and toast: Normal table tab1 -> OID=128975 - Toast table pg_toast_128975 -> OID=128978
ERROR:  could not open relation with OID 128978 --this is the OID of Toast table
CONTEXT:  PL/pgSQL function fetch_toast_data_example() line 11 at RETURN

为避免此错误，请不要在 RETURN 子句之前删除主表。请注意，TRUNCATE 子句也会引发错误，因为 TRUNCATE 会销毁对象，并使用另一个表文件编号进行重新创建。

解决这种情况的一种方法是，将上述函数转换为存储过程，因为它们是非原子性的上下文，有关更多详细信息，请查看邮件讨论。

CREATE OR REPLACE PROCEDURE fetch_toast_data_example()
AS $$
DECLARE
  txt text;
BEGIN
  CREATE TABLE tab1 (i int, j text);
  -- Force to use store in toast without compresion:
  ALTER TABLE tab1 ALTER COLUMN j SET STORAGE EXTERNAL;
  INSERT INTO tab1
    SELECT i, repeat('textvalue ', 10000)
      FROM generate_series (1, 1000 ) AS i;

  RAISE NOTICE 'data about tables and toast: %',
        (SELECT 'Normal table ' || relname || ' -> OID=' || oid || ' - Toast table ' ||
          (SELECT relname FROM pg_class WHERE oid = c.reltoastrelid) || ' -> OID=' ||
          (SELECT oid FROM pg_class WHERE oid = c.reltoastrelid)  
          FROM pg_class c WHERE relname = 'tab1' );

  -- Fetch the data from toast table :
  txt:= (SELECT j  FROM tab1 WHERE i = 20); 
  -- Drop tab1 table before return data:
  DROP TABLE tab1; 
  RAISE NOTICE 'text: %', txt;
  -- No return needed
END;
$$ LANGUAGE plpgsql;

处理此问题的另一种方法是使用临时表，这些表在事务结束后会自动删除：

...
CREATE TEMP TABLE tab1 (i int, j text);
...
RETURN 'text: ' || txt;
...

结论

正如您在上面所理解的，即使函数可能已正确定义，在运行时也可能会出现一些错误。此处展示的示例可能是极端情况，但尽管这些情况不太可能发生，但它们都是真实的案例。您需要意识到这一点，此外，使用扩展程序 plpgsql_check 可以帮助避免一些令人头疼的问题。