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

Представлення (view) - збережений запит з іменем. Його можна читати як таблицю, але дані не зберігаються: при кожному зверненні PostgreSQL виконує запит, що лежить в основі.

CREATE VIEW active_customers AS
SELECT u.id, u.email, count(o.id) AS orders_count, max(o.created_at) AS last_order_at
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.deleted_at IS NULL
GROUP BY u.id, u.email;

SELECT * FROM active_customers WHERE orders_count > 5;

Планувальник підставляє запит представлення в зовнішній запит і оптимізує їх разом - умова orders_count > 5 не означає, що спершу рахуються всі клієнти.

Навіщо представлення:

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

Звичайне проти матеріалізованого:

Оновлювані представлення: просте представлення з однієї таблиці без агрегатів PostgreSQL дозволяє оновлювати через INSERT/UPDATE/DELETE. WITH CHECK OPTION забороняє вставку рядків, які не пройдуть умову WHERE представлення.

У Laravel представлення створюють у міграції через DB::statement('CREATE VIEW ...'), а читають звичайною моделлю:

class ActiveCustomer extends Model
{
    protected $table = 'active_customers';
}

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

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

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

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

CREATE EXTENSION IF NOT EXISTS pg_trgm;
SELECT extname, extversion FROM pg_extension;    -- що вже встановлено
SELECT name, default_version FROM pg_available_extensions;   -- що доступно на сервері

Корисні розширення:

pg_stat_statements - особливий: його потрібно завантажити при старті сервера:

shared_preload_libraries = 'pg_stat_statements'

SELECT calls, round(mean_exec_time::numeric, 1) AS avg_ms, query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

У Laravel розширення вмикають у міграції:

public function up(): void
{
    DB::statement('CREATE EXTENSION IF NOT EXISTS pg_trgm');
}

Що врахувати:

• права: створювати більшість розширень може лише суперкористувач чи власник бази; користувач застосунку з мінімальними правами міграцію не виконає - розширення встановлює адміністратор;
• керовані бази (RDS, Cloud SQL, Neon, Supabase) дозволяють лише розширення зі свого списку - перевірте до того, як будувати на ньому архітектуру;
• тестова база теж має мати розширення, інакше міграції в CI впадуть;
• оновлення PostgreSQL: розширення мають бути доступні й у новій версії, а ALTER EXTENSION ... UPDATE - частина плану оновлення.

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

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

Схема - простір імен усередині бази. Таблиці billing.invoices і public.invoices - різні об'єкти. За замовчуванням усе створюється в схемі public.

CREATE SCHEMA billing;
CREATE TABLE billing.invoices (...);

search_path - список схем, у яких PostgreSQL шукає об'єкт, названий без схеми:

SHOW search_path;           -- "$user", public
SET search_path = billing, public;
SELECT * FROM invoices;     -- billing.invoices

"$user" - схема з ім'ям поточного користувача, якщо вона існує.

Навіщо схеми:

• модулі застосунку: billing, analytics, audit - окремі права й зрозуміла структура;
• мультитенантність «схема на тенанта»: однакові таблиці в tenant_42, tenant_43, перемикання через search_path. Ізоляція краща за колонку tenant_id, але тисячі схем ускладнюють міграції й навантажують каталог;
• розширення окремо від даних (CREATE EXTENSION pg_trgm SCHEMA extensions);
• права: GRANT USAGE ON SCHEMA analytics TO bi_reader - доступ до всієї групи таблиць.

Безпека - головне про search_path:

1. Підміна об'єктів. Якщо користувач може створювати об'єкти в схемі, що стоїть у search_path раніше за потрібну, він може «перехопити» ім'я таблиці чи функції. Саме тому з PostgreSQL 15 звичайні користувачі не можуть створювати об'єкти в public за замовчуванням (раніше могли всі).

2. Функції з SECURITY DEFINER виконуються з правами власника. Якщо в них не зафіксовано search_path, зловмисник створює функцію з тим самим ім'ям у своїй схемі - і вона виконується з правами власника:

CREATE FUNCTION billing.close_period() RETURNS void
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = billing, pg_temp
AS $$ ... $$;

pg_temp останнім - щоб тимчасові об'єкти сесії не могли нічого підмінити.

У Laravel:

• 'search_path' => 'public' у конфігурації з'єднання pgsql (ключ search_path);
• міграції й моделі можуть звертатися до схеми явно: protected $table = 'billing.invoices';
• PgBouncer у режимі transaction pooling: SET search_path на рівні сесії «протікає» між клієнтами, що ділять з'єднання. Безпечніше задавати search_path для ролі (ALTER ROLE app SET search_path = ...) чи використовувати повні імена.

Правило: у продакшен-базі користувач застосунку не повинен мати права CREATE у схемах, які є в search_path інших ролей.

Докладніше в документації: Схеми: шлях пошуку

