В'ю, збережені процедури і тригери

Чим вʼю (view) відрізняється від таблиці?

Вʼю — це збережений під власним іменем SELECT, а не сховище даних. Ключове: вʼю не має власних даних — це формула, а не копія. Кожне звернення до вʼю змушує СУБД наново виконати прихований за нею запит по живих таблицях, тому результат завжди відбиває поточний стан даних. Таблиця ж фізично тримає рядки на диску. Практичний наслідок для QA: у вʼю немає окремого стану, який міг би «застаріти», — на відміну від матеріалізованої вʼю, з якою її часто плутають.

Навіщо взагалі потрібні вʼю?

Три причини. Перша — інкапсуляція складності: замість JOIN пʼяти таблиць застосунок і аналітики працюють з одним коротким іменем, не занурюючись у внутрішню кухню. Друга — безпека й обмеження доступу: вʼю показує урізаний набір стовпців (скажімо, ховає password_hash і salary) чи рядків, і права видають саме на неї, а не на таблицю. Третя — стабільний інтерфейс: коли схему перейменували чи розбили, достатньо підлаштувати вʼю — зовнішній контракт не змінюється. Для тестувальника особливо важлива безпекова роль: вʼю треба перевіряти не лише на те, що вона показує потрібне, а й на те, що вона не показує зайвого.

Чи пришвидшує вʼю запит?

Ні — і це поширена пастка інтуїції. Вʼю нічого не кешує й не оптимізує: під час виконання оптимізатор розгортає її визначення прямо в зовнішній запит, тож вартість лишається тією самою, що й у написаного руками JOIN. Вʼю дає зручність і безпеку, прискорення — ні. Якщо потрібно саме прискорити важкий підрахунок, це вже робота матеріалізованої вʼю, яка тримає готовий результат. Тому твердження «зробимо вʼю, щоб швидше» — червоний прапорець на код-ревʼю.

Чи можна писати (INSERT/UPDATE) у вʼю?

Залежить від того, що всередині. Якщо вʼю — простий SELECT з однієї таблиці без агрегатів, PostgreSQL вважає її оновлюваною: команди INSERT/UPDATE/DELETE вона транслює в базову таблицю. Щойно у визначенні зʼявляється JOIN, GROUP BY чи DISTINCT, прямий запис стає неможливим — таку вʼю можна зробити записуваною хіба що через INSTEAD OF-тригери, які перехоплюють операцію і самі вирішують, куди її розкласти. Для тестів це означає: перш ніж очікувати, що UPDATE view спрацює, треба знати структуру вʼю, інакше отримаєш помилку «cannot update view».

Що таке матеріалізована вʼю (materialized view) і в чому її головний підступ?

На відміну від звичайної, матеріалізована вʼю фізично зберігає результат на диску — фактично це знімок (snapshot) стану на момент останнього оновлення. Її беруть, коли дорогий запит вигідніше порахувати один раз, а далі лише читати готове. Підступ, через який QA взагалі має знати це слово: вона відстає від життя. У PostgreSQL зміни базових таблиць самі в неї не потрапляють — потрібен явний REFRESH, запущений руками чи планувальником. Поки рефреш не відбувся, вʼю показує стару картину, і це не баг, а її природа.

Автотест створює замовлення через API, одразу відкриває дашборд і бачить стару виручку. Це баг підрахунку?

Найімовірніше — ні. Якщо за дашбордом стоїть матеріалізована вʼю, вона просто ще не рефрешнулась: підрахунок правильний, дані застарілі за побудовою. Відрізнити це від справжнього дефекту можна лише знаючи, що за цифрою стоїть знімок, а не жива таблиця. Втім, буває і реальний баг: упав джоб рефрешу — і цифри застигають на дні чи тижні для всіх користувачів. Тому правильна діагностика — перевірити, коли востаннє відпрацював REFRESH, а не одразу заводити «неправильний підрахунок». У тесті надійніше або викликати рефреш явно, або асертити дані на рівні базових таблиць.

Чим поведінка матеріалізованих вʼю відрізняється між СУБД?

