Postgres DO块使用动态limit/offset报错列page不存在如何解决

• PL/pgSQL 静态 SQL 语句不会自动解析 LIMIT/OFFSET 位置的变量，会将代码中的 page、j 识别为查询表的字段，因此抛出列不存在的错误。
• 额外隐藏问题：PostgreSQL 匿名DO块默认在单事务上下文运行，旧版本不支持块内直接执行COMMIT提交，修复变量问题后可能还会遇到事务提交报错。

使用EXECUTE执行动态SQL，通过USING子句传入分页参数，即可正确识别变量，兼容所有支持PL/pgSQL的PostgreSQL版本：

DO $$ 
DECLARE 
  page int := 50;
  total_cnt int;
BEGIN
  -- 预先查询总条数，避免循环内重复查询损耗性能
  SELECT count(*) INTO total_cnt FROM records_to_update;
  FOR j IN 0..total_cnt BY page LOOP 
    EXECUTE $sql$
      WITH subset_to_update AS (
        SELECT * FROM records_to_update
        ORDER BY target_class_id, main_type_id, source_class_id 
        OFFSET $1
        LIMIT $2
      )
      UPDATE large_table_to_update
      SET to_update_class_id = stu.target_class_id
      FROM subset_to_update stu
      WHERE large_table_to_update.annotation_class_id = stu.source_class_id;
    $sql$ USING j, page;
    -- PostgreSQL 11及以上版本支持匿名块内提交，低版本需封装为PROCEDURE后调用
    COMMIT;            
  END LOOP;
END; $$;

如果records_to_update表数据量超过10万，OFFSET分页的性能会随偏移量增大明显下降，建议改用基于排序字段的键集分页（Keyset Pagination），用上次循环取到的最大排序字段值作为过滤条件，避免全表扫描偏移的开销。

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