postgresql - 错误 : out of shared memory

Question

我有一个插入给定数量的测试记录的查询。它看起来像这样:

CREATE OR REPLACE FUNCTION _miscRandomizer(vNumberOfRecords int)
RETURNS void AS $$
declare
    -- declare all the variables that will be used
begin
    select into vTotalRecords count(*) from tbluser;
    vIndexMain := vTotalRecords;

    loop
        exit when vIndexMain >= vNumberOfRecords + vTotalRecords;

        -- set some other variables that will be used for the insert
        -- insert record with these variables in tblUser
        -- insert records in some other tables
        -- run another function that calculates and saves some stats regarding inserted records

        vIndexMain := vIndexMain + 1;
        end loop;
    return;
end
$$ LANGUAGE plpgsql;

当我对 300 条记录运行此查询时，它会抛出以下错误:

********** Error **********

ERROR: out of shared memory
SQL state: 53200
Hint: You might need to increase max_locks_per_transaction.
Context: SQL statement "create temp table _counts(...)"
PL/pgSQL function prcStatsUpdate(integer) line 25 at SQL statement
SQL statement "SELECT prcStatsUpdate(vUserId)"
PL/pgSQL function _miscrandomizer(integer) line 164 at PERFORM

函数 prcStatsUpdate 如下所示:

CREATE OR REPLACE FUNCTION prcStatsUpdate(vUserId int)
RETURNS void AS
$$
declare
    vRequireCount boolean;
    vRecordsExist boolean;
begin
    -- determine if this stats calculation needs to be performed
    select into vRequireCount
        case when count(*) > 0 then true else false end
    from tblSomeTable q
    where [x = y]
      and [x = y];

    -- if above is true, determine if stats were previously calculated
    select into vRecordsExist
        case when count(*) > 0 then true else false end
    from tblSomeOtherTable c
    inner join tblSomeTable q
       on q.Id = c.Id
    where [x = y]
      and [x = y]
      and [x = y]
      and vRequireCount = true;

    -- calculate counts and store them in temp table
    create temp table _counts(...);
    insert into _counts(x, y, z)
    select uqa.x, uqa.y, count(*) as aCount
    from tblSomeOtherTable uqa
    inner join tblSomeTable q
       on uqa.Id = q.Id
    where uqa.Id = vUserId
      and qId = [SomeOtherVariable]
      and [x = y]
      and vRequireCount = true
    group by uqa.x, uqa.y;

    -- if stats records exist, update them; else - insert new
    update tblSomeOtherTable 
    set aCount = c.aCount
    from _counts c
    where c.Id = tblSomeOtherTable.Id
      and c.OtherId = tblSomeOtherTable.OtherId
      and vRecordsExist = true
      and vRequireCount = true;

    insert into tblSomeOtherTable(x, y, z)
    select x, y, z
    from _counts
    where vRecordsExist = false
      and vRequireCount = true;

    drop table _counts;
end;
$$ LANGUAGE plpgsql;

看起来错误是某处内存累积的结果，但由于我创建了临时表，使用它并立即删除(因此根据我的理解释放内存)，我不明白这怎么可能。

更新

我更新了 prcStatsUpdate 函数来表示我拥有的实际函数。我只是将表名和列名替换为通用名称。我第一次没有发布的原因是它主要是非常简单的 sql 操作，我认为它不会有任何问题。

此外，您从哪里开始行计数？它说错误在第 25 行，但这不可能是真的，因为如果您从头开始计数，第 25 行是 where 子句中的条件。你从 begin 开始数吗？

有什么想法吗？

Answer 1

当临时表被删除时，直到事务结束时才会释放锁。

看到related answer .

如果可能，您应该重新组织代码以在函数外部创建临时表并在函数内部截断/填充它。