Deferrable Check Constraint in Postgresql

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 ]

Is there a way to catch exception of a deferred constraint in pgsql?

You can set deferrable constraints to IMMEDIATE just for your transaction without "turning immediate mode on" (without changing the constraint definition).

That's exactly what this separate command SET CONSTRAINTS is for:

SET CONSTRAINTS child_pid_fk IMMEDIATE;

Or if you don't know constraint name(s):

SET CONSTRAINTS ALL IMMEDIATE;

The manual:

SET CONSTRAINTS sets the behavior of constraint checking within the current transaction.

Bold emphasis mine.

And:

When SET CONSTRAINTS changes the mode of a constraint from
DEFERRED to IMMEDIATE, the new mode takes effect retroactively:
any outstanding data modifications that would have been checked at the
end of the transaction are instead checked during the execution of the
SET CONSTRAINTS command. If any such constraint is violated, the
SET CONSTRAINTS fails (and does not change the constraint mode).
Thus, SET CONSTRAINTS can be used to force checking of constraints
to occur at a specific point in a transaction.

Exactly what you need.

How to defer foreign key constraint in Postgres

From the updated code, and subsequent comments, we know now that the issue was that the two queries were executed separately and not in a single transaction.

expected one of the following: CHECK CONSTRAINT DEFERRABLE EXCLUDE FOREIGN INITIALLY LIKE NOT PRIMARY UNIQUE identifier

Did you define the sequence? This works:

create sequence table_name_id_seq;

create table roles (
    id bigint not null default nextval('table_name_id_seq'::regclass),
    name character varying(20),
    constraint table_name_pkey primary key (id)
);

I wouldn't recommend using sequences for this. Postgres has better methods:

create table roles (
    id bigint not null generated always as identity,
    name character varying(20),
    constraint table_name_pkey primary key (id)
);

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.

Violating foreign key constraint with deferred constraint

You probably have some issue with the transaction demarcation. I ran a simple test and works wells.

insert into foo (id, foo) values (1, 'Anne');

start transaction;

insert into access (id, foo_id) values (101, 1);

insert into access (id, foo_id) values (107, 7); -- 7 does not exist yet...

insert into foo (id, foo) values (7, 'Ivan'); -- 7 now exists!

commit; -- at this point all is good

See running example at DB Fiddle.

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.