NOT DEFERRABLE и DEFERRABLE INITIALLY IMMEDIATE в SQL

Быстрый ответ

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

-- Установка откладываемого ограничения дает возможность временно игнорировать правила
ALTER TABLE my_table
  ADD CONSTRAINT my_constraint UNIQUE(column)
  DEFERRABLE INITIALLY IMMEDIATE;

-- В рамках данной транзакции ограничение можно отложить
BEGIN;
SET CONSTRAINTS my_constraint DEFERRED;
-- Допускаются действия, влекущие за собой игнорирование ограничения
COMMIT;  // Здесь происходит проверка ограничения, гарантируя целостность данных

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

Как выбирать между ОТКЛАДЫВАЕМЫМИ и НЕМЕДЛЕННЫМИ ограничениями

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

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

Примеры использования: ОТКЛАДЫВАЕМЫЕ или НЕМЕДЛЕННЫЕ

Корректный выбор упирается в деловые приоритеты и технические особенности. Например:

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

Опасности и подводные камни ОТКЛАДЫВАЕМЫХ ограничений

Откладываемыми ограничениями стоит пользоваться с осторожностью:

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

Адаптация под особенности различных СУБД

Команда SET CONSTRAINTS взаимодействует с откладываемыми ограничениями, но поддержка этого может отличаться в разных СУБД, включая PostgreSQL и Oracle. Изучите документацию вашей СУБД, чтобы понимать особенности работы с откладываемыми ограничениями в ней.

Визуализация

Демонстрируем разницу между немедленными и откладываемыми изначально немедленными на примере Lego:

Lego: 

| Действие                  | Немедленные (🚫)         | Откладываемые изначально немедленные (✅🕒) |
| -------------------------- | ---------------------------- | ----------------------------------------- |
| Размещение каждого блока   | Сразу идеально или никак     | Возможны корректировки до завершения транзакции |
| Подтверждение последнего блока | Безотлагательное исполнение правил для каждого блока | Проверка правил после установки всех блоков |
| Реакция на нарушение правила | Немедленное прекращение работы | Возможность исправления до конца текущей транзакции |

Пояснение:

• 🚫 Немедленные: Требования ограничения должны быть выполнены мгновенно в процессе работы.
• ✅🕒 Откладываемые изначально немедленные: В течение транзакции у вас есть возможность для корректировок, но все должно быть в идеальном порядке перед её завершением.

Советы для специалистов SQL

• Вопрос времени: Время проверки ограничений критично для бесперебойной работы баз данных.
• Массовые изменения: Команда SET CONSTRAINTS ALL DEFERRED позволяет сэкономить время, позволяя отложить сразу несколько ограничений.
• Тестирование: Опытный переключения между немедленными и откладываемыми изначально немедленными ограничениями поможет выявить различия в производительности и выявить потенциальные проблемные зоны.

Полезные материалы

1. PostgreSQL: Документация: 16: 5.4. Ограничения — Детальное руководство по ограничениям в PostgreSQL.
2. ruby – Как проверить дату в rails? – Stack Overflow — Обсуждение механизмов проверки дат в Ruby on Rails, включая сравнение различных видов ограничений.
3. table_constraint (Transact-SQL) – SQL Server | Microsoft Learn — Руководство по ограничениям в SQL Server.
4. Deferred Unique Constraints – PostgreSQL wiki — Информация о поведении откладываемых уникальных ограничений в PostgreSQL.
5. constraint-checking-in-postgresql-what-you-should-know – CYBERTEC — Статья о времени проверки и управлении ограничениями в PostgreSQL.