PostgreSQL: следующая находка в сети

После того теста по PostgreSQL, который я случайно нашёл в сети (и благополучно разобрал в прошлой статье), стало ясно, что базовые знания — это хорошо, но в мире SQL всегда есть что-то, что может удивить. А что, если найти тест, который бросит мне реальный вызов? Что-то более сложное, с тонкими нюансами и хитрыми вопросами.

И вот, мои поиски увенчались успехом. На этот раз я столкнулся с тестом, который обещает прокачать знания не только по основам, но и углубиться в оптимизацию запросов, индексацию и управление сложными отношениями между таблицами. В этой статье я разберу, что интересного было в этом тесте, на какие вопросы я ответил легко, а где пришлось помучаться (и немного погуглить).

Дисклеймер

Прежде чем перейти к разбору, хотелось бы поделиться неприятным моментом. Обжалование блокировки Дзена и ВК от НеНазванной платформы, продолжается. Администрация информационных ресурсов ждут от заявителя разъяснений.

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

Вопрос 1

На какой тип данных нельзя применить ограничение уникальности (unique constraint)?

Варианты ответа:

1. jsonb
2. smallint
3. json
4. timestamp
5. serial

Обоснование:

Ограничение уникальности (unique constraint) требует, чтобы данные были сравнимы для проверки уникальности. Типы данных, такие как json и jsonb, хранят сложные структуры, которые могут содержать массивы или объекты, и не обладают встроенным способом определения уникальности, так как сравнение их содержимого выполняется иначе. Однако jsonb поддерживает индексацию и определение уникальности через ключи, а json — нет. Другие типы данных (например, smallint, timestamp, serial) поддерживают уникальные ограничения, так как они являются примитивными типами и имеют четкую структуру для сравнения.

📌Правильный ответ:

json

Вопрос 2

Была создана специальная последовательность, генерирующая только четные числа, с названием even_sequence. Что нужно подставить на место пропуска [...], чтобы в случае, если значение even_column не было указано при вставке, значение бралось из even_sequence?

Варианты ответа:

1. integer with ‘even_sequence’
2. integer default nextval(‘even_sequence’)
3. integer from ‘even_sequence’
4. integer by nextval(‘even_sequence’)
5. integer (select nextval(‘even_sequence’))

Обоснование:

В SQL при создании таблицы можно задать значение по умолчанию для столбца, используя ключевое слово DEFAULT. Если для столбца не указано значение при вставке, значение берется из указанного выражения по умолчанию. В случае с последовательностью используется функция nextval, которая получает следующее значение из последовательности. Поэтому правильный синтаксис для задания значения по умолчанию из последовательности выглядит как DEFAULT nextval('sequence_name').

📌Правильный ответ:

integer default nextval(‘even_sequence’)

Вопрос 3

Созданы структуры двух таблиц, и в обе таблицы добавлены записи. Какие проблемы могут возникнуть при выполнении команды truncate table second_table?

Варианты ответа:

1. Придется обязательно выполнять команду vacuum для освобождения дискового пространства
2. С существующими ссылками на first_table будет падать с ошибкой и просить использовать дополнительный флаг restart identity
3. Необходимо будет вручную удалить созданную автоматически последовательность second_table_id_seq
4. Возникнет ошибка, так как существующие записи second_table ссылаются на строки из таблицы first_table
5. При последующей вставке в second_table дефолтное значение для id не будет начинаться с 1

Обоснование:

Команда TRUNCATE удаляет все строки из таблицы мгновенно и освобождает пространство, не записывая в журнал транзакций (лог), как при обычном DELETE. Однако она не работает с таблицами, на которые есть активные внешние ключи (foreign key), если только не используется флаг CASCADE. Поскольку second_table содержит ссылку на first_table, команда TRUNCATE потребует либо удаления связанных записей, либо добавления флага CASCADE или других модификаторов, например, RESTART IDENTITY, если вы хотите сбросить последовательность.

📌Правильный ответ:

С существующими ссылками на first_table будет падать с ошибкой и просить использовать дополнительный флаг restart identity.

Вопрос 4

Была создана таблица, в которую добавили записи. Добавление записей в таблицу происходило из нескольких мест, и в каждом случае нужно было учитывать следующую логику:

Поскольку вставка происходила из нескольких мест, было решено перенести эти ограничения на уровень базы данных, чтобы случайно не вставить некорректные данные. Был выполнен запрос:

С какими проблемами можно столкнуться?

Варианты ответа:

1. Проблемы возникнут, если существующие записи не соответствуют указанному ограничению
2. Проблема возникнет, так как в названии ограничения (constraint) должно содержаться в имени все фигурирующие в условии названия колонок
3. Проблемы возникнут с тем, что созданное ограничение будет применено только к новым записям, и в приложении все равно придется оставить проверку
4. Проблемы возникнут с порядком выполнения логических операторов. Операторы AND нужно взять в скобки для корректности задуманной проверки
5. Проблемы возникнут, так как discount может иметь значение NULL, а в условии он сравнивается с price, который не может быть NULL

