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

Схема - простір імен усередині бази. Таблиці 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