时间戳

首页 > 解决方案 > 如何在 Postgres 中删除多个 blob 大对象

postgresql - 如何在 Postgres 中删除多个 blob 大对象

问题描述

我要删除的 pg_largeobject_metadata 表中有数百万行。到目前为止我尝试过的是：

错误：共享内存不足提示：您可能需要增加 max_locks_per_transaction。上下文：SQL 语句“SELECT lo_unlink(c_row.oid)”PL/pgSQL 函数 inline_code_block 第 21 行，执行 SQL 状态：53200

这是我尝试编写的程序，但仍然出现Out of shared memory ERROR。

DO $proc$
DECLARE
v_fetch     bigInt;
v_offset    bigInt;
nbRows      bigInt;
c_row       record;
c_rows      CURSOR(p_offset bigInt, p_fetch bigInt) FOR SELECT oid FROM pg_largeobject_metadata WHERE oid BETWEEN 1910001 AND 2900000 OFFSET p_offset ROWS FETCH NEXT p_fetch ROWS ONLY;

BEGIN
v_offset    := 0;
v_fetch     := 100;
select count(*) into nbRows FROM pg_largeobject_metadata WHERE oid BETWEEN 1910001 AND 2900000;
RAISE NOTICE 'End loop nbrows = %', nbRows;
LOOP                                        -- Loop the different cursors 
    RAISE NOTICE 'offseter = %', v_offset;          
    OPEN c_rows(v_offset, v_fetch);
    LOOP                                    -- Loop through the cursor results
        FETCH c_rows INTO c_row;
        EXIT WHEN NOT FOUND;
        perform lo_unlink(c_row.oid);
    END LOOP;
    CLOSE c_rows;
    EXIT WHEN  v_offset > nbRows;
    v_offset := v_offset + v_fetch;         -- The next 10000 rows
END LOOP;
END;
$proc$;

我正在使用 Pg 9.5 有没有人遇到过这个问题，可以帮忙吗？

标签： postgresqlblobpostgresql-9.5

解决方案

每个都lo_unlink()在它删除的对象上获取一个锁。这些锁仅在事务结束时被释放，并且被限制max_locks_per_transaction * (max_connections + max_prepared_transactions)（参见锁管理）。默认情况下max_locks_per_transaction是 64，将其提高几个数量级并不是一个好的解决方案。

典型的解决方案是将 DO 块中的外部 LOOP 移动到客户端代码中，并在每次迭代时提交事务（因此每个事务会删除 10000 个大对象并提交）。

从 PostgreSQL 版本 11 开始，DO 块内的 COMMIT是可能的，就像过程中的事务控制是可能的一样。

推荐阅读