Postgres约束延迟生效(deferrable)

Postgres约束延迟生效(deferrable) Postgres建表时可以指定各种约束，但还有几个不常见的参数可以来控制约束的即时生效或者延迟生效。 OS:CentOS 6.2 DB:Postgres 9.1.3 1.建表语句 CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [

Postgres约束延迟生效(deferrable)

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里的语法和概念也是差不多这样，很相近的。