PostgreSQL主外键延迟测试

__风__

已于 2023-03-08 14:13:37 修改

阅读量429

点赞数

分类专栏： PostgreSQL 文章标签： postgresql 数据库 mysql

于 2023-03-08 14:06:27 首次发布

本文链接：https://blog.csdn.net/liufeng1980423/article/details/129397758

版权

PostgreSQL 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

文章详细介绍了在PostgreSQL数据库中如何处理主外键约束的情况，包括在插入数据时遇到主表中不存在关联值的问题，以及如何通过禁用和启用触发器、使用notvalid参数和deferrable特性来灵活管理这些约束。

摘要由CSDN通过智能技术生成

测试过程

建主外键表

create table testp(id int primary key,value text);
CREATE TABLE
create table testf(id int primary key ,id1 int references testp(id),value text);
CREATE TABLE

建好表之后查看表信息
 \d testp
              数据表 "sde.testp"
 栏位  |  类型   | 校对规则 |  可空的  | 预设
-------+---------+----------+----------+------
 id    | integer |          | not null |
 value | text    |          |          |
索引：
    "testp_pkey" PRIMARY KEY, btree (id)
由引用：
    TABLE "testf" CONSTRAINT "testf_id1_fkey" FOREIGN KEY (id1) REFERENCES testp(id)

\d testf
              数据表 "sde.testf"
 栏位  |  类型   | 校对规则 |  可空的  | 预设
-------+---------+----------+----------+------
 id    | integer |          | not null |
 id1   | integer |          |          |
 value | text    |          |          |
索引：
    "testf_pkey" PRIMARY KEY, btree (id)
外部键(FK)限制：
    "testf_id1_fkey" FOREIGN KEY (id1) REFERENCES testp(id)

其constraint信息如下

select * from pg_constraint where conname='testf_id1_fkey' and conrelid='testf'::regclass;
-[ RECORD 1 ]-+---------------
oid           | 1178501
conname       | testf_id1_fkey
connamespace  | 1071669
contype       | f
condeferrable | f
condeferred   | f
convalidated  | t
conrelid      | 1178493
contypid      | 0
conindid      | 1178491
conparentid   | 0
confrelid     | 1178485
confupdtype   | a
confdeltype   | a
confmatchtype | s
conislocal    | t
coninhcount   | 0
connoinherit  | t
conkey        | {2}
confkey       | {1}
conpfeqop     | {96}
conppeqop     | {96}
conffeqop     | {96}
conexclop     |
conbin        |

插入记录相关记录

insert into testp values(1,'a');
INSERT 0 1
insert into testp values(2,'b');
INSERT 0 1
insert into testf values (1,1,'a');
INSERT 0 1
insert into testf values (2,1,'a');
INSERT 0 1
insert into testf values (3,2,'a');
INSERT 0 1

在某些特殊情况下需要先插入字表，但是关联字段的值并不存在与主表中该如何处理

1. 临时禁止掉trigger

实际上PostgreSQL实现主外键主要用的是trigger，只是trigger是PG内部实现的，如下

SELECT t.tgname, pg_catalog.pg_get_triggerdef(t.oid, true), t.tgenabled, t.tgisinternal FROM pg_catalog.pg_trigger t WHERE t.tgrelid = 'testf'::regclass AND (NOT t.tgisinternal OR (t.tgisinternal) OR EXISTS (SELECT 1 FROM pg_catalog.pg_depend WHERE objid = t.oid AND refclassid = 'pg_catalog.pg_trigger'::pg_catalog.regclass))ORDER BY 1;
-[ RECORD 1 ]
tgname            | RI_ConstraintTrigger_c_1178504
pg_get_triggerdef | CREATE CONSTRAINT TRIGGER "RI_ConstraintTrigger_c_1178504" AFTER INSERT ON testf FROM testp NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE FUNCTION "RI_FKey_check_ins"()
tgenabled         | O
tgisinternal      | t
-[ RECORD 2 ]
tgname            | RI_ConstraintTrigger_c_1178505
pg_get_triggerdef | CREATE CONSTRAINT TRIGGER "RI_ConstraintTrigger_c_1178505" AFTER UPDATE ON testf FROM testp NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE FUNCTION "RI_FKey_check_upd"()
tgenabled         | O
tgisinternal      | t

2. alter table testf alter constraint all; //需要superuser权限

alter table testf disable trigger all;
ALTER TABLE
\d testf
              数据表 "sde.testf"
 栏位  |  类型   | 校对规则 |  可空的  | 预设
-------+---------+----------+----------+------
 id    | integer |          | not null |
 id1   | integer |          |          |
 value | text    |          |          |
索引：
    "testf_pkey" PRIMARY KEY, btree (id)
外部键(FK)限制：
    "testf_id1_fkey" FOREIGN KEY (id1) REFERENCES testp(id)
禁用内部触发器：
    "RI_ConstraintTrigger_c_1178504" AFTER INSERT ON testf FROM testp NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE FUNCTION "RI_FKey_check_ins"()
    "RI_ConstraintTrigger_c_1178505" AFTER UPDATE ON testf FROM testp NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE FUNCTION "RI_FKey_check_upd"()

