In PostgreSQL 10, why is a foreign key violation error not caught by pgplsql exception handling for deferrable constraints?

Question

When calling a SQL function that performs an insert, from a PGPLSQL function with an EXCEPTION WHEN OTHERS block, the exception is raised rather than caught if the violated foreign key constraint is deferrable.

I'm using Amazon Aurora PostgreSQL compatible (v 10.4). I found that my exception handler wasn't always catching exceptions, which were instead being raised to the application (in my case an AWS Lambda Python function using Pyscopg2).

Took a lot of troubleshooting to narrow it down to the deferrable constraint, so I created a test function that reproduces the problem pretty reliably.

I've also reproduced the same behaviour on an RDS (non-Aurora) instance running version 10.5, and also on a 9.6.6 RDS instance so it's not Aurora and it's not specific to version 10.4.

Is this a bug? Or am I missing something that's documented with deferrable constraints?

Here are two tables and two functions.

CREATE TABLE public.load (
  load_id           BIGINT                                 NOT NULL,
  created_timestamp TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL,
  ds_code           TEXT                                   NOT NULL,
  route             TEXT,
  file_name         TEXT,
  user_name         TEXT,
  staged            BOOLEAN,
  staging_duration  INTERVAL,
  CONSTRAINT load_pkey
    PRIMARY KEY (load_id));


CREATE TABLE load_content (
  load_id           BIGINT NOT NULL,
  load_content      TEXT,
  CONSTRAINT data_load_pk
    PRIMARY KEY (load_id),
  CONSTRAINT data_load_load_load_id_fk
    FOREIGN KEY (load_id) REFERENCES public.load
)
;
CREATE FUNCTION insert_something() RETURNS void
  LANGUAGE SQL
AS
$$
INSERT INTO public.load_content values (1);

$$
;
CREATE FUNCTION test_load() RETURNS TEXT
  LANGUAGE plpgsql
AS
$$

BEGIN

  PERFORM public.insert_something();
  RETURN 'success';


  EXCEPTION
  WHEN OTHERS THEN
    RETURN 'failure';

END
  ;
$$
;

Executing public.test_load() returns a single row 'failure'.

If you then do this:

alter table public.load_content
  drop constraint data_load_load_load_id_fk;

ALTER TABLE public.load_content
  ADD CONSTRAINT data_load_load_load_id_fk
    FOREIGN KEY (load_id) REFERENCES public.load
DEFERRABLE INITIALLY DEFERRED
;

Then execute public.test_load() the exception just happens:

[2019-01-14 16:36:32] [23503] ERROR: insert or update on table "load_content" violates foreign key constraint "data_load_load_load_id_fk"
[2019-01-14 16:36:32] Detail: Key (load_id)=(1) is not present in table "load".

Obviously, I've simplified this considerably for test purposes - what's actually happening in my system is a trigger on the "load" table that fires to normalise raw data (usually JSON) into a staging data model.

Answer 1

A DEFERRED constraint check is postponed to the commit, an IMMEDIATE constraint check is executed as the name says, immediatly. The behaviour of a DEFERRABLE constraint can be changed within the current transaction with the SET CONSTRAINTS { ALL | name [, ...] } { DEFERRED | IMMEDIATE } (link to docs).

Should you want to change the behaviour of the test_load() function, here is the code example.

CREATE FUNCTION test_load() RETURNS TEXT
  LANGUAGE plpgsql
AS
$$

BEGIN

  SET CONSTRAINTS data_load_load_load_id_fk IMMEDIATE;
  PERFORM public.insert_something();
  RETURN 'success';


  EXCEPTION
  WHEN OTHERS THEN
    RETURN 'failure';

END
  ;
$$
;