代码改变世界

PostgreSQL中enable、disable和validate外键约束

  abce  阅读(3597)  评论(0)  编辑  收藏  举报
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
postgres=# create table t1(a int primary key,b text,c date);
CREATE TABLE
postgres=# create table t2(a int primary key,b int references t1(a),c text);
CREATE TABLE
postgres=# insert into t1 (a,b,c) values(1,'aa',now());
INSERT 0 1
postgres=# insert into t1 (a,b,c) values(2,'bb',now());
INSERT 0 1
postgres=# insert into t2 (a,b,c) values (1,1,'aa');
INSERT 0 1
postgres=# insert into t2 (a,b,c) values (2,2,'aa');
INSERT 0 1
postgres=# \d t1
                 Table "public.t1"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 a      | integer |           | not null |
 b      | text    |           |          |
 c      | date    |           |          |
Indexes:
    "t1_pkey" PRIMARY KEY, btree (a)
Referenced by:
    TABLE "t2" CONSTRAINT "t2_b_fkey" FOREIGN KEY (b) REFERENCES t1(a)
 
postgres=# \d t2
                 Table "public.t2"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 a      | integer |           | not null |
 b      | integer |           |          |
 c      | text    |           |          |
Indexes:
    "t2_pkey" PRIMARY KEY, btree (a)
Foreign-key constraints:
    "t2_b_fkey" FOREIGN KEY (b) REFERENCES t1(a)
 
postgres=#

假设我们想通过脚本向表中加载一些数据。因为我们不知道脚本中加载的顺序，我们决定将表t2上的外键约束禁用掉，在数据加载之后载开启外键约束：

?
1
2
3
postgres=# alter table t2 disable trigger all;
ALTER TABLE
postgres=#

这里看起来可能有点奇怪，但是它的确禁用了外键约束。如果有其他外键约束，当然也是被禁用了。

我们再来看看表t2：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
postgres=# \d t2
                 Table "public.t2"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 a      | integer |           | not null |
 b      | integer |           |          |
 c      | text    |           |          |
Indexes:
    "t2_pkey" PRIMARY KEY, btree (a)
Foreign-key constraints:
    "t2_b_fkey" FOREIGN KEY (b) REFERENCES t1(a)
Disabled internal triggers:
    "RI_ConstraintTrigger_c_75213" AFTER INSERT ON t2 FROM t1 NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_check_ins"()
    "RI_ConstraintTrigger_c_75214" AFTER UPDATE ON t2 FROM t1 NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_check_upd"()
 
postgres=#

关键字all将表上的其他内部触发器也禁用了，需要superser才可以执行成功。

?
1
2
3
4
5
6
7
8
9
10
11
postgres=# create user abce with login password 'abce';
CREATE ROLE
postgres=# \c postgres abce
You are now connected to database "postgres" as user "abce".
postgres=> create table t3 ( a int primary key, b text, c date);
CREATE TABLE
postgres=> create table t4 ( a int primary key, b int references t3(a), c text);
CREATE TABLE
postgres=> alter table t4 disable trigger all;
ERROR:  permission denied: "RI_ConstraintTrigger_c_75235" is a system trigger
postgres=>

那作为普通用户，该如何禁用触发器呢？

?
1
postgres=> alter table t4 disable trigger user;

具体语法为：

?
1
DISABLE TRIGGER [ trigger_name | ALL | USER ]

　　

回到t1、t2表。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
postgres=# select * from t1;
 a | b  |     c     
---+----+------------
 1 | aa | 2020-11-04
 2 | bb | 2020-11-04
(2 rows)
 
postgres=# select * from t2;
 a | b | c 
---+---+----
 1 | 1 | aa
 2 | 2 | aa
(2 rows)
 
postgres=# insert into t2 (a,b,c) values (3,3,'cc');
INSERT 0 1
postgres=#

这里插入了一条在t1中不匹配的记录，但是插入成功了。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
postgres=# alter table t2 enable trigger all;
ALTER TABLE
postgres=# \d t2
                 Table "public.t2"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 a      | integer |           | not null |
 b      | integer |           |          |
 c      | text    |           |          |
Indexes:
    "t2_pkey" PRIMARY KEY, btree (a)