3. 插入不满足条件的记录

insert into testf values (4,3,'b');
INSERT 0 1

select * from testp;
 id | value
----+-------
  1 | a
  2 | b
(2 行记录)


select * from testf;
 id | id1 | value
----+-----+-------
  1 |   1 | a
  2 |   1 | a
  3 |   2 | a
  4 |   3 | b
(4 行记录)

4. 然后再启用trigger

alter table testf enable trigger all;
ALTER TABLE

第二种情况，如果对已有数据的两张表要建立主外键关系

默认建立主外键会扫描已有的记录来确定是否有不符合条件的记录，这一过程hang住后续的update操作，因此如果表很大，会对业务产生比较大的影响

可以使用not valid参数来避开对已有数据的验证

test8=# select * from testp;
 id | value
----+-------
  1 | a
  2 | b
(2 行记录)


test8=# select * from testf;
 id | id1 | value
----+-----+-------
  1 |   1 | a
  2 |   1 | a
  3 |   2 | a
  4 |   3 | b

test8=# \d testf
              数据表 "sde.testf"
 栏位  |  类型   | 校对规则 |  可空的  | 预设
-------+---------+----------+----------+------
 id    | integer |          | not null |
 id1   | integer |          |          |
 value | text    |          |          |
索引：
    "testf_pkey" PRIMARY KEY, btree (id)
外部键(FK)限制：
    "testf_id1_fkey" FOREIGN KEY (id1) REFERENCES testp(id)

test8=# alter table testf drop constraint testf_id1_fkey;
ALTER TABLE

//默认直接验证已有数据
test8=# alter table testf add constraint testf_id1_fkey foreign key(id1) references testp(id);
ERROR:  insert or update on table "testf" violates foreign key constraint "testf_id1_fkey"
描述:  Key (id1)=(3) is not present in table "testp".

//需要添加not valid参数，跳过现有数据验证
test8=# alter table testf add constraint testf_id1_fkey foreign key(id1) references testp(id) not valid;
ALTER TABLE

test8=# \d testf
              数据表 "sde.testf"
 栏位  |  类型   | 校对规则 |  可空的  | 预设
-------+---------+----------+----------+------
 id    | integer |          | not null |
 id1   | integer |          |          |
 value | text    |          |          |
索引：
    "testf_pkey" PRIMARY KEY, btree (id)
外部键(FK)限制：
    "testf_id1_fkey" FOREIGN KEY (id1) REFERENCES testp(id) NOT VALID

//新修改还是会影响
test8=# insert into testf values (5,3,'d');
ERROR:  insert or update on table "testf" violates foreign key constraint "testf_id1_fkey"
描述:  Key (id1)=(3) is not present in table "testp".

//对已有数据进行validate constraint进行处理
test8=# alter table testf validate constraint testf_id1_fkey;
ERROR:  insert or update on table "testf" violates foreign key constraint "testf_id1_fkey"
描述:  Key (id1)=(3) is not present in table "testp".


test8=# delete from testf where id1=3;
DELETE 1
test8=# alter table testf validate constraint testf_id1_fkey;
ALTER TABLE
test8=# \d testf
              数据表 "sde.testf"
 栏位  |  类型   | 校对规则 |  可空的  | 预设
-------+---------+----------+----------+------
 id    | integer |          | not null |
 id1   | integer |          |          |
 value | text    |          |          |
索引：
    "testf_pkey" PRIMARY KEY, btree (id)
外部键(FK)限制：
    "testf_id1_fkey" FOREIGN KEY (id1) REFERENCES testp(id)

另外通过constraint的deferrable也可以解决上述问题

测试过程如下：

alter table testf alter constraint testf_id1_fkey deferrable;
ALTER TABLE
test8=# begin;
BEGIN
test8=# set constraints all deferred;
SET CONSTRAINTS
test8=# insert into testf values (6,5,'e');
INSERT 0 1
test8=# insert into testp values (5,'d');
INSERT 0 1
test8=# commit;
COMMIT

test8=# select * from testp;
 id | value
----+-------
  1 | a
  2 | b
  5 | d
(3 行记录)


test8=# select * from testf;
 id | id1 | value
----+-----+-------
  1 |   1 | a
  2 |   1 | a
  3 |   2 | a
  6 |   5 | e
(4 行记录)

如果不想使用set constraints在session级别进行控制可以将表直接设置为DEFERRABLE INITIALLY DEFERRED
\d testf
              数据表 "sde.testf"
 栏位  |  类型   | 校对规则 |  可空的  | 预设
-------+---------+----------+----------+------
 id    | integer |          | not null |
 id1   | integer |          |          |
 value | text    |          |          |
索引：
    "testf_pkey" PRIMARY KEY, btree (id)
外部键(FK)限制：
    "testf_id1_fkey" FOREIGN KEY (id1) REFERENCES testp(id) DEFERRABLE INITIALLY DEFERRED

test8=# begin;
BEGIN
test8=# insert into testf values(7,7,'g');
INSERT 0 1
test8=# insert into testp values(7,'g');
INSERT 0 1
test8=# commit;
COMMIT