PostgreSQL : Retarder la vérification des contraintes
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 :
1CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 text);
2 CREATE TABLE t2 (c1 INT REFERENCES t1(c1), c2 text);
3
4INSERT INTO t1 VALUES(1,'a');
5 INSERT INTO t1 VALUES(2,'b');
6
7INSERT INTO t2 VALUES(3,'a');
8 ERROR: INSERT OR UPDATE ON TABLE "t2" violates FOREIGN KEY CONSTRAINT "t2_c1_fkey"
9 DETAIL: KEY (c1)=(3) IS NOT present IN TABLE "t1".
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 :
1INSERT INTO t2 VALUES(1,'a');
2
3SELECT * FROM t1;
4 c1 | c2
5 ----+----
6 1 | a
7 2 | b
8 (2 ROWS)
9
10SELECT * FROM t2;
11 c1 | c2
12 ----+----
13 1 | a
14 (1 ROW)
Que se passe t-il si on souhaite modifier la clé primaire de t1?
1BEGIN;
2 UPDATE t1 SET c1=3 WHERE c1=1;
3 ERROR: UPDATE OR DELETE ON TABLE "t1" violates FOREIGN KEY CONSTRAINT "t2_c1_fkey" ON TABLE "t2"
4 DETAIL: KEY (c1)=(1) IS still referenced FROM TABLE "t2".
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)
1BEGIN;
2 SET CONSTRAINTS ALL DEFERRED;
3 UPDATE t1 SET c1=3 WHERE c1=1;
4 ERROR: UPDATE OR DELETE ON TABLE "t1" violates FOREIGN KEY CONSTRAINT "t2_c1_fkey" ON TABLE "t2"
5 DETAIL: KEY (c1)=(1) IS still referenced FROM TABLE "t2".
Ç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
1ALTER TABLE t2 ALTER CONSTRAINT t2_c1_fkey DEFERRABLE;
2
3BEGIN;
4 SET CONSTRAINTS ALL DEFERRED;
5 UPDATE t1 SET c1=3 WHERE c1=1;
6 UPDATE t2 SET c1=3 WHERE c1=1;
7 commit;
8
9SELECT * FROM t1;
10 c1 | c2
11 ----+----
12 2 | b
13 3 | a
14 (2 ROWS)
15
16SELECT * FROM t2;
17 c1 | c2
18 ----+----
19 3 | a
20 (1 ROW)
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.
1TRUNCATE t1 cascade;
2 NOTICE: TRUNCATE cascades TO TABLE "t2"
3 TRUNCATE TABLE
4
5EXPLAIN analyse INSERT INTO T1 (c1,c2) (SELECT *,md5(i::text) FROM (SELECT * FROM generate_series(1,1000000)) i);
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).
1BEGIN;
2
3EXPLAIN analyse INSERT INTO T2 (c1,c2) (SELECT *,md5(i::text) FROM (SELECT * FROM generate_series(1,1000000)) i);
4
5QUERY PLAN
6 ------------------------------------------------------------------------------------------------------------------------------------
7 INSERT ON t2 (cost=0.00..17.50 ROWS=1000 width=36) (actual TIME=3451.308..3451.308 ROWS=0 loops=1)
8 -> FUNCTION Scan ON generate_series (cost=0.00..17.50 ROWS=1000 width=36) (actual TIME=170.218..1882.406 ROWS=1000000 loops=1)
9 Planning TIME: 0.054 ms
10 TRIGGER FOR CONSTRAINT t2_c1_fkey: TIME=16097.543 calls=1000000
11 Execution TIME: 19654.595 ms
12 (5 ROWS)
13
14TIME: 19654.971 ms
15
16DELETE FROM t2 WHERE c1 <= 1000000;
17 DELETE 1000000
18 TIME: 2088.318 ms
19
20EXPLAIN analyse INSERT INTO T2 (c1,c2) (SELECT *,md5(i::text) FROM (SELECT * FROM generate_series(1,1000000)) i);
21 QUERY PLAN
22 ------------------------------------------------------------------------------------------------------------------------------------
23 INSERT ON t2 (cost=0.00..17.50 ROWS=1000 width=36) (actual TIME=3859.265..3859.265 ROWS=0 loops=1)
24 -> FUNCTION Scan ON generate_series (cost=0.00..17.50 ROWS=1000 width=36) (actual TIME=169.826..1845.421 ROWS=1000000 loops=1)
25 Planning TIME: 0.054 ms
26 TRIGGER FOR CONSTRAINT t2_c1_fkey: TIME=14600.258 calls=1000000
27 Execution TIME: 18558.108 ms
28 (5 ROWS)
29
30TIME: 18558.386 ms
31 commit;
32 COMMIT
33 TIME: 8.563 ms
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 :
1BEGIN;
2 BEGIN
3 TIME: 0.130 ms
4
5SET CONSTRAINTS ALL deferred ;
6 SET CONSTRAINTS
7
8EXPLAIN analyse INSERT INTO T2 (c1,c2) (SELECT *,md5(i::text) FROM (SELECT * FROM generate_series(1,1000000)) i);
9
10QUERY PLAN
11 ------------------------------------------------------------------------------------------------------------------------------------
12 INSERT ON t2 (cost=0.00..17.50 ROWS=1000 width=36) (actual TIME=3241.172..3241.172 ROWS=0 loops=1)
13 -> FUNCTION Scan ON generate_series (cost=0.00..17.50 ROWS=1000 width=36) (actual TIME=169.770..1831.893 ROWS=1000000 loops=1)
14 Planning TIME: 0.096 ms
15 Execution TIME: 3269.624 ms
16 (4 ROWS)
17
18TIME: 3270.018 ms
19
20DELETE FROM t2 WHERE c1 <= 1000000;
21 DELETE 2000000
22 TIME: 2932.070 ms
23
24EXPLAIN analyse INSERT INTO T2 (c1,c2) (SELECT *,md5(i::text) FROM (SELECT * FROM generate_series(1,1000000)) i);
25 QUERY PLAN
26 ------------------------------------------------------------------------------------------------------------------------------------
27 INSERT ON t2 (cost=0.00..17.50 ROWS=1000 width=36) (actual TIME=3181.294..3181.294 ROWS=0 loops=1)
28 -> FUNCTION Scan ON generate_series (cost=0.00..17.50 ROWS=1000 width=36) (actual TIME=170.137..1811.889 ROWS=1000000 loops=1)
29 Planning TIME: 0.055 ms
30 Execution TIME: 3209.712 ms
31 (4 ROWS)
32
33TIME: 3210.067 ms
34
35commit;
36 COMMIT
37 TIME: 16630.155 ms
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 :
1SELECT * FROM t1;
2 c1 | c2
3 ----+----
4 1 | un
5 (1 ROW)
6
7BEGIN;
8 SET constraints ALL deferred ;
9 SET CONSTRAINTS
10 anayrat=# INSERT INTO t2 VALUES ('2','un');
11 INSERT 0 1
12 anayrat=# commit;
13 ERROR: INSERT OR UPDATE ON TABLE "t2" violates FOREIGN KEY CONSTRAINT "t2_c1_fkey"
14 DETAIL: KEY (c1)=(2) IS NOT present IN TABLE "t1".
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.
1ALTER TABLE t2 DROP CONSTRAINT t2_c1_fkey ;
2 ALTER TABLE
3 SELECT * FROM t1;
4 c1 | c2
5 ----+----
6 1 | un
7 (1 ROW)
8
9SELECT * FROM t2;
10 c1 | c2
11 ----+----
12 (0 ROWS)
13
14INSERT INTO t2 VALUES (2,'deux');
15 INSERT 0 1
16 SELECT * FROM t2;
17 c1 | c2
18 ----+------
19 2 | deux
20 (1 ROW)
21
22ALTER TABLE t2 ADD CONSTRAINT t2_c1_fkey FOREIGN KEY (c1) REFERENCES t1(c1) NOT VALID;
23 ALTER TABLE
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 :
1INSERT INTO t2 VALUES (3,'trois');
2 ERROR: INSERT OR UPDATE ON TABLE "t2" violates FOREIGN KEY CONSTRAINT "t2_c1_fkey"
3 DETAIL: KEY (c1)=(3) IS NOT present IN TABLE "t1".
De même, une erreur est bien remontée lors de la vérification de contrainte :
1ALTER TABLE t2 VALIDATE CONSTRAINT t2_c1_fkey ;
2 ERROR: INSERT OR UPDATE ON TABLE "t2" violates FOREIGN KEY CONSTRAINT "t2_c1_fkey"
3 DETAIL: KEY (c1)=(2) IS NOT present IN TABLE "t1".
En supprimant l’enregistrement posant problème, les contraintes peuvent être validées :
1DELETE FROM t2 WHERE t2.c1 NOT IN (SELECT c1 FROM t1);
2 DELETE 1
3 ALTER TABLE t2 VALIDATE CONSTRAINT t2_c1_fkey ;
4 ALTER TABLE
J’ai occupé pendant 7 ans divers postes d’ingénieur système et réseau. Et c’est à partir de 2013 que j’ai commencé à mettre les doigts dans PostgreSQL. J’ai occupé des postes de consultant, formateur, mais également DBA de production (Doctolib, Peopledoc).
Ce blog me permet de partager mes connaissances et trouvailles, ainsi que mes interventions et conférences.