PostgreSQL继承表批量删旧数据存储过程执行无效问题

1. 核心错误：变量名不匹配 #

你的存储过程里定义了sql_query用来拼接删除语句，但执行时调用的是未定义的consulta_sql——相当于执行了空语句，数据根本没被删除！

修正后的存储过程代码 #

CREATE OR REPLACE FUNCTION new_schema.delete_old_rows()
RETURNS TABLE (child_table text)
LANGUAGE plpgsql
AS $function$
DECLARE
    child_table text;
    sql_query text;
BEGIN
    FOR child_table IN
        SELECT table_name
        FROM information_schema.tables
        WHERE table_schema = 'new_schema'
        AND table_name LIKE 'result_%'
    loop
        sql_query := 'DELETE FROM new_schema.' || child_table || ' WHERE new_date < ''2023-11-01'';';
        EXECUTE sql_query; -- 此处将consulta_sql改为sql_query
        RAISE NOTICE 'Data deleted in table: %', child_table;
        RETURN NEXT child_table; -- 匹配函数返回TABLE的声明
        COMMIT; -- 每次删除后提交，避免事务过大
    END LOOP;
END
$function$;

2. 额外排查点（若修正后仍有问题） #

• 字段类型校验：确认new_date是timestamp/timestamptz或date类型。如果是带时区的时间戳，建议明确时区格式，比如new_date < '2023-11-01 00:00:00+08'（根据业务时区调整）。
• 继承关系确认：执行以下语句，检查所有result_%表是否真的继承自父表result：

  SELECT inhrelid::regclass FROM pg_inherits WHERE inhparent = 'new_schema.result'::regclass;
  

• 锁阻塞检查：执行以下语句，排查是否有未释放的行级锁导致删除语句未生效：

  SELECT * FROM pg_locks WHERE relation = 'new_schema.result_23'::regclass;
  

3. 35亿级数据的删除优化 #

直接全量删除会引发事务日志暴涨、性能卡顿问题，建议改成分批删除：

CREATE OR REPLACE FUNCTION new_schema.delete_old_rows_batch()
RETURNS TABLE (child_table text, total_deleted bigint)
LANGUAGE plpgsql
AS $function$
DECLARE
    child_table text;
    batch_deleted bigint;
    total bigint := 0;
BEGIN
    FOR child_table IN
        SELECT table_name
        FROM information_schema.tables
        WHERE table_schema = 'new_schema'
        AND table_name LIKE 'result_%'
    loop
        total := 0;
        LOOP
            -- 用format()避免SQL注入，每次删10万条
            EXECUTE format('DELETE FROM new_schema.%I WHERE new_date < ''2023-11-01'' LIMIT 100000;', child_table)
            INTO batch_deleted;
            
            total := total + batch_deleted;
            RAISE NOTICE 'Batch deleted % rows from %', batch_deleted, child_table;
            COMMIT;
            
            EXIT WHEN batch_deleted = 0;
        END LOOP;
        
        RETURN NEXT (child_table, total);
        RAISE NOTICE 'Finished cleaning old data from %, total deleted: %', child_table, total;
    END LOOP;
END
$function$;

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