Создание, модификация и удаление таблиц

1. Место DDL в экосистеме SQL: Метаданные и каталог

DML: Манипулирование данными

SELECT, INSERT, UPDATE, DELETE

Работает со строками и кортежами на страницах данных.
Не изменяет схему и метаописание отношений.
Генерирует построчный журнал в WAL (Write-Ahead Log).

DDL: Определение данных

CREATE, ALTER, DROP, TRUNCATE

Модифицирует системный каталог СУБД (pg_class, pg_attribute, pg_constraint).
Управляет дисковыми файлами (аллокация и удаление relfilenode).
Требует эксклюзивных блокировок отношения.

2. Синтаксис и опции CREATE TABLE

Создание надежной реляционной таблицы требует декларативного описания первичных ключей, типов данных и значений по умолчанию:

CREATE TABLE IF NOT EXISTS warehouses (
    warehouse_id SERIAL PRIMARY KEY,
    warehouse_code VARCHAR(20) NOT NULL UNIQUE,
    warehouse_name VARCHAR(150) NOT NULL,
    total_area_sqm NUMERIC(10, 2) NOT NULL CHECK (total_area_sqm > 0),
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Идемпотентность

IF NOT EXISTS предотвращает ошибку при повторном запуске скрипта миграции.

DEFAULT-значения

Автоматически заполняют атрибут, если поле не передано в INSERT.

Именованный CHECK

Явное именование ограничений критично для последующего сопровождения схемы.

3. Временные таблицы: CREATE TEMPORARY TABLE

Изоляция и сессионный контекст

Размещаются в динамической схеме pg_temp_*.
Видны только создавшей их сессии клиента.
Автоматически уничтожаются при разрыве TCP-соединения.
Не требуют тяжелой записи в журнал WAL (прирост скорости ETL в разы!).

Управление жизненным циклом (ON COMMIT)

CREATE TEMPORARY TABLE temp_shipment (
    barcode VARCHAR(64) NOT NULL,
    quantity INT CHECK (quantity > 0)
) ON COMMIT PRESERVE ROWS;

PRESERVE ROWS — сохраняются до конца сеанса.
DROP — уничтожается при COMMIT транзакции.
DELETE ROWS — очищается при завершении транзакции.

4. Модификация структуры: Семейство ALTER TABLE

В живой базе данных пересоздание таблицы недопустимо. Структура модифицируется «на лету»:

Действие	Синтаксис SQL	Влияние на данные
ADD COLUMN	`ALTER TABLE t ADD COLUMN zone VARCHAR(20) DEFAULT 'STD';`	Мгновенное добавление за $O(1)$ в PG 11+
DROP COLUMN	`ALTER TABLE t DROP COLUMN zone CASCADE;`	Логическое скрытие, очистка при VACUUM
ALTER TYPE	`ALTER TABLE t ALTER COLUMN cost TYPE NUMERIC(10,2) USING cost::NUMERIC;`	Перезапись дисковых страниц, требует USING
RENAME	`ALTER TABLE t RENAME COLUMN old_c TO new_c;`	Обновление метаданных каталога за микросекунды
ADD CONSTRAINT	`ALTER TABLE t ADD CONSTRAINT chk_v CHECK (cost > 0);`	Валидация существующих кортежей
DROP CONSTRAINT	`ALTER TABLE t DROP CONSTRAINT chk_v;`	Снятие правила проверки целостности

5. Изменение типа данных и конструкция USING

Ошибка без явного каста

ALTER TABLE hotel_rooms 
ALTER COLUMN price TYPE NUMERIC(10, 2);

Результат:
ERROR: column "price" cannot be cast automatically to type numeric

СУБД блокирует опасные преобразования байтов без явного указания инженера!

Решение с инструкцией USING

ALTER TABLE hotel_rooms 
ALTER COLUMN price TYPE NUMERIC(10, 2) 
USING price::NUMERIC(10, 2);

Результат: Успешная конвертация каждой строки через заданное функциональное выражение или приведение типов (::).

6. Управление ограничениями: ADD и DROP CONSTRAINT

Ограничения целостности гарантируют бизнес-инварианты на уровне реляционного ядра:

Добавление ограничений

-- Проверочное ограничение CHECK
ALTER TABLE hotel_rooms 
ADD CONSTRAINT chk_hotel_rooms_positive_price 
CHECK (price_per_night > 0.00);

-- Внешний ключ FOREIGN KEY
ALTER TABLE bank_accounts 
ADD CONSTRAINT fk_bank_accounts_client 
FOREIGN KEY (client_id) 
REFERENCES bank_clients(client_id) 
ON DELETE RESTRICT;

Удаление ограничений

-- Удаление ограничения уникальности
ALTER TABLE bank_accounts 
DROP CONSTRAINT unq_account_number;

-- Удаление внешнего ключа
ALTER TABLE bank_accounts 
DROP CONSTRAINT fk_bank_accounts_client;

Совет: всегда давайте ограничениям понятные системные имена, иначе их невозможно удалить без поиска в pg_constraint!

7. Безопасное удаление DROP TABLE: CASCADE vs RESTRICT

RESTRICT (по умолчанию)

Защитный барьер

DROP TABLE bank_clients RESTRICT;

Операция прерывается с ошибкой, если на таблицу ссылаются другие объекты (таблицы, VIEW).
Гарантирует защиту от случайного разрушения связей схемы.

CASCADE (разрушительное удаление)

Тотальное снятие зависимостей

DROP TABLE transaction_audit_logs CASCADE;

Удаляет таблицу и автоматически уничтожает все зависимые внешние ключи и представления.
Внимание: данные в ссылающихся таблицах сохраняются, но таблица лишается внешнего ключа!

8. Быстрая очистка: TRUNCATE TABLE против DELETE

Критерий сравнения	TRUNCATE TABLE	DELETE FROM table
Подмножество языка	DDL (Data Definition Language)	DML (Data Manipulation Language)
Сложность выполнения	$O(1)$ — деаллокация файла кучи	$O(N)$ — построчное чтение и пометка
Объем журнала WAL	Минимальный (запись о новом relfilenode)	Колоссальный (запись каждого кортежа)
Построчные триггеры	Игнорируются (не вызываются)	Вызываются для каждой удаленной строки
Сброс автоинкремента	Поддерживает `RESTART IDENTITY`	Счетчик sequence никогда не сбрасывается
Условие WHERE	Не поддерживается (чистит всю таблицу)	Поддерживается построчный фильтр

9. Сброс автоинкремента: Опция RESTART IDENTITY

Без RESTART IDENTITY (по умолчанию)

TRUNCATE TABLE student_attendance_log;

Данные удалены, таблица пуста.
Счетчик SERIAL сохраняет последнее значение.
Следующая вставка INSERT получит ID = 10001, а не 1.

С RESTART IDENTITY

TRUNCATE TABLE student_attendance_log 
RESTART IDENTITY;

Данные удалены за 4 миллисекунды.
Связанная последовательность (sequence) сброшена в 1.
Следующая строка гарантированно получит ID = 1!

10. Блокировки DDL в Production: AccessExclusiveLock

Большинство операций ALTER TABLE и DROP TABLE захватывают самую жесткую блокировку в СУБД:

Механика возникновения аварии (Outage)

Команда DDL запрашивает AccessExclusiveLock.
СУБД ждет завершения ВСЕХ активных чтений (даже фонового отчета на 30 секунд).
Все последующие запросы к таблице (даже простые SELECT) блокируются в очереди ЗА DDL!
Пул соединений переполняется, сервер перестает отвечать, клиенты получают ошибку 504.

11. Паттерн Zero-Downtime: Эволюция схемы без простоя

Защита от очередей

1. Установка lock_timeout

SET lock_timeout = '2s';
ALTER TABLE orders ADD COLUMN ...;

Если блокировка не захвачена за 2 секунды, транзакция прерывается, сохраняя доступность базы для пользователей.

Двухфазная валидация

2. NOT VALID + VALIDATE

-- Фаза 1: мгновенное наложение (O(1))
ALTER TABLE orders 
ADD CONSTRAINT chk_sum CHECK (sum > 0) 
NOT VALID;

-- Фаза 2: фоновая проверка без блокировок
ALTER TABLE orders 
VALIDATE CONSTRAINT chk_sum;

12. Инженерный чек-лист выполнения DDL в Production

Идемпотентность: использование IF EXISTS / IF NOT EXISTS.
Транзакционный DDL: оборачивайте скрипты миграции в BEGIN ... COMMIT (в PostgreSQL DDL транзакционен!).
Ограничение времени блокировки: всегда выставляйте SET lock_timeout = '2s';.
Явные имена: никогда не создавайте безымянные CONSTRAINT.
Контроль каскадов: перед выполнением CASCADE проверьте системные зависимости в pg_depend.
Быстрая очистка: для полных сбросов таблиц используйте TRUNCATE RESTART IDENTITY вместо DELETE.

13. Дифференцированные кейсы рабочего листа (zanyatie.html)

Вариант 1: Складской терминал

Создание таблицы Склады, добавление столбца Температурный_режим VARCHAR(20) DEFAULT 'STANDARD', переименование атрибута, безопасный DROP временной таблицы.

Вариант 2: Гостиничный сервис

Конвертация типа столбца Стоимость с INTEGER на NUMERIC(10,2) с обязательным явным кастом USING, добавление ограничения CHECK (Стоимость > 0).

Вариант 3: Банковская система

Добавление внешнего ключа в таблицу Счета с опцией ON DELETE RESTRICT, удаление устаревшего UNIQUE, безопасный DROP архивных логов с CASCADE.

Вариант 4: Образовательный портал

Создание таблицы Дисциплины, наложение составного ограничения UNIQUE(Код, Семестр), высокоскоростная очистка посещаемости через TRUNCATE RESTART IDENTITY.

14. Инженерная культура: Ответственность за DDL и целостность

Тематический интернет-мем: «Exploits of a Mom» (культовый комикс про «Little Bobby Tables», xkcd №327).
Автор: Randall Munroe. Первоисточник: xkcd.com/327. Лицензия: Creative Commons Attribution-NonCommercial 2.5 Generic (CC BY-NC 2.5).
Связь с темой занятия: Оператор DROP TABLE обладает абсолютной разрушительной силой. Ошибки в проектировании схемы, отсутствие санитизации и неконтролируемые каскадные удаления способны мгновенно уничтожить инфраструктуру данных организации.