文档章节

PostgreSQL 约束延迟生效(deferrable)

kenyon_君羊
 kenyon_君羊
发布于 2013/04/28 23:08
字数 1120
阅读 2092
收藏 13
Postgres建表时可以指定各种约束,但还有几个不常见的参数可以来控制约束的即时生效或者延迟生效。

OS:CentOS 6.2
DB:Postgres 9.1.3

1.建表语句
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name
    OF type_name [ (
  { column_name WITH OPTIONS [ column_constraint [ ... ] ]
    | table_constraint }
    [, ... ]
) ]
[ WITH ( storage_parameter [= value] [, ... ] ) | WITH OIDS | WITHOUT OIDS ]
[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]
[ TABLESPACE tablespace_name ]

where column_constraint is:

[ CONSTRAINT constraint_name ]
{ NOT NULL |
  NULL |
  CHECK ( expression ) [ NO INHERIT ] |
  DEFAULT default_expr |
  UNIQUE index_parameters |
  PRIMARY KEY index_parameters |
  REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
    [ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

and table_constraint is:

[ CONSTRAINT constraint_name ]
{ CHECK ( expression ) [ NO INHERIT ] |
  UNIQUE ( column_name [, ... ] ) index_parameters |
  PRIMARY KEY ( column_name [, ... ] ) index_parameters |
  EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] ) index_parameters [ WHERE ( predicate ) ] |
  FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]
    [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]


2.文档释义
DEFERRABLE
NOT DEFERRABLE
This controls whether the constraint can be deferred. A constraint that is not deferrable will be checked immediately after every command. Checking of constraints that are deferrable can be postponed until the end of the transaction (using the SET CONSTRAINTS command). NOT DEFERRABLE is the default. Currently, only UNIQUE, PRIMARY KEY, EXCLUDE, and REFERENCES (foreign key) constraints accept this clause. NOT NULL and CHECK constraints are not deferrable.

INITIALLY IMMEDIATE
INITIALLY DEFERRED
If a constraint is deferrable, this clause specifies the default time to check the constraint. If the constraint is INITIALLY IMMEDIATE, it is checked after each statement. This is the default. If the constraint is INITIALLY DEFERRED, it is checked only at the end of the transaction. The constraint check time can be altered with the SET CONSTRAINTS command.

SET CONSTRAINTS { ALL | name [, ...] } { DEFERRED | IMMEDIATE }

大概说明一下, DEFERRABLE| NOT DEFERRABLE这个参数控制着约束是否可被延迟生效,当约束设置是非延迟时将会对每一个命令立即进行检查,当约束设置可延迟生效时可以使用set constraints命令来推迟检查直到事务结束,默认是非延迟。当前,只有UNIQUE,PK,exclude,FK可以延迟,NOT NULL和CHECK都是非延迟,这点和ORACLE有所不同,oracle中check也可以延迟。

当约束是可延迟生效时,这个可以选择检查时间来检查约束,如INITIALLY IMMEDIATE,它会在每一个语句执行后进行检查,而INITIALLY DEFERRED则会只在事务结束时才检查,选项可以通过SET CONSTRAINTS来更改。

set constraints中可以设置all或者name,name是约束名称,可以在pg_constraint中查询,可见下例

总的来说
非延迟是立即生效的,不允许延迟
延迟生效初始化立即生效是对每个语句结束时检查
延迟生效初始化延迟生效是直到事务完成才检查(可使用set constraints调整)

3.例子说明
a.非延迟(not deferrable)
[postgres@localhost ~]$ psql -d db_kenyon
Password: 
psql (9.1.3)
Type "help" for help.

db_kenyon=# create table t_kenyon(id int primary key not deferrable);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t_kenyon_pkey" for table "t_kenyon"
CREATE TABLE
db_kenyon=# insert into t_kenyon values(1);
INSERT 0 1
db_kenyon=# begin;
BEGIN
db_kenyon=# insert into t_kenyon values(1);
ERROR:  duplicate key value violates unique constraint "t_kenyon_pkey"
DETAIL:  Key (id)=(1) already exists.

db_kenyon=# begin;
BEGIN
db_kenyon=# set constraints all deferred;
SET CONSTRAINTS
db_kenyon=# insert into t_kenyon values(1);
ERROR:  duplicate key value violates unique constraint "t_kenyon_pkey"
DETAIL:  Key (id)=(1) already exists.
可以 看到设置非延迟时插入违反约束的数据是会立即报错的,即使设置约束延迟也没用

b.延迟生效之 INITIALLY IMMEDIATE
db_kenyon=# drop table t_kenyon;
DROP TABLE
db_kenyon=# create table t_kenyon(id int primary key deferrable initially immediate);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t_kenyon_pkey" for table "t_kenyon"
CREATE TABLE
db_kenyon=# insert into t_kenyon values(1);
INSERT 0 1
db_kenyon=# insert into t_kenyon values(1);
ERROR:  duplicate key value violates unique constraint "t_kenyon_pkey"
DETAIL:  Key (id)=(1) already exists.
db_kenyon=# begin transaction;
BEGIN
db_kenyon=# insert into t_kenyon values(1);
ERROR:  duplicate key value violates unique constraint "t_kenyon_pkey"
DETAIL:  Key (id)=(1) already exists.
db_kenyon=# rollback;
ROLLBACK
db_kenyon=# end;
WARNING:  there is no transaction in progress
COMMIT
db_kenyon=# begin transaction;
BEGIN
db_kenyon=# set constraints all deferred;
SET CONSTRAINTS
db_kenyon=# insert into t_kenyon values(1);
INSERT 0 1
db_kenyon=# set constraints all immediate;
ERROR:  duplicate key value violates unique constraint "t_kenyon_pkey"
DETAIL:  Key (id)=(1) already exists.

可以看到是有区别的,再来看看另外一个场景

c.延迟生效之 INITIALLY DEFERRED
db_kenyon=# create table t_kenyon(id int primary key deferrable initially deferred);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "t_kenyon_pkey" for table "t_kenyon"
CREATE TABLE
db_kenyon=# insert into t_kenyon values(1);
INSERT 0 1
db_kenyon=# begin;
BEGIN
db_kenyon=# insert into t_kenyon values(1);
INSERT 0 1
db_kenyon=# insert into t_kenyon values(2);
INSERT 0 1
db_kenyon=# commit;
ERROR:  duplicate key value violates unique constraint "t_kenyon_pkey"
DETAIL:  Key (id)=(1) already exists.
db_kenyon=# end;
WARNING:  there is no transaction in progress
COMMIT
db_kenyon=# select conname from pg_constraint where contype = 'p';
    conname    
---------------
 t_kenyon_pkey
(1 row)
db_kenyon=# begin;
BEGIN
db_kenyon=# insert into t_kenyon values(1);
INSERT 0 1
db_kenyon=# set constraints t_kenyon_pkey immediate;
ERROR:  duplicate key value violates unique constraint "t_kenyon_pkey"
DETAIL:  Key (id)=(1) already exists.


这里应该了然了,延迟的两种方式可以在事务里自行切换,如果设置非延迟就不行,这就是区别。ORACLE里的语法和概念也是差不多这样,很相近的。



© 著作权归作者所有

共有 人打赏支持
kenyon_君羊
粉丝 499
博文 170
码字总数 121714
作品 0
杭州
其他
私信 提问
加载中

评论(3)

宏哥
宏哥

引用来自“kenyon(君羊)”的评论

引用来自“宏哥”的评论

这个特性我在存储过程,表设计大量运用

我觉得在迁移、级联更新和物化过程中有些用处

在很多业务计算当中也有用处。
最经典的就是账务, 需要记录流水以及关联最后状态。

比如记录一笔流水, 然后更新用户最后状态到最后一笔流水。
kenyon_君羊
kenyon_君羊

引用来自“宏哥”的评论

这个特性我在存储过程,表设计大量运用

我觉得在迁移、级联更新和物化过程中有些用处
宏哥
宏哥
这个特性我在存储过程,表设计大量运用
PostgreSQL的外键深入使用

有开发同事问及postgresql外键的用法,这里普及一下。外键是一个很基础的概念,使用得当可以对事务的一致性有很好的保障,方法上和Oracle是很接近的,作用很简单地说就是保证子表的数据都能在...

kenyon_君羊
2013/06/01
0
4
Locking issue with concurrent DELETE / INSERT in PostgreSQL - 解法 advisory lock

背景 两个会话,同时对同一个ID值的记录执行删除后插入,有一个会话会出现删除0,插入失败的现象。 现象 CREATE TABLE test (id INT PRIMARY KEY); INSERT INTO TEST VALUES (1); INSERT IN...

pg小助手
2018/10/19
0
0
PostgreSQL Oracle 兼容性之 - ALTER TRIGGER ENABLE|DISABLE

标签 PostgreSQL , 触发器 , enable , disable 背景 Oracle 临时关闭或启用触发器的语法如下 https://docs.oracle.com/cd/B1930601/server.102/b14200/statements4001.htm PostgreSQL 触发器......

德哥
2018/05/06
0
0
Postgres 的 deferrable

仅 Postgres 支持 deferrable deferrable 即 一、定义字段时指定 定义:考试表里 字段关联了 科目表的 字段 1、原生 SQL 1.DEFERRABLE INITIALLY DEFERRED 2.DEFERRABLE INITIALLY IMMEDIATE...

小蒋不素小蒋
2018/08/07
0
0
Toad 数据建模工具发布 4.0 版本

Toad Data Modeler 4.0 发布了,这是一个多平台的数据建模和设计工具,该版本增加了对 PostgreSQL 9.0 的支持 主要特性: - Entity Relationship Diagrams - Generation of SQL code - Reve...

红薯
2011/08/05
1K
0

没有更多内容

加载失败,请刷新页面

加载更多

RocketMQ高性能之底层存储设计

说在前面 RocketMQ在底层存储上借鉴了Kafka,但是也有它独到的设计,本文主要关注深刻影响着RocketMQ性能的底层文件存储结构,中间会穿插一点点Kafka的东西以作为对比。 例子 Commit Log,一...

薛定谔的旺
18分钟前
2
0
实战:基于Spring Boot快速开发RESTful风格API接口

写在前面的话 这篇文章计划是在过年期间完成的,示例代码都写好了,结果亲戚来我家做客,文章没来得及写。已经很久没有更新文章了,小伙伴们,有没有想我啊。言归正传,下面开始,今天的话题...

JAVA_冯文议
26分钟前
0
0
Anaconda3 5.0.0中配置Python 3.6 + TensorFlow环境

参考:https://blog.csdn.net/hu_zhenghui/article/details/78156193

Pasenger
30分钟前
0
0
Nginx反向代理Tomcat配置

Nginx代理Tomcat 为什么要为Tomcat配置反向代理? 1)如果同一台机器既有nginx又有Tomcat,则会产生端口冲突2)我们需要把8080端口变成80端口3)nginx对于静态的请求速度上要优于Tomcat,T...

wzb88
43分钟前
10
0
JavaScript email邮箱/邮件地址的正则表达式及分析

在做用户注册时,常会用到邮箱/邮件地址的正则表达式。本文列举了几种方案,大家可以根据自己的项目情况,选择最适合的方案。 方案1 (常用) 规则定义如下: 以大写字母[A-Z]、小写字母[a-z]...

前端小攻略
45分钟前
1
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部