Middle: питання на співбесіді з теми «Функції, тригери й розширення»

PL/pgSQL - процедурна мова PostgreSQL: змінні, умови, цикли, обробка винятків поверх SQL.

CREATE FUNCTION transfer(from_id bigint, to_id bigint, amount numeric)
RETURNS void
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE accounts SET balance = balance - amount
    WHERE id = from_id AND balance >= amount;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Insufficient funds on account %', from_id
            USING ERRCODE = 'check_violation';
    END IF;

    UPDATE accounts SET balance = balance + amount WHERE id = to_id;
END;
$$;

SELECT transfer(1, 2, 500);

Функція чи процедура:

Прості функції - на SQL, а не PL/pgSQL: LANGUAGE sql функцію планувальник може вбудувати в запит, а тіло PL/pgSQL для нього - «чорна скринька».

Коли логіка в базі виправдана:

• інваріанти даних, які мають виконуватися незалежно від того, хто пише в базу (застосунок, скрипт, адмін через psql): тригери, обмеження;
• обробка великих обсягів даних без передачі їх у застосунок і назад;
• атомарні операції, які інакше потребували б кількох запитів з блокуваннями.

Коли - ні (і це більшість бізнес-логіки):

• тестування й налагодження складніші: немає звичних інструментів, покриття, IDE;
• версіонування й деплой: код функції живе в міграціях, рев'ю й відкат незручні;
• масштабування: база - найважче для масштабування місце, а обчислення в ній додають навантаження;
• розподіл знань: команда пише на PHP, а критичний код - на іншій мові.

У Laravel: функції створюють у міграціях (DB::unprepared() для тіла з $$), викликають через DB::select('SELECT transfer(?, ?, ?)', [...]). Винятки з RAISE EXCEPTION приходять як QueryException з кодом SQLSTATE, який можна перевірити.

Безпека: динамічний SQL усередині функції (EXECUTE) - лише з format('%I', ...) для ідентифікаторів і USING для значень, інакше SQL-ін'єкція переїжджає з PHP у базу.

Докладніше в документації: PL/pgSQL

Тригер - функція, яку PostgreSQL викликає автоматично при INSERT, UPDATE, DELETE чи TRUNCATE.

CREATE FUNCTION set_updated_at() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
    NEW.updated_at := now();
    RETURN NEW;
END;
$$;

CREATE TRIGGER products_updated_at
BEFORE UPDATE ON products
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();

Варіанти тригерів:

Типові застосування:

• журнал аудиту змін (хто, що, коли - навіть при змінах поза застосунком);
• денормалізовані лічильники й агрегати;
• підтримка tsvector для повнотекстового пошуку;
• перевірки, які не виразити обмеженням CHECK.

Підводні камені:

• невидима логіка: розробник бачить UPDATE products SET price = ... і не знає, що змінюються ще три таблиці. Тригери мають бути задокументовані й мати зрозумілі імена;
• продуктивність: рядковий тригер виконується для кожного рядка - масовий UPDATE мільйона рядків стає мільйоном викликів функції. Для масових змін - тригери рівня оператора з таблицями переходів;
• каскади й рекурсія: тригер оновлює таблицю, на якій теж є тригер, - ланцюжки важко відстежити, а рекурсію - зупинити (pg_trigger_depth());
• блокування й взаємоблокування: тригер, що оновлює рядок-лічильник, серіалізує всі паралельні вставки на цьому рядку;
• тести: у тестах Laravel з SQLite тригерів PostgreSQL немає - тести мають працювати на тій самій СУБД, що й продакшен.

Тригер чи подія Eloquent:

• тригер спрацьовує завжди, навіть при масовому update(), сирому SQL чи зміні з іншого сервісу;
• подія Eloquent - лише при збереженні моделі, зате вона в PHP-коді, тестується звичайно й може ставити завдання в чергу.

Правило: інваріанти даних - у базі, побічні ефекти (листи, кеш, черги) - у застосунку.

Докладніше в документації: PL/pgSQL: тригерні функції

LISTEN / NOTIFY - вбудований у PostgreSQL механізм публікації й підписки. Одне з'єднання підписується на канал, інше надсилає в нього повідомлення:

-- з'єднання A
LISTEN order_events;

-- з'єднання B
NOTIFY order_events, '{"order_id": 42, "status": "paid"}';
-- або функцією, зручно в тригерах
SELECT pg_notify('order_events', json_build_object('order_id', 42)::text);

Підписник отримує повідомлення асинхронно - без опитування таблиці.

Ключові властивості:

• транзакційність: повідомлення доставляються лише після коміту транзакції, що їх надіслала. Відкат - повідомлень немає. Це головна перевага перед відправкою подій із застосунку;
• дедуплікація: однакові повідомлення в одній транзакції об'єднуються в одне;
• без збереження: якщо в момент NOTIFY ніхто не слухає, повідомлення зникає. Підписник, що перепідключився, пропущених повідомлень не отримає;
• розмір: до 8000 байтів за замовчуванням - передають ідентифікатор, а не дані;
• черга повідомлень на сервері обмежена (8 ГБ за замовчуванням): повільний підписник, що не читає повідомлення, врешті блокує NOTIFY для всіх.

Типове застосування - тригер + NOTIFY:

CREATE FUNCTION notify_order_change() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
    PERFORM pg_notify('order_events', NEW.id::text);
    RETURN NEW;
END;
$$;

Сервіс-підписник отримує id, читає актуальні дані й інвалідує кеш, оновлює пошуковий індекс чи відправляє подію в WebSocket.

Обмеження для веб-застосунку:

• PHP-FPM не тримає довгих з'єднань - слухати потрібно окремим довгоживучим процесом (консольна команда під Supervisor), що читає повідомлення через pgsqlGetNotify у PDO;
• PgBouncer у режимі transaction pooling не підтримує LISTEN - підписнику потрібне пряме з'єднання з базою;
• гарантій доставки немає: для подій, які не можна втратити, - transactional outbox (таблиця подій), а NOTIFY лише як сигнал «перевір таблицю», щоб не опитувати її щосекунди.

Порівняно з Redis pub/sub і Reverb: NOTIFY не потребує додаткової інфраструктури й знає про транзакції, але не масштабується на тисячі підписників і не призначений для доставки клієнтам напряму. Схема на практиці: база → NOTIFY → процес-слухач → broadcasting через Reverb до браузерів.

Докладніше в документації: NOTIFY