PostgreSQL中使用SCROLL游标时出现unexpected plan node type:41错误排查

问题背景 #

我有一段检查refcursor是否存在数据的逻辑，多数函数中运行正常，但在特定函数里执行时，PostgreSQL会抛出**"游标无法向后滚动"**的错误。

基础逻辑代码如下：

MOVE FORWARD 1 FROM ref;
IF FOUND THEN
  has_rows := TRUE;
END IF;

-- 如果无数据则抛出错误
IF NOT has_rows THEN
  RAISE EXCEPTION 'no data available';
ELSE
  MOVE BACKWARD 1 FROM ref; -- 找到数据后回退到游标起始位置以返回所有行
END IF;

为解决滚动问题，我给游标添加了SCROLL选项，结果又出现新错误："ERROR: unexpected plan node type: 41"。这个问题仅出现在该特定函数中，其他使用相同逻辑的函数均正常运行。公开资料中未找到该错误的相关信息，仅在PostgreSQL源码中发现该错误与查询计划创建有关。

为复现问题，我编写了测试函数：

CREATE OR REPLACE FUNCTION pg_temp.test_cursor()
 RETURNS refcursor
 LANGUAGE plpgsql
AS $$

DECLARE ref REFCURSOR;
has_rows BOOLEAN := FALSE;

BEGIN

ref := 'results_refcursor';
OPEN ref scroll FOR

SELECT 1 AS m;

  MOVE FORWARD 1 FROM ref;
  IF FOUND THEN
    has_rows := TRUE;
  END IF;

  -- 如果无数据则抛出错误
  IF NOT has_rows THEN
    RAISE EXCEPTION 'no data available';
  ELSE
  MOVE BACKWARD 1 FROM ref; -- 找到数据后回退到游标起始位置以返回所有行
  END IF;

RETURN ref;

END; $$;

使用的PostgreSQL版本：

PostgreSQL 15.0 on x86_64-amazon-linux-gnu, compiled by gcc (GCC) 11.3.1 20221121 (Red Hat 11.3.1-4), 64-bit

注：必须使用游标实现，无法替换该方案。

解决方案 #

1. 替换MOVE BACKWARD逻辑 #

出现"unexpected plan node type: 41"错误，是因为PostgreSQL 15对部分简单查询（如SELECT 1）使用SCROLL游标并执行向后移动时，查询计划生成会出现异常。可以换一种方式检查数据，同时避免回退游标：

DECLARE
  ref REFCURSOR;
  has_rows BOOLEAN := FALSE;
  temp_val INT; -- 对应查询列的类型
BEGIN
  ref := 'results_refcursor';
  OPEN ref FOR SELECT 1 AS m;

  -- 先获取第一行数据
  FETCH NEXT FROM ref INTO temp_val;
  IF FOUND THEN
    has_rows := TRUE;
    -- 重新创建游标，将已获取的行和剩余数据合并
    CLOSE ref;
    OPEN ref SCROLL FOR
      SELECT temp_val AS m
      UNION ALL
      SELECT 1 AS m; -- 替换为原查询语句
  ELSE
    RAISE EXCEPTION 'no data available';
  END IF;

  RETURN ref;
END;

2. 显式声明SCROLL游标 #

不在OPEN时添加SCROLL，而是在声明游标时直接指定SCROLL属性：

DECLARE
  ref SCROLL REFCURSOR; -- 显式声明为可滚动游标
  has_rows BOOLEAN := FALSE;
BEGIN
  ref := 'results_refcursor';
  OPEN ref FOR SELECT 1 AS m;

  MOVE FORWARD 1 FROM ref;
  IF FOUND THEN
    has_rows := TRUE;
  END IF;

  IF NOT has_rows THEN
    RAISE EXCEPTION 'no data available';
  ELSE
    MOVE BACKWARD 1 FROM ref;
  END IF;

  RETURN ref;
END;

3. 升级PostgreSQL版本 #

该错误大概率是PostgreSQL 15.0的版本bug，后续小版本（如15.1及以上）已修复该问题，建议升级到对应分支的最新版本。

内容的提问来源于stack exchange，提问作者DatabaseShouter