PostgreSQL循环删除超期表同步清理DDL日志报错排查

问题背景 #

• 现有DDL日志表存储表创建记录，原有逻辑为删除所有创建时间超过90天的表
• 需新增逻辑：超期表执行DROP操作完成后，同步删除日志表中对应的关联记录

日志表样例数据如下：

ddl_date           ddl_tag     ID  object_name
2022-07-07 16:40:06   CREATE FOREIGN  1   raj.auth
2022-02-07 17:14:33   CREATE TABLE    6   john.plots_source
2022-03-07 17:14:33   CREATE TABLE    7   john.plots1
2022-04-07 17:14:33   CREATE TABLE    8   johnb.plots_pkey2
2022-05-07 17:14:33   CREATE TABLE    9   johna.plots_address3

原有问题代码 #

DO $$ 
  DECLARE 
    r RECORD;
BEGIN
  FOR r IN 
    (
      SELECT id, object_name from user_monitor.ddl_history WHERE ddl_date < NOW() - INTERVAL '90 days'
    ) 
  LOOP
     EXECUTE 'DROP TABLE IF EXISTS ' || r.object_name || ' CASCADE';
  END LOOP;
 delete from user_monitor.ddl_history where not exists (select tablename from pg_catalog.pg_tables b where r.object_name = b.tablename);
     commit;
END $$ ;

报错信息 #

SQL Error [42601]: ERROR: query has no destination for result data
Hint: If you want to discard the results of a SELECT, use PERFORM instead. Where: PL/pgSQL function inline_code_block line 12 at SQL statement

错误原因 #

• 直接报错原因：FOR循环执行结束后，DELETE语句的子查询中引用了循环变量r，PL/pgSQL引擎解析时将该子查询判定为独立SELECT语句，但未给该查询指定结果存储变量，也未使用PERFORM丢弃结果，直接触发语法错误。
• 隐藏逻辑错误1：循环结束后r只会保留最后一次循环的记录值，就算不报错，也无法匹配所有需要删除的日志记录。
• 隐藏逻辑错误2：object_name字段存储的是模式名.表名格式的全限定名，和pg_tables中仅存储表名的tablename字段做等值匹配永远无法命中，就算语法正确也删不掉对应日志。
• 隐藏逻辑错误3：PostgreSQL的匿名DO块运行在外部事务上下文中，块内部不允许手动执行COMMIT，就算前面的逻辑正常，执行到COMMIT也会抛出新错误。

修正后代码 #

DO $$ 
DECLARE 
  r RECORD;
BEGIN
  FOR r IN 
    SELECT id, object_name from user_monitor.ddl_history WHERE ddl_date < NOW() - INTERVAL '90 days'
  LOOP
     -- 使用format+%I安全处理带特殊字符的表名，避免SQL注入或语法错误
     EXECUTE format('DROP TABLE IF EXISTS %I CASCADE', r.object_name);
     -- 删表完成后直接通过主键ID删除对应日志记录，无需额外查询系统表匹配
     DELETE FROM user_monitor.ddl_history WHERE id = r.id;
  END LOOP;
END $$ ;

修正说明 #

• 移除循环外错误的DELETE语句，将日志删除逻辑移到FOR循环内部，每处理完一条超期记录就同步删除对应日志，避免循环变量失效问题
• 使用format(%I)处理对象标识符，替换原本的字符串直接拼接，兼容特殊字符、大小写混合的表名
• 删除DO块内非法的COMMIT语句，块执行成功后会自动随外部事务提交
• 去掉了冗余的系统表匹配逻辑，直接通过日志表主键ID删除记录，逻辑更可靠、执行效率更高

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