Обсуждение: NOT DEFERRABLE constraints are checked before command finishes
The following documentation comment has been logged on the website: Page: https://www.postgresql.org/docs/13/sql-createtable.html Description: According to the docs: A constraint that is not deferrable will be checked immediately after every command. But this is the behavior I observe on PG 13.3: create table t (n int primary key); insert into t values (1), (2), (3); update t set n = n + 1; ERROR: 23505: duplicate key value violates unique constraint "t_pkey" DETAIL: Key (n)=(2) already exists. If the constraint was checked *after* the command it should work. It appears it is checked before the command has finished. In contrast a DEFERRABLE INITIALLY IMMEDIATE constraint which is documented as "If the constraint is INITIALLY IMMEDIATE, it is checked after each statement." behaves as expected. create table t (n int primary key deferrable initially immediate); insert into t values (1), (2), (3); update t set n = n + 1; --> UPDATE 3
Non-Deferred Uniqueness Constraints
When a UNIQUE
or PRIMARY KEY
constraint is not deferrable, PostgreSQL checks for uniqueness immediately whenever a row is inserted or modified. The SQL standard says that uniqueness should be enforced only at the end of the statement; this makes a difference when, for example, a single command updates multiple key values. To obtain standard-compliant behavior, declare the constraint as DEFERRABLE
but not deferred (i.e., INITIALLY IMMEDIATE
). Be aware that this can be significantly slower than immediate uniqueness checking.
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/13/sql-createtable.html
Description:
According to the docs:
A constraint that is not deferrable will be checked immediately after every
command.
But this is the behavior I observe on PG 13.3:
create table t (n int primary key);
insert into t values (1), (2), (3);
update t set n = n + 1;
ERROR: 23505: duplicate key value violates unique constraint "t_pkey"
DETAIL: Key (n)=(2) already exists.
If the constraint was checked *after* the command it should work. It appears
it is checked before the command has finished.
In contrast a DEFERRABLE INITIALLY IMMEDIATE constraint which is documented
as "If the constraint is INITIALLY IMMEDIATE, it is checked after each
statement." behaves as expected.
create table t (n int primary key deferrable initially immediate);
insert into t values (1), (2), (3);
update t set n = n + 1; --> UPDATE 3
UNIQUE constraints have this behaviour. It is explained in the section:Non-Deferred Uniqueness Constraints
When a
UNIQUE
orPRIMARY KEY
constraint is not deferrable, PostgreSQL checks for uniqueness immediately whenever a row is inserted or modified. The SQL standard says that uniqueness should be enforced only at the end of the statement; this makes a difference when, for example, a single command updates multiple key values. To obtain standard-compliant behavior, declare the constraint asDEFERRABLE
but not deferred (i.e.,INITIALLY IMMEDIATE
). Be aware that this can be significantly slower than immediate uniqueness checking.On Wed, Jul 14, 2021 at 9:29 AM PG Doc comments form <noreply@postgresql.org> wrote:The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/13/sql-createtable.html
Description:
According to the docs:
A constraint that is not deferrable will be checked immediately after every
command.
But this is the behavior I observe on PG 13.3:
create table t (n int primary key);
insert into t values (1), (2), (3);
update t set n = n + 1;
ERROR: 23505: duplicate key value violates unique constraint "t_pkey"
DETAIL: Key (n)=(2) already exists.
If the constraint was checked *after* the command it should work. It appears
it is checked before the command has finished.
In contrast a DEFERRABLE INITIALLY IMMEDIATE constraint which is documented
as "If the constraint is INITIALLY IMMEDIATE, it is checked after each
statement." behaves as expected.
create table t (n int primary key deferrable initially immediate);
insert into t values (1), (2), (3);
update t set n = n + 1; --> UPDATE 3