Практикум SQL

Практикум SQL объясняет, как запускать примеры книги. Полные скрипты каждой главы находятся рядом с книгой и содержат версию PostgreSQL, на которой их проверяли, поэтому вы можете создать схему, выполнить операции и самостоятельно увидеть ошибки ограничений. Приложение описывает запуск локального экземпляра PostgreSQL и организацию скриптов.

Что понадобится

У каждой из 52 глав о паттернах и антипаттернах есть скрипт book/assets/<slug>/<slug>.sql. В заголовках указана проверенная версия PostgreSQL 18.6. Используйте сервер PostgreSQL 18 и его клиент psql: примеры включают uuidv7(), временные ключи, диапазоны PostgreSQL, PL/pgSQL и команды psql с обратной косой чертой. Примечания о других движках в главах — рекомендации по переносу, а не утверждение, что эти файлы выполняются там без изменений.

Используйте одноразовый кластер с подключением суперпользователя. Некоторые примеры создают роли, меняют разрешения и устанавливают поставляемые расширения ltree или btree_gist. Примеры изоляции арендаторов также создают роли входа без паролей, а «База арендатора» создаёт dt_a и dt_b и переподключается под своими рабочими ролями. Эти переподключения требуют локальной аутентификации trust, принятой в практикуме. Одна новая база не изолирует роли уровня кластера и дополнительные базы.

Команды ниже запускаются из корня репозитория. Выберите один вариант запуска сервера, затем выполните один пример или весь набор. Оставьте сообщения сервера на английском: некоторые скрипты сравнивают не только SQLSTATE, но и точный текст диагностики. Не отключайте plpgsql.check_asserts, чтобы проверки ASSERT выполнялись.

Контейнер Docker с PostgreSQL 18

Официальный образ PostgreSQL содержит сервер и клиенты. Этот практикум не публикует порт, отключает сеть контейнера и хранит кластер во временной памяти; локальная аутентификация trust позволяет скриптам арендаторов переподключаться без паролей. Образы PostgreSQL 18 используют /var/lib/postgresql как путь тома.

docker run --detach --name ddp-sql-lab \
  --network none \
  --tmpfs /var/lib/postgresql \
  --env POSTGRES_HOST_AUTH_METHOD=trust \
  --mount "type=bind,source=$PWD,target=/book,readonly" \
  postgres:18

until docker exec ddp-sql-lab pg_isready -q -h 127.0.0.1 -U postgres; do
  sleep 1
done
docker exec ddp-sql-lab psql -X -U postgres -d postgres \
  -c 'SELECT version();'

Если готовность так и не наступила, посмотрите docker logs ddp-sql-lab. Репозиторий смонтирован только для чтения в /book; SQL-скрипты остаются обычными файлами, а не обработчиками инициализации. Например, запустите «Суррогатный ключ» в отдельной новой базе:

docker exec ddp-sql-lab createdb -U postgres --template=template0 lab_one
docker exec ddp-sql-lab psql -X -v ON_ERROR_STOP=1 -U postgres \
  -d lab_one -f /book/book/assets/surrogate-key/surrogate-key.sql
docker exec ddp-sql-lab dropdb -U postgres lab_one

Завершив все эксперименты, удалите контейнер и его временный кластер:

docker rm --force --volumes ddp-sql-lab

Локальный одноразовый кластер

Установите полный серверный дистрибутив PostgreSQL 18, включая initdb, pg_ctl, postgres, клиенты и поставляемые расширения. Установка только клиента не позволяет создать кластер. Поместите каталог bin этого дистрибутива первым в PATH; ниже показан путь Homebrew на Apple Silicon. Для другой установки замените его каталогом исполняемых файлов PostgreSQL 18.

export PATH=/opt/homebrew/opt/postgresql@18/bin:$PATH
postgres --version
psql --version

lab_dir=$(mktemp -d /tmp/ddp.XXXXXX)
initdb -D "$lab_dir/data" -U postgres --locale=C --encoding=UTF8 \
  --auth-local=trust --auth-host=reject
pg_ctl -D "$lab_dir/data" -l "$lab_dir/server.log" \
  -o "-k $lab_dir -c listen_addresses=''" -w start

export PGHOST="$lab_dir" PGPORT=5432 PGUSER=postgres PGDATABASE=postgres
psql -X -c 'SELECT version();'

Выполняйте это от обычного пользователя операционной системы, не от root. initdb создаёт кластер и указанного суперпользователя базы данных; pg_ctl запускает сервер и ждёт готовности. Короткий путь /tmp ограничивает длину имени Unix-сокета. Пустое listen_addresses отключает TCP; PGHOST выбирает каталог сокета, поэтому существующий сервер на TCP-порту 5432 не используется.

Сохраняйте оболочку и значение lab_dir до очистки. После примеров остановите сервер до удаления каталога:

pg_ctl -D "$lab_dir/data" -m fast -w stop
rm -rf -- "$lab_dir"
unset PGHOST PGPORT PGUSER PGDATABASE

Запуск одного скрипта

С работающим локальным кластером и экспортированными переменными подключения создайте базу из template0, выполните файл и удалите базу:

createdb --maintenance-db=postgres --template=template0 lab_one
psql -X -v ON_ERROR_STOP=1 --dbname=lab_one \
  --file=book/assets/surrogate-key/surrogate-key.sql
dropdb --maintenance-db=postgres lab_one

