Часть №1: Транзакции и блокировки в PostgreSQL

Цель: изучить механизмы управления транзакциями в PostgreSQL, научиться выполнять операции BEGIN, COMMIT, ROLLBACK, работать с точками сохранения (SAVEPOINT), а также применять табличные блокировки (LOCK TABLE).

Задание

1. Создание тестовой таблицы. Создайте таблицу accounts со столбцами id (тип SERIAL – первичный ключ), name (TEXT) и balance (DECIMAL). Например:

• CREATE TABLE accounts (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  balance DECIMAL NOT NULL
  );

• Добавьте несколько записей:

• INSERT INTO accounts (name, balance) VALUES
  ('Alice', 1000),
  ('Bob', 500),
  ('Carol', 2000);

• Пояснение: такая таблица смоделирует банковские счета клиентов.

1. Транзакции: COMMIT и ROLLBACK. Выполните следующие шаги:

2. Запустите новую транзакцию командой BEGIN;.

3. Обновите баланс, имитируя перевод денег: отнимите 200 у Alice и прибавьте 200 Bob:

• UPDATE accounts SET balance = balance - 200 WHERE name = 'Alice';
  UPDATE accounts SET balance = balance + 200 WHERE name = 'Bob';

1. Выполните ROLLBACK;. Проверьте таблицу accounts: изменения должны быть отменены (балансы вернулись к исходным).

2. Снова начните транзакцию (BEGIN;), повторите обновления и сделайте COMMIT;. После этого проверьте, что изменения сохранены в таблице (200 списаны у Alice и добавлены Bob).

Пояснение: BEGIN инициирует транзакционный блок, в котором все действия выполняются как единое целое[1]. COMMIT фиксирует изменения и делает их видимыми другим сессиям[2], а ROLLBACK отменяет их, возвращая состояние до BEGIN[3].

1. Точки сохранения (SAVEPOINT). Внутри одной транзакции выполните:

2. BEGIN;

3. Установите точку сохранения: SAVEPOINT sp1;

4. Выполните несколько изменений, например добавить новые записи:

• INSERT INTO accounts (name, balance) VALUES ('Dave', 300);
  UPDATE accounts SET balance = balance + 50 WHERE name = 'Bob';

1. Выполните ROLLBACK TO SAVEPOINT sp1; — это отменит все изменения после sp1.

2. После этого вставьте другую запись и сделайте COMMIT;.

3. Проверьте, какие изменения сохранились в таблице: та запись, которая была добавлена после sp1 (например, Dave) не появится[4], а та, что была добавлена после отката до savepoint — появится.

Пояснение: SAVEPOINT устанавливает метку внутри транзакции. Откат к ней отменяет все команды, выполненные после неё, но не прерывает транзакцию целиком[4]. Это позволяет частично отменять изменения.

1. Явная блокировка таблицы. В пределах одной транзакции выполните команду блокировки:

• BEGIN;
  LOCK TABLE accounts IN EXCLUSIVE MODE;

• Пояснение: LOCK TABLE приобретает блокировку на таблицу (по умолчанию ACCESS EXCLUSIVE), которая удерживается до конца транзакции[5]. Такая блокировка помешает другим сессиям одновременно изменять таблицу. После выполнения COMMIT или ROLLBACK блокировка снимается автоматически.

1. Проверка поведения при блокировке. В том же сеансе после LOCK TABLE попробуйте выполнить операцию над таблицей, которая обычно требует блокировки (например, UPDATE accounts SET balance = balance + 1). Вы увидите, что текущая транзакция продолжит работать нормально, поскольку собственные блокировки не конфликтуют[6]. Блокировка нужна для ситуации конкурирующих сессий, что выходит за рамки одной сессии-ограничения песочниц.

Ожидаемый результат

• После шагов из пункта 2:

• При ROLLBACK никаких изменений в таблице не видно (значения балансов как изначально).

• При COMMIT баланс Alice уменьшится на 200, у Bob увеличится на 200, что отражается в результирующем наборе SELECT * FROM accounts.

• После использования SAVEPOINT (п. 3): запись с Dave не появится (откат), а изменение Bob после отката не учтётся.

