Not Deferrable Versus Deferrable Initially Immediate

NOT DEFERRABLE versus DEFERRABLE INITIALLY IMMEDIATE

With DEFERRABLE INITIALLY IMMEDIATE you can defer the constraints on demand when you need it.

This is useful if you normally want to check the constraints at statement time, but for e.g. a batch load want to defer the checking until commit time.

The syntax how to defer the constraints is different for the various DBMS though.

With NOT DEFERRABLE you will never ever be able to defer the checking until commit time.

Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?

I remember having raised an almost identical point when PG9 was in alpha state. Here was the answer from Tom Lane (high-profile PG core developer):

http://archives.postgresql.org/pgsql-general/2010-01/msg00221.php

In short: won't fix.

Not to say that I agree with your suggestion that the current behavior is a bug. Look at it from the opposite angle: it's the behavior of NOT DEFERRABLE that is incorrect.

In fact, the constraint violation in this UPDATE should never happen in any case, since at the end of the UPDATE the constraint is satisfied. The state at the end of the command is what matters. The intermediate states during the execution of a single statement should not be exposed to the user.

It seems like the PostgreSQL implements the non deferrable constraint by checking for duplicates after every row updated and failing immediately upon the first duplicate, which is essentially flawed. But this is a known problem, probably as old as PostgreSQL.
Nowadays the workaround for this is precisely to use a DEFERRABLE constraint. And there is some irony in that you're looking at it as deficient because it fails to fail, while somehow it's supposed to be the solution to the failure in the first place!

Summary of the status quo since PostgreSQL 9.1

• NOT DEFERRABLE UNIQUE or PRIMARY KEY constraints are checked after each row.

• DEFERRABLE constraints set to IMMEDIATE (INITIALLY IMMEDIATE or via SET CONSTRAINTS) are checked after each statement.

• DEFERRABLE constraints set to DEFERRED (INITIALLY DEFERRED or via SET CONSTRAINTS) are checked after each transaction.

Note the special treatment of UNIQUE / PRIMARY KEY constraints.
Quoting the manual page for CREATE TABLE:

A constraint that is not deferrable will be checked immediately after every command.

While it states further down in the Compatibility section under 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.

Bold emphasis mine.

If you need any FOREIGN KEY constraints to reference the column(s), DEFERRABLE is not an option because (per documentation):

The referenced columns must be the columns of a non-deferrable unique
or primary key constraint in the referenced table.

Alembic 1.8 + DEFERRABLE INITIALLY IMMEDIATE

This is an issue (or design choice) of your DDL -> text generator -not with sqlalchemy.

The actual constraint information Postgres uses is stored in the table pg_catalog.pg_constraint. If you take a look at the docs for the pg_constraint table, you'll notice that the concept of deferability is (v7.2-v15+) controlled entirely by the two boolean columns condeferrable and condeferred.

Thus, if a constraint is DEFERRABLE and it's not INITIALLY DEFERRED (checked end of transaction), it can only be INITIALLY IMMEDIATE (checked end of statement).

If you want to be absolutely certain, you can run this simple query:

SELECT
    pgc.conname constraint_name,
    pgc.confrelid::regclass tbl_name,
    CASE
      WHEN pgc.condeferrable
      THEN
        CASE
            WHEN pgc.condeferred
              THEN 'DEFERRABLE INITIALLY DEFERRED'
              ELSE 'DEFERRABLE INITIALLY IMMEDIATE'
            END
      ELSE 'NOT DEFERRABLE'
    END deferrability

FROM
    pg_catalog.pg_constraint pgc

WHERE
    conname = 'auth_user_groups_group_id_fkey'

Is there any difference between a not deferrable constraint trigger and a normal after event row level trigger ?

No, it is pointless to define a non-deferrable constraint trigger.

The documentation describes the behavior exactly:

