PostgreSQL函数与直接查询返回行数不一致问题排查

版本信息 #

PostgreSQL 13.8

问题描述 #

创建了返回TABLE类型的public.function1函数，直接执行函数内的查询语句时始终返回9行数据；但调用该函数（传入参数：10, '2023-04-17', '180 min'）时，返回行数在9-13行之间波动，需排查异常原因。

函数调用语句 #

SELECT * FROM public.function1(10, '2023-04-17', '180 min')

函数定义 #

CREATE OR REPLACE FUNCTION public.function1(
    argument1 bigint,
    argument2 date,
    argument3 interval)
    RETURNS TABLE(column1 double precision, column2 text) 
    LANGUAGE 'sql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000

AS $BODY$
WITH     "t_temp" AS (
              SELECT "table"."column1",
                   "table"."column2" + $3 AS "column2",
                   "table"."column3" AS "CurrentColumn3",
                   LAG("table"."column3") OVER (ORDER BY "table"."column2") AS "PreviousColumn3",
                   LEAD("table"."column3") OVER (ORDER BY "table"."column2") AS "NextColumn3"
              FROM "table"
              WHERE "table"."column4" = $1 AND
                  TO_DATE(cast("table"."column2" + $3 as TEXT), 'YYYY-MM-DD') = $2 AND
                  "table"."column3" IS NOT NULL
              ORDER BY "table"."column2"
          )
SELECT   EXTRACT(EPOCH FROM "t_temp"."column2") AS "column2",
         encode("t_temp"."CurrentColumn3", 'hex'::text) AS "column3"
FROM     "t_temp"
WHERE    "t_temp"."CurrentColumn3" <> "t_temp"."PreviousColumn3" OR
         "t_temp"."PreviousColumn3" IS NULL OR
           "t_temp"."NextColumn3" IS NULL
GROUP BY "column2", "column3"
ORDER BY "column2";
$BODY$;

直接查询语句 #

WITH     "t_temp" AS (
              SELECT "table"."column1",
                   "table"."column2" + '180 min' AS "column2",
                   "table"."column3" AS "CurrentColumn3",
                   LAG("table"."column3") OVER (ORDER BY "table"."column2") AS "PreviousColumn3",
                   LEAD("table"."column3") OVER (ORDER BY "table"."column2") AS "NextColumn3"
              FROM "table"
              WHERE "table"."column4" = 10 AND
                  TO_DATE(cast("table"."column2" + '180 min' as TEXT), 'YYYY-MM-DD') = '2023-04-17' AND
                  "table"."column3" IS NOT NULL
              ORDER BY "table"."column2"
          )
SELECT   EXTRACT(EPOCH FROM "t_temp"."column2") AS "column2",
         encode("t_temp"."CurrentColumn3", 'hex'::text) AS "column3"
FROM     "t_temp"
WHERE    "t_temp"."CurrentColumn3" <> "t_temp"."PreviousColumn3" OR
         "t_temp"."PreviousColumn3" IS NULL OR
           "t_temp"."NextColumn3" IS NULL
GROUP BY "column2", "column3"
ORDER BY "column2";

表结构 #

CREATE TABLE IF NOT EXISTS public."table"
(
    column1 bigint NOT NULL DEFAULT nextval('table_column1_seq'::regclass),
    column2 bytea,
    column3 timestamp(0) without time zone NOT NULL,
    column4 bigint NOT NULL,
    CONSTRAINT column1_pkey PRIMARY KEY (column1),
    CONSTRAINT table_column4_foreign FOREIGN KEY (column4)
        REFERENCES public.table2 (column1) MATCH SIMPLE
        ON UPDATE CASCADE
        ON DELETE CASCADE
)

问题原因分析 #

1. 窗口函数排序的不确定性：函数中LAG和LEAD窗口函数依赖ORDER BY "table"."column2"排序，但column2是bytea类型，若存在多行column2值相同的情况，PostgreSQL无法保证这些行的固定排序顺序——每次执行时相同bytea值的行排列顺序可能随机变化，导致LAG/LEAD获取的前后行column3值不稳定。

2. CTE中ORDER BY的无效性：CTE中的ORDER BY如果没有配合LIMIT子句，PostgreSQL不会强制保留该排序结果，后续查询（包括窗口函数）可能会重新排序，进一步加剧结果的不确定性。而直接查询时可能因执行计划优化（比如使用主键索引隐式排序），意外保证了排序稳定性，所以结果固定。

3. 过滤条件依赖不稳定的窗口结果：WHERE条件中CurrentColumn3 <> PreviousColumn3、NextColumn3 IS NULL等判断完全依赖窗口函数的结果，一旦PreviousColumn3/NextColumn3值因排序变化而改变，符合条件的行数就会波动。

解决方案 #

1. 为窗口函数添加唯一排序键 #

修改窗口函数的ORDER BY子句，加入唯一标识列（比如主键column1），确保排序绝对稳定：

LAG("table"."column3") OVER (ORDER BY "table"."column2", "table"."column1") AS "PreviousColumn3",
LEAD("table"."column3") OVER (ORDER BY "table"."column2", "table"."column1") AS "NextColumn3"

2. 修正日期判断逻辑 #

原查询中TO_DATE(cast("table"."column2" + $3 as TEXT), 'YYYY-MM-DD') = $2的写法存在性能问题，且可能因字符转换产生意外错误，建议改为直接日期运算：

DATE("table"."column2" + $3) = $2

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