postgresql - 在 PostgreSQL 10 中，为什么可延迟约束的 pgplsql 异常处理没有捕捉到外键违规错误？

Question

当从带有 EXCEPTION WHEN OTHERS block 的 PGPLSQL 函数调用执行插入的 SQL 函数时，如果违反的外键约束是可延迟的，则引发异常而不是捕获异常。

我正在使用 Amazon Aurora PostgreSQL 兼容 (v 10.4)。我发现我的异常处理程序并不总是捕获异常，而是将异常引发给应用程序(在我的例子中是使用 Pyscopg2 的 AWS Lambda Python 函数)。

进行了大量故障排除，将其缩小到可延迟约束，因此我创建了一个测试函数，可以非常可靠地重现该问题。

我还在运行版本 10.5 的 RDS(非 Aurora)实例以及 9.6.6 RDS 实例上重现了相同的行为，因此它不是 Aurora，也不特定于版本 10.4。

这是一个错误吗？还是我遗漏了用可延迟约束记录的内容？

这里有两个表和两个函数。

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
  ;
$$
;

执行 public.test_load() 返回单行“失败”。

如果你这样做:

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
;

然后执行 public.test_load() 异常就发生了:

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

显然，出于测试目的，我已经大大简化了这一点 - 我的系统中实际发生的是触发“加载”表的触发器，该触发器将原始数据(通常是 JSON)规范化为暂存数据模型。

Answer 1

DEFERRED 约束检查推迟到提交，IMMEDIATE 约束检查如名称所示立即执行。DEFERRABLE 约束的行为可以在当前事务中使用 SET CONSTRAINTS { ALL |名称 [, ...] } { 延迟 |立即 (link to docs) .

如果您想更改 test_load() 函数的行为，这里是代码示例。

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
  ;
$$
;