Домашнее задание - Операторы манипулирования данными

Задание 1. Проектирование комплексного запроса UPSERT (ON CONFLICT)

Контекст предметной области: Автоматизированная система процессинга платежей принимает финансовые реестры от терминалов самообслуживания. На стороне СУБД функционирует таблица лицевых счетов клиентов:

CREATE TABLE client_bank_accounts (
  account_number VARCHAR(20) PRIMARY KEY,
  client_id INT NOT NULL,
  balance NUMERIC(12, 2) NOT NULL DEFAULT 0.00,
  transactions_count INT NOT NULL DEFAULT 0,
  last_activity_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Техническое задание: Составьте единый атомарный SQL-запрос INSERT INTO ... ON CONFLICT, который регистрирует поступление платежа на счет '40817810000012345678' клиента id = 1054 на сумму 4 500.00 рублей:

1. Если записи с таким account_number еще нет в базе — строка должна быть вставлена с начальным балансом 4 500.00 руб. и transactions_count = 1.
2. Если запись с таким номером уже существует — запрос должен обновить строку: прибавить сумму поступившего платежа к текущему балансу с помощью псевдотаблицы EXCLUDED, увеличить transactions_count на 1 и актуализировать last_activity_at текущей меткой времени CURRENT_TIMESTAMP.
3. Запрос обязан использовать секцию RETURNING для возврата результирующих значений колонок account_number, balance, transactions_count.

Задание 2. Массовое обновление с соединениями и RETURNING

Контекст предметной области: В клинике необходимо провести массовую актуализацию статусов медицинских карт. Имеется таблица приемов doctor_appointments и таблица страховых полисов patient_insurances:

-- Таблица приемов
CREATE TABLE doctor_appointments (
  appointment_id SERIAL PRIMARY KEY,
  patient_id INT NOT NULL,
  doctor_id INT NOT NULL,
  cost NUMERIC(8, 2) NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'PENDING_APPROVAL',
  updated_at TIMESTAMP
);

-- Таблица полисов ДМС
CREATE TABLE patient_insurances (
  patient_id INT PRIMARY KEY,
  is_vip BOOLEAN NOT NULL DEFAULT FALSE,
  expires_at DATE NOT NULL
);

Техническое задание: Напишите запрос UPDATE с конструкцией FROM, который для всех приемов в статусе 'PENDING_APPROVAL' пациентов, имеющих действующий VIP-полис ДМС (is_vip = TRUE и expires_at >= CURRENT_DATE):

1. Устанавливает статус 'APPROVED_BY_INSURANCE'.
2. Предоставляет 10% скидку на прием (cost = cost * 0.90).
3. Записывает текущее время в поле updated_at.
4. Возвращает через RETURNING список измененных appointment_id, patient_id, новую стоимость cost и новый статус status.

Задание 3. Сравнительный инженерный анализ: DELETE vs TRUNCATE

В системе логирования веб-сервиса накопилось 25 миллионов записей в таблице http_access_logs. Разработчик предлагает ночью выполнить DELETE FROM http_access_logs; для очистки таблицы. Старший администратор БД категорически запретил это и потребовал выполнить TRUNCATE TABLE http_access_logs RESTART IDENTITY;.

Вопрос: Дайте подробное инженерное обоснование позиции администратора БД. Раскройте: 1) Уровни блокировок; 2) Объем генерируемого WAL-журнала; 3) Физическое освобождение дискового пространства; 4) Поведение B-Tree индексов и счетчика первичного ключа.