• После LOCK TABLE (п. 4) сама команда выполнится успешно; последующее изменение, выполненное той же транзакцией, будет применено только после COMMIT. Другие сессии при попытке изменения блокируются (это можно описать, если бы имелся второй сеанс).

• В целом демонстрация должно показать принципы атомарности и изолированности транзакций[1][2], а также работу точек сохранения[4] и табличных блокировок[5].

Вопросы и задачи для самопроверки

• Чем отличается поведение COMMIT и ROLLBACK? (См. определение: COMMIT делает все изменения постоянными и видимыми другим[2], а ROLLBACK отменяет все изменения текущей транзакции[3].)

• Какова цель установки SAVEPOINT в транзакции? При каких сценариях это может быть полезно[4]?

• Что происходит с блокировкой таблицы после выполнения COMMIT или ROLLBACK? Почему в песочнице одна сессия не увидит эффект блокировки от самой себя[5]?

• Какую блокировку (lock mode) по умолчанию использует LOCK TABLE? (По умолчанию – ACCESS EXCLUSIVE, самая жесткая[7].)

Часть №2: Триггеры и аудит изменений в PostgreSQL

Цель: познакомиться с созданием триггеров на таблицу, функциями-обработчиками и реализовать простую систему аудита, фиксирующую операции INSERT, UPDATE и DELETE в отдельной таблице лога.

Задание

1. Создание основной таблицы. Создайте таблицу employees со столбцами emp_id (SERIAL, первичный ключ), name (TEXT) и salary (INTEGER):

• CREATE TABLE employees (
  emp_id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  salary INTEGER NOT NULL
  );

• Добавьте несколько записей сотрудников:

• INSERT INTO employees (name, salary) VALUES
  ('Иван Иванов', 50000),
  ('Мария Петрова', 60000);

1. Создание таблицы для лога. Создайте таблицу employees_log, которая будет хранить записи аудита. Добавьте поля: идентификатор (log_id SERIAL PRIMARY KEY), тип операции (op CHAR(1)), дату/время (stamp TIMESTAMP), имя пользователя (username TEXT), а также поля emp_id, name, salary для хранения данных строки из employees:

• CREATE TABLE employees_log (
  log_id SERIAL PRIMARY KEY,
  op CHAR(1) NOT NULL,
  stamp TIMESTAMP NOT NULL,
  username TEXT NOT NULL,
  emp_id INTEGER,
  name TEXT,
  salary INTEGER
  );

• Пояснение: столбец op может содержать 'I', 'U' или 'D' — для операций вставки (Insert), обновления (Update) или удаления (Delete) соответственно. В stamp сохраняем время операции, username — текущего пользователя (логин, current_user).

1. Создание функции-триггера. Напишите функцию на языке PL/pgSQL, которая будет срабатывать после изменения таблицы employees и добавлять соответствующую запись в employees_log. Например:

• CREATE OR REPLACE FUNCTION audit_employee_changes() RETURNS trigger AS $$
  BEGIN
  IF (TG_OP = 'INSERT') THEN
  INSERT INTO employees_log(op, stamp, username, emp_id, name, salary)
  VALUES ('I', now(), current_user, NEW.emp_id, NEW.name, NEW.salary);
  ELSIF (TG_OP = 'UPDATE') THEN
  INSERT INTO employees_log(op, stamp, username, emp_id, name, salary)
  VALUES ('U', now(), current_user, NEW.emp_id, NEW.name, NEW.salary);
  ELSIF (TG_OP = 'DELETE') THEN
  INSERT INTO employees_log(op, stamp, username, emp_id, name, salary)
  VALUES ('D', now(), current_user, OLD.emp_id, OLD.name, OLD.salary);
  END IF;
  RETURN NULL;
  END;
  $$ LANGUAGE plpgsql;

• Пояснение: в теле функции мы проверяем специальную переменную TG_OP, которая содержит тип операции ('INSERT', 'UPDATE' или 'DELETE'). В зависимости от этого вставляем в лог нужную запись[8]. Для DELETE используем данные из OLD, для INSERT и UPDATE — из NEW. Функция возвращает NULL, так как триггер – AFTER и возвращаемое значение игнорируется.

1. Создание триггера. Создайте триггер, который будет вызывать эту функцию для каждой строки при INSERT, UPDATE или DELETE в таблице employees:

