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