Сценарий учебного занятия (Развернутый конспект)

Прямая речь преподавателя: «Здравствуйте, коллеги! Садитесь. Прошу дежурного доложить о присутствующих.

Представьте типичную рабочую ситуацию системного администратора или backend-разработчика: компания запустила интернет-сервис, база данных спроектирована, в ней хранятся сотни тысяч клиентских записей, заказов и платежей. Проходит полгода, бизнес растет, и маркетинг требует: „Нам срочно нужно ввести разделение клиентов по статусам лояльности, хранить стоимость товаров не в целых рублях, а с копейками с точностью до двух знаков, а также привязать складские зоны к температурным режимам хранения!“.

Как поступить неопытному специалисту? Первая наивная мысль: „Давайте удалим таблицу командой DROP TABLE и создадим ее заново скриптом с новыми полями!“. Что произойдет, если мы так сделаем на живом сервере?»

Прямая речь преподавателя: «Совершенно верно. В реальном production-окружении база данных — это живой организм, который обязан непрерывно функционировать и при этом развиваться. Сегодняшняя тема нашего занятия: „Создание, модификация и удаление таблиц“. Мы освоим инструментарий языка определения данных — DDL (Data Definition Language). Мы научимся не просто создавать таблицы с нуля, но и производить сложнейшие архитектурные миграции „на лету“, изменять типы данных, накладывать ограничения целостности и безопасно очищать миллионы строк без остановки сервиса».

Прямая речь преподавателя: «Прежде чем перейти к синтаксису, давайте четко разграничим два фундаментальных подмножества языка SQL: DML и DDL. На предыдущих занятиях вы уже сталкивались с операторами SELECT, INSERT, UPDATE, DELETE. Вспомните, с какими объектами они работают?»

Прямая речь преподавателя: «Точно. А команды DDL — CREATE, ALTER, DROP, TRUNCATE — оперируют метаданными. Взгляните на Слайд 2. В СУБД PostgreSQL таблицы не висят в воздухе. Сама база данных хранит информацию о таблицах в специальных служебных системных каталогах:

• pg_class — регистрирует факт существования таблицы и связывает ее с физическим файлом на диске (relfilenode);
• pg_attribute — содержит описание каждого столбца: имя, тип данных, признак NOT NULL;
• pg_constraint — хранит декларированные ключи и проверочные правила.

Когда мы пишем DDL-запрос, ядро СУБД перестраивает записи в этих системных каталогах. Давайте изучим, как это делать правильно и безопасно».

Прямая речь преподавателя (Блок 1: CREATE TABLE и TEMPORARY TABLE): «Перейдем к Слайду 3. Синтаксис оператора CREATE TABLE знаком вам по базовым примерам, но в профессиональной разработке к нему предъявляются жесткие требования.

Во-первых, идемпотентность: мы всегда добавляем фразу IF NOT EXISTS. Если скрипт миграции случайно запустится повторно в процессе автоматического развертывания CI/CD, СУБД не упадет с ошибкой, а корректно пропустит шаг.

Во-вторых, каждый столбец должен обладать строгим семантическим типом. Для физических величин, денег и точных площадей мы никогда не используем неточный FLOAT — строго NUMERIC(p, s)!

В-третьих, обратите внимание на временные таблицы на Слайде 4: CREATE TEMPORARY TABLE. Чем они уникальны? Они живут только в рамках вашей текущей сессии подключения. Они создаются в скрытой схеме pg_temp_*, изолированы от других пользователей и автоматически удаляются при разрыве соединения. Кроме того, временные таблицы не пишут избыточных данных в журнал упреждающей записи WAL, благодаря чему работают в десятки раз быстрее обычных таблиц. Это идеальный инструмент для промежуточных вычислений и загрузки файлов!».

Прямая речь преподавателя (Блок 2: ALTER TABLE и выражение USING): «Теперь откройте Слайд 5 и 6. Перед нами сердцевина темы — оператор ALTER TABLE.

Когда мы добавляем столбец: ALTER TABLE warehouses ADD COLUMN temperature_zone VARCHAR(20) DEFAULT 'STANDARD'; — начиная с PostgreSQL 11 эта операция выполняется мгновенно за долю миллисекунды, потому что СУБД не переписывает все терабайты данных на диске, а просто записывает значение по умолчанию в каталог pg_attribute.

Но что происходит, если нам нужно изменить тип данных столбца? Допустим, колонка price_per_night имела тип INTEGER, а бизнес потребовал вести расчеты в копейках — NUMERIC(10, 2). Если мы просто напишем: ALTER TABLE hotel_rooms ALTER COLUMN price_per_night TYPE NUMERIC(10, 2); — в целом ряде случаев СУБД выдаст ошибку: „column cannot be cast automatically to type numeric“.

Почему? Потому что ядро СУБД отказывается угадывать, как именно преобразовать двоичные байты старого формата в новый! Мы обязаны передать подсказку через специальную конструкцию USING:

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

Конструкция USING — это обязательный промышленный стандарт явной конвертации».

Прямая речь преподавателя (Блок 3: Безопасное удаление: CASCADE vs RESTRICT): «Посмотрите на Слайд 8. Что происходит, когда мы удаляем таблицу оператором DROP TABLE?

По умолчанию СУБД использует режим RESTRICT. Если у нас есть таблица клиентов clients, а на нее ссылается внешним ключом таблица заказов orders, то команда DROP TABLE clients RESTRICT; будет заблокирована с ошибкой! СУБД защитит целостность вашей базы.

