Методическое руководство для преподавателя

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

Дисциплина: ОП.08 Основы проектирования баз данных | Раздел: Тема 5. Организация запросов SQL (§2.2 с.7–8 программы) | СИЭУиП

1. Паспорт занятия и поминутный тайминг

Вид занятия: Урок с объяснением теории (2 академических часа).

Занятие рассчитано на два альтернативных сценария проведения в зависимости от учебного расписания колледжа: основной регламент (80 минут) и сокращенный регламент (60 минут).

Основной регламент (80 минут)

Этап	Время	Содержание
1. Оргмомент и мотивация	5 мин	Проблема «живой» базы и неизбежности изменений
2. Актуализация опорных знаний	10 мин	Различие DDL и DML, структура метаданных
3. Изучение нового материала	35 мин	CREATE, ALTER (ADD/DROP/TYPE/USING/RENAME), DROP (CASCADE/RESTRICT), TRUNCATE, TEMPORARY TABLE, блокировки
4. Первичное закрепление	20 мин	Интерактивный разбор DDL-миграций в рабочем листе
5. Подведение итогов, рефлексия	10 мин	Анализ рисков AccessExclusiveLock, ДЗ

Сокращенный регламент (60 минут)

Этап	Время	Содержание
1. Оргмомент и актуализация	5 мин	Экспресс-опрос: DDL vs DML, цель занятия
2. Интенсивное изложение теории	25 мин	Синтаксис DDL, явный каст USING, каскады, TRUNCATE vs DELETE
3. Практикум на рабочем листе	20 мин	Решение кейсов (1 вариант по выбору студента)
4. Итоги и инструктаж ДЗ	10 мин	Критерии 4-балльной рубрики, закрытие пары

2. Архитектура и семантика DDL (Data Definition Language)

В отличие от операторов манипулирования данными (DML: SELECT, INSERT, UPDATE, DELETE), которые оперируют строками и кортежами, операторы языка определения данных (DDL: CREATE, ALTER, DROP, TRUNCATE) изменяют метаданные системного каталога СУБД (в PostgreSQL это таблицы схем pg_catalog: pg_class, pg_attribute, pg_constraint).

2.1. Синтаксис и спецификация CREATE TABLE

Оператор CREATE TABLE аллоцирует логическую сущность «отношение» и регистрирует физический файл кучи (heap file) на диске. Ключевые рекомендации для студентов:

Обязательное использование конструкции IF NOT EXISTS для обеспечения идемпотентности скриптов миграции.
Явное указание первичного ключа (PRIMARY KEY) для каждой постоянной таблицы. В учебных и промышленных средах рекомендуется суррогатный ключ на базе SERIAL / BIGSERIAL (или стандарта SQL:2008 GENERATED ALWAYS AS IDENTITY).
Ограничения атрибутов: NOT NULL, DEFAULT, CHECK. Значение DEFAULT вычисляется во время выполнения INSERT, если столбец опущен.

