欢迎关注公众号, 持续发布PostgreSQL、openGauas/MogDB等数据库干货文章。

本期分享如下几个案例：

• 临时取消外键检查

• 设置约束延迟生效

• 校验json字符串

• pg_cron定时任务更新

1.如何在会话中临时取消外键检查

下面t1表的id字段被t2表的id字段外键引用

CREATE TABLE t1(id int NOT NULL UNIQUE);
CREATE TABLE t2(id int REFERENCES t1(id));

INSERT INTO t1 values(1),(2);
INSERT INTO t2 values(1);

此时如果要对父表t1进行数据删除，可能会失败，因为t1表的id字段已经被外键引用：

postgres=# DELETE FROM t1 WHERE id = 1;
ERROR:  update or delete on table "t1" violates foreign key constraint "t2_id_fkey" on table "t2"
DETAIL:  Key (id)=(1) is still referenced from table "t2".

如何在会话中临时取消外键检查，以便调整父表数据。

设置参数session_replication_role为replica，可以在会话内禁用外键约束：

SET session_replication_role = 'replica';

注意：下面对t1表设置DISABLE TRIGGER也可禁用外键，设置会永久生效。

ALTER TABLE t1 DISABLE TRIGGER ALL;

2.约束如何设置延迟生效

执行数据批量更新操作时，暂时允许违反约束条件，直到事务提交时才进行约束条件检查和生效，这就是约束延迟生效功能。它可以帮助用户在数据更新时，避免由于约束条件限制而导致更新失败。

约束的延迟生效功能有如下三种设置：

• 不延迟(NOT DEFERRABLE)

• 语句级延迟生效(DEFERRABLE INITIALLY IMMEDIATE)

• 事务级延迟生效(DEFERRABLE INITIALLY DEFERRED)

延迟生效功能仅影响主外键、唯一约束和排他约束，而非空约束和检查约束暂不支持。

主键约束的延迟示例如下：

CREATE TABLE t(id int primary key not deferrable);

默认是不延迟，可以显式带上关键字not deferrable。

语句级延迟生效示例如下(每一条语句执行后进行约束检查)：

CREATE TABLE t(id int primary key deferrable initially immediate);

事务级延迟生效示例如下(事务结束时才检查约束)：

CREATE TABLE t(id int primary key deferrable initially deferred); 

语句级延迟生效和事务级延迟生效可以在transaction内使用SET CONSTRAINTS命令进行切换：

postgres=# start transaction ;
START TRANSACTION
postgres=*# SET CONSTRAINTS t_pkey DEFERRED ;
SET CONSTRAINTS
postgres=*# SET CONSTRAINTS t_pkey IMMEDIATE ;
SET CONSTRAINTS

表的约束延迟生效选项可以通过系统表pg_constraint进行查询：

• condeferred字段，字段定义为DEFERRED时为true

• condeferrable字段，字段的延迟生效选项是否允许事务内切换

3.如何校验json字符串是否合法

PG 15beta版本发布了大量json的新功能函数，但正式发布时撤回了，当时因为没有提供对json类型进行检测的内部函数，直到PG 16增加了如下两个系统函数：

• pg_input_is_valid：检测数据类型与输入值是否有效

• pg_input_error_info：如果检测失败可查看错误信息

数据类型检测函数可以有效避免类型转换失败而导致事务中断，与此相关，大量json新功能函数也正提交到17。

下面是使用pg_input_is_valid函数检测json字符串的示例：

postgres=# select pg_input_is_valid('{key:"val"}','json');
 pg_input_is_valid 
-------------------
 f
(1 row)

postgres=# select pg_input_is_valid('{"key":"val"}','json');
 pg_input_is_valid 
-------------------
 t
(1 row)

4.pg_cron定时任务插件更新

来自墨天轮仙人掌的文章：PostgreSQL定时任务插件pg_cron被误杀后可以自己重启了

pg_cron定时任务插件v1.6.1版本解决了两个易用性问题：

• 支持设置时区，不再需要根据固定GMT去计算时差

• 支持自动重启，解决调度进程被误杀后可自动重启，而无需重启数据库

当前pg_cron最新版本为v1.6.2。

本文到此结束，欢迎点评...