From: | PG Doc comments form <noreply(at)postgresql(dot)org> |
---|---|
To: | pgsql-docs(at)lists(dot)postgresql(dot)org |
Cc: | jack(at)jncsoftware(dot)com |
Subject: | NOT DEFERRABLE constraints are checked before command finishes |
Date: | 2021-07-13 20:15:53 |
Message-ID: | 162620735306.699.15544461247317554611@wrigleys.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-docs |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Daniel Gustafsson | 2021-07-13 21:32:16 | Re: Minor doc fixes |
Previous Message | Ekaterina Kiryanova | 2021-07-13 13:13:00 | Minor doc fixes |