PostgreSQL主外键延迟测试

测试过程

建主外键表

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