Комплексна відладка та оптимізація багатокрокової процедури

Багатокрокові процедури — це "швейцарські ножі" для баз даних. Вони часто складаються з валідації вхідних даних, виконання змін (наприклад, оновлення записів, вставка логів), а іноді ще й з аналітики. Але ось проблема: чим складніше процедура, тим більша ймовірність помилки. Логічна помилка, повільний запит, пропущений нюанс — і все може піти шкереберть.

Комплексна відладка включає такі аспекти:

1. Аналіз вхідних даних: правильно налаштовані параметри? Вірні дані передані?
2. Перевірка виконання ключових етапів: всі кроки процедури виконуються коректно?
3. Логування проміжних результатів: щоб знати, що сталося до того, як щось "зламалося".
4. Оптимізація вузьких місць продуктивності: покращуємо слабкі місця, які "гальмують" запити.

Постановка задачі: приклад багатокрокової процедури

Для нашого практичного прикладу уявімо, що ми працюємо з базою даних інтернет-магазину. Нам треба створити процедуру для обробки замовлення. Вона буде виконувати такі кроки:

1. Перевіряти наявність товару на складі.
2. Бронювати товар.
3. Оновлювати статус замовлення.
4. Записувати події (наприклад, успішне бронювання або помилку) в таблицю логів.

Скрипт структури бази даних:

-- Таблиця товарів
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    stock_quantity INTEGER NOT NULL
);

-- Таблиця замовлень
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    product_id INTEGER REFERENCES products(product_id),
    order_status TEXT NOT NULL
);

-- Таблиця логів
CREATE TABLE order_logs (
    log_id SERIAL PRIMARY KEY,
    order_id INTEGER,
    log_message TEXT,
    log_time TIMESTAMP DEFAULT NOW()
);

Крок 1: Створення багатокрокової процедури

Давай створимо базову процедуру process_order. Вона буде приймати ідентифікатор замовлення і виконувати всі етапи обробки.

CREATE OR REPLACE FUNCTION process_order(p_order_id INTEGER)
RETURNS VOID LANGUAGE plpgsql AS $$
DECLARE
    v_product_id INTEGER;
    v_stock_quantity INTEGER;
BEGIN
    -- 1. Отримуємо ідентифікатор товару та статус замовлення
    SELECT product_id INTO v_product_id
    FROM orders
    WHERE order_id = p_order_id;

    IF v_product_id IS NULL THEN
        RAISE EXCEPTION 'Замовлення % не існує або відсутній product_id', p_order_id;
    END IF;

    -- 2. Перевіряємо наявність товару на складі
    SELECT stock_quantity INTO v_stock_quantity
    FROM products
    WHERE product_id = v_product_id;

    IF v_stock_quantity <= 0 THEN
        RAISE EXCEPTION 'Товар % відсутній на складі', v_product_id;
    END IF;

    -- 3. Оновлюємо кількість на складі
    UPDATE products
    SET stock_quantity = stock_quantity - 1
    WHERE product_id = v_product_id;

    -- 4. Оновлюємо статус замовлення
    UPDATE orders
    SET order_status = 'Оброблено'
    WHERE order_id = p_order_id;

    -- 5. Записуємо успішну подію в лог
    INSERT INTO order_logs(order_id, log_message)
    VALUES (p_order_id, 'Замовлення успішно оброблено.');
END;
$$;

Крок 2: Логування помилок з RAISE NOTICE та RAISE EXCEPTION

Ось тут починається магія. Додаємо логування проміжних кроків, щоб зловити помилки і зрозуміти, що відбувається на кожному етапі.

Оновлений код з логуванням:

CREATE OR REPLACE FUNCTION process_order(p_order_id INTEGER)
RETURNS VOID LANGUAGE plpgsql AS $$
DECLARE
    v_product_id INTEGER;
    v_stock_quantity INTEGER;
BEGIN
    RAISE NOTICE 'Обробка замовлення %...', p_order_id;

    -- 1. Отримуємо ідентифікатор товару
    SELECT product_id INTO v_product_id
    FROM orders
    WHERE order_id = p_order_id;

    IF v_product_id IS NULL THEN
        RAISE EXCEPTION 'Замовлення % не існує або відсутній product_id', p_order_id;
    END IF;
    RAISE NOTICE 'Product ID для замовлення %: %', p_order_id, v_product_id;

    -- 2. Перевіряємо наявність товару на складі
    SELECT stock_quantity INTO v_stock_quantity
    FROM products
    WHERE product_id = v_product_id;

    IF v_stock_quantity <= 0 THEN
        RAISE EXCEPTION 'Товар % відсутній на складі', v_product_id;
    END IF;
    RAISE NOTICE 'Кількість на складі для товару %: %', v_product_id, v_stock_quantity;

    -- 3. Оновлюємо кількість на складі
    UPDATE products
    SET stock_quantity = stock_quantity - 1
    WHERE product_id = v_product_id;

    -- 4. Оновлюємо статус замовлення
    UPDATE orders
    SET order_status = 'Оброблено'
    WHERE order_id = p_order_id;

    -- 5. Логуємо успішне виконання
    INSERT INTO order_logs(order_id, log_message)
    VALUES (p_order_id, 'Замовлення успішно оброблено.');
    RAISE NOTICE 'Замовлення % успішно оброблено.', p_order_id;

EXCEPTION WHEN OTHERS THEN
    -- Логуємо помилку
    INSERT INTO order_logs(order_id, log_message)
    VALUES (p_order_id, 'Помилка: ' || SQLERRM);
    RAISE;
END;
$$;

Крок 3: Оптимізація за допомогою індексів

Якщо в базі багато товарів або замовлень, пошук потрібних рядків може стати вузьким місцем. Додаємо індекси для прискорення вибірки в процесі обробки:

-- Індекс для прискорення пошуку в таблиці orders
CREATE INDEX idx_orders_product_id ON orders(product_id);

-- Індекс для прискорення пошуку в таблиці products
CREATE INDEX idx_products_stock_quantity ON products(stock_quantity);

Крок 4: Аналіз продуктивності з EXPLAIN ANALYZE

Тепер перевіримо, наскільки швидко виконується наша функція. Для цього викликаємо її з аналізом продуктивності:

EXPLAIN ANALYZE
SELECT process_order(1);

Результат покаже, скільки часу займає кожен етап. Ми зможемо визначити, який з кроків працює повільніше за все — це допоможе нам оптимізувати процедуру далі.

Крок 5: Покращення з використанням транзакцій

Для підвищення надійності можна запакувати всю процедуру в транзакцію. Таким чином, якщо щось піде не так, всі зміни відкотяться.

BEGIN;

-- Виклик функції
SELECT process_order(1);

-- Коміт транзакції
COMMIT;

У самому коді функції можна використовувати SAVEPOINT і ROLLBACK TO SAVEPOINT для обробки часткових помилок.

Практичне завдання: обробка масових замовлень

Давай завершимо лекцію прикладом обробки одразу кількох замовлень. Створимо функцію, яка буде обробляти всі замовлення в статусі Очікує:

CREATE OR REPLACE FUNCTION process_all_orders()
RETURNS VOID LANGUAGE plpgsql AS $$
DECLARE
    v_order_id INTEGER;
BEGIN
    FOR v_order_id IN
        SELECT order_id
        FROM orders
        WHERE order_status = 'Очікує'
    LOOP
        BEGIN
            PERFORM process_order(v_order_id);
        EXCEPTION WHEN OTHERS THEN
            RAISE NOTICE 'Не вдалося обробити замовлення %: %', v_order_id, SQLERRM;
        END;
    END LOOP;
END;
$$;

При виклику цієї функції всі замовлення в статусі Очікує будуть оброблені, а будь-які помилки просто логуватимуться.

Таким чином, ми показали, як відлагоджувати та оптимізувати складні процедури, підвищуючи їхню надійність, продуктивність і читабельність. Ці знання знадобляться тобі в реальних проектах, де від якості процедур залежить успіх застосунку.