Ошибка нехватка разделяемой памяти

Getting problem when taking backup on database contains around 50 schema with each schema having around 100 tables.

pg_dump throwing below error suggesting that to increase max_locks_per_transaction.

pg_dump: WARNING:  out of shared memory
pg_dump: SQL command failed
pg_dump: Error message from server: ERROR:  out of shared memory
HINT:  You might need to increase max_locks_per_transaction.
pg_dump: The command was: SELECT tableoid, oid, prsname, prsnamespace, prsstart::oid, prstoken::oid, prsend::oid, prsheadline::oid, prslextype::oid FROM pg_ts_parser

An updated of max_locks_per_transaction to 256 in postgresql.conf did not solve the problem.

Are there any possibilities which can cause this problem?

Edited:(07 May, 2016)

Postgresql version = 9.1

Operating system = Ubuntu 14.04.2 LTS

shared_buffers in postgresql.conf = 2GB

Edited:(09 May, 2016)

My postgres.conf

maintenance_work_mem = 640MB
wal_buffers = 64MB
shared_buffers = 2GB
max_connections = 100
max_locks_per_transaction=10000

Getting problem when taking backup on database contains around 50 schema with each schema having around 100 tables.

pg_dump throwing below error suggesting that to increase max_locks_per_transaction.

pg_dump: WARNING:  out of shared memory
pg_dump: SQL command failed
pg_dump: Error message from server: ERROR:  out of shared memory
HINT:  You might need to increase max_locks_per_transaction.
pg_dump: The command was: SELECT tableoid, oid, prsname, prsnamespace, prsstart::oid, prstoken::oid, prsend::oid, prsheadline::oid, prslextype::oid FROM pg_ts_parser

An updated of max_locks_per_transaction to 256 in postgresql.conf did not solve the problem.

Are there any possibilities which can cause this problem?

Edited:(07 May, 2016)

Postgresql version = 9.1

Operating system = Ubuntu 14.04.2 LTS

shared_buffers in postgresql.conf = 2GB

Edited:(09 May, 2016)

My postgres.conf

maintenance_work_mem = 640MB
wal_buffers = 64MB
shared_buffers = 2GB
max_connections = 100
max_locks_per_transaction=10000

CREATE OR REPLACE FUNCTION calculate_beta (VARCHAR)
RETURNS float8 AS $beta$
DECLARE
    beta float8;
BEGIN
    WITH weekly_returns AS (
        WITH RECURSIVE
        spy AS (
            SELECT time, close FROM stock_prices WHERE ticker = 'SPY'
        ),
        stock AS (
            SELECT time, close FROM stock_prices WHERE ticker = $1
        )
        SELECT
            spy.time,
            stock.close / LAG(stock.close) OVER (ORDER BY spy.time DESC) AS stock,
            spy.close / LAG(spy.close) OVER (ORDER BY spy.time DESC) AS spy
        FROM stock
        JOIN spy
        ON stock.time = spy.time
        WHERE EXTRACT(DOW FROM spy.time) = 1
        ORDER BY spy.time DESC
        LIMIT 52
    )
    SELECT 
        COVAR_SAMP(stock, spy) / VAR_SAMP(spy) INTO beta
    FROM weekly_returns
    ;
    RETURN beta;
END;
$beta$ LANGUAGE plpgsql;

UPDATE companies_display SET beta = calculate_beta(ticker);

DO
$do$
DECLARE
    ticker_ VARCHAR;
BEGIN
    FOR ticker_ IN SELECT ticker FROM companies_display
    LOOP
        UPDATE companies_display
        SET beta = calculate_beta(ticker_)
        WHERE ticker = ticker_
        ;
    END LOOP;
END
$do$;

1 ответ

Лучший ответ

У меня есть запрос, который вставляет заданное количество тестовых записей. Это выглядит примерно так:

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?

Есть идеи?