PostgreSQL : Deferrable constraints
Differ constraints verification
Note: This article was written during my activity at Dalibo
Postgres respects ACID properties, so it guarantees the consistency of the database: a transaction will bring the database from one valid state to another.
The data in the different tables is not independent but obeys semantic rules put in place when designing the conceptual model. The main purpose of integrity constraints is to ensure the consistency of the data between them, and therefore to ensure that they respect these semantic rules. If an insert, an update or a delete violates these rules, the transaction is purely and simply canceled.
Postgres performs constraint verification on each change (when constraints have been defined). It is also possible to delay the checking of the constraints at the end of the transaction, at the time of the commit. Thus, the verifications will only be produced on the effective changes between the delete, update and insert operations of the whole transaction.
Example:
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".
The line inserted in t2 must respect the foreign key constraint, t1 does not contain any line where c1 = 3. Let’s insert a correct line:
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)
What happens if you want to change the primary key of 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".
Constraint check is done during the UPDATE and triggers an error. It is possible to ask postgres to perform the constraint check at the end of the transaction with the order SET CONSTRAINTS ALL DEFERRED;. Also note that the keyword ALL can be replaced by the name of a constraint (if it is named and is 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".
It still does not work, in fact it must be specified that the application of the constraint can be delayed with the keyword 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)
In this case postgres accepts to do the verification at the end of the transaction.
Other interest, if a line is erased and reinserted in the same transaction, the checks on this line are not executed (because useless).
Example, we truncate tables and insert 1 million rows.
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);
We insert 100 000 lines, then we delete them to reinsert them again (without telling the engine to differ the constraint check).
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
Postgres will perform the checks at each insertion (about 18 seconds at each insertion).
Perform the same operation by delaying the constraint check:
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
Inserts are faster, but the commit is longer because postgres performs the constraint check. In the end, postgres performs a single check at the end of the transaction (commit). The operation is faster.
It is possible to create the constraint with another attribute DEFERRABLE INITIALLY DEFERRED which allows to get rid of SET CONSTRAINTS ALL DEFERRED. Also note that the keyword ALL can be replaced by the name of a constraint (if it is named and is DEFERRABLE)
If the modified rows do not respect the constraints, the transaction is canceled at commit time:
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".
On the other hand, the solution of disabling the constraints can indeed pose problems if one wishes to reactivate them and that the data does not allow it (constraint actually broken), this solution remains possible, within a transaction, however this causes an exclusive lock on the modified table during the whole transaction which can pose serious performance problems.
It is also possible to declare the constraint in NOT VALID. The creation of the constraint will be almost immediate, the data currently present will not be validated. However, any data inserted or updated later will be validated against these constraints.
Then we can ask postgres to check the constraints for all records with the order VALIDATE CONSTRAINT. This order results in an exclusive lock on the table. As of version 9.4, the lock is lighter: SHARE UPDATE EXCLUSIVE on the modified table. If the constraint is a foreign key, the lock is of type ROW SHARE on the referencing table.
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
t2 contains a row that does not respect the t2_c1_fkey constraint. No error has been reported because the check is only for new changes:
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".
Similarly, an error has been raised during the check:
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".
By deleting the problematic record, the constraints can be validated:
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
For seven years, I held various positions as a systems and network engineer. And it was in 2013 that I first started working with PostgreSQL. I held positions as a consultant, trainer, and also production DBA (Doctolib, Peopledoc).
This blog is a place where I share my findings, knowledge and talks.