Postgres DBA

-- scenario3.sql -- 6.0 -- OLTP -- UPDATE CREATE OR REPLACE FUNCTION scenario3() RETURNS integer AS $$ DECLARE  min_i bigint ;  max_i bigint ;  current_aid bigint ;  current_delta bigint ;  counter integer; BEGIN   -- Атомарный выбор и блокировка одной строки с пропуском заблокированных   -- Используем LIMIT 1 и FOR UPDATE SKIP LOCKED для выбора одной доступной строки min_i = 1 ; SELECT MAX(aid) INTO max_i FROM pgbench_accounts ; FOR counter IN 1..10 LOOP -- Генерация случайного сдвига current_delta := (ROUND(RANDOM())::BIGINT) * 10 + 1; current_aid = floor(random() * (max_i - min_i + 1)) + min_i ; SELECT aid INTO current_aid FROM pgbench_accounts WHERE aid = current_aid FOR UPDATE SKIP LOCKED; -- Если строка найдена — обновляем её IF current_aid IS NOT NULL THEN UPDATE pgbench_accounts SET abalance = abalance + current_delta WHERE aid = current_aid; END IF; END LOOP;  return 0 ; END $$ LANGUAGE plpgsql;

-- scenario3.sql -- 6.0 -- OLAP -- UPDATE CREATE OR REPLACE FUNCTION scenario3() RETURNS integer AS $$ DECLARE  min_i bigint ;  max_i bigint ;  current_aid bigint ;  current_delta bigint ; BEGIN -- Генерация случайного сдвига   current_delta := (ROUND(RANDOM())::BIGINT) * 10 + 1;   -- Атомарный выбор и блокировка одной строки с пропуском заблокированных   -- Используем LIMIT 1 и FOR UPDATE SKIP LOCKED для выбора одной доступной строки min_i = 1 ; SELECT MAX(aid) INTO max_i FROM pgbench_accounts ; current_aid = floor(random() * (max_i - min_i + 1)) + min_i ;   SELECT aid INTO current_aid   FROM pgbench_accounts   WHERE aid = current_aid   FOR UPDATE SKIP LOCKED;   -- Если строка найдена — обновляем её   IF current_aid IS NOT NULL THEN    UPDATE pgbench_accounts    SET abalance = abalance + current_delta    WHERE aid = current_aid;   END IF;  return 0 ; END $$ LANGUAGE plpgsql;

-- scenario2.sql -- 6.0 -- OLTP -- INSERT CREATE OR REPLACE FUNCTION scenario2() RETURNS integer AS $$ DECLARE  min_i bigint ;  max_i_aid bigint ;  max_i_tid bigint ;  max_i_bid bigint ;  current_aid bigint ;  current_tid bigint ;  current_bid bigint ;  counter integer ; BEGIN min_i = 1 ; SELECT MAX(aid) INTO max_i_aid FROM pgbench_accounts ; SELECT MAX(tid) INTO max_i_tid FROM pgbench_tellers ; SELECT MAX(bid) INTO max_i_bid FROM pgbench_branches ; FOR counter IN 1..10 LOOP current_aid = floor(random() * (max_i_aid - min_i + 1)) + min_i ; current_tid = floor(random() * (max_i_tid - min_i + 1)) + min_i ; current_bid = floor(random() * (max_i_bid - min_i + 1)) + min_i ; INSERT INTO pgbench_history ( tid, bid, aid, delta, mtime , filler , random_fill ) VALUES ( current_tid , current_bid , current_aid , random() * 1000.0 , CURRENT_TIMESTAMP , '1234567890123456789000', random() * 1000.0 ); END LOOP ;  return 0 ; END $$ LANGUAGE plpgsql;

-- scenario2.sql -- 6.0 -- OLAP -- INSERT CREATE OR REPLACE FUNCTION scenario2() RETURNS integer AS $$ DECLARE  min_i bigint ;  max_i bigint ;  current_aid bigint ;  current_tid bigint ;  current_bid bigint ;  counter integer ; BEGIN min_i = 1 ; SELECT MAX(aid) INTO max_i FROM pgbench_accounts ; current_aid = floor(random() * (max_i - min_i + 1)) + min_i ; SELECT MAX(tid) INTO max_i FROM pgbench_tellers ; current_tid = floor(random() * (max_i - min_i + 1)) + min_i ; SELECT MAX(bid) INTO max_i FROM pgbench_branches ; current_bid = floor(random() * (max_i - min_i + 1)) + min_i ; INSERT INTO pgbench_history ( tid, bid, aid, delta, mtime , filler ) VALUES ( current_tid , current_bid , current_aid , random() * 1000.0 , CURRENT_TIMESTAMP , '1234567890123456789000');  return 0 ; END $$ LANGUAGE plpgsql;

-- scenario1.sql -- 6.0 -- OLTP -- SELECT CREATE OR REPLACE FUNCTION scenario1() RETURNS integer AS $$ DECLARE  test_rec record ;  min_i bigint ;  max_i bigint ;  current_aid bigint ;  current_tid bigint ;  current_bid bigint ;  current_delta bigint ;  counter bigint; BEGIN --------------------------------------------------- --СЦЕНАРИЙ 1 - SELECT min_i = 1 ; SELECT MAX(aid) INTO max_i FROM pgbench_accounts ; current_aid = floor(random() * (max_i - min_i + 1)) + min_i ; select br.bbalance into test_rec from pgbench_branches br join pgbench_accounts acc on (br.bid = acc.bid ) where acc.aid = current_aid ; --СЦЕНАРИЙ 1 - SELECT ONLY --------------------------------------------------- return 0 ; END $$ LANGUAGE plpgsql;

-- scenario1.sql -- 6.0 -- OLAP -- SELECT CREATE OR REPLACE FUNCTION scenario1() RETURNS integer AS $$ DECLARE  test_rec record ; BEGIN WITH branch_summary AS ( SELECT b.bid, COUNT(a.aid) as account_count, SUM(a.abalance) as total_balance, AVG(a.abalance) as avg_balance FROM pgbench_branches b LEFT JOIN pgbench_accounts a ON b.bid = a.bid GROUP BY b.bid ), transaction_summary AS ( SELECT h.bid, COUNT(*) as transaction_count, SUM(h.delta) as net_flow, COUNT(DISTINCT h.aid) as active_accounts, MIN(h.mtime) as first_transaction, MAX(h.mtime) as last_transaction FROM pgbench_history h WHERE h.mtime > CURRENT_TIMESTAMP - INTERVAL '7 days' GROUP BY h.bid ) SELECT bs.bid, bs.account_count, bs.total_balance, bs.avg_balance, COALESCE(ts.transaction_count, 0) as transaction_count, COALESCE(ts.net_flow, 0) as net_flow, COALESCE(ts.active_accounts, 0) as active_accounts, EXTRACT(DAY FROM ts.last_transaction - ts.first_transaction) as activity_days, CASE WHEN ts.transaction_count > 0 THEN bs.total_balance / ts.transaction_count ELSE 0 END as balance_per_transaction INTO test_rec FROM branch_summary bs LEFT JOIN transaction_summary ts ON bs.bid = ts.bid WHERE bs.total_balance > 0 ORDER BY bs.total_balance DESC LIMIT 100; return 0 ; END $$ LANGUAGE plpgsql;