Обоснование:

1. Существующие записи: При добавлении ограничения CHECK база данных проверяет только новые вставки или обновления данных. Однако, если существующие записи не соответствуют новому правилу, добавление ограничения вызовет ошибку.
2. Проблема с именем ограничения: Нет требования, чтобы в имени ограничения указывались названия всех колонок, упомянутых в условии. Это правило касается стиля кодирования, но не функциональности.
3. Применение только к новым данным: Это частично верно. Хотя ограничение будет проверяться при всех новых вставках и обновлениях, старые записи останутся неизмененными, если они не соответствуют. Однако оставлять проверку на стороне приложения необходимо только в редких случаях.
4. Порядок выполнения операторов AND: Операторы AND имеют четкий порядок выполнения, и дополнительные скобки не требуются для данной логики.
5. NULL в discount: Если колонка discount допускает значение NULL, это вызовет проблемы при сравнении, так как любое сравнение с NULL вернет FALSE. Чтобы избежать ошибок, нужно либо сделать колонку discount NOT NULL, либо изменить условие на discount IS NOT NULL AND ....

📌Правильный ответ:

Проблемы возникнут, если существующие записи не соответствуют указанному ограничению.

Вопрос 5

Создана структура таблиц, указанная на изображении. Какой вид join необходимо использовать на месте пропуска [...], чтобы в результате для записей с type = 'table_aw' была заполнена колонка naming, а для записей с type = 'table2' — колонка serial_number?

Код таблиц:

Код запроса:

Варианты ответа:

1. right
2. left
3. cross
4. inner
5. Оставить пустым

Обоснование:

Для данного запроса требуется присоединить данные из двух таблиц (table_aw и table2) к таблице multirelation. Применяется фильтрация по значению колонки type. Здесь нужен inner join, так как требуется выбрать только те строки, которые удовлетворяют условию соединения (совпадение entity_id и соответствие типу).

Inner join объединяет только строки, где есть совпадение по условию, что идеально подходит для этого сценария, так как нет необходимости оставлять строки без совпадений.

📌Правильный ответ:

inner

Вопрос 6

Существуют таблицы t1 и t2. При использовании какого типа данных PostgreSQL выдаст ошибку?

Варианты ответа:

1. bigserial
2. bigint
3. int
4. numeric
5. integer

Обоснование

В PostgreSQL для создания внешнего ключа (references) тип данных колонки в связанной таблице должен быть совместим с типом данных первичного ключа (primary key). В данном случае первичный ключ таблицы t1 имеет тип integer. Типы данных integer, int, и bigint совместимы с integer, так как они относятся к числовым типам. Однако bigserial является псевдотипом, который автоматически генерирует последовательности, и его нельзя использовать в качестве типа данных для внешнего ключа. Это вызовет ошибку.

📌Правильный ответ:

bigserial

Вопрос 7

Какое выражение на месте пропуска [...] автоматически приведет к созданию индекса?

Варианты ответа:

1. unique
2. integer check (col_name > 0)
3. not null
4. serial
5. references other_table(col_name)

Обоснование:

1. unique: При добавлении ограничения уникальности автоматически создается индекс для обеспечения уникальности значений в столбце.
2. integer check (col_name > 0): Это ограничение проверяет значение, но индекс не создается.
3. not null: Ограничение NOT NULL не требует индекса, так как оно просто гарантирует, что в столбце не будет значений NULL.
4. serial: Тип serial создает последовательность для автоинкрементирования, но сам по себе индекс не создается.
5. references other_table(col_name): При создании внешнего ключа (FOREIGN KEY) индекс на столбце не создается автоматически, но может быть добавлен вручную для оптимизации.

📌Правильный ответ:

unique

Вопрос 8

Какой тип индекса быстрее, если все запросы к таблице product будут содержать product.id = <some_value>? Запросы могут быть и select, и join.

Варианты ответа:

1. create index <index_name> on product using btree(id)
2. Нет необходимости отдельно создавать индекс, достаточно в конец строки объявления колонки id добавить primary key
3. Ввиду малой селективности подобные запросы не будут использовать индекс в пользу последовательного перебора
4. Нет необходимости отдельно создавать индекс, достаточно добавить unique к объявлению колонки id
5. create unique index <index_name> on product using hash(id)

Обоснование:

1. B-Tree (btree): Индексы B-Tree являются стандартным и универсальным выбором для запросов с точным соответствием (=), диапазонами и сортировкой. Они наиболее эффективны для запросов, где используется равенство или сравнение.
2. Primary Key: Создание первичного ключа на колонке id автоматически добавляет индекс B-Tree, что делает явное создание индекса избыточным.
3. Малая селективность: Если уникальность значений низкая (мало уникальных значений), индекс может быть менее эффективным, но в случае идентификаторов это маловероятно.
4. Unique: Добавление ограничения unique также автоматически создаст индекс B-Tree.
5. Hash: Хеш-индексы быстрее для точного соответствия, но не поддерживают диапазоны и менее универсальны. Для равенства (=) они могут быть быстрее, но PostgreSQL рекомендует B-Tree для большей универсальности.

