ERROR: out of shared memory

Question

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:

********** 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:

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.

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 where clause if you start counting from the beginning. Do you start counting from begin?

Any ideas?

I updated my question. Thanks for suggestion to put the whole code. — Dmitry Efimenko, May 10 '13 at 23:29
I didn't yet. Before I do so I thought I'd ask if anybody could point on an error that I might have missed. I'd like to understand what's causing this to happen. — Dmitry Efimenko, May 11 '13 at 02:31

score 12 · Accepted Answer · edited May 23 '17 at 10:25

12

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.

edited May 23 '17 at 10:25

Community

1
1

answered May 13 '13 at 17:32

Daniel Vérité

58,074
15
129
156

score 0 · Answer 2 · answered Aug 24 '21 at 15:39

A quick fix for PSQLException error out of shared memory is to set the

max_locks_per_transaction

parameter in PostgreSQL config file, which specifies number of object locks allocated for each transaction (if there are statements/routines that work on large number of tables to finish

Note: postgresql restart is required after update

ERROR: out of shared memory

2 Answers2