Retarder la vérification des contraintes

Remarque : Cet article a été rédigé durant le cadre de mon activité chez Dalibo.

Postgres respecte le modèle ACID, ainsi il garantie la cohérence de la base : une transaction amène la base d’un état stable à un autre.

Les données dans les différentes tables ne sont pas indépendantes mais obéissent à des règles sémantiques mises en place au moment de la conception du modèle conceptuel des données. Les contraintes d’intégrité ont pour principal objectif de garantir la cohérence des données entre elles, et donc de veiller à ce qu’elles respectent ces règles sémantiques. Si une insertion, une mise à jour ou une suppression viole ces règles, l’opération est purement et simplement annulée.

Le moteur effectue la vérification des contraintes à chaque modification (lorsque des contraintes ont été définies). Il est également possible de retarder la vérification des contraintes à la fin de la transaction, au moment du commit. Ainsi, les vérifications ne seront produites que sur les changements effectifs entre les opérations de delete, update et insert de la transaction.

Exemple :

La ligne insérée dans t2 doit respecter la contrainte d’intégrité référentielle, la table t1 ne contient aucune ligne où c1=3. Insérons une ligne correcte :

Que se passe t-il si on souhaite modifier la clé primaire de t1?

La vérification de la contrainte se fait lors de l’UPDATE et déclenche une erreur. Il est possible de demander au moteur d’effectuer la vérification des contraintes à la fin de la transaction avec l’ordre SET CONSTRAINTS ALL DEFERRED;. A noter également que le mot clef ALL peut-être remplacé par le nom d’une contrainte (si elle est nommée et est DEFERRABLE)

Ça ne fonctionne toujours pas, en effet il faut préciser que que l’application de la contrainte peut être retardée avec le mot clé DEFERRABLE

Dans ce cas le moteur accepte de faire la vérification en fin de transaction.

Autre intérêt, si une ligne est effacée et réinsérée dans la même transaction, les vérifications sur cette ligne ne sont pas exécutées (car inutiles).

Exemple, on vide les tables puis on insère 1 million de lignes.

Ensuite on insère 100 000 lignes, puis on les supprime pour les réinsérer à nouveau (sans indiquer au moteur de différer la vérification de contraintes).

Le moteur va effectuer les vérifications à chaque insertion (environ 18 secondes à chaque insertion).

Effectuons la même opération en retardant la vérification des contraintes :

Les insertions sont plus rapides, en revanche le commit est plus long car le moteur effectue la vérification des contraintes. Au final, le moteur effectue une seule vérification à la fin de la transaction (commit). L’opération est donc plus rapide.

Il est possible de créer la contrainte avec un autre attribut DEFERRABLE INITIALLY DEFERRED qui permet de s’affranchir du SET CONSTRAINTS ALL DEFERRED. A noter également que le mot clef ALL peut-être remplacé par le nom d’une contrainte (si elle est nommée et est DEFERRABLE)

Si les enregistrements modifiés ne respectent pas les contraintes, la transaction est annulée au moment du commit :

D’autre part, la solution de désactivation des contraintes peut effectivement poser des problèmes si on souhaite les réactiver et que les données ne le permettent pas (contrainte effectivement rompue), cette solution reste possible, au sein d’une transaction, toutefois cela provoque un verrouillage exclusif sur la table modifiée pendant toute la transaction ce qui peut poser de sérieux problèmes de performance.

Il est également possible de déclarer la contrainte en NOT VALID. La création de la contrainte sera quasi immédiate, les données actuellement présentes ne seront pas validées. Cependant, toutes les données insérées ou mises à jour par la suite seront validées vis à vis de ces contraintes.

Ensuite on peut demander au moteur de faire la vérification des contraintes pour l’intégralité des enregistrements avec l’ordre VALIDATE CONSTRAINT. Cet ordre entraîne un verrou exclusif sur la table. A partir de la version 9.4 le verrou est plus léger : SHARE UPDATE EXCLUSIVE sur la table modifiée. Si la contrainte est une clé étrangère, le verrou est de type ROW SHARE sur la table référente.

La table t2 contient un enregistrement ne respectant pas la contrainte t2_c1_fkey. Aucune erreur n’est remontée car la vérification se fait seulement pour les nouvelles modifications :

De même, une erreur est bien remontée lors de la vérification de contrainte :

En supprimant l’enregistrement posant problème, les contraintes peuvent être validées :

Share

Ecrire un commentaire

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url=""> 

requis