Мінливість (volatility) - обіцянка, яку функція дає планувальнику про свою поведінку. Від неї залежить, які оптимізації PostgreSQL може застосувати.

Що від цього залежить:

1. Індекси за виразом дозволені лише для IMMUTABLE-функцій:

CREATE INDEX ON users (lower(email));           -- працює
CREATE INDEX ON events ((created_at::date));    -- помилка для timestamptz

ERROR: functions in index expression must be marked IMMUTABLE

Приведення timestamptz до date залежить від часового поясу сесії, тож результат не незмінний. Рішення - зафіксувати пояс: ((created_at AT TIME ZONE 'UTC')::date).

2. Обчислення один раз: STABLE чи IMMUTABLE функцію з константними аргументами в WHERE планувальник може обчислити один раз і використати індекс. VOLATILE - викликається для кожного рядка, і індекс за нею не використати.

3. Згенеровані колонки й умови партицій теж вимагають IMMUTABLE.

Пастка - неправдива позначка. Власну функцію можна оголосити IMMUTABLE, навіть якщо вона читає таблицю:

CREATE FUNCTION tax_rate(country text) RETURNS numeric
LANGUAGE sql IMMUTABLE    -- неправда: читає таблицю
AS $$ SELECT rate FROM tax_rates WHERE code = country $$;

PostgreSQL повірить. Індекс за такою функцією зберігатиме значення на момент вставки, і після зміни tax_rates індекс поверне неправильні дані - тихо, без помилок. Кешовані плани підготовлених запитів теж можуть закріпити старий результат.

Правило: позначайте функцію найсуворішою категорією, яка справді правдива. Сумніви - STABLE чи VOLATILE. Вигода від неправдивого IMMUTABLE не варта пошкоджених даних.

Пов'язане - PARALLEL SAFE: чи можна виконувати функцію в паралельних воркерах. Власні функції за замовчуванням PARALLEL UNSAFE - це вимикає паралельні плани для запитів з ними.

У Laravel такі функції й індекси за виразами створюють у міграціях через DB::statement(), а запити мають використовувати точно той самий вираз, що в індексі (whereRaw('lower(email) = ?', [...])), інакше індекс не підхопиться.

Докладніше в документації: Категорії мінливості функцій

Звичайні обмеження (NOT NULL, CHECK, UNIQUE, FOREIGN KEY) перевіряються одразу після кожного оператора. Але деякі правила за природою тимчасово порушуються посеред транзакції.

Приклад 1 - обмін позиціями. Колонка position унікальна, і треба поміняти місцями два записи:

UPDATE steps SET position = 2 WHERE id = 1;   -- конфлікт: позиція 2 вже зайнята
UPDATE steps SET position = 1 WHERE id = 2;

Відкладене обмеження перевіряється в момент коміту:

ALTER TABLE steps ADD CONSTRAINT steps_position_unique
UNIQUE (list_id, position) DEFERRABLE INITIALLY IMMEDIATE;

BEGIN;
SET CONSTRAINTS steps_position_unique DEFERRED;
UPDATE steps SET position = 2 WHERE id = 1;
UPDATE steps SET position = 1 WHERE id = 2;
COMMIT;   -- перевірка тут

DEFERRABLE можна задати для UNIQUE, PRIMARY KEY, FOREIGN KEY і EXCLUDE, але не для CHECK і NOT NULL.

Приклад 2 - правило між рядками. «Сума часток власників компанії дорівнює 100%» - CHECK бачить лише один рядок. Тут допомагає constraint-тригер: тригер AFTER, який можна відкласти до коміту:

CREATE CONSTRAINT TRIGGER shares_total_check
AFTER INSERT OR UPDATE OR DELETE ON shares
DEFERRABLE INITIALLY DEFERRED
FOR EACH ROW EXECUTE FUNCTION check_shares_total();

Функція рахує суму для компанії й кидає виняток, якщо вона не 100. Транзакція може вставити чотири рядки по 25% - перевірка відбудеться після останнього.

Приклад 3 - заборона перетину інтервалів: EXCLUDE USING gist (room_id WITH =, during WITH &&) - вбудоване рішення без тригерів.

Ризики правил у тригерах:

• конкуренція: дві паралельні транзакції можуть кожна побачити суму 75% без змін іншої й обидві закомітитися - разом порушивши правило. Потрібне блокування батьківського рядка (SELECT ... FROM companies WHERE id = ? FOR UPDATE) чи рівень SERIALIZABLE;
• продуктивність: відкладені рядкові тригери накопичуються в пам'яті до коміту - масова операція може стати дуже дорогою;
• повідомлення про помилку приходить при коміті, а не на рядку, що порушив правило, - застосунку важче показати користувачу зрозумілу причину.

Розподіл відповідальності з Laravel: валідація в застосунку дає зрозумілі повідомлення користувачу, обмеження в базі - гарантію, що некоректні дані не потраплять у таблицю навіть з іншого коду. Обидва рівні доповнюють один одного, а не замінюють.

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