pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedNOT DEFERRABLE constraints are checked before command finishes
3+ messages / 3 participants
[nested] [flat]
* NOT DEFERRABLE constraints are checked before command finishes
@ 2021-07-13 20:15 PG Doc comments form <noreply@postgresql.org>
2021-07-14 08:33 ` Re: NOT DEFERRABLE constraints are checked before command finishes Pantelis Theodosiou <ypercube@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: PG Doc comments form @ 2021-07-13 20:15 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org; +Cc: jack@jncsoftware.com
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: NOT DEFERRABLE constraints are checked before command finishes
2021-07-13 20:15 NOT DEFERRABLE constraints are checked before command finishes PG Doc comments form <noreply@postgresql.org>
@ 2021-07-14 08:33 ` Pantelis Theodosiou <ypercube@gmail.com>
2021-07-14 13:11 ` Re: NOT DEFERRABLE constraints are checked before command finishes Jack Christensen <jack@jncsoftware.com>
0 siblings, 1 reply; 3+ messages in thread
From: Pantelis Theodosiou @ 2021-07-14 08:33 UTC (permalink / raw)
To: jack@jncsoftware.com; pgsql-docs@lists.postgresql.org
UNIQUE constraints have this behaviour. It is explained in the section:
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.
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
>
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: NOT DEFERRABLE constraints are checked before command finishes
2021-07-13 20:15 NOT DEFERRABLE constraints are checked before command finishes PG Doc comments form <noreply@postgresql.org>
2021-07-14 08:33 ` Re: NOT DEFERRABLE constraints are checked before command finishes Pantelis Theodosiou <ypercube@gmail.com>
@ 2021-07-14 13:11 ` Jack Christensen <jack@jncsoftware.com>
0 siblings, 0 replies; 3+ messages in thread
From: Jack Christensen @ 2021-07-14 13:11 UTC (permalink / raw)
To: Pantelis Theodosiou <ypercube@gmail.com>; +Cc: pgsql-docs@lists.postgresql.org
Thanks! Perhaps this behavior should also be explained elsewhere. It didn't
occur to me to look in the "Compatibility" section.
On Wed, Jul 14, 2021 at 3:33 AM Pantelis Theodosiou <ypercube@gmail.com>
wrote:
> UNIQUE constraints have this behaviour. It is explained in the section:
>
> 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.
>
> 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
>>
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2021-07-14 13:11 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-07-13 20:15 NOT DEFERRABLE constraints are checked before command finishes PG Doc comments form <noreply@postgresql.org>
2021-07-14 08:33 ` Pantelis Theodosiou <ypercube@gmail.com>
2021-07-14 13:11 ` Jack Christensen <jack@jncsoftware.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox