为何第10次调用time_bucket_gapfill会持续抛出PSQLException？

问题根因 #

1. TimescaleDB的time_bucket_gapfill函数要求第三个（起始时间）、第四个（结束时间）入参必须是简单不变表达式，不允许将运行时动态计算的表达式作为这两个位置的入参。
2. 前9次调用正常、第10次报错是PostgreSQL的执行计划缓存机制导致的：默认配置下，PostgreSQL对预处理语句会先执行5次自定义计划（每次都会把参数直接代入生成针对当前入参的执行计划，此时:start AT TIME ZONE :timezone会被提前计算为常量，符合time_bucket_gapfill的入参要求），之后会自动评估是否切换到通用计划，通常第10次左右就会完成切换，切换后参数不会提前代入计算，:start AT TIME ZONE :timezone会被识别为动态表达式，直接触发报错。

可行解决方案 #

• 方案1：参数提前在业务层计算后传入SQL
  不要在SQL内执行时区转换操作，在Java代码层先把start、end按客户端传入的时区转换为对应时区的时间常量，直接作为参数传入SQL，修改后SQL参考：

  SELECT time_bucket_gapfill(CAST(:numOfHours * INTERVAL '1 hour' AS INTERVAL), 
         timestamp AT TIME ZONE 'UTC' AT TIME ZONE :timezone,
         :startConverted, :endConverted) AS time
  FROM info
  WHERE timestamp >= :start AND timestamp < :end
  GROUP BY time
  

  其中:startConverted、:endConverted为Java层完成时区转换后的时间参数。

• 方案2：强制禁用当前查询的通用执行计划
  可以在原有SQL中新增一条无意义的动态参数条件，阻止PostgreSQL生成通用执行计划，修改后SQL参考：

  SELECT time_bucket_gapfill(CAST(:numOfHours * INTERVAL '1 hour' AS INTERVAL), 
         timestamp AT TIME ZONE 'UTC' AT TIME ZONE :timezone,
         :start AT TIME ZONE :timezone, :end AT TIME ZONE :timezone) AS time
  FROM info
  WHERE timestamp >= :start AND timestamp < :end
  AND :forceCustomPlan = 1
  GROUP BY time
  

  调用时:forceCustomPlan固定传1即可，PostgreSQL会识别到语句包含动态参数条件，不会生成通用执行计划，避免切换后报错。

• 方案3：调整执行计划切换策略
  针对当前会话执行SET plan_cache_mode = force_custom_plan，强制该会话所有查询都使用自定义执行计划，不过该方案会影响会话内所有查询的性能，非特殊场景不推荐使用。

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