在表上添加触发器时，PSQLException和锁定问题 [英] PSQLException and lock issue when trigger added on table

问题描述

更新:我从问题中消除了休眠.我对问题的描述进行了完全的重新设计，以尽可能地简化它.

UPDATE: I eliminated Hibernate from the problem. I completely reworked description of problem to simplify it as much as possible.

我有带无操作触发器的master表和具有master和detail表之间的两个关系的detail表:

I have master table with noop trigger and detail table with two relations between master and detail table:

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打开两个终端窗口，然后执行以下步骤:

In such setup, I open two terminal windows with psql and perform following steps:

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

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

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

Last command in second terminal timeouts with message

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"

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

My observation and questions (changes are independent):

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

• when the db is setup without trigger, i.e. drop trigger trg on master; is called after initial setup, everything works fine. Why the presence of noop trigger has such an influence? I don't get it.
• when the db is setup without unique constraint on master.detail_id (i.e. alter table master drop constraint master_detail_id_key; is called after initial setup), everything works fine too. Why?
• when I omit explicit detail=null assignment in update statement in first terminal (since there's null value from setup anyway), everything works fine too. Why?

尝试使用Postgres 9.6.12(嵌入式)，9.6.15(在Docker中)，11.5(在Docker中).

Tried on Postgres 9.6.12 (embedded), 9.6.15 (in Docker), 11.5 (in Docker).

问题可以在DockerHub上可用的Docker映像tomaszalusky/trig-example中重现，也可以从此Dockerfile构建(内部说明).

Problem is reproducible in Docker image tomaszalusky/trig-example which is available on DockerHub or can be built from this Dockerfile (instructions inside).

更新2:我在上面发现了三种观察的常见行为.我在第二次交易中从 pgrowlocks扩展产生了查询select * from pgrowlocks('master'). 行级锁 >在失败情况下为FOR UPDATE，但在所有三个工作情况下均为FOR NO KEY UPDATE.这完全符合文档中的模式匹配表因为FOR UPDATE模式是更强的模式，而insert语句请求的模式是FOR KEY SHARE(从错误消息中可以明显看出，也调用select ... for key share命令与insert命令具有相同的效果).

UPDATE 2: I found common behaviour of three observation above. I spawned the query select * from pgrowlocks('master') from pgrowlocks extension in second transaction. The row-level lock of updated row in master is FOR UPDATE in failing case but FOR NO KEY UPDATE in all three working cases. This is in perfect compliance with mode match table in documentation since FOR UPDATE mode is the stronger one and mode requested by insert statement is FOR KEY SHARE (which is apparent from error message, also invoking the select ... for key share command has same effect as insert command).

FOR UPDATE模式下的文档说:

FOR UPDATE锁定模式还可以通过(...)UPDATE来修改，该UPDATE会修改某些列上的值.当前，在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模式以及为什么触发器存在会导致这种情况.

It is true for master.detail_id column. However, still it's not clear why FOR UPDATE mode isn't chosen independently on trigger presence and why trigger presence caused it.

推荐答案

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

Interesting problem. This is my best guess. I have tested none of it.

通常来说，postgres对数据将产生什么样的影响陈述的有根据的猜测并没有扩展到触发逻辑中.当执行第二条语句时，postgres会看到外键约束，并知道它必须检查被赋值(插入)的值是否有效，也就是说，它是否表示外表中的有效键.不管采取哪种不良做法，触发器都有可能对提议的外键的有效性产生影响(例如，如果触发器删除了记录).

Generally speaking, postgres's educated guessing of what effect statements will have on data does not extend into trigger logic. When executing the second statement, postgres sees the foreign key constraint, and knows it has to check if the value being assigned (inserted) is valid, that is, if it represents a valid key in the foreign table. It is possible, however bad practice, that the trigger may have an effect on the validity of the foreign key being proposed (e.g. if the trigger deletes records).

(情况1)如果没有触发器，则它可以查看数据(预提交和已分阶段提交)，并确定建议的值是否有效. (情况2)如果没有FK约束，则触发器不会影响插入的有效性，因此是允许的. (情况3)如果省略detail_id=null，则更新不会更改，因此触发器不会触发，因此它的存在无关紧要.

(case 1) If there is no trigger, then it can look at the data (both pre-commit and staged for commit) and decide if the proposed value is gauranteed valid. (case 2) If there is no FK constraint, then the trigger cannot impact the validity of the insertion, so it is allowed. (case 3) If you omit the detail_id=null, there is no change in the update so the trigger won't fire, so its presence is irrelevant.

我尽量避免FK约束和触发器.我认为，最好让数据库意外地包含部分不正确的数据，然后使其完全挂起，就像您在这里看到的那样.我将删除所有FK约束和触发器，并强制所有更新和插入操作通过存储的函数进行操作，这些函数在begin/commit锁内执行验证，并适当且立即处理不正确/无效的插入/更新尝试，而不是迫使postgres进行操作.等待命令1提交，然后再决定是否允许命令2.

I try to avoid both FK constraints and triggers whenever possible. It's better, in my opinion, to let the database accidentally contain partially incorrect data then to have it hang completely, like you're seeing here. I would drop all FK constraints and triggers, and force all update and insert operations to operate via stored functions, which perform validation inside a begin/commit lock, and handle incorrect/invalid insert/update attempts appropriately and immediately, rather than forcing postgres to wait for command 1 to commit before deciding if command 2 is allowed.

编辑:请参见此问题

我可以从

可以将触发器指定为在尝试对该行进行操作之前触发(在检查约束并尝试执行INSERT，UPDATE或DELETE之前)；或在操作完成之后(在检查约束并完成INSERT，UPDATE或DELETE之后)；或者或代替操作(对于视图执行插入，更新或删除操作).如果触发器在事件发生之前或之前触发，则触发器可以跳过当前行的操作，或更改要插入的行(仅适用于INSERT和UPDATE操作).

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).

这有点不清楚，如果触发发生在约束检查之前适用于其他事务的约束检查.无论是哪种情况，此问题要么是错误，要么是文献记载不充分.

This is a bit unclear, if the trigger happening before the constraint check applies to constraint check of other transactions. Whatever the case, this issue is either a bug or poorly documented.

这篇关于在表上添加触发器时，PSQLException和锁定问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！