Суттєво, і це варто памʼятати, щоб не переносити звички з однієї бази на іншу. У PostgreSQL матеріалізована вʼю оновлюється лише явним REFRESH (вручну або за розкладом). Oracle уміє робити це автоматично — наприклад, у режимі ON COMMIT, одразу після коміту в базові таблиці. У MySQL такої фічі немає зовсім: команди відтворюють її звичайною таблицею, актуальність якої підтримують тригерами. Тому припущення «матеріалізована вʼю сама оновиться» небезпечне: завжди уточнюй стратегію рефрешу на конкретному проєкті.

Чим функція (function) відрізняється від збереженої процедури (stored procedure)?

Функція повертає значення і вбудовується прямо в запит — у SELECT, WHERE, як стовпець; історично вона транзакцією не керує. Процедуру запускають окремою командою CALL; вона існує заради дій — записів у кілька таблиць — і їй дозволено те, чого функції ні: власні COMMIT/ROLLBACK посеред виконання. Простими словами: функцію ти вбудовуєш у запит заради результату, процедуру викликаєш заради дії. У PostgreSQL процедури зʼявилися лише у версії 11 — до того всю процедурну логіку писали функціями. Для тестів різниця в тому, як запускати: функцію — через SELECT my_func(...), процедуру — через CALL my_proc(...).

Навіщо тягнути бізнес-логіку в базу, а не тримати в застосунку? Які тредофи?

Аргументи «за» реальні: швидкодія на великих обсягах (мільйон рядків обробляється там, де вони лежать, без ганяння даних мережею туди-сюди), атомарність (зміну кількох таблиць природно загорнути в одну транзакцію) і єдине джерело правди (коли до однієї бази ходять кілька застосунків, зашите в БД правило спрацьовує однаково для кожного). Платити доводиться тестопридатністю: код у базі незручно версіонувати в git, складно покривати юніт-тестами й дебажити, до того ж він замикає на конкретний діалект (PL/pgSQL, T-SQL). На співбесіді правильної відповіді «де краще» немає — інтервʼюер дивиться на розуміння тредофів, а для QA головний наслідок один: частину бізнес-логіки в коді застосунку просто не видно.

Логіки знижки немає в коді застосунку. Чи означає це, що фічі немає?

Ні. Податок, бонуси чи перевірка ліміту цілком можуть рахуватися функцією або процедурою всередині бази, а не кодом сервісу. Порожній результат пошуку в репозиторії — ще не доказ відсутності фічі: спершу подивись у БД, чи не рахує це збережена функція, тригер або агрегат. Це прямий практичний навик: до того як заводити баг «застосунок рахує неправильно», зʼясуй, який шар насправді відповідає за підрахунок. Якщо цифру дає збережена функція — і баг, і тест мусять цілитися в базу, а не в код застосунку.

Що таке тригер (trigger) і чому кажуть, що «дані змінюються самі»?

Тригер — це код, що висить на таблиці й запускається самою СУБД, щойно з даними стається подія: INSERT, UPDATE чи DELETE. Явно його ніхто не викликає — він стартує сам у момент події. Звідси й відчуття, що «дані змінилися самі»: ти зробив один INSERT, а десь поруч змінився лічильник, до якого ти не торкався. Для QA це джерело найпідступніших фантомних багів: перевірка, що не знає про тригер, приймає «зайві» зміни за брудні дані чи гонку, хоча це його штатна робота.

BEFORE vs AFTER тригер — коли який?

BEFORE спрацьовує ще до того, як рядок ліг у таблицю, тому може його відредагувати (проставити created_at, привести email до нижнього регістру), перевірити чи взагалі відхилити операцію. AFTER запускається, коли зміну вже застосовано, — правити рядок пізно, тож його ставлять на побічні ефекти: аудит-лог, перерахунок агрегату в сусідній таблиці, каскад у повʼязані сутності. Просте правило: треба змінити чи провалідувати сам рядок — це BEFORE; треба зробити щось стороннє після факту — це AFTER. На співбесіді це майже гарантоване питання, і відповідь «BEFORE для зміни/валідації рядка, AFTER для побічних ефектів» закриває його.

Що таке NEW і OLD усередині тригера?

Це спеціальні записи, доступні в тілі рядкового тригера, які дають доступ до даних зачепленого рядка. NEW — новий рядок (той, що вставляється або яким стане після UPDATE); OLD — старий рядок (яким він був до UPDATE чи DELETE). У BEFORE-тригері можна змінювати поля NEW — наприклад, NEW.email = lower(NEW.email), — і саме це значення піде в таблицю. При INSERT доступний лише NEW, при DELETE — лише OLD, при UPDATE — обидва. Розуміння цих записів пояснює, звідки в таблиці беруться значення, яких тест не писав: їх підставив BEFORE-тригер через NEW.

FOR EACH ROW vs FOR EACH STATEMENT — у чому різниця?

Це рівень спрацювання тригера. FOR EACH ROW спрацьовує на кожен зачеплений рядок: якщо UPDATE змінив 1000 рядків, тіло тригера виконається 1000 разів. FOR EACH STATEMENT виконується рівно один раз на команду, незалежно від того, скільки рядків вона зачепила — хоч нуль. Рядковий рівень потрібен, коли логіка залежить від конкретного рядка (NEW/OLD доступні саме тут); табличний — коли достатньо одного дотику на команду, наприклад записати «таблицю змінили» в аудит. Для тестів це має практичний бік: масовий UPDATE з важким FOR EACH ROW-тригером може бути неочікувано повільним, бо тіло виконується на кожен рядок.

Ти зробив один INSERT, а змінилося дві таблиці. Що сталося і чи це баг?

Це типова робота тригерів, а не баг. Найімовірніше спрацював ланцюжок: BEFORE-тригер доклав у рядок поля, яких у твоєму запиті не було (created_at, нормалізований email), а AFTER-тригер оновив іншу таблицю — наприклад, підняв лічильник замовлень у клієнта. Перевірка, що не знає про тригери, бачить тут брудні дані або гонку, хоча все штатно. Гірше, що ефекти вміють ланцюжитися: зміна таблиці A смикає її тригер, той пише в таблицю B, де висить власний тригер, — і простежити всі наслідки одного запису буває непросто. Практичний висновок: прибирання за тестом теж має враховувати, що один INSERT міг зачепити кілька таблиць.

Як протестувати збережену функцію або процедуру?

Прямим викликом — фактично юніт-тест, що виконується всередині бази. Готуєш вхідні дані, викликаєш (SELECT my_func(...) для функції, CALL my_proc(...) для процедури), звіряєш вихід або побічний ефект з очікуваним. Крізь UI цього робити не варто: там видно лише фінальну цифру без розуміння, який шар її сформував. У Playwright це роблять через клієнт до БД (наприклад, pg): підготував замовлення з відомими позиціями, викликав order_total, перевірив суму. Обовʼязково покрий межові випадки — порожній вхід, NULL, некоректні дані, — бо саме там логіка в БД найчастіше поводиться інакше, ніж очікує застосунок.

Як протестувати тригер?

Запускаєш подію, на яку він підписаний (INSERT/UPDATE/DELETE), і асертиш наслідок: новий рядок в аудит-лозі, збільшений лічильник, автоматично заповнене поле. Тобто тестуєш не сам тригер безпосередньо, а його ефект. Обовʼязково перевір і негативний бік: якщо BEFORE-тригер має відхиляти невалідний рядок, переконайся, що операція справді падає з очікуваною помилкою. Памʼятай про каскади: одна дія може дати кілька ефектів у різних таблицях, і повна перевірка враховує всі. І тримай у голові ізоляцію — тригер міг проставити поля, які ламають наївне порівняння «записав ≠ лежить у таблиці».

Автотест вставляє рядок і звіряє його повністю, але падає на полі created_at, якого не писав. Чому і як правильно?

Тригери й дефолти роблять розрив «що я записав» ≠ «що лежить у таблиці» нормою: created_at проставив BEFORE-тригер або DEFAULT, і повне порівняння вставленого обʼєкта з рядком у базі впало саме на ньому. Це не брудні дані — це штатна поведінка. Правильний підхід — асертити тільки поля, якими керує сам тест, а згенеровані базою враховувати окремо (наприклад, перевірити, що created_at не порожній і близький до «зараз», а не дорівнює конкретному літералу). Наївний асерт «весь обʼєкт дорівнює очікуваному» тут завжди крихкий, і його треба будувати з поправкою на те, що частину полів наповнює база.

Логіку краще тримати в базі чи в застосунку — що відповісти на співбесіді?

Правильної відповіді немає, і інтервʼюер це знає — він дивиться на аргументацію тредофів. Логіка в БД виграє у швидкодії на великих даних, атомарності та ролі єдиного джерела правди для всіх клієнтів; логіка в застосунку — у звичних юніт-тестах, git-версіонуванні, портативності між СУБД і видимості в репозиторії. Мейнстрім сьогодні схиляється до «тонкої» бази, але в легасі й у важких за даними операціях логіку свідомо лишають у БД. Сильна відповідь від QA додає головне: для тесту важливо не обрати табір, а зʼясувати фактичне місце логіки — саме воно визначає, який шар покривати тестами й куди заводити баг.

Три кейси, де логіка ховається в базі, а не в застосунку: прямий тест збереженої функції, перевірка тригера з його побічним ефектом і негативним боком, і діагностика «стара цифра на дашборді» — баг підрахунку чи застаріла матеріалізована вʼю. Скрізь — що дивитися і чому.

Кейс 1. Тест збереженої функції прямим викликом

Розрахунок суми замовлення живе не в коді сервісу, а у функції order_total всередині бази. Тестувати його крізь UI — марно: там видно лише підсумкову цифру на екрані, і незрозуміло, хто її дав. Правильно викликати функцію прямо й звірити результат на відомих даних — це юніт-тест, виконаний у базі.

-- Функція повертає значення і вживається прямо в запиті
CREATE FUNCTION order_total(p_order_id int) RETURNS numeric AS $$
  SELECT sum(price * qty) FROM order_items WHERE order_id = p_order_id;
$$ LANGUAGE sql;

import { test, expect } from '@playwright/test';
import { Client } from 'pg';

test('order_total рахує суму позицій замовлення', async () => {
  const db = new Client({ connectionString: process.env.DB_URL });
  await db.connect();
  // готуємо відоме замовлення: 2 x 50.00 + 1 x 50.00 = 150.00
  await db.query(`INSERT INTO orders(id) VALUES (42)`);
  await db.query(`INSERT INTO order_items(order_id, price, qty)
                  VALUES (42, 50.00, 2), (42, 50.00, 1)`);

  const { rows } = await db.query('SELECT order_total($1) AS total', [42]);
  expect(Number(rows[0].total)).toBe(150);

  // межовий випадок: замовлення без позицій -> sum() дає NULL, не 0
  const { rows: empty } = await db.query('SELECT order_total($1) AS total', [999]);
  expect(empty[0].total).toBeNull();

  await db.end();
});

Що дивитися і чому:

• Функцію викликають через SELECT func(...), процедуру — через CALL proc(...). Це не деталь синтаксису: функція повертає значення й вбудовується в запит, процедура викликається заради побічного ефекту й може керувати транзакцією. Плутанина в способі виклику — типова помилка новачка в тесті БД.
• Межовий випадок часто і є баг. sum() над порожнім набором повертає NULL, а не 0 — якщо застосунок очікує число й підставляє NULL у розрахунок знижки, він тихо зламається. Такі кейси видно лише прямим тестом функції, крізь UI вони маскуються.
• Логіка в БД невидима в репозиторії застосунку. Не знайшовши розрахунку в коді сервісу, не пиши «фічі немає» — спершу перевір, чи його не рахує збережена функція чи агрегат. Баг і тест мають бути націлені на той шар, де логіка живе фактично.

Кейс 2. Тест тригера: побічний ефект, негативний бік і пастка ізоляції

Один INSERT у orders тягне за собою більше, ніж видно: BEFORE-тригер проставляє created_at, а AFTER-тригер піднімає лічильник у customers. Наївна перевірка «вставив рядок — звірив рядок повністю» тут падає, і не через баг продукту.

-- AFTER-тригер: після вставки замовлення підняти лічильник у клієнта
CREATE TRIGGER trg_bump_orders_count
AFTER INSERT ON orders
FOR EACH ROW
EXECUTE FUNCTION bump_orders_count();

import { test, expect } from '@playwright/test';
import { Client } from 'pg';

test('INSERT замовлення піднімає orders_count у клієнта (AFTER-тригер)', async () => {
  const db = new Client({ connectionString: process.env.DB_URL });
  await db.connect();

  const before = await db.query(
    'SELECT orders_count FROM customers WHERE id = $1', [7]);

  await db.query(
    `INSERT INTO orders(customer_id, total) VALUES ($1, $2)`, [7, 100]);

  // асертимо ПОБІЧНИЙ ЕФЕКТ в іншій таблиці, а не сам вставлений рядок
  const after = await db.query(
    'SELECT orders_count FROM customers WHERE id = $1', [7]);
  expect(after.rows[0].orders_count).toBe(before.rows[0].orders_count + 1);

  await db.end();
});

Обовʼязково перевіряємо і негативний бік — BEFORE-тригер, що має відхиляти невалідний рядок, справді його відхиляє:

test('BEFORE-тригер відхиляє замовлення з відʼємною сумою', async () => {
  const db = new Client({ connectionString: process.env.DB_URL });
  await db.connect();
  await expect(
    db.query(`INSERT INTO orders(customer_id, total) VALUES ($1, $2)`, [7, -5])
  ).rejects.toThrow(); // тригер має скасувати операцію
  await db.end();
});

Що дивитися і чому:

• Тестуємо наслідок, а не сам тригер. Тригер не викликають напряму — виконуєш подію (INSERT) і асертиш побічний ефект: лічильник у сусідній таблиці, рядок в аудиті, проставлене поле. Мета — переконатися, що ефект настав саме той і саме там.
• created_at ламає наївний асерт. Через BEFORE-тригер і DEFAULT правило «що я записав» ≠ «що лежить у таблиці» стає нормою. Не звіряй увесь обʼєкт з очікуваним літералом — перевіряй лише поля під контролем тесту, а згенеровані базою враховуй окремо (created_at не порожній і близький до «зараз»).
• Прибирання враховує каскад. Один INSERT зачепив дві таблиці (orders і customers), а тригери ще й каскадять далі. Teardown, що чистить лише orders, лишить «хвіст» у customers — і наступний прогін впаде на неочікуваному стані.

Кейс 3. Стара цифра на дашборді: баг підрахунку чи застаріла матеріалізована вʼю

Класична ситуація: тест створює оплату через API, одразу відкриває дашборд виручки — і бачить учорашню цифру. Спокуса завести «баг підрахунку» велика, але спершу треба зрозуміти, що стоїть за дашбордом.

CREATE MATERIALIZED VIEW sales_daily AS
SELECT date_trunc('day', created_at) AS day, sum(total) AS revenue
FROM orders
GROUP BY 1;

-- Дані НЕ оновляться самі — потрібен явний рефреш:
REFRESH MATERIALIZED VIEW sales_daily;

У тесті цифру не «ловлять наосліп», а керують рефрешем явно — інакше отримаєш плаваючий флак, що залежить від того, чи встиг спрацювати розклад:

import { test, expect } from '@playwright/test';
import { Client } from 'pg';

test('після REFRESH матеріалізована вʼю збігається з базовою таблицею', async () => {
  const db = new Client({ connectionString: process.env.DB_URL });
  await db.connect();

  await db.query(`INSERT INTO orders(total, created_at) VALUES (100, now())`);

  // без рефрешу вʼю показує старе — це НЕ баг, а її природа
  await db.query('REFRESH MATERIALIZED VIEW sales_daily');

  const view = await db.query(
    `SELECT revenue FROM sales_daily WHERE day = date_trunc('day', now())`);
  const table = await db.query(
    `SELECT sum(total) AS revenue FROM orders WHERE created_at >= date_trunc('day', now())`);
  expect(Number(view.rows[0].revenue)).toBe(Number(table.rows[0].revenue));

  await db.end();
});

Що дивитися і чому:

• Знімок ≠ жива таблиця. Матеріалізована вʼю зберігає результат на диску на момент останнього REFRESH. Між рефрешами вона за побудовою показує старе — це не дефект, поки джоб рефрешу працює за розкладом.
• Відрізнити застарілість від справжнього бага можна лише знаючи механізм. Стара цифра одразу після дії — майже завжди відкладений рефреш; застигла на дні — привід перевірити, чи не впав REFRESH. Обидва діагнози починаються з питання «за дашбордом жива таблиця чи знімок?».
• REFRESH ... CONCURRENTLY не блокує читання, але вимагає унікального індексу. Якщо в тесті рефреш конкурує з іншими читачами, звичайний REFRESH бере блокування; конкурентний варіант його знімає ціною вимоги унікального індексу на вʼю — це варто знати, коли рефреш у тесті раптом «завис».

Вʼю і матеріалізована вʼю

• Розумію, що вʼю (view) — це іменований SELECT без власних даних (не копія, не кеш), тож завжди повертає актуальний зріз; знаю три ролі: інкапсуляція складного JOIN, безпека (лише частина стовпців/рядків, права на вʼю) і стабільний зовнішній інтерфейс.
• Знаю пастку: вʼю сама собою нічого не пришвидшує — оптимізатор підставляє її визначення, і важка вʼю рахується так само важко, як руками написаний JOIN.
• Розумію оновлюваність: проста вʼю над однією таблицею в PostgreSQL приймає INSERT/UPDATE, а вʼю з JOIN/GROUP BY/DISTINCT — лише для читання без INSTEAD OF-тригерів.
• Можу пояснити матеріалізовану вʼю (materialized view): вона зберігає результат фізично на диску (знімок на момент оновлення); у PostgreSQL дані застарівають і не оновлюються самі — потрібен явний REFRESH вручну або за розкладом.
• Знаю відмінності СУБД: Oracle уміє ON COMMIT-оновлення, MySQL нативних матеріалізованих вʼю не має (імітує таблицею + тригерами); REFRESH ... CONCURRENTLY не блокує читання ціною унікального індексу на вʼю.

Функції, процедури і логіка в БД

• Можу пояснити різницю функції та процедури: функція повертає значення й вживається в запиті; процедура викликається CALL, робить побічні ефекти і може керувати транзакцією (COMMIT/ROLLBACK); у PostgreSQL процедури зʼявилися у версії 11.
• Розумію, навіщо логіку тримають у БД: швидкодія на масивах даних, атомарність зміни кількох таблиць, єдине джерело правди для всіх клієнтів.
• Знаю ціну логіки в БД: важче версіонувати в git, важче покрити юніт-тестами, важче дебажити, привʼязка до діалекту (PL/pgSQL, T-SQL).
• Памʼятаю головний наслідок для QA: бізнес-логіка (податок, бонуси, ліміт) може бути невидимою в коді застосунку — перш ніж казати «фічі немає», глянь у БД.

Тригери

• Розумію, що тригер (trigger) — код, привʼязаний до таблиці, який СУБД викликає автоматично на INSERT/UPDATE/DELETE; ніхто не викликає його явно.
• Можу пояснити BEFORE vs AFTER: BEFORE змінює/валідує сам рядок до запису, AFTER робить побічні ефекти (аудит, агрегати, каскади) після.
• Знаю записи NEW (новий рядок) і OLD (старий), коли який доступний і що правити NEW можна лише в BEFORE; розрізняю рівні FOR EACH ROW (на кожен рядок) vs FOR EACH STATEMENT (раз на команду).
• Памʼятаю, що тригери каскадять: тригер на таблиці A міняє B, у якої свій тригер, — один INSERT може зачепити кілька таблиць.

Тестування логіки в БД

• Знаю, що логіку в БД тестують на її рівні (прямий виклик), а не лише крізь UI, бо UI показує лише кінцевий результат.
• Можу протестувати функцію/процедуру: підготував вхід, викликав SELECT/CALL, звірив вихід або побічний ефект; покрив межові випадки (NULL, порожній вхід).
• Можу протестувати тригер: виконати подію і асертити побічний ефект, не забувши негативний бік (BEFORE, що має відхилити невалідний рядок, справді відхиляє); матеріалізовану вʼю тестую у звʼязці з REFRESH.
• Розумію проблему ізоляції: через тригери й дефолти «що я записав» ≠ «що лежить у таблиці», тому наївний асерт падає на згенерованих полях (created_at), а прибирання за тестом має враховувати каскад одного INSERT.

Де живе логіка

• Розумію, що вибір «логіка в БД чи в застосунку» — тредоф без єдиної правильної відповіді (швидкодія/атомарність проти тестопридатності/портативності).
• Головне для тесту — знати, де логіка лежить фактично: це визначає, який шар тестувати й куди заводити баг.
• Перш ніж заводити «застосунок рахує неправильно», зʼясовую, хто саме рахує — код застосунку, збережена функція чи агрегат у тригері.