Но что будет, если программист применит опцию CASCADE: DROP TABLE clients CASCADE;? СУБД удалит таблицу клиентов, но внимание: она автоматически сбросит и удалит внешний ключ в таблице заказов! Таблица заказов останется, но связь будет разорвана навсегда. В боевой системе это катастрофа, ведущая к появлению несогласованного мусора в данных. Никогда не используйте CASCADE вслепую!».

Прямая речь преподавателя (Блок 4: TRUNCATE TABLE против DELETE): «Переходим к Слайдам 9 и 10. Самый частый вопрос на собеседованиях администраторов баз данных: „Чем отличается TRUNCATE от DELETE?“. Запишите ключевые тезисы:

1. DELETE — это DML. Он построчно читает диск, помечает каждую строку заголовком „мертвый кортеж“ и пишет каждую строку в журнал транзакций WAL. На таблице в 10 миллионов строк DELETE будет выполняться 10 минут и забьет диск логами.
2. TRUNCATE TABLE — это DDL. Он не читает строки. Он моментально отвязывает старый файл данных и создает пустой файл на диске за 3 миллисекунды ($O(1)$)!
3. TRUNCATE поддерживает опцию RESTART IDENTITY. Если вы очищаете таблицу через TRUNCATE TABLE student_attendance_log RESTART IDENTITY;, счетчик автоинкремента SERIAL обнуляется и новая запись получит ID=1, а не ID=1000000!».

Прямая речь преподавателя (Блок 5: Блокировки DDL и Zero-Downtime): «И наконец, высший пилотаж системного администрирования — Слайд 11 и 12: блокировки DDL. Любая команда ALTER TABLE требует эксклюзивной блокировки AccessExclusiveLock. Это значит, что пока идет миграция, никто не может даже прочитать данные из таблицы. Если команда DDL ждет долгого SELECT-запроса, за ней выстраивается очередь из всех запросов пользователей, пул соединений переполняется, и весь сервис падает с ошибкой 504 Gateway Timeout!

Как инженеры решают эту проблему?

1. Перед DDL выставляют тайм-аут ожидания блокировки: SET lock_timeout = '2s';. Если за 2 секунды таблицу захватить не удалось — транзакция мягко отменяется, сервис не падает.
2. Ограничения CHECK накладывают в два шага: сначала мгновенно с параметром NOT VALID (проверяются только новые строки), а затем валидируют существующие данные отдельной командой VALIDATE CONSTRAINT без блокировки пользователей!».

Прямая речь преподавателя: «Теперь закрепим изученные концепции на практике. Откройте в браузерах интерактивный рабочий лист zanyatie.html.

Обязательно заполните свои фамилию, имя и группу в верхней части листа. Распределяемся по вариантам:

• Вариант 1 (Складской терминал): проектирование таблицы складов, добавление столбца Температурный_режим со значением по умолчанию DEFAULT 'STANDARD', переименование столбца и безопасное удаление временной таблицы.
• Вариант 2 (Гостиничный сервис): изменение типа столбца Стоимость с INTEGER на NUMERIC(10,2) с обязательным явным преобразованием USING и наложение ограничения CHECK (Стоимость > 0).
• Вариант 3 (Банковская система): наложение внешнего ключа с опцией ON DELETE RESTRICT, удаление ограничения уникальности и каскадный DROP TABLE ... CASCADE архивных логов.
• Вариант 4 (Образовательный портал): создание таблицы Дисциплины, составное уникальное ограничение UNIQUE(Код, Семестр) и моментальная очистка журнала через TRUNCATE ... RESTART IDENTITY.

В каждом варианте есть блок вопросов для самопроверки. Приступайте к выполнению, через 15 минут проведем экспресс-разбор решений».

Прямая речь преподавателя: «Время вышло. Нажмите кнопку „Сохранить локально“ в рабочем листе. Все введенные данные надежно сохранены в локальной памяти вашего браузера.

Давайте разберем главные ошибки, которые я заметил во время обхода:

1. Многие забыли указать выражение USING при модификации типов данных. Помните: СУБД строга, она не будет угадывать за вас правила преобразования форматов!
2. В четвертом варианте некоторые написали просто TRUNCATE TABLE без RESTART IDENTITY. Таблица очистилась, но следующий вставленный студент получил бы ID номер 50001 вместо единицы!

А теперь взгляните на экран — Слайд 15. Перед вами легендарный мем мировой IT-культуры — комикс про маленького Бобби Тейблса от Randall Munroe (xkcd №327). Мама назвала сына Robert'); DROP TABLE Students;--, и когда школа попыталась внести его имя в базу данных без экранирования, оператор DROP уничтожил всю базу учеников за этот год!

Мораль этого мема в контексте сегодняшней пары проста: операторы DDL обладают абсолютной властью над физической архитектурой базы данных. Ошибка в DML испортит одну строчку. Ошибка в DDL уничтожит весь бизнес компании. Относитесь к изменению и удалению таблиц с высочайшей инженерной ответственностью».

Прямая речь преподавателя (Домашнее задание и критерии): «Работы оцениваются по 4-балльной закрытой рубрике:

• 2 балла — корректность синтаксиса DDL (CREATE, ALTER, корректные типы, USING);
• 1 балл — грамотное управление ограничениями, CASCADE и RESTRICT;
• 1 балл — аргументированные ответы на блок самопроверки и понимание блокировок DDL.

Для тех, кто пропустил пару или направлен на дистанционный формат, в материалах доступно резервное задание domashnee_zadanie.html с темой „Домашнее задание - Создание, модификация и удаление таблиц“. Выполняйте его только в том случае, если я назначу его индивидуально. Срок сдачи — до начала следующей пары.

Всем спасибо за работу, занятие окончено!».