CREATE TABLE IF NOT EXISTS inventory_warehouses (
    warehouse_id SERIAL PRIMARY KEY,
    facility_code VARCHAR(20) NOT NULL UNIQUE,
    capacity_tons NUMERIC(10, 2) NOT NULL CHECK (capacity_tons > 0),
    is_operational BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

2.2. Операторы модификации ALTER TABLE

Реляционные структуры не статичны: изменение бизнес-логики требует эволюции схемы. Преподаватель должен подчеркнуть, что в производственных базах данных пересоздание таблицы недопустимо из-за риска потери данных и простоя сервиса. Используется семейство команд ALTER TABLE:

Действие	Синтаксис PostgreSQL	Особенности выполнения и риски
ADD COLUMN	`ALTER TABLE t ADD COLUMN col_name TYPE [DEFAULT v] [NOT NULL];`	Начиная с PostgreSQL 11 добавление столбца с константным `DEFAULT` происходит моментально без перезаписи таблицы на диске.
DROP COLUMN	`ALTER TABLE t DROP COLUMN col_name [CASCADE];`	Столбец логически скрывается в каталоге, физические данные вычищаются позже при `VACUUM FULL`. Опция `CASCADE` удаляет зависимые индексы и представления.
ALTER COLUMN TYPE	`ALTER TABLE t ALTER COLUMN col TYPE new_type USING col::new_type;`	Требует выражения `USING`, если автоматическое приведение невозможно. Приводит к полной перезаписи таблицы и блокировке!
ADD CONSTRAINT	`ALTER TABLE t ADD CONSTRAINT name CHECK (...) [NOT VALID];`	Проверяет все существующие строки. В production рекомендуется добавлять с `NOT VALID`, а затем валидировать отдельно.
DROP CONSTRAINT	`ALTER TABLE t DROP CONSTRAINT constraint_name;`	Удаляет именованное ограничение. Требует точного знания системного имени констрейнта.
RENAME	`ALTER TABLE t RENAME COLUMN old_name TO new_name;` `ALTER TABLE t RENAME TO new_table_name;`	Быстрая операция уровня метаданных каталога, но ломает внешние обращения бэкенда и хранимые процедуры!

Педагогический акцент на выражении USING: При изменении типа данных, например из VARCHAR в INTEGER или из INTEGER в NUMERIC(10,2), СУБД не может гарантировать корректность преобразования байтов без явной инструкции разработчика. Конструкция USING col::NUMERIC(10,2) или USING to_date(col, 'YYYY-MM-DD') является обязательной частью промышленной компетенции инженера баз данных.

3. Деструктивные операции и целостность: DROP TABLE, TRUNCATE, DELETE

3.1. Безопасное удаление DROP TABLE (CASCADE vs RESTRICT)

Оператор DROP TABLE физически удаляет отношение, освобождает файлы на диске и удаляет записи из системных каталогов. Стандарт SQL и PostgreSQL поддерживают две взаимоисключающие опции разрешения ссылочных зависимостей:

RESTRICT (по умолчанию): операция прерывается с ошибкой, если на удаляемую таблицу ссылается хотя бы один внешний ключ другой таблицы или от нее зависит представление (VIEW). Это защитный барьер от случайного разрушения связей.
CASCADE: операция удаляет целевую таблицу и автоматически уничтожает все зависимые объекты (внешние ключи дочерних таблиц, представления, правила). Внимание: сами данные в дочерних таблицах не удаляются, удаляются именно ссылки-ограничения!

Опасность CASCADE в production: Неопытные администраторы используют DROP TABLE t CASCADE;, надеясь «быстро почистить мусор», и непреднамеренно снимают внешние ключи с критических финансовых таблиц, превращая базу в несогласованное состояние без ссылочной целостности.

3.2. TRUNCATE TABLE против DELETE

Студенты часто путают TRUNCATE и DELETE FROM table. Преподавателю необходимо наглядно продемонстрировать фундаментальные различия архитектурного уровня:

Характеристика	TRUNCATE TABLE	DELETE FROM table
Категория языка	DDL (Data Definition Language)	DML (Data Manipulation Language)
Физическая механика	Освобождает страницы данных, создает новый пустой файл кучи (relfilenode). Время выполнения $O(1)$.	Построчно помечает кортежи как удаленные (dead tuples). Время выполнения $O(N)$ от числа строк.
Нагрузка на журнал WAL	Минимальная (логируется только факт выделения нового сегмента).	Огромная (каждое удаление кортежа фиксируется в WAL).
Триггеры	Вызывает триггеры уровня таблицы (`FOR EACH STATEMENT`). Построчные триггеры игнорируются!	Вызывает построчные триггеры `FOR EACH ROW` для каждой строки.
Сброс автоинкремента	Поддерживает опцию `RESTART IDENTITY` (сбрасывает связанный sequence в 1).	Счетчик автоинкремента никогда не сбрасывается автоматически.
Фильтрация WHERE	Не поддерживается (удаляется вся таблица целиком).	Поддерживается (можно удалить конкретное подмножество строк).

4. Временные таблицы и управление блокировками DDL в Production

4.1. Временные таблицы (CREATE TEMPORARY TABLE)

Временные таблицы создаются с ключевым словом TEMPORARY (или TEMP) и предназначены для промежуточной обработки данных в рамках сессии:

Создаются в динамической изолированной схеме вида pg_temp_N.
Их жизненный цикл ограничен сессией подключения (ON COMMIT PRESERVE ROWS) либо текущей транзакцией (ON COMMIT DROP или ON COMMIT DELETE ROWS).
Не требуют фиксации в WAL (при ненадежном хранении), что обеспечивает колоссальный выигрыш в скорости ETL-конвейеров.
Имена временных таблиц могут совпадать с именами постоянных — временная таблица экранирует постоянную в пространстве видимости текущей сессии!

4.2. Блокировки DDL: AccessExclusiveLock и «миграции без простоя»

Критический аспект подготовки системных администраторов и инженеров баз данных — понимание природы блокировок. Большинство команд ALTER TABLE и DROP TABLE захватывают самый тяжелый уровень блокировки: AccessExclusiveLock.

Анатомия инцидента в Production (Outage):

Разработчик выполняет команду ALTER TABLE orders ADD COLUMN ...; на таблице, к которой идет 5000 запросов в секунду.
Для захвата AccessExclusiveLock СУБД должна дождаться завершения ВСЕХ активных транзакций, удерживающих даже легкую блокировку AccessShareLock (любой длительный SELECT).
Команда DDL встает в очередь ожидания.
Катастрофическое следствие: ВСЕ последующие входящие запросы (даже простые SELECT * FROM orders WHERE id = ?) встают в очередь ЗА этой командой DDL! Пул соединений исчерпывается за 2-3 секунды, бэкенд начинает возвращать ошибки 504 Gateway Timeout, сервис падает.

Золотые правила безопасных миграций схемы:

Установка lock_timeout: перед DDL-скриптом обязательно выставлять SET lock_timeout = '2s';. Если блокировка не захвачена за 2 секунды, команда аварийно завершится, сохранив работоспособность продакшена.
Двухфазное добавление CHECK: сначала ADD CONSTRAINT name CHECK (...) NOT VALID; (короткая блокировка, не сканирует диск), затем ALTER TABLE name VALIDATE CONSTRAINT name; (сканирует диск под слабой блокировкой, не мешающей чтению и записи).
Параллельное создание индексов: использовать CREATE INDEX CONCURRENTLY.

5. Типовые студенческие ошибки и педагогические приемы

Типовая ошибка студента	В чем проявляется	Педагогический прием и контрвопрос
Смешение DDL и DML	Студент пытается «обновить структуру» запросом `UPDATE` или удалить столбец через `DELETE COLUMN`.	Метафора: «DML красит стены и расставляет мебель внутри комнат. DDL сносит несущие стены и пристраивает новые этажи здания».
Потеря данных при смене типов	Использование `ALTER COLUMN TYPE` без учета несовместимости строковых и числовых форматов.	Демонстрация ошибки PostgreSQL: «column cannot be cast automatically to type numeric». Разбор конструкции `USING` как функции-адаптера.
Безымянные ограничения	Создание ограничений вида `CHECK (price > 0)` внутри `CREATE TABLE` без явного `CONSTRAINT chk_price`.	Вопрос: «Как вы удалите это ограничение через полгода, если СУБД назвала его `table_price_check1`, а на другом сервере — `table_price_check2`?» Требование явного именования.
Иллюзия идентичности TRUNCATE и DELETE	«Какая разница, чем очищать? Я привык к DELETE, он короче».	Эксперимент: сравнение времени очистки таблицы на 1 000 000 строк (DELETE выполняется 15 секунд и раздувает базу, TRUNCATE выполняется за 4 миллисекунды). Анализ сброса `RESTART IDENTITY`.
Слепой CASCADE	Добавление `CASCADE` к `DROP TABLE` в любой непонятной ситуации.	Разбор сценария: падение связанных представлений (VIEW) финансовой отчетности при случайном каскадном удалении справочника валют.