• CREATE TRIGGER trg_audit_employee
  AFTER INSERT OR UPDATE OR DELETE ON employees
  FOR EACH ROW EXECUTE FUNCTION audit_employee_changes();

• Пояснение: такой триггер сработает после каждой соответствующей операции над строкой employees и запишет информацию об изменении в employees_log.

1. Проверка работы. Выполните несколько операций с таблицей employees, например:

• INSERT INTO employees (name, salary) VALUES ('Ольга Смирнова', 55000);
  UPDATE employees SET salary = salary + 5000 WHERE name = 'Иван Иванов';
  DELETE FROM employees WHERE name = 'Мария Петрова';

• Затем сделайте SELECT * FROM employees_log; — в этой таблице должны появиться строки с записями о вставке (op = 'I'), обновлении ('U') и удалении ('D'), с текущим временем и логином пользователя.

Ожидаемый результат: после выполнения операций в employees_log появятся соответствующие аудит-записи:

log_id | op | stamp | username | emp_id | name | salary
-------+----+--------------------+----------+--------+----------------+--------
1 | I | 2025-10-18 02:.. | user | 3 | Ольга Смирнова | 55000
2 | U | 2025-10-18 02:.. | user | 1 | Иван Иванов | 55000
3 | D | 2025-10-18 02:.. | user | 2 | Мария Петрова | 60000

(Порядок операций может отличаться, главное — наличие всех трёх типов.)

Ожидаемый результат

• Таблица employees. После операций останутся актуальные записи: Ольга Смирнова (добавленная) и Иван Иванов (с обновлённым salary).

• Таблица employees_log. В логе появятся строки для каждой операции ('I', 'U', 'D') с правильным значением emp_id, именем и salary из таблицы employees, временем (now()) и именем текущего пользователя. Таким образом, реализуется аудит изменений[8].

• Если выполнить дополнительные операции (например, ещё UPDATE или DELETE), для каждого действия будет новая запись в employees_log.

Вопросы и задачи для самопроверки

• Как триггер определяет тип операции (INSERT/UPDATE/DELETE) внутри функции? (С помощью переменной TG_OP. Приравнивая её к 'INSERT', 'UPDATE' или 'DELETE', мы вставляем соответствующую метку в поле op[8].)

• Почему функция возвращает NULL? (Потому что это триггер AFTER для каждой строки, и возвращаемое значение не влияет на фактическую операцию; возвращать NEW или OLD нужно только в BEFORE триггерах.)

• Как обеспечить, чтобы при каждом изменении данных автоматически заполнялся лог с пользователем и меткой времени? (Использовать CURRENT_USER и now() в функции-триггере, как показано выше.)

• Дополнительная задача: измените функцию-триггер так, чтобы она записывала в лог старые и новые значения salary при обновлении (например, в двух полях old_salary и new_salary), или чтобы при удалении в поле name фиксировалось значение из OLD. (Это можно реализовать, добавив соответствующие столбцы в employees_log и изменив INSERT в зависимости от TG_OP.)

Литература и источники

• Документация PostgreSQL: блоки транзакций BEGIN/COMMIT/ROLLBACK[1][2]; использование точек сохранения SAVEPOINT[4]; команда LOCK TABLE[5].

• PostgreSQL Tutorial: примеры создания аудиторного триггера, запись операций в лог[8].

[1] PostgreSQL: Documentation: 18: BEGIN

https://www.postgresql.org/docs/current/sql-begin.html

[2] PostgreSQL: Documentation: 18: COMMIT

https://www.postgresql.org/docs/current/sql-commit.html

[3] PostgreSQL: Documentation: 9.4: ROLLBACK

https://www.postgresql.org/docs/9.4/sql-rollback.html

[4] PostgreSQL: Documentation: 18: SAVEPOINT

https://www.postgresql.org/docs/current/sql-savepoint.html

[5] [6] [7] PostgreSQL: Documentation: 18: LOCK

https://www.postgresql.org/docs/current/sql-lock.html

[8] PostgreSQL: Documentation: 18: 41.10. Trigger Functions

https://www.postgresql.org/docs/current/plpgsql-trigger.html

Полезные ссылки: https://webprogr.ru/abd3.docx