psql -X игнорирует файлы запуска, а ON_ERROR_STOP=1 останавливает файл при неожиданных ошибках с ненулевым кодом завершения. Сохраняйте параметр, даже если большинство скриптов задают его сами. Не добавляйте --single-transaction: скрипты сами определяют границы транзакций, некоторые намеренно фиксируют изменения, а «База арендатора» выполняет CREATE DATABASE и переподключается.

Многие примеры в конце откатывают учебную транзакцию. Другие оставляют схемы и строки для изучения, поэтому для каждой попытки используйте новую базу, не предполагая повторяемость файла. Примеры арендаторов оставляют и объекты уровня кластера; для очистки используйте полный запускатель ниже либо после ручного выполнения удаляйте весь кластер. Последовательные операторы демонстрируют протоколы и утверждения; успешное выполнение файла само по себе не проверяет каждую гонку двух сеансов из главы.

Ожидаемые ошибки

Строка ERROR иногда означает, что демонстрация успешна. Оценивайте её по соседней проверке ожидания и итоговому коду процесса, а не по поиску слова ERROR в выводе.

Например, скрипт «Суррогатного ключа» использует такую последовательность внутри учебной транзакции:

SAVEPOINT expected_error;
\set ON_ERROR_STOP off
INSERT INTO customer (customer_number, display_name)
VALUES ('C-204', 'Duplicate');
\set rejected_state :SQLSTATE
\set ON_ERROR_STOP on
ROLLBACK TO SAVEPOINT expected_error;
SELECT :'rejected_state' = '23505' AS rejected \gset
\if :rejected
\else
    \quit 1
\endif

Сохранённое состояние должно быть 23505 — нарушение уникальности. Оно запоминается до ROLLBACK TO, потому что последующий SQL меняет :SQLSTATE. expected_error — имя точки сохранения в нескольких скриптах, а не особая возможность PostgreSQL; другие файлы используют описательные имена вроде rejected_operation. Откат восстанавливает транзакцию, чтобы следующие проверки могли выполняться.

Исключающая дуга вместо этого перехватывает check_violation или foreign_key_violation в блоках DO и печатает ожидаемый SQLSTATE. Намеренный RAISE EXCEPTION после операции не позволяет молча принять неожиданный успех. Некоторые обработчики также сравнивают SQLERRM. Списки с разделителями определяют повторно используемый временный помощник, а Таблица связей включает ASSERT для сохранённых строк и каскадных эффектов.

Таким образом, успешные проверки могут выдавать обычные ошибки, уведомления и результаты запросов в одном запуске. Ненулевой код psql означает неудачный запуск практикума, даже если раньше появлялись уведомления об ожидаемых ошибках. Если неизменённый скрипт падает из-за другого языка диагностики или версии сервера, это всё равно нужно исследовать; не подавляйте проверки ради успешного результата.

Запуск всех скриптов

Запускатель на POSIX shell использует set -eu и клиенты PostgreSQL из PATH. Адрес узла/сокета, порт, пользователя и другие параметры подключения он берёт из стандартных переменных окружения PG. PGDATABASE задаёт служебную базу, по умолчанию postgres; каждому скрипту явно выбирается новая база. Пример «База арендатора» также переподключается к postgres, поэтому сохраняйте её доступной.

Для локального кластера:

sh book/assets/sql-lab/run-all.sh

Для Docker выполните тот же файл внутри контейнера с установленными там клиентами и локальным сокетом:

docker exec \
  --env PGHOST=/var/run/postgresql --env PGUSER=postgres \
  --env PGDATABASE=postgres \
  ddp-sql-lab sh /book/book/assets/sql-lab/run-all.sh

Запускатель находит book/assets/*/*.sql в порядке путей независимо от рабочего каталога. Для каждого файла он создаёт базу с уникальным именем из template0, запускает psql -X -v ON_ERROR_STOP=1, удаляет базу и печатает PASS <slug>. Он останавливается при первом ненулевом завершении скрипта и печатает FAIL <slug>; обработчик выхода пытается очистить объекты при ошибке или прерывании. Ошибки очистки также делают запуск неуспешным. Если проходят все текущие файлы, последняя строка — PASS: 52 scripts.

Запускатель также удаляет вспомогательные базы примеров арендаторов (dt_a, dt_b) и роли (ss_app, st_owner, st_app_a, st_app_b, dt_owner, dt_app_a, dt_app_b). «Журнал аудита» создаёт audit_example_app внутри откатываемой транзакции. Предварительная проверка отклоняет любое из этих существующих имён до начала работы; она не использует и не удаляет уже имеющиеся учебные объекты. Выполняйте только один набор за раз в одноразовом кластере. После неперехватываемого завершения или ошибки очистки удалите кластер и начните с нового.

Полный набор успешно запускался на одноразовом кластере PostgreSQL 18.6: все 52 файла прошли с включёнными проверками. Пример outbox предполагает подтверждение брокера в SQL, не обращаясь к брокеру; примеры очереди и резервирования продвигают тестовое состояние вместо ожидания реальных сроков работников.

Каталог скриптов

Группы и порядок глав следуют оглавлению книги. Каждый файл самодостаточен для документированной учебной среды; предыдущая глава не является обязательной.

Идентификация

Связи

Справочники и состояние

Иерархии

Типы и атрибуты

Время и удаление

Структуры предметной области

Надёжная запись

Изоляция арендаторов

Рост и изменения

Антипаттерны