Многоэтапные процедуры — это "швейцарские ножи" для баз данных. Они часто состоят из валидации входных данных, выполнения изменений (например, обновление записей, вставка логов), а иногда ещё и из аналитики. Но вот проблема: чем сложнее процедура, тем больше вероятность ошибки. Логическая ошибка, медленный запрос, пропущенный нюанс — и всё может пойти наперекосяк.
Комплексная отладка включает следующие аспекты:
- Анализ входных данных: правильно ли настроены параметры? Верные ли данные переданы?
- Проверка выполнения ключевых этапов: все ли шаги процедуры выполняются корректно?
- Логирование промежуточных результатов: чтобы знать, что произошло до того, как что-то "взломалось".
- Оптимизация узких мест производительности: улучшаем слабые места, которые "тормозят" запросы.
Постановка задачи: пример многоэтапной процедуры
Для нашего практического примера представим, что мы работаем с базой данных интернет-магазина. Нам нужно создать процедуру для обработки заказа. Она будет выполнять следующие шаги:
- Проверять наличие товара на складе.
- Бронировать товар.
- Обновлять статус заказа.
- Записывать события (например, успешное бронирование или ошибку) в таблицу логов.
Скрипт структуры базы данных:
-- Таблица товаров
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 'Order % does not exist or missing 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 'Product % is out of stock', 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 = 'Processed'
WHERE order_id = p_order_id;
-- 5. Записываем успешное событие в лог
INSERT INTO order_logs(order_id, log_message)
VALUES (p_order_id, 'Order processed successfully.');
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 'Processing order %...', 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 'Order % does not exist or missing product_id', p_order_id;
END IF;
RAISE NOTICE 'Product ID for order %: %', 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 'Product % is out of stock', v_product_id;
END IF;
RAISE NOTICE 'Stock quantity for product %: %', 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 = 'Processed'
WHERE order_id = p_order_id;
-- 5. Логируем успешное выполнение
INSERT INTO order_logs(order_id, log_message)
VALUES (p_order_id, 'Order processed successfully.');
RAISE NOTICE 'Order % processed successfully.', p_order_id;
EXCEPTION WHEN OTHERS THEN
-- Логируем ошибку
INSERT INTO order_logs(order_id, log_message)
VALUES (p_order_id, 'Error: ' || 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 для обработки частичных ошибок.
Практическая задача: обработка массовых заказов
Давайте завершим лекцию примером обработки сразу нескольких заказов. Мы создадим функцию, которая будет обрабатывать все заказы в статусе Pending:
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 = 'Pending'
LOOP
BEGIN
PERFORM process_order(v_order_id);
EXCEPTION WHEN OTHERS THEN
RAISE NOTICE 'Failed to process order %: %', v_order_id, SQLERRM;
END;
END LOOP;
END;
$$;
При вызове этой функции все заказы в статусе Pending будут обработаны, а любые ошибки будут только логироваться.
Таким образом, мы продемонстрировали, как отлаживать и оптимизировать сложные процедуры, повышая их надёжность, производительность и читаемость. Эти знания пригодятся вам в реальных проектах, где от качества процедур зависит успех приложения.
ПЕРЕЙДИТЕ В ПОЛНУЮ ВЕРСИЮ