PostgreSQL循环删除Schema报共享内存不足问题求助

核心原因 #

你的DO块在单个事务中执行所有Schema删除操作，删除300个Schema会持续占用大量共享内存（锁资源、事务状态等），超出PostgreSQL 9的默认共享内存限制，触发"out of shared memory"错误。

解决方案 #

因为你不需要事务原子性，推荐以下两种方法：

方法1：用dblink实现单个Schema删除独立事务 #

dblink可以在独立事务中执行SQL语句，每个删除操作完成后立即释放内存和锁资源。

步骤1：安装dblink扩展（如果未安装）

CREATE EXTENSION IF NOT EXISTS dblink;

步骤2：修改后的PL/pgSQL代码

DO $$ DECLARE
  schema_name text;
  dropped integer := 0;
BEGIN
  FOR schema_name IN
    SELECT DISTINCT table_schema
    FROM information_schema.tables
    -- 过滤系统Schema和不需要删除的Schema，根据实际需求调整
    WHERE table_schema NOT IN ('pg_catalog', 'information_schema', 'public')
  LOOP
    BEGIN
      -- 通过dblink在独立事务中执行删除
      PERFORM dblink_exec(
        current_setting('dbname'), -- 连接当前数据库
        'DROP SCHEMA IF EXISTS ' || quote_ident(schema_name) || ' CASCADE'
      );
      RAISE NOTICE 'Dropped schema %', schema_name;
      dropped := dropped + 1;
    EXCEPTION WHEN others THEN
      RAISE NOTICE 'Failed to drop schema %: %', schema_name, SQLERRM;
    END;
  END LOOP;
  RAISE NOTICE 'Dropped % schemas', dropped;
END $$;

• 用quote_ident()包裹schema名称，避免SQL注入风险；
• 每个DROP SCHEMA操作在独立事务中执行，执行后立即释放资源，不会累积占用共享内存。

方法2：生成独立SQL脚本逐条执行 #

如果不想使用dblink，可以先生成所有删除语句，再逐条执行（每条语句默认是独立事务）。

步骤1：生成删除语句

SELECT 'DROP SCHEMA IF EXISTS ' || quote_ident(table_schema) || ' CASCADE;' AS drop_stmt
FROM information_schema.tables
WHERE table_schema NOT IN ('pg_catalog', 'information_schema', 'public')
GROUP BY table_schema;

步骤2：执行脚本

将查询结果导出为SQL文件（比如drop_schemas.sql），然后用psql执行：

psql -U your_username -d your_database -f drop_schemas.sql

每条DROP SCHEMA语句会在单独事务中执行，不会累积占用共享内存。

不推荐的方法：调整共享内存参数 #

如果坚持要在单个事务中执行，可以调整max_locks_per_transaction等共享内存参数，但需要重启PostgreSQL，且本质上只是提升了内存上限，没有解决事务累积占用的问题，不推荐。

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