Продолжаю изучать английский, тренироваться с переводами и, параллельно, читать интересные статьи про PostgreSQL на английском языке. Так что еще один перевод.
В этот раз снова от Cybertec.
Ссылка на оригинал.
Я столкнулся с запросом, в котором использовался необычный уникальный ключ, который сначала озадачил меня. Так как решение, которое я придумал, является хорошей демонстрацией диапазонных типов данных, я поделюсь им. И, так как я не могу устоять, то я еще поговорю и про NULL.
Проблема: необычный уникальный ключ
У нас есть вот такая таблица:
CREATE TABLE tab ( a integer NOT NULL, b integer );
Теперь мы хотим убедиться, что комбинация a и b уникальна в следующих условиях:
- Если «b» NULL, то «a» не может повторяться;
- Если «b» не NULL, то значения «a» и «b» могут повторяться, но их сочетания — нет.
Давайте проиллюстрируем это следующими строками:
-- OK INSERT INTO tab VALUES (1, NULL); -- конфликт с первым пунктом INSERT INTO tab VALUES (1, 2); -- OK, "a" отличается INSERT INTO tab VALUES (5, 2); -- OK из-за второго пункта INSERT INTO tab VALUES (5, 3); -- конфликтует с существующими строками INSERT INTO tab VALUES (5, NULL);
Нет простым решениям
Моей первой реакцией было: это не реализовать с помощью уникального ключа, но, может быть, можно создать уникальный индекс, обеспечивающий соблюдение этих правил. Однако, я не смог найти такое решение (возможно, вы умнее меня и нашли его).
Следующая идея в том, чтобы реализовать это ограничение с помощью триггера. Но такие триггеры всегда будут находится в состоянии гонки, если вы не работаете с уровнем изоляции SERIALIZABLE, что также не очень хорошо.
Роль NULL в этой проблеме
Подумав некоторое время, я понял, что необычная роль NULL делает эту проблему сложной.
Стандарт SQL определяет NULL-значение как «специальное значение, которое используется для указания на отсутствие какого-либо значения» («special value that is used to indicate the absence of any data value»). Однако далее говорится, что «NULL-значение не равно ни не равно никакому другому значению — неизвестно, равно ли оно какому-либо заданному значению» («the null value is neither equal to any other value nor not equal to any other value — it is unknown whether or not it is equal to any given value»). Это звучит несколько противоречиво: значение, которое не может быть равным известному значению, не так ли? Эта путаница лежит в основе SQL NULL. Если мой супруг NULL, означает ли это, что я не женат? Значит ли это, что неизвестно, женат я или нет? Значит ли это, что я женат, но неизвестно на ком? Запутанная семантика NULL в SQL является признаком неопределенности в определении и приведенном выше примере.
Я бы сказал, что можно моделировать как отсутствующие, так и неизвестные значения с помощью NULL. Но многие люди злоупотребляют NULL по разным причинам, например, для представления бесконечности. Это плохой стиль, который приведет к неинтуитивным и сложным запросам. Это необязательно делать, так как в PostgreSQL Infinity является допустимым значением для большинства числовых типов данных и типов данных даты/времени.
Похоже, подобное злоупотребление лежит в основе нашей проблемы. Здесь NULL — это значение, которое конфликтует со всем остальным. Таким образом, мы могли бы сказать, что NULL здесь означает «все возможные значения». Это было для меня ключом к решению: как лучше представить «все возможные значения»?
Диапазонные типы
Начиная с версии 9.2, PostgreSQL имеет диапазонные типы, представляющие интервалы: значения между нижней и верхней границей. Есть также неограниченные диапазоны. Диапазоны могут включать или не включать границы: это представлено квадратной скобкой (`[`) или обычной (`(`).
Несколько примеров:
SELECT '[2022-09-15 00:00:00,2022-09-16 00:00:00)'::tsrange;
tsrange
═══════════════════════════════════════════════
["2022-09-15 00:00:00","2022-09-16 00:00:00")
SELECT '[-10,10]'::int4range;
int4range
═══════════
[-10,11)
SELECT '[0,)'::numrange;
numrange
══════════
[0,)
Второй пример не является ошибкой: с целыми числами диапазон, который доходит до 10 включительно, совпадает с диапазоном, который доходит до 11, исключая верхнюю границу.
pavel=# SELECT '[-10,11]'::int4range; int4range ----------- [-10,12) (1 row) pavel=# SELECT '[-10,9]'::int4range; int4range ----------- [-10,10) (1 row) pavel=# SELECT '[-9,9]'::int4range; int4range ----------- [-9,10) (1 row) pavel=# SELECT '[-9,11]'::int4range; int4range ----------- [-9,12) (1 row)
Но:
pavel=# SELECT '[-10,10)'::int4range; int4range ----------- [-10,10) (1 row) pavel=# SELECT '[-10,11)'::int4range; int4range ----------- [-10,11) (1 row) pavel=# SELECT '(-10,11)'::int4range; int4range ----------- [-9,11) (1 row)
Третий пример — диапазон, неограниченный сверху.
Есть много полезных операторов для диапазонов, таких как оператор включения <@ и оператор «пересечения» &&. В PostgreSQL v14 появились мультидиапазоны, которые еще больше упрощают работу с диапазонами.
Размышляя о нашей проблеме, я бы сказал, что вместо NULL нам лучше использовать диапазон, неограниченный с обеих сторон, чтобы он содержал (и конфликтовал) со всеми числами.
Решение: ограничение-исключение вместо ограничения уникальности
Еще одно расширение PostgreSQL для стандартного SQL — ограничения-исключения (и ссылочка на доку). Ограничения-исключения являются расширением ограничения уникальности: в то время как ограничение уникальности предотвращает две строки с одинаковыми значениями для столбца, ограничения-исключения расширяют это от равенства до других операторов. Ограничения исключения реализуются с помощью индексов GiST, которые, в свою очередь, являются расширением индексов B-дерева.
В нашем случае решением будет использование оператора «пересечения» &&, если мы превратим обычные целые числа в одноэлементные диапазоны. Но есть трудность: мы также должны проверить столбец a на равенство, как и в случае ограничения уникальности из двух столбцов. Сейчас ядро PostgreSQL не содержит классов операторов для «обычных» операторов, таких как =, для скалярных типов данных, таких как integer. Это связано с тем, что в таком случае обычно всегда используется индекс B-дерева. Но мы можем создать расширение btree_gist для предоставления отсутствующих классов операторов:
CREATE EXTENSION IF NOT EXISTS btree_gist;
Теперь наше ограничение выглядит так:
ALTER TABLE tab ADD CONSTRAINT null_unique EXCLUDE USING gist ( a WITH =, int4range(b, b, '[]') WITH && );
Позвольте мне объяснить. Если b не NULL, функция int4range создаст целочисленный диапазон, содержащий только b. Если b NULL, результирующий диапазон не будет ограничен с обеих сторон. Так что это именно то, что нам нужно проверить на пересечение!
Тестирование решения на конфликты «уникальных ограничений»
Давайте проверим, что ограничение работает так, как ожидалось:
-- OK INSERT INTO tab VALUES (1, NULL); -- конфликтует с вышеизложенным из-за первого пункта INSERT INTO tab VALUES (1, 2); ERROR: conflicting key value violates exclusion constraint "null_unique" DETAIL: Key (a, int4range(b, b, '[]'::text))=(1, [2,3)) conflicts with existing key (a, int4range(b, b, '[]'::text))=(1, (,)). -- OK, "a" отличается INSERT INTO tab VALUES (5, 2); -- OK из-за второго пункта INSERT INTO tab VALUES (5, 3); -- конфликтует с существующими строками INSERT INTO tab VALUES (5, NULL); ERROR: conflicting key value violates exclusion constraint "null_unique" DETAIL: Key (a, int4range(b, b, '[]'::text))=(5, (,)) conflicts with existing key (a, int4range(b, b, '[]'::text))=(5, [2,3)).
Вывод
Первоначальная проблема была сложной, потому что она использовала NULL для чего-то, что не соответствовало его семантике. Нам пришлось заменить NULL чем-то другим, прежде чем мы смогли применить ограничение-исключения. Я думаю, что здесь можно извлечь три урока:
- Не используйте NULL для представления чего-либо, кроме отсутствующих или неизвестных значений.
- Если уникальное ограничение с индексом B-tree не подходит, рассмотрите более общее ограничение-исключения с индексом GiST.
- Диапазонные типы данных — это круто.
Лоренц Альбе — старший консультант и инженер службы поддержки CYBERTEC. Он работает с PostgreSQL и вносит свой вклад в развитие PostgreSQL с 2006 года.
Еще раз ссылка на оригинал.
От переводчика: если у вас есть пятом свободных минут, тогда рекомендую посмотреть доклад моего коллеги Алексей Борщева на PGConf.Russia 2022 NULLs в Postgres. Либо почитать статью на хабре на основе этого доклада: NULL-значения в PostgreSQL: правила и исключения.
Ну и статьи на этом сайте тоже можете почитать, связанные с NULL:
А если у вас есть еще пара минут, то можете почитать вот эти интересные статьи с CITForum’a:
- Неопределенные значения в SQL (Джон Грант)
- NULL, трехзначная логика и неопределенность в SQL: критика критики Дейта (Клод Рубинсон)
- Критика статьи Клода Рубинсона «NULL, трехзначная логика и неопределенность в SQL: критика критики Дейта» (К. Дж. Дейт)
- Дубликаты, неопределенные значения, первичные и возможные ключи и другие экзотические прелести языка SQL (Сергей Кузнецов)
- Критика критики критики Дейта (Сергей Кузнецов)
Leave a Reply