Restricciones NOT VALID y triggers desactivados: las barreras dormidas
Inventario de las restricciones CHECK/FK creadas NOT VALID (nunca re-validadas) y de los triggers desactivados: otras tantas reglas de negocio que la base ya no aplica.
Requisitos
PostgreSQL (pg_constraint, pg_trigger)
SQL
SELECT
'constraint' AS type_objet,
con.conrelid::regclass::text AS table_name,
con.conname AS nom,
'NOT VALID — données existantes jamais vérifiées' AS probleme
FROM pg_constraint con
WHERE NOT con.convalidated
AND con.contype IN ('c', 'f')
UNION ALL
SELECT
'trigger',
tg.tgrelid::regclass::text,
tg.tgname,
'désactivé (tgenabled = D)'
FROM pg_trigger tg
WHERE tg.tgenabled = 'D'
AND NOT tg.tgisinternal
ORDER BY type_objet, table_name;Resultado
type_objet | table_name | nom | probleme ------------+-------------+--------------------------+------------------------------------------------- constraint | invoices | fk_invoices_customer | NOT VALID — données existantes jamais vérifiées constraint | order_lines | chk_qty_positive | NOT VALID — données existantes jamais vérifiées trigger | customers | trg_audit_customers | désactivé (tgenabled = D) trigger | stock | trg_stock_recalc | désactivé (tgenabled = D) (4 rows) Remède : ALTER TABLE ... VALIDATE CONSTRAINT / ALTER TABLE ... ENABLE TRIGGER
SQLAuditContraintesTriggers