title: In PostgreSQL 10, why is a foreign key violation error not caught by pgplsql exception handling for deferrable constraints?
body: <p>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.</p>
  
  <p>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).</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>Is this a bug?  Or am I missing something that's documented with deferrable constraints?</p>
  
  <p>Here are two tables and two functions.  </p>
  
  <pre><code>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
    ;
  $$
  ;
  </code></pre>
  
  <p>Executing public.test_load() returns a single row 'failure'.</p>
  
  <p>If you then do this:</p>
  
  <pre><code>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
  ;
  </code></pre>
  
  <p>Then execute public.test_load() the exception just happens:</p>
  
  <pre><code>[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".
  </code></pre>
  
  <p>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.</p>