Статья основана на третьем видео из 30 тем курса SQL 2.0 — PL/pgSQL в PostgreSQL от Аристова Евгения, который является логическим продолжением курса SQL c 0. Ссылки на видео на платформах RUTUBE и VK видео.
В данной статье подробно разбирается разница между языками SQL, PL/pgSQL, PL/Python, их плюсы и минусы при программировании.
В прошлой статье мы разобрали структуру функции в PostgreSQL.
Презентация и исходники доступны по ссылке.
Язык PL/PgSQL
Несмотря на то, что мы можем писать функции на чистом SQL, его функционал довольно ограничен и в PostgreSQL был придуман язык PL/pgSQL.
Основные цели загружаемого процедурного языка:
- может выполнять сложные вычисления
- используется для создания функций и триггеров
- добавляет управляющие структуры к языку SQL
- наследует все пользовательские типы, функции и операторы
- прост в использовании
Функции PL/pgSQL могут использоваться везде, где допустимы встроенные функции. Также они могут вызываться и как источник записей в указании FROM.
В версии PostgreSQL 9 и выше, PL/pgSQL встроен по умолчанию, до этого его было необходимо устанавливать отдельно.
PL/pgSQL позволяет сгруппировать блок вычислений и последовательность запросов внутри сервера базы данных, таким образом, мы получаем силу процедурного языка и простоту использования SQL при значительной экономии накладных расходов на клиент-серверное взаимодействие.
Важные особенности:
- Расширяется функционал простого SQL
- Доступ к дополнительному, в т.ч. внешнему функционалу
- Расширенные варианты возврата значений из функций
- Промежуточные ненужные результаты не передаются между сервером и клиентом
- Есть возможность избежать многочисленных разборов одного запроса
- Медленнее простого SQL
В результате это может привести к значительному увеличению производительности по сравнению с приложением, которое не использует хранимых функций. Кроме того, PL/pgSQL позволяет использовать все типы данных, операторы и функции SQL.
Особенности синтаксиса PL/PgSQL:
CREATE FUNCTION sales(total real) RETURNS real AS $$
BEGIN
RETURN (4 статья) total * 2;
END;
$$ LANGUAGE plpgsql;Важно не путать использование BEGIN/END для группировки операторов в PL/pgSQL с одноимёнными SQL-командами для управления транзакциями (BEGIN/COMMIT/ROLLBACK).
BEGIN/END в PL/pgSQL служат только для группировки команд в блоки (6 тема курса); они не начинают и не заканчивают транзакции.
Разница SQL и PL/pgSQL.
SQL:
CREATE or REPLACE FUNCTION increment(integer) RETURNS integer
AS $$ SELECT $1 + 1; $$
LANGUAGE SQL;PL/pgSQL:
CREATE OR REPLACE FUNCTION increment(i integer) RETURNS integer AS $$
BEGIN
RETURN i + 1;
END;
$$ LANGUAGE plpgsql;В PL/pgSQL добавляются команды BEGIN/END и применяется RETURN для возвращения значения из функции.
PL/Python
PL/Python — это процедурное расширение для PostgreSQL, которое позволяет писать функции, триггеры и процедуры на языке Python. Его использование оправдано в specific scenarios, где встроенные возможности SQL и PL/pgSQL недостаточны.
Ключевые причины использования PL/Python:
1. Доступ к богатой экосистеме Python
- Библиотеки для Data Science: Интеграция с pandas, numpy, scikit-learn для сложной аналитики и ML прямо внутри БД.
- Работа с данными: Использование json, xml, yaml парсеров для обработки сложных структур.
- Сетевые возможности: Вызов API (requests), парсинг веб-страниц (BeautifulSoup), работа с сетевыми протоколами.
- Системные вызовы: Взаимодействие с ОС (например, работа с файловой системой).
2. Сложные вычисления и алгоритмы
- Машинное обучение: Обучение и применение моделей напрямую в БД (например, классификация данных в реальном времени).
- Статистический анализ: Расчёты, которые сложно реализовать на SQL (например, регрессия, кластеризация).
- Криптография: Хеширование, шифрование с использованием библиотек типа cryptography.
3. Интеграция с внешними системами
- Веб-сервисы: Автоматическая отправка данных в CRM, ERP или другие системы через API.
- Работа с очередями: Отправка задач в RabbitMQ, Kafka или Redis.
- Нотификации: Отправка email (smtplib) или сообщений в Slack/Telegram через вебхуки.
4. Преобразование данных
- Парсинг сложных форматов: Обработка нестандартных текстовых или бинарных данных.
- Конвертация данных: Например, преобразование изображений в текстовое описание с помощью OCR (Tesseract).
- Генерация контента: Создание отчетов в PDF, Excel или HTML прямо внутри БД.
5. Автоматизация административных задач
- Кастомный мониторинг: Проверка состояния БД и отправка алертов.
- Резервное копирование: Интеграция с облачными хранилищами (S3, Google Cloud Storage).
- Динамическое управление: Изменение конфигураций БД на основе условий.
Примеры использования PL/Python:
- Вызов внешнего API
CREATE FUNCTION get_currency_rate(currency VARCHAR)
RETURNS JSON
AS $$
import requests
response = requests.get(f'https://api.exchangerate.host/ latest?base={currency}')
return response.json()
$$ LANGUAGE plpython3u;- Работа с файловой системой
CREATE FUNCTION read_logs(log_path VARCHAR)
RETURNS TEXT[]
AS $$
with open(log_path, 'r') as f:
return f.readlines()
$$ LANGUAGE plpython3u;- Классификация текста с помощью ML
CREATE FUNCTION classify_text(text TEXT)
RETURNS VARCHAR
AS $$
import pickle
model = pickle.load(open('/models/text_classifier.pkl', 'rb'))
return model.predict([text])[0]
$$ LANGUAGE plpython3u;Ограничения и риски
Безопасность:
- Используйте только plpython3u (untrusted), если нет полного доверия к пользователям.
- Риск выполнения опасного кода (например, системных вызовов).
Производительность:
- Запуск Python-кода требует больше ресурсов, чем нативный SQL.
- Не подходит для высоконагруженных OLTP-операций.
Зависимости:
- Необходимость установки Python-библиотек на сервере БД.
- Совместимость версий Python и библиотек.
Переносимость:
- Привязка к PostgreSQL и конкретной версии Python.
Если сравнивать PL/Python и PL/pgSQL, то при работе непосредственно с данными, он с высокой долей вероятности будет работать медленнее.
Например цикл в 1 млн.итераций на PL/pgSQL:
DO $$
DECLARE a int; i int;
BEGIN FOR i IN 0..99999 LOOP
SELECT count(*) INTO a FROM pg_class;
END LOOP;
END;
$$ LANGUAGE plpgsql;Время выполнения около 3 секунд.
Для 1 млн. запросов: 35 секунд.
На PL/Python:
DO $$
for i in range (0,100000) :
plpy.execute('SELECT count(*) FROM pg_class')
$$ LANGUAGE plpython3u;Время выполнения около 5 секунд.
Для 1 млн. запросов: 55 секунд.
Но мы можем ускорить этот вариант, используя подготовленные запросы, чтобы не нужно было каждый раз строить план выполнения:
DO $$
h = plpy.prepare('SELECT count(*) FROM pg_class') for i in range (0,100_000): plpy.execute(h)
$$ LANGUAGE plpython3u;
Время выполнения около 4 секунд.
Для 1 млн запросов: 42 секунд.
В следующей теме будут рассмотрены варианты возврата параметров из функции.
Добавить комментарий