When the CONSTRAINT option is specified, this command creates a constraint trigger. This is the same as a regular trigger except that the timing of the trigger firing can be adjusted using SET CONSTRAINTS. Constraint triggers must be AFTER ROW triggers on plain tables (not foreign tables). They can be fired either at the end of the statement causing the triggering event, or at the end of the containing transaction; in the latter case they are said to be deferred. A pending deferred-trigger firing can also be forced to happen immediately by using SET CONSTRAINTS. Constraint triggers are expected to raise an exception when the constraints they implement are violated.

Why is my regular foreign key constraint not deferrable?

A) The doc link is for version 9.1 which is well past EOL(~5.25 yrs).

B) Yes, you missed the next paragraph:

Upon creation, a constraint is given one of three characteristics: DEFERRABLE INITIALLY DEFERRED, DEFERRABLE INITIALLY IMMEDIATE, or NOT DEFERRABLE. .

So the constraint needs to be created as DEFERRABLE or altered to it. See ALTER TABLE:

ALTER CONSTRAINT constraint_name [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

Why `not deferrable` constraint is deferred when using `with`?

According to the docs non-deferrable unique constraints are checked for each row, contrary to the standards specification that they are checked only at the end of a statement.

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...

But this exception to the standards spec is only for uniqueness, not for foreign key. Foreign key constraints are checked at the end of the statement if they are either not deferrable or if they are deferrable but not deferred. Since any problems have been cured by the end of the statement in your first two examples, there is no error.

What are cons and pros a of defining a constraint deferrable

The major use case for deferrable constraints is that you don't need to worry about the order in which you do DML statements for multiple tables that have a foreign key relationship.

Consider the following example:

create table parent
(
   id integer not null primary key
);

create table child
(
  id integer not null primary key,
  parent_id integer not null references parent
);

create table grand_child
(
  id integer not null primary key,
  child_id integer not null references child
);

If the constraints are immediate you have to insert (or delete) rows (that reference each other) in the proper sequence, which can e.g. be a problem when bulk loading data. If the constraints are deferred you can insert/delete the rows in any sequence as long as everything is fine when you commit your transaction.

So with a deferrable constraint (which the above example does not create!) you could the following:

insert into grand_child values (1,1);
insert into child values (1,1);
insert into parent values (1);
commit;

That would not be possible if the constraints were immediate.

A special case of the above example are cyclic references:

create table one
(
   id integer not null primary key,
   id_two integer not null
);

create table two
(
   id integer not null primary key
   id_one integer not null
);

alter table one add constraint fk_one_two (id_two) references two(id);
alter table two add constraint fk_two_one (id_one) references one(id);

Without declaring the constraints as deferrable you will not be able to insert data into those tables at all.

The workaround for DBMS that do not support deferrable constraints would be to make the fk columns nullable. And then insert null values first:

insert into one values (1, null);
insert into two values (1, 1);
update one
set id_two = 1
where id = 1;

With a deferrable constraint you don't need the additional update statement.

(The design using a cyclic reference is however very often questionable!)

I don't use deferrable constraints where often, but I wouldn't want to live without them.

One drawback of deferrable constraints is error checking though. You don't know until you commit if your data is correct. That makes finding out what went wrong a bit more complicated. If you get the error when doing the insert (or delete or update) you immediately know which values caused the error.

In MySQL, can I defer referential integrity checks until commit

Looks like my answer is here...

Like MySQL in general, in an SQL statement that inserts, deletes, or updates many rows, InnoDB checks UNIQUE and FOREIGN KEY constraints row-by-row. When performing foreign key checks, InnoDB sets shared row-level locks on child or parent records it has to look at. InnoDB checks foreign key constraints immediately; the check is not deferred to transaction commit. According to the SQL standard, the default behavior should be deferred checking. That is, constraints are only checked after the entire SQL statement has been processed. Until InnoDB implements deferred constraint checking, some things will be impossible, such as deleting a record that refers to itself using a foreign key.

Back to the drawing board.

---

Related Topics