Уникальный индекс в PostgreSQL пропускает дубли с NULL: найти, посчитать, починить
Иван Недомолков · Dərc edilib:
В таблице стоит UNIQUE, а одинаковые строки в ней лежат парами. Если хотя бы одна колонка ключа допускает NULL, так и должно быть: PostgreSQL по стандарту SQL считает два NULL разными значениями, и строка с пустым полем не совпадает ни с одной другой, даже со своей копией. Ошибки нет, в журнале пусто, ограничение на месте.
Ниже порядок работы, которым мы проверяем служебные таблицы вокруг базы 1С: буфер обмена, журнал вызовов веб-сервиса, таблицу состояний интеграции. Сначала найти все такие ключи, потом посчитать, сколько строк на самом деле под защитой, потом решить, что с ними делать, и в конце доказать, что ремонт сработал. Все запросы прогнаны на PostgreSQL 17.10, поведение MS SQL Server проверено на 2022-й версии.
Воспроизвести за минуту
CREATE TABLE integration_event (
id bigserial PRIMARY KEY,
entity_id uuid NOT NULL,
state text NOT NULL,
kind text, -- на ранних состояниях не заполняется
UNIQUE (entity_id, state, kind)
);
INSERT INTO integration_event (entity_id, state, kind)
VALUES ('11111111-1111-1111-1111-111111111111', 'CREATED', NULL);
INSERT INTO integration_event (entity_id, state, kind)
VALUES ('11111111-1111-1111-1111-111111111111', 'CREATED', NULL);
SELECT count(*) FROM integration_event; -- 2Обе вставки проходят. Тот же ключ на MS SQL Server ведёт себя наоборот:
CREATE TABLE #t (a int NOT NULL, b int NULL, UNIQUE (a, b));
INSERT INTO #t (a, b) VALUES (1, NULL);
INSERT INTO #t (a, b) VALUES (1, NULL); -- ошибка 2627, нарушение UNIQUE KEY| СУБД | Две строки (1, NULL) под UNIQUE (a, b) |
|---|---|
| PostgreSQL до 15 | обе ложатся, переключить нельзя |
| PostgreSQL 15 и старше | обе ложатся, пока не объявлено NULLS NOT DISTINCT |
| MS SQL Server | вторая падает с ошибкой 2627 |
Из таблицы следует главное для тех, кто переезжает. Ключ, который годами держал дубли на MS SQL, после переноса на PostgreSQL создаётся без единого предупреждения и перестаёт защищать строки с пустым полем. Отчёт переноса чистый, схема та же.
Шаг 1. Найти все такие ключи одним запросом
Признак дыры простой: уникальный индекс, в ключе которого есть колонка без NOT NULL. Ищем по pg_index, а не по pg_constraint. Ограничение UNIQUE всегда опирается на индекс, поэтому в pg_index видно и объявленные ограничения, и индексы, созданные напрямую через CREATE UNIQUE INDEX. Имя ограничения, если оно есть, подтягиваем отдельным соединением.
Вариант для PostgreSQL 15 и старше:
SELECT n.nspname AS shema,
c.relname AS tablitsa,
i.relname AS indeks,
coalesce(con.conname, '-') AS ogranichenie,
string_agg(a.attname, ', ' ORDER BY k.pos) AS kolonki_bez_not_null,
pg_get_expr(ix.indpred, ix.indrelid) AS uslovie_indeksa
FROM pg_index ix
JOIN pg_class i ON i.oid = ix.indexrelid
JOIN pg_class c ON c.oid = ix.indrelid
JOIN pg_namespace n ON n.oid = c.relnamespace
CROSS JOIN LATERAL unnest(ix.indkey::int2[]) WITH ORDINALITY AS k(attnum, pos)
JOIN pg_attribute a ON a.attrelid = ix.indrelid AND a.attnum = k.attnum
LEFT JOIN pg_constraint con ON con.conindid = ix.indexrelid AND con.contype = 'u'
WHERE ix.indisunique
AND NOT ix.indisprimary
AND NOT ix.indnullsnotdistinct -- поведение уже переключено, дыры нет
AND k.pos <= ix.indnkeyatts -- колонки из INCLUDE в ключ не входят
AND NOT a.attnotnull
AND n.nspname NOT IN ('pg_catalog', 'information_schema')
AND n.nspname NOT LIKE 'pg_toast%'
GROUP BY n.nspname, c.relname, i.relname, con.conname, ix.indpred, ix.indrelid
ORDER BY 1, 2, 3;На PostgreSQL 11-14 колонки indnullsnotdistinct ещё нет, запрос упадёт на ней. Уберите строку с этим условием, остальное работает без изменений: indnkeyatts появилась в 11-й вместе с INCLUDE.
Запрос только читает каталог, прав суперпользователя и остановки базы не требует. Если он смотрит в базу 1С, в выдачу могут попасть таблицы платформы. Их структуру мы руками не правим, поэтому фильтруйте по схеме или по именам своих таблиц.
Почему не два простых запроса
Первая версия проверки, которую мы готовили к публикации, была из двух запросов: по pg_constraint с contype = 'u' и по pg_index с отсевом частичных индексов. На тестовой схеме из семи таблиц она ошиблась пятью разными способами, и каждый из них встретится и у вас.
| Что было в запросе | Что вышло на прогоне | Как исправлено |
|---|---|---|
нет фильтра по pg_toast | на пустом кластере 88 лишних строк про служебные индексы chunk_id и chunk_seq | nspname NOT LIKE 'pg_toast%' |
attnum = ANY (indkey) берёт все колонки индекса | колонка из INCLUDE (note) названа дырой, хотя в уникальность не входит | порядковый номер колонки не больше indnkeyatts |
запрос по pg_constraint не знает про NULLS NOT DISTINCT | ограничение, где поведение уже переключено, попало в список | флаг indnullsnotdistinct из pg_index |
| частичные индексы отсеяны целиком | индекс с условием WHERE active пропал из списка, а дыра у него та же | условие выводится колонкой, решение принимает человек |
| ограничение и его индекс в двух запросах | одна дыра выдана дважды под одним именем | один проход по pg_index |
Два ложных срабатывания запрос оставляет, и их надо знать в лицо. Вычисляемая колонка вида coalesce(kind, '__NULL__') формально допускает NULL, хотя пустой не бывает никогда: такой индекс попадёт в список, и его можно вычеркнуть. Индекс по выражению, например (a, lower(b)), наоборот, выражение не покажет: в indkey у него ноль, и соединение с pg_attribute его теряет. Таких индексов в служебных таблицах обычно единицы, их проще проверить глазами по \d имя_таблицы.
Частичный индекс с условием поле IS NOT NULL в выдаче останется, и это правильно. Если рядом нет второго индекса на поле IS NULL, строки с пустым полем не защищены вовсе. Об этом в шаге 4.
Шаг 2. Посчитать долю строк без защиты
Список ключей не говорит, насколько всё плохо. Ключ, у которого пустое поле встречается в двух строках из тысячи, подождёт. Ключ, где пустых больше половины, почти ничего не защищает. Для каждой найденной колонки:
SELECT count(*) AS vsego,
count(*) FILTER (WHERE kind IS NULL) AS bez_zashchity,
round(100.0 * count(*) FILTER (WHERE kind IS NULL)
/ nullif(count(*), 0), 1) AS dolya_pct
FROM integration_event;Если необязательных колонок в ключе несколько, в FILTER пишется kind IS NULL OR другая_колонка IS NULL: одного пустого поля хватает, чтобы строка выпала из-под проверки.
Для масштаба наш случай. Служебная таблица истории внешних операций, ключ из трёх полей, третье заполняется только на поздних состояниях. За 66 суток в неё пришло около 2,2 тысячи событий, примерно 33 в сутки. Пустой признак стоял у 79 % строк. Под защитой было около 460 строк, мимо неё около 1 740. Нашёл это финальный тест миграции, который прогнал те же события второй раз и ждал ноль новых строк. Ваша доля будет другой, она целиком зависит от того, как заполняется конкретное поле, поэтому переносить к себе стоит запрос, а не цифру.
Накопленные дубли ищутся группировкой по тому же ключу:
SELECT entity_id, state, kind, count(*) AS strok
FROM integration_event
GROUP BY entity_id, state, kind
HAVING count(*) > 1;Здесь работает то же расхождение, из-за которого дефект живёт годами. GROUP BY складывает пустые значения в одну группу, уникальный индекс их различает. Одна и та же база на вопрос “есть ли дубли” отвечает двумя способами, и группировка честнее.
Шаг 3. Решить, одна это сущность или две
Перед ремонтом стоит ответить на вопрос про смысл данных: две строки с одинаковым ключом и пустым полем описывают одно и то же или разное?
Для таблицы состояний интеграции ответ очевиден: повтор события после перезапуска обработчика это та же запись, и её надо отвергнуть. Так же с буфером обмена и очередью заданий, где обработчик обязан быть перезапускаемым.
Бывает и наоборот. В справочных и архивных данных пустое поле иногда значит “неизвестно”, а иногда “неприменимо”, и две такие строки действительно разные. Склеивать их уникальностью нельзя: данные испортятся молча и без возврата. Лечится это явным значением вместо пустоты, и уже потом, если нужно, ограничением. Когда повторы с пустым полем законны по задаче, например в истории версий, уникальность на этом ключе не нужна вовсе.
Шаг 4. Починить по версии СУБД
PostgreSQL 15 и старше
Сначала убрать то, что уже накопилось, иначе новый индекс не создастся: could not create unique index ... Key (...)=(..., null) is duplicated. Удаление оставляет в каждой группе строку с наименьшим id, пустые значения сравниваются через IS NOT DISTINCT FROM:
DELETE FROM integration_event e
USING integration_event d
WHERE e.entity_id = d.entity_id
AND e.state = d.state
AND e.kind IS NOT DISTINCT FROM d.kind
AND e.id > d.id;Какую строку из группы оставлять, решает смысл данных, а не этот запрос. Если у дублей разная дата или разный ответ внешней системы, сначала разберите их выборкой из шага 2.
Потом построить новый индекс без блокировки записи и подменить им старое ограничение:
CREATE UNIQUE INDEX CONCURRENTLY integration_event_uq_new
ON integration_event (entity_id, state, kind) NULLS NOT DISTINCT;
ALTER TABLE integration_event
DROP CONSTRAINT integration_event_entity_id_state_kind_key,
ADD CONSTRAINT integration_event_uq UNIQUE USING INDEX integration_event_uq_new;USING INDEX превращает готовый индекс в ограничение и переименовывает его, PostgreSQL сообщит об этом строкой NOTICE. Флаг NULLS NOT DISTINCT при этом сохраняется, \d integration_event покажет UNIQUE CONSTRAINT ... NULLS NOT DISTINCT. Для новой таблицы то же пишется прямо в объявлении: UNIQUE NULLS NOT DISTINCT (entity_id, state, kind).
CREATE INDEX CONCURRENTLY нельзя выполнять внутри транзакции. Если построение упало, остаётся невалидный индекс, его надо удалить и построить заново.
PostgreSQL 11-14
Настройки нет, остаётся два обхода, и у обоих своя цена.
Два частичных индекса, один на заполненное поле и один на пустое:
CREATE UNIQUE INDEX integration_event_uq_kind
ON integration_event (entity_id, state, kind)
WHERE kind IS NOT NULL;
CREATE UNIQUE INDEX integration_event_uq_no_kind
ON integration_event (entity_id, state)
WHERE kind IS NULL;Частая ошибка здесь в том, что ставят только первый. Он защищает ровно те строки, которые и так были защищены, а пустые остаются как были. Второй минус этого обхода виден на ключе с несколькими необязательными колонками: на каждую комбинацию пустых полей нужен свой индекс, две колонки дают четыре индекса, три колонки восемь.
Вычисляемая колонка с подставным значением:
ALTER TABLE integration_event
ADD COLUMN kind_key text
GENERATED ALWAYS AS (coalesce(kind, '__NULL__')) STORED;
CREATE UNIQUE INDEX ON integration_event (entity_id, state, kind_key);Проверено: вторая вставка (1, NULL) падает на Key (a, kind_key)=(1, __NULL__) already exists. Риск в том, что подставное значение живёт в одном домене с настоящими. Если внешняя система однажды пришлёт признак с текстом __NULL__, две разные строки станут одинаковыми. Берите значение, которого во входных данных не бывает физически, а для чисел значение вне допустимого диапазона, закреплённое CHECK. GENERATED ... STORED появился в PostgreSQL 12, на 11-й остаются только частичные индексы.
Оба обхода годятся как временная мера и хорошо работают как аргумент за обновление: защита от дублей на старой версии стоит двух индексов на каждое необязательное поле.
Шаг 5. Доказать, что защита работает
Проверка “повторный прогон прошёл без ошибок” здесь бесполезна: дубли записываются тихо, ошибок не бывает ни до ремонта, ни после. Проверять надо число новых строк, и оно обязано быть нулём.
Самый короткий способ без обработчика: вставить таблицу саму в себя внутри транзакции и откатить.
BEGIN;
CREATE TEMP TABLE do_povtora ON COMMIT DROP AS
SELECT count(*) AS n FROM integration_event;
INSERT INTO integration_event (entity_id, state, kind)
SELECT entity_id, state, kind FROM integration_event
ON CONFLICT DO NOTHING;
SELECT (SELECT count(*) FROM integration_event)
- (SELECT n FROM do_povtora) AS novyh_strok;
ROLLBACK;Каждая строка, которую ограничение пропустило, ляжет второй раз, и novyh_strok покажет их число. На тестовой таблице из 100 строк, где признак заполнен у каждой пятой, до ремонта вышло 80, после ремонта 0. Идентификатор в выборку не берём, он придёт из последовательности. Последовательность после отката не возвращается, в id останется разрыв. Триггеры на вставку при такой проверке сработают, поэтому гоняйте её на копии.
Копия нужна свежая. Доля пустых значений на тестовой базе, поднятой полгода назад, к сегодняшнему проду отношения не имеет, а синтетические данные дают ноль процентов: автор теста заполняет все поля. Поднимать тест из последнего бэкапа ночным заданием умеет тестовая база из бэкапа рабочей, она печатает скрипт восстановления и расписание и сама к СУБД не подключается.
Если обработчик у вас есть, проверка ещё проще: прогнать его второй раз на тех же данных и сравнить число строк до и после. В регламенте переезда это одна строка, “повторный прогон создаёт ноль новых записей”, и она ловит весь этот класс дефектов.
Где это всплывает рядом с 1С
Таблицы самой платформы мы не трогаем. Зато всё, что заведено рядом с базой, наше целиком: промежуточные таблицы обмена, буферы интеграций, журналы вызовов веб-сервисов, очереди фоновых заданий. Уникальность там держит только схема, и именно там она чаще всего стоит на ключе с необязательным полем.
Сама семантика программисту 1С знакома по языку запросов: сравнение с NULL через равенство не срабатывает, нужен ЕСТЬ NULL. В результате запроса пустое значение видно глазами и его идут чинить. В ограничении уникальности его не видит никто.
Если служебная таблица подключена к конфигурации как внешний источник данных, долю пустых можно посчитать и запросом 1С, без доступа к серверу СУБД:
Запрос = Новый Запрос;
Запрос.Текст =
"ВЫБРАТЬ
| КОЛИЧЕСТВО(*) КАК Всего,
| ЕСТЬNULL(СУММА(ВЫБОР
| КОГДА События.Признак ЕСТЬ NULL
| ТОГДА 1
| ИНАЧЕ 0
| КОНЕЦ), 0) КАК БезЗащиты
|ИЗ
| ВнешнийИсточникДанных.Интеграция.Таблица.СобытияИнтеграции КАК События";
Итог = Запрос.Выполнить().Выгрузить();Имена источника, таблицы и полей условные. Сам ключ уникальности конфигурация не знает: ключевые поля внешней таблицы платформа использует для своих нужд и ограничений в СУБД не создаёт. Поэтому список ключей всё равно берётся запросом из шага 1.
Самый частый путь к этой дыре - переезд. Базу 1С перед переносом на PostgreSQL проверяет обработка “Проверка базы перед миграцией на PostgreSQL”: она смотрит, какие уникальные индексы схлопнут строки на невидимых символах, длины строковых измерений и другие места, где новая СУБД поведёт себя иначе. Пустые значения в ключах служебных таблиц она не ищет, это как раз шаг 1 отсюда, и в чек-лист переезда его стоит вписать отдельной строкой. Что ещё ломается на этом пути, разобрано в статье о миграции 1С на PostgreSQL. В обратную сторону свой набор расхождений, для него есть проверка перед переносом на MS SQL, а настройки самого сервера баз данных смотрит чек-ап СУБД.
Аудит служебных таблиц и репетицию переезда на копии мы делаем в рамках обслуживания баз 1С.
Прогоните запрос из шага 1 у себя и напишите, сколько уникальных ключей с необязательными колонками нашлось и какая доля пустых получилась в худшем из них. Нам интересно, 79 % это выброс или обычная картина для таблиц вокруг учётной системы.
