postgresql - 在表上添加触发器时出现 PSQLException 和锁定问题

Question

更新:我从问题中排除了 Hibernate。我完全修改了问题描述以尽可能简化它。

我有带 noop 触发器的 master 表和 detail 表，master 和 detail 表之间有两种关系:

create table detail (
  id bigint not null,
  code varchar(255) not null,
  primary key (id)
);

create table master (
  id bigint not null,
  name varchar(255),
  detail_id bigint, -- "preferred" detail is one-to-one relation
  primary key (id),
  unique (detail_id),
  foreign key (detail_id) references detail(id)
);

create table detail_candidate ( -- "candidate" details = many-to-many relation modeled as join table
  master_id bigint not null,
  detail_id bigint not null,
  primary key (master_id, detail_id),
  foreign key (detail_id) references detail(id),
  foreign key (master_id) references master(id)
);

create or replace function trgf() returns trigger as $$
begin
  return NEW;
end;
$$ language 'plpgsql';

create trigger trg
  before insert or update
  on master
  for each row execute procedure trgf();

insert into master (id, name) values (1000, 'x'); -- this is part of database setup
insert into detail (code, id) values ('a', 1);    -- this is part of database setup

在这样的设置中，我使用 psql 打开两个终端窗口并执行以下步骤:

1. 在第一个终端中，更改 master(保持事务打开)

begin;
update master set detail_id=null, name='y' where id=1000;

1. 在第二个终端中，在自己的交易中添加要掌握的详细信息候选人

begin;
set statement_timeout = 4000;
insert into detail_candidate (master_id, detail_id) values (1000, 1);

第二个终端中的最后一个命令超时消息

ERROR:  canceling statement due to statement timeout
CONTEXT:  while locking tuple (0,1) in relation "master"
SQL statement "SELECT 1 FROM ONLY "public"."master" x WHERE "id" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x"

我的观察和问题(变化是独立的):

• 当数据库设置时没有触发器，即 drop trigger trg on master; 在初始设置后调用，一切正常。为什么 noop 触发器的存在会产生这样的影响？我不明白。
• 当数据库设置时 master.detail_id 没有唯一约束(即 alter table master drop constraint master_detail_id_key; 在初始设置后调用)，一切正常也很好。为什么？
• 当我在第一个终端的更新语句中省略显式 detail=null 赋值时(因为无论如何设置中都有 null 值)，一切也都正常。为什么？

在 Postgres 9.6.12(嵌入式)、9.6.15(在 Docker 中)、11.5(在 Docker 中)上尝试过。

问题在 Docker 镜像 tomaszalusky/trig-example 中重现，它在 DockerHub 上可用或可以从 this Dockerfile 构建(里面的说明)。

---

更新 2: 我发现了以上三个观察结果的共同行为。我生成了查询 select * from pgrowlocks('master') from pgrowlocks extension在第二次交易中。 row-level lock master 中的更新行在失败情况下是FOR UPDATE，但在所有三种工作情况下都是FOR NO KEY UPDATE。这完全符合 mode match table in documentation因为 FOR UPDATE 模式更强，插入语句请求的模式是 FOR KEY SHARE(从错误消息中可以明显看出，还调用了 select ... for key share 命令与 insert 命令具有相同的效果。

FOR UPDATE 模式的文档说:

The FOR UPDATE lock mode is also acquired by (...) an UPDATE that modifies the values on certain columns. Currently, the set of columns considered for the UPDATE case are those that have a unique index on them that can be used in a foreign key (...)

master.detail_id 列为真。然而，仍然不清楚为什么 FOR UPDATE 模式不是根据触发器的存在而独立选择的，以及为什么触发器的存在会导致它。

Answer 1

有趣的问题。这是我最好的猜测。我没有测试过。

一般来说，postgres 对语句将对数据产生什么影响的有根据的猜测不会扩展到触发器逻辑中。当执行第二条语句时，postgres 看到了外键约束，并且知道它必须检查分配(插入)的值是否有效，也就是说，它是否代表外表中的有效键。触发器可能会影响所提议的外键的有效性(例如，如果触发器删除记录)，但这种做法可能会很糟糕。

(情况 1)如果没有触发器，则它可以查看数据(预提交和准备提交的数据)并确定建议的值是否保证有效。 (case 2) 如果没有 FK 约束，则触发器不会影响插入的有效性，因此是允许的。 (案例 3)如果您省略 detail_id=null，则更新中没有任何变化，因此触发器不会触发，因此它的存在是无关紧要的。

我尽量避免 FK 约束和触发器。在我看来，最好是让数据库不小心包含部分不正确的数据，然后让它完全挂起，就像您在这里看到的那样。我会放弃所有 FK 约束和触发器，并强制所有更新和插入操作通过存储函数进行操作，这些函数在开始/提交锁内执行验证，并立即适本地处理不正确/无效的插入/更新尝试，而不是强制 postgres在决定是否允许命令 2 之前等待命令 1 提交。

编辑:参见this question

编辑 2: 我能找到的关于触发器时间与约束检查最接近的官方文档来自 triggers docs。

The trigger can be specified to fire before the operation is attempted on a row (before constraints are checked and the INSERT, UPDATE, or DELETE is attempted); or after the operation has completed (after constraints are checked and the INSERT, UPDATE, or DELETE has completed); or instead of the operation (in the case of inserts, updates or deletes on a view). If the trigger fires before or instead of the event, the trigger can skip the operation for the current row, or change the row being inserted (for INSERT and UPDATE operations only).

这个有点不清楚，约束检查前发生的触发是否适用于其他事务的约束检查。不管怎样，这个问题要么是一个错误，要么是文档不足。