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

Question

Consider the following code:

drop table if exists demo cascade;
drop table if exists child cascade;

create table demo
(
    id bigint not null 
    constraint demo_pk primary key
);

create table child
(
    id bigint not null 
        constraint child_pk primary key,
    pid bigint,
        constraint child_pid_fk
        foreign key (pid)
        references demo (id)
        deferrable initially deferred  -- remove this line and exceptions get caught
);

insert into demo values (1);
insert into child values (11, 1);

do language plpgsql $$
begin
    delete from demo where id = 1;
exception
    when others then
        raise notice 'exception caught';
end;
$$;

I would like to catch any exception thrown by constraints, but for performance reasons I defer checking constraints until commit (deferrable initially deferred). Is there a way to catch exceptions without turning immediate mode on?

Answer 1

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.