Avoid ‘out of shared memory error’ in PostgreSQL function

I have two tables, a table called companies_display with information about publicly traded companies such as ticker symbol, market cap etc. and a partitioned table stock_prices with the historical stock prices for each company. I want to calculate the beta of each stock and write that into companies_display. For that I wrote the function calculate_beta(ticker) that calculates it:

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;

​x
 
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;​

The function works for just a few companies, but when i tried to update the whole table

UPDATE companies_display SET beta = calculate_beta(ticker);

 
UPDATE companies_display SET beta = calculate_beta(ticker);​

I get an out of shared memory error and it says that max_locks_per_transaction is too low. My guess is that the function creates a lock on the stock_prices table for each company and doesn’t drop them till it would have run to completion. What I tried was to create a loop

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$;

 
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$;​

but I ran into the same problem. Is there a way to drop the lock on stock_prices after each beta calculation or do the update in batches? For about 5 companies at a time my method works.

Answer

If someone runs into the same problem, I avoided the error by calling my sql function from the server I use to update my database. That way I have one transaction per company and I don’t get to many locks.

Advertisement

Answer