📌Правильный ответ:

Нет необходимости отдельно создавать индекс, достаточно в конец строки объявления колонки id добавить primary key.

Добавление primary key на колонку id автоматически создаст индекс B-Tree, который оптимален для запросов вида product.id = <some_value>.

Вопрос 9

Для чего служит параметр xmax, входящий в заголовок версии строки?

Варианты ответа:

1. Для обеспечения целостности вида check (column > constant)
2. Для обеспечения целостности вида check (column < constant)
3. Для обозначения номера транзакции, создающей эту строку
4. Для обеспечения блокировки строки от других транзакций
5. Для определения максимального числа, которого может достичь колонка числового типа данных

Обоснование:

В PostgreSQL параметр xmax используется для указания идентификатора транзакции, которая пометила строку как удаленную (или сделала её недействительной). Это часть механизма MVCC (многоверсионного управления параллелизмом), который позволяет различным транзакциям видеть разные версии строки в зависимости от их контекста. Он не используется для создания строки или блокировки, а также не связан с проверками целостности или максимальными значениями числовых колонок.

📌Правильный ответ:

Для обеспечения блокировки строки от других транзакций.

Вопрос 10

Какой вид изоляции транзакции необходимо установить, чтобы «в результатах запроса могли появляться промежуточные результаты параллельной транзакции, которая еще не завершилась»?

Варианты ответа:

1. read committed
2. read uncommitted
3. repeatable read
4. Ситуация невозможна в PostgreSQL
5. serializable

Обоснование:

В PostgreSQL уровень изоляции read uncommitted фактически недоступен в полной мере, так как PostgreSQL даже на этом уровне изоляции не показывает неподтвержденные (uncommitted) данные. Уровень read committed, который является стандартным в PostgreSQL, гарантирует, что транзакция видит только данные, подтвержденные другими транзакциями.

Однако, чтобы промежуточные (неподтвержденные) данные параллельной транзакции могли быть видны, как описано в вопросе, потребовался бы уровень изоляции read uncommitted.

Так как ситуация «в результатах запроса появляются неподтвержденные данные» невозможна в PostgreSQL из-за строгого механизма MVCC, правильным ответом будет:

📌Правильный ответ:

Ситуация невозможна в PostgreSQL.

Вопрос 11

Существуют две транзакции. Сначала первая транзакция выполняет команду. Затем вторая транзакция выполняет команду. Далее первая транзакция продолжает выполняться. Какая последовательность <id> приведет к взаимоблокировке?

Варианты ответа:

1. 1122
2. 1212
3. 1234
4. 1112
5. 2112

Обоснование:

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

1. Первая транзакция блокирует запись с id = 1.
2. Вторая транзакция блокирует запись с id = 2 и пытается обновить запись с id = 1, но не может, так как она заблокирована первой транзакцией.
3. Первая транзакция пытается обновить запись с id = 2, но не может, так как она заблокирована второй транзакцией.

Эта ситуация возможна при последовательности <id>: 1212.

📌Правильный ответ:

1212.

Вопрос 12

Создана следующая таблица. Какой оператор можно использовать на месте пропуска [...], чтобы команда могла быть выполнена?

Варианты ответа:

1. limit
2. distinct
3. order by
4. except
5. union

Обоснование:

Для выполнения команды insert into comedies, представление comedies должно быть обновляемым. PostgreSQL позволяет обновлять представления, если они построены на основе одной таблицы, без дополнительных сложных конструкций, таких как DISTINCT, GROUP BY, EXCEPT, UNION, или ORDER BY. Использование этих операторов делает представление неизменяемым, что вызовет ошибку при попытке вставить данные.

В данном случае правильным ответом является Никакой оператор (оставить пустым), но из предложенных вариантов наиболее подходящий выбор: distinct, так как использование DISTINCT делает представление неизменяемым, что не позволит выполнить вставку.

Однако, чтобы команда работала, оператор на месте [...] должен отсутствовать.

📌Правильный ответ:

DISTINCT

Ha confirmado un nivel intermedio de habilidad

PostgreSQL
Los empleadores verán esto en su currículum: ¡postúlese a las vacantes!

Заключение

Что ж, в этот раз тест оказался действительно посложнее, и я не пожалел, что потратил на него время. Такие задания не только освежают знания, но и заставляют взглянуть на привычные задачи с новой стороны. PostgreSQL — это не просто база данных, а целый мир со своими законами, хитростями и неожиданными "подводными камнями".

А теперь, как и в прошлый раз, я пойду ещё немного пороюсь в интернете. Вдруг наткнусь на очередной интересный тест или материал, который захочется обсудить? Оставайтесь на связи, следите за обновлениями в Telegram, и давайте вместе изучать мир баз данных — с юмором, сарказмом и пользой! 🚀