Retarder la vérification des contraintes¶
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 :
CREATE TABLE t1 (c1 INT PRIMARY KEY, c2 text);
CREATE TABLE t2 (c1 INT REFERENCES t1(c1), c2 text);
CREATE TABLE
INSERT INTO t1 VALUES(1,'a');
INSERT INTO t1 VALUES(2,'b');
INSERT INTO t2 VALUES(3,'a');
ERROR: insert or update on table "t2" violates foreign key constraint "t2_c1_fkey"
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 :
INSERT INTO t2 VALUES(1,'a');
INSERT 0 1
SELECT * FROM t1;
c1 | c2
----+----
1 | a
2 | b
(2 rows)
SELECT * FROM t2;
c1 | c2
----+----
1 | a
(1 row)
Que se passe t-il si on souhaite modifier la clé primaire de t1?
BEGIN;
BEGIN
UPDATE t1 SET c1=3 WHERE c1=1;
ERROR: update or delete on table "t1" violates foreign key constraint "t2_c1_fkey" on table "t2"
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)
BEGIN;
SET CONSTRAINTS ALL DEFERRED;
SET CONSTRAINTS
UPDATE t1 SET c1=3 WHERE c1=1;
ERROR: update or delete on table "t1" violates foreign key constraint "t2_c1_fkey" on table "t2"
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
ALTER TABLE t2 ALTER CONSTRAINT t2_c1_fkey DEFERRABLE;
BEGIN;
SET CONSTRAINTS ALL DEFERRED;
UPDATE t1 SET c1=3 WHERE c1=1;
UPDATE t2 SET c1=3 WHERE c1=1;
COMMIT;
SELECT * FROM t1;
c1 | c2
----+----
2 | b
3 | a
(2 rows)
SELECT * FROM t2;
c1 | c2
----+----
3 | a
(1 row)
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.
TRUNCATE t1 CASCADE;
NOTICE: truncate cascades to table "t2"
TRUNCATE TABLE
EXPLAIN ANALYZE 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).
BEGIN;
BEGIN
EXPLAIN analyse INSERT INTO T2 (c1,c2) (SELECT *,md5(i::text) FROM (SELECT * FROM generate_series(1,1000000)) i);
QUERY PLAN
----------------------------------------------------------------------------------------------
--------------------------------------------
Insert on t2 (cost=0.00..17500.00 rows=0 width=0) (actual time=5752.656..5752.965 rows=0 loo
ps=1)
-> Function Scan on generate_series (cost=0.00..17500.00 rows=1000000 width=36) (actual t
ime=188.272..2332.632 rows=1000000 loops=1)
Planning Time: 5.814 ms
Trigger for constraint t2_c1_fkey: time=10899.000 calls=1000000
Execution Time: 17647.282 ms
(5 rows)
Time: 17677.143 ms (00:17.677)
DELETE FROM t2 WHERE c1 <= 1000000;
DELETE 1000000
Time: 1273.689 ms (00:01.274)
EXPLAIN analyse INSERT INTO T2 (c1,c2) (SELECT *,md5(i::text) FROM (SELECT * FROM generate_series(1,1000000)) i);
QUERY PLAN
----------------------------------------------------------------------------------------------
--------------------------------------------
Insert on t2 (cost=0.00..17500.00 rows=0 width=0) (actual time=6182.739..6182.743 rows=0 loo
ps=1)
-> Function Scan on generate_series (cost=0.00..17500.00 rows=1000000 width=36) (actual t
ime=121.278..2608.385 rows=1000000 loops=1)
Planning Time: 0.332 ms
Trigger for constraint t2_c1_fkey: time=9426.951 calls=1000000
Execution Time: 16543.667 ms
(5 rows)
Time: 16544.855 ms (00:16.545)
COMMIT;
COMMIT
Time: 39.250 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 :
BEGIN;
BEGIN
Time: 1.455 ms
SET CONSTRAINTS ALL deferred ;
SET CONSTRAINTS
Time: 5.868 ms
EXPLAIN analyse INSERT INTO T2 (c1,c2) (SELECT *,md5(i::text) FROM (SELECT * FROM generate_series(1,1000000)) i);
QUERY PLAN
----------------------------------------------------------------------------------------------
--------------------------------------------
Insert on t2 (cost=0.00..17500.00 rows=0 width=0) (actual time=4694.060..4694.064 rows=0 loo
ps=1)
-> Function Scan on generate_series (cost=0.00..17500.00 rows=1000000 width=36) (actual t
ime=187.362..2174.116 rows=1000000 loops=1)
Planning Time: 0.773 ms
Execution Time: 4751.428 ms
(4 rows)
Time: 4763.592 ms (00:04.764)
DELETE FROM t2 WHERE c1 <= 1000000;
DELETE 2000000
Time: 3352.804 ms (00:03.353)
EXPLAIN analyse INSERT INTO T2 (c1,c2) (SELECT *,md5(i::text) FROM (SELECT * FROM generate_series(1,1000000)) i);
QUERY PLAN
----------------------------------------------------------------------------------------------
--------------------------------------------
Insert on t2 (cost=0.00..17500.00 rows=0 width=0) (actual time=6381.994..6381.997 rows=0 loo
ps=1)
-> Function Scan on generate_series (cost=0.00..17500.00 rows=1000000 width=36) (actual t
ime=108.108..2236.890 rows=1000000 loops=1)
Planning Time: 9.303 ms
Execution Time: 6408.423 ms
(4 rows)
Time: 6421.976 ms (00:06.422)
COMMIT;
COMMIT
Time: 9710.038 ms (00:09.710)
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 :
SELECT * FROM t1;
c1 | c2
----+----
1 | un
(1 row)
BEGIN;
BEGIN
SET constraints ALL deferred ;
SET CONSTRAINTS
INSERT INTO t2 VALUES ('2','un');
INSERT 0 1
COMMIT ;
ERROR: insert or update on table "t2" violates foreign key constraint "t2_c1_fkey"
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.
ALTER TABLE t2 DROP CONSTRAINT t2_c1_fkey ;
ALTER TABLE
SELECT * FROM t1;
c1 | c2
----+----
1 | un
(1 row)
SELECT * FROM t2;
c1 | c2
----+----
(0 rows)
INSERT INTO t2 VALUES (2,'deux');
INSERT 0 1
SELECT * FROM t2;
c1 | c2
----+------
2 | deux
(1 row)
ALTER TABLE t2 ADD CONSTRAINT t2_c1_fkey FOREIGN KEY (c1) REFERENCES t1(c1) NOT VALID;
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 :
INSERT INTO t2 VALUES (3,'trois');
ERROR: insert or update on table "t2" violates foreign key constraint "t2_c1_fkey"
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 :
En supprimant l’enregistrement posant problème, les contraintes peuvent être validées :