Postgres约束延迟生效(deferrable)(二)
b_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里的语法和概念也是差不多这样，很相近的。