postgresql 错误：共享内存不足
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16490664/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ERROR: out of shared memory
提问by Dmitry Efimenko
I have a query that inserts a given number of test records. It looks something like this:
我有一个插入给定数量的测试记录的查询。它看起来像这样：
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;
When I run this query for 300 records it throws the following error:
当我为 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
The function prcStatsUpdate looks like this:
函数 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;
It looks like the error is a result of a memory building up somewhere but since I create temp table, use it and drop right away (thus to my understanding releasing memory), I don't see how that would be possible.
看起来错误是内存在某处建立的结果，但由于我创建了临时表，使用它并立即删除（因此我的理解是释放内存），我不知道这怎么可能。
Update
更新
I updated prcStatsUpdate function to represent the actual function that I have. I just replaced table and column names to be something generic. The reason I didn't post this first time is that it's mostly very simple sql operations and I assumed there could not be any issues with it.
我更新了 prcStatsUpdate 函数来表示我拥有的实际函数。我只是将表名和列名替换为通用名称。我第一次没有发布的原因是它主要是非常简单的 sql 操作，我认为它不会有任何问题。
Also, where do you start line counting from? It says error is on line 25, but that just can't be true since line 25 is a condition in the whereclause if you start counting from the beginning. Do you start counting from begin?
另外，你从哪里开始计数？它说错误在第 25 行，但这不可能是真的，因为where如果您从头开始计数，第25 行是子句中的条件。你从 开始数begin吗？
Any ideas?
有任何想法吗？
回答by Daniel Vérité
Locks are not released until the end of the transaction when a temporary table is dropped.
当临时表被删除时，直到事务结束时才会释放锁。
See that related answer.
请参阅相关答案。
If possible, you should reorganize the code to create the temp table once outside the function and truncate/populate it inside the function.
如果可能，您应该重新组织代码以在函数外部创建临时表并在函数内部截断/填充它。
