Как обеспечить действительность внешних ключей в Postgres

Question

Использование Postgres 10.6

Проблема:

• Некоторые данные в моих таблицах нарушают ограничения внешнего ключа (не знаю, как). Ограничения ON DELETE CASCADE ON UPDATE CASCADE
• В pg_dump базы данных эти внешние ключи удаляются (из-за того, что они находятся в недопустимом состоянии?)
• pg_restore делается в пустую базу данных, которая больше не имеет внешних ключей
• В новой базе данных все ее первичные ключи обновлены до допустимых ключей, не используемых во второй базе данных. Таблицы с недействительными данными не обновляют свои внешние ключи из-за отсутствующего ограничения.
• pg_dump из новой базы данных сделано, затем база данных удаляется
• На pg_restore во вторую базу данных, которая имеет ограничения внешнего ключа, данные импортируются в недопустимом состоянии и повреждают новую базу данных.

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

Я читал о ALTER TABLE ... VALIDATE CONSTRAINT ... но это не решило бы мою проблему, так как данные в настоящее время не помечены как NOT VALID, Я знаю, может сделать такие заявления, как:

DELETE FROM a WHERE a.b_id NOT IN ( SELECT b.id )

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

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

Пример таблицы:

CREATE TABLE dependencies (
    ...
    from_task int references tasks(id) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL, 
    to_task int references tasks(id) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL, 
    ...
);

Зависимости в конечном итоге со значениями для to_task а также from_task которые не существуют в tasks таблица (см. изображение)

Замечания:

• Пытался EXPLAINANALYZE ничего странного
• pg_tablespace, имеет только две записи. pg_default и pg_global
• relforcerowsecurity, relispartition оба имеют значение "false" в обеих таблицах
• Аргументы в pg_dump (из вызова C++) arguments << "--file=" + fileName << "--username=" + connection.userName() << databaseName << "--format=c"

Answer 1

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

pg_dump никогда не будет "отбрасывать" ограничение молча - возможно, при восстановлении дампа произошла ошибка, которую вы не заметили.

Правильное решение - очистить данные, которые нарушают ограничение, и заново создать их.

Если это проблема с повреждением данных, проверьте ваше оборудование.

Нет необходимости регулярно проверять наличие данных, PostgreSQL не имеет привычки портить данные сам по себе.

Лучший тест будет взять pg_dump регулярно и посмотрите, если восстановление дампа вызывает какие-либо ошибки.