Foreign-key constraints:
    "t2_b_fkey" FOREIGN KEY (b) REFERENCES t1(a)
 
postgres=# alter table t2 validate constraint t2_b_fkey;
ALTER TABLE
postgres=#

是不是很惊讶，PostgreSQL没有报告不匹配的记录。为什么呢？

查看一个pg_constraint：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
postgres=# select * from pg_constraint where conname='t2_b_fkey' and conrelid='t2'::regclass;
-[ RECORD 1 ]-+----------
conname       | t2_b_fkey
connamespace  | 2200
contype       | f
condeferrable | f
condeferred   | f
convalidated  | t
conrelid      | 75202
contypid      | 0
conindid      | 75200
conparentid   | 0
confrelid     | 75194
confupdtype   | a
confdeltype   | a
confmatchtype | s
conislocal    | t
coninhcount   | 0
connoinherit  | t
conkey        | {2}
confkey       | {1}
conpfeqop     | {96}
conppeqop     | {96}
conffeqop     | {96}
conexclop     |
conbin        |
consrc        |
 
postgres=#

convalidated字段的值为t，表明该外键约束还是有效的。

哪怕是我们再次将其disable，仍然会显示是有效的：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
postgres=# alter table t2 disable trigger all;
ALTER TABLE
postgres=# select * from pg_constraint where conname='t2_b_fkey' and conrelid='t2'::regclass;
-[ RECORD 1 ]-+----------
conname       | t2_b_fkey
connamespace  | 2200
contype       | f
condeferrable | f
condeferred   | f
convalidated  | t
conrelid      | 75202
contypid      | 0
conindid      | 75200
conparentid   | 0
confrelid     | 75194
confupdtype   | a
confdeltype   | a
confmatchtype | s
conislocal    | t
coninhcount   | 0
connoinherit  | t
conkey        | {2}
confkey       | {1}
conpfeqop     | {96}
conppeqop     | {96}
conffeqop     | {96}
conexclop     |
conbin        |
consrc        |
 
postgres=#

这表明当我们开启(enable)内部触发器的时候，PostgreSQL不会验证(validate)约束，因此也不会验证数据是否会有冲突，因为外键约束的状态始终是有效的。

 

我们需要做的是先将其变成无效的：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
postgres=# alter table t2 alter CONSTRAINT t2_b_fkey not valid;
ERROR:  ALTER CONSTRAINT statement constraints cannot be marked NOT VALID
## 需要先将外键删掉，然后重建外键约束并将其状态设置成无效
 
postgres=# alter table t2 drop constraint t2_b_fkey;
ALTER TABLE
postgres=# delete from t2 where a in (3);
DELETE 1
postgres=# alter table t2 add constraint t2_b_fkey foreign key (b) references t1(a) not valid;
ALTER TABLE
postgres=# \d t2
                 Table "public.t2"
 Column |  Type   | Collation | Nullable | Default
--------+---------+-----------+----------+---------
 a      | integer |           | not null |
 b      | integer |           |          |
 c      | text    |           |          |
Indexes:
    "t2_pkey" PRIMARY KEY, btree (a)
Foreign-key constraints:
    "t2_b_fkey" FOREIGN KEY (b) REFERENCES t1(a) NOT VALID

现在，可以看到状态是无效的了：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
postgres=# select * from pg_constraint where conname='t2_b_fkey' and conrelid='t2'::regclass;
-[ RECORD 1 ]-+----------
conname       | t2_b_fkey
connamespace  | 2200
contype       | f
condeferrable | f
condeferred   | f
convalidated  | f
conrelid      | 75202
contypid      | 0
conindid      | 75200
conparentid   | 0
confrelid     | 75194
confupdtype   | a
confdeltype   | a
confmatchtype | s
conislocal    | t
coninhcount   | 0
connoinherit  | t
conkey        | {2}
confkey       | {1}
conpfeqop     | {96}
conppeqop     | {96}
conffeqop     | {96}
conexclop     |
conbin        |
consrc        |
 
postgres=#

继续插入数据：

?
1
2
3
4
postgres=# insert into t2(a,b,c) values (3,3,'cc');
ERROR:  insert or update on table "t2" violates foreign key constraint "t2_b_fkey"
DETAIL:  Key (b)=(3) is not present in table "t1".
postgres=#

是不是更惊讶了？创建了一个无效的约束，只是通知PostgreSQL

不要扫描整个表去验证所有的行记录是否有效。对于新插入或更新的行，仍然会检查是否满足约束条件，这就是为什么上面插入失败了。

 

我们该怎么做呢？

1.删除所有的外键

2.加载数据

3.重新创建外键，但是将其状态设置成无效的，从而避免扫描整个表。之后，新的数据会被验证了

4.在系统负载低的时候开启约束验证(validate the constraints)

 

另一种方法是：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
postgres=# alter table t2 alter constraint t2_b_fkey deferrable;
ALTER TABLE
postgres=# begin;
BEGIN
postgres=# set constraints all deferred;
SET CONSTRAINTS
postgres=# insert into t2 (a,b,c) values (3,3,'cc');
INSERT 0 1
postgres=# insert into t2 (a,b,c) values (4,4,'dd');
INSERT 0 1
postgres=# insert into t1 (a,b,c) values (3,'cc',now());
INSERT 0 1
postgres=# insert into t1 (a,b,c) values (4,'dd',now());
INSERT 0 1
postgres=# commit;
COMMIT

这样做不好的方面是，在下一次提交时才起作用，因此，你需要将所有的工作放到一个事务中。

 

本文的关键点是，下面的假设将验证你的数据是错误的:

?
1
2
3
4
5
6
7
postgres=# alter table t2 disable trigger all;
ALTER TABLE
postgres=# insert into t2 (a,b,c) values (5,5,'ee');
INSERT 0 1
postgres=# alter table t2 enable trigger all;
ALTER TABLE
postgres=#

这只会验证新的数据，但是并不保证所有的数据都满足约束：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
postgres =  # insert into t2 (a,b,c) values (6,6,'ff');
ERROR: insert or update on table "t2" violates foreign key constraint "t2_b_fkey"
DETAIL: Key(b) = (6) is not present in table "t1".
postgres =  # select * from t2 where b = 5;
a | b | c
---+---+----
5 | 5 | ee
(1 row)
 
postgres =  # select * from t1 where a = 5;
a | b | c
---+---+---
(0 rows)

 

最终，还有一种方式来解决，直接修改pg_constraint目录表。但是并建议用户这么做！

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
postgres=# delete from t2 where b = 5;
DELETE 1
postgres=# delete from t2 where b = 5;
DELETE 1
postgres=# alter table t2 disable trigger all;
ALTER TABLE
postgres=# insert into t2 values (5,5,'ee');
INSERT 0 1
postgres=# alter table t2 enable trigger all;
ALTER TABLE
postgres=# update pg_constraint set convalidated = false where conname = 't2_b_fkey' and conrelid = 't2'::regclass;
UPDATE 1
postgres=# alter table t2 validate constraint t2_b_fkey;
ERROR:  insert or update on table "t2" violates foreign key constraint "t2_b_fkey"
DETAIL:  Key (b)=(5) is not present in table "t1".
postgres=#

　　

 

编辑推荐：
· AI与.NET技术实操系列（二）：开始使用ML.NET
· 记一次.NET内存居高不下排查解决与启示
· 探究高空视频全景AR技术的实现原理
· 理解Rust引用及其生命周期标识（上）
· 浏览器原生「磁吸」效果！Anchor Positioning 锚点定位神器解析
阅读排行：
· DeepSeek 开源周回顾「GitHub 热点速览」
· 物流快递公司核心技术能力-地址解析分单基础技术分享
· .NET 10首个预览版发布：重大改进与新特性概览！
· AI与.NET技术实操系列（二）：开始使用ML.NET
· .NET10 - 预览版1新功能体验（一）
历史上的今天：
2018-11-14 service_names配置不正确，导致dg创建失败
2015-11-14 Remote 'attachhome' failed on nodes:XXX
2015-11-14 RAC安装GI时运行root.sh脚本结果
2015-11-14 clscfg.bin: error while loading shared libraries: libcap.so.1:
2015-11-14 RAC安装重新运行root.sh
2015-11-14 libXext.so.6 libXp.so.6 libXt.so.6 is needed by openmotif21-2.1.30-11.el7.i686
点击右上角即可分享
微信分享提示
AI FOR CODE 大赛