NOT DEFERRABLE constraints are checked before command finishes

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

Responses

Browse pgsql-docs by date

  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