Розбір типових помилок при відладці та оптимізації PL/pgSQL

Сьогодні, щоб завершити цю епічну подорож по PL/pgSQL, ми розберемо найпоширеніші помилки, які можуть тебе підстерігати під час відладки та оптимізації функцій і процедур. Знання цих помилок допоможе не тільки уникати проблем у майбутньому, а й ефективніше розбиратися з багами, якщо вони все ж таки з’являться.

Типові помилки при відладці та оптимізації

1. Неправильне використання змінних

Одна з найчастіших помилок при написанні та відладці функцій у PL/pgSQL — це неправильне оголошення або використання змінних. Наприклад, якщо ти забув явно вказати тип змінної або заплутався у значеннях, переданих через параметри. Давай подивимось, як це може виглядати на практиці:

CREATE OR REPLACE FUNCTION calculate_discount(order_total NUMERIC)
RETURNS NUMERIC AS $$
DECLARE
    discount_rate NUMERIC;
BEGIN
    -- Ой! Забув проініціалізувати змінну discount_rate
    RETURN order_total * discount_rate;
END;
$$ LANGUAGE plpgsql;

При виклику цієї функції ти отримаєш помилку, пов’язану з використанням NULL у обчисленнях, бо змінна discount_rate спочатку не ініціалізована.

Як уникнути:

1. Завжди присвоюй змінним значення за замовчуванням при оголошенні:

   DECLARE
       discount_rate NUMERIC := 0.1; -- Значення за замовчуванням

2. Перевіряй використання змінних через RAISE NOTICE, щоб впевнитися, що вони містять очікувані значення:

RAISE NOTICE 'Значення discount_rate: %', discount_rate;

2. Відсутність логування помилок

Ще одна поширена проблема — відсутність механізму логування. Якщо щось іде не так, а ти не логував хід виконання своєї функції, це як шукати чорну кішку в темній кімнаті, особливо якщо ти ще не впевнений, чи є там кішка.

Ось приклад функції без логування:

CREATE OR REPLACE FUNCTION process_order(order_id INT)
RETURNS VOID AS $$
BEGIN
    -- Якась складна логіка обробки замовлення
    UPDATE orders SET status = 'processed' WHERE id = order_id;
END;
$$ LANGUAGE plpgsql;

Що, якщо order_id передається некоректний? Що, якщо запис у таблиці orders не існує?

Як уникнути: Додай RAISE NOTICE або RAISE EXCEPTION, щоб логувати критичні кроки:

CREATE OR REPLACE FUNCTION process_order(order_id INT)
RETURNS VOID AS $$
BEGIN
    -- Логуємо вхідні дані
    RAISE NOTICE 'Обробка замовлення з ID %', order_id;

    -- Складна логіка обробки
    UPDATE orders SET status = 'processed' WHERE id = order_id;

    -- Логуємо результат
    RAISE NOTICE 'Статус замовлення оновлено для ID %', order_id;
END;
$$ LANGUAGE plpgsql;

Тепер ти зможеш легко відслідкувати, де виникає помилка, завдяки виведеним повідомленням.

3. Ігнорування продуктивності запитів

Це один з головних антагоністів будь-якого розробника баз даних. Наприклад, ти пишеш функцію, яка виглядає коректно, але виконується надто повільно. А одна з головних причин повільних запитів — це відсутність індексів або неефективні плани виконання запитів.

Приклад повільного запиту:

CREATE OR REPLACE FUNCTION get_large_orders()
RETURNS TABLE(order_id INT, total NUMERIC) AS $$
BEGIN
    RETURN QUERY
    SELECT id, total FROM orders WHERE total > 1000;
END;
$$ LANGUAGE plpgsql;

Якщо поле total у таблиці orders не індексоване, запит буде сканувати всю таблицю, що дуже неефективно.

Як уникнути:

1. Використовуй EXPLAIN ANALYZE, щоб впевнитися в ефективності запитів:

EXPLAIN ANALYZE SELECT id, total FROM orders WHERE total > 1000;

2. Створюй індекси на часто використовувані стовпці:

CREATE INDEX idx_orders_total ON orders(total);

4. Використання неправильного рівня ізоляції транзакцій

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

Приклад потенційного deadlock:

BEGIN;
UPDATE orders SET status = 'processed' WHERE id = 1;

-- Очікування блокування іншої транзакції
UPDATE inventory SET stock = stock - 1 WHERE product_id = 100;
COMMIT;

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

Як уникнути:

1. Продумай порядок виконання операцій і дотримуйся його.
2. Використовуй рівень ізоляції SERIALIZABLE, якщо це потрібно.

5. Відсутність обробки помилок

Обробка помилок — це не тільки гарна практика, а й інструмент для забезпечення стабільності твого коду. Наприклад, у наступному коді відсутня обробка можливих помилок:

CREATE OR REPLACE FUNCTION add_order(order_id INT)
RETURNS VOID AS $$
BEGIN
    INSERT INTO orders (id, status) VALUES (order_id, 'new');
END;
$$ LANGUAGE plpgsql;

Якщо раптом order_id вже існує, ти отримаєш помилку duplicate key value violates unique constraint.

Як уникнути: Використовуй блоки обробки виключень:

CREATE OR REPLACE FUNCTION add_order(order_id INT)
RETURNS VOID AS $$
BEGIN
    INSERT INTO orders (id, status) VALUES (order_id, 'new');
EXCEPTION WHEN unique_violation THEN
    RAISE NOTICE 'Замовлення з ID % вже існує!', order_id;
END;
$$ LANGUAGE plpgsql;

Приклади помилок і їх виправлення

Помилка 1: Запити виконуються повільно через відсутність індексації

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

Виправлення: Створи індекс для відповідного стовпця.

Помилка 2: Логіка функції заплутана і важко відлагоджується

Ситуація: Функція містить занадто багато логіки і не розбита на підфункції.

Виправлення: Розділи складну функцію на менші підфункції. Це покращить читабельність і спростить відладку.

Помилка 3: Неправильне використання RAISE EXCEPTION

Ситуація: RAISE EXCEPTION використовується для всіх помилок, навіть для незначних.

Виправлення: Використовуй RAISE NOTICE для інформаційних повідомлень і тільки RAISE EXCEPTION для критичних випадків.

RAISE NOTICE 'Все під контролем — поточний етап функції завершено.';
RAISE EXCEPTION 'Щось зламалось! Зверни увагу на вхідні параметри.';

Рекомендації для уникнення помилок

1. Додавай логування: на критичних етапах своєї функції використовуй RAISE NOTICE для відстеження виконання.
2. Тестуй функції: регулярно використовуй тестові дані для перевірки функцій і процедур.
3. Підтримуй читабельність коду: розбивай комплексні функції на менші підфункції та процедури.
4. Аналізуй продуктивність: використовуй EXPLAIN ANALYZE, щоб впевнитися, що твої запити працюють ефективно.
5. Будь готовий до несподіванок: завжди додавай блоки обробки виключень для роботи з помилками.

EXCEPTION
    WHEN OTHERS THEN
        RAISE EXCEPTION 'Виникла непередбачена помилка: %', SQLERRM;

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