PSQLException и проблема блокировки при добавлении триггера в таблицу

ОБНОВЛЕНИЕ: я устранил Hibernate из проблемы. Я полностью переработал описание проблемы, чтобы максимально упростить ее.

У меня есть таблица master с триггером noop и таблица 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. в первом терминале смените мастер (оставьте транзакцию открытой)
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 в операторе обновления в первом терминале (так как в любом случае в настройке есть нулевое значение), все тоже работает нормально. Почему?

Пробовал на Postgres 9.6.12 (встроенный), 9.6.15 (в Docker), 11.5 (в Docker).

Проблема воспроизводима в образе Docker tomaszalusky/trig-example, который доступен на DockerHub или может быть создан из этого файла Docker (инструкции внутри).

ОБНОВЛЕНИЕ 2: Я обнаружил обычное поведение трех наблюдений выше. Я породил запрос select * from pgrowlocks('master') из расширения pgrowlocks во второй транзакции. Блокировка на уровне строк обновленной строки в master - это FOR UPDATE в случае сбоя, но FOR NO KEY UPDATE во всех трех рабочих случаях. Это полностью соответствует таблице соответствия режима в документации, поскольку режим FOR UPDATE является более сильным, а режим, запрашиваемый оператором вставки, - FOR KEY SHARE (что видно из сообщения об ошибке, также вызывающего select ... for key share команда имеет тот же эффект, что и команда insert.

Документация по режиму FOR UPDATE гласит:

Режим блокировки FOR UPDATE также получает (...) UPDATE, который изменяет значения в определенных столбцах. В настоящее время для случая UPDATE рассматриваются столбцы с уникальным индексом, который можно использовать во внешнем ключе (...)

Это верно для столбца master.detail_id. Однако все еще неясно, почему режим FOR UPDATE не выбирается независимо от наличия триггера и почему наличие триггера вызвало его.

Ответы

Ответ 1

Интересная проблема. Это моя лучшая догадка. Я не проверял ничего из этого.

Вообще говоря, постгресное догадывание о том, как заявления будут влиять на данные, не распространяется на логику триггера. При выполнении второго оператора postgres видит ограничение внешнего ключа и знает, что он должен проверить, является ли присваиваемое (вставленное) значение допустимым, то есть представляет ли он действительный ключ во внешней таблице. Возможно, однако плохая практика, что триггер может повлиять на действительность предлагаемого внешнего ключа (например, если триггер удаляет записи).

(случай 1) Если триггера нет, он может посмотреть на данные (как перед принятием, так и подготовить к принятию) и решить, является ли предложенное значение достоверным. (случай 2) Если нет ограничения FK, то триггер не может повлиять на достоверность вставки, поэтому это разрешено. (случай 3) Если вы опустите detail_id=null, в обновлении нет изменений, поэтому триггер не сработает, поэтому его присутствие не имеет значения.

Я стараюсь избегать ограничений FK и триггеров, когда это возможно. На мой взгляд, лучше, чтобы база данных случайно содержала частично неверные данные, а не зависать полностью, как вы видите здесь. Я бы отбросил все ограничения и триггеры FK и заставил бы все операции обновления и вставки работать через хранимые функции, которые выполняют валидацию в блокировке начала/принятия и обрабатывали неправильные/недопустимые попытки вставки/обновления соответствующим образом и немедленно, вместо того, чтобы заставлять postgres дождитесь принятия команды 1, прежде чем принять решение, разрешена ли команда 2.

Изменить: см. этот вопрос

Редактировать 2: Наиболее близкая вещь, которую я могу найти к официальной документации относительно времени запуска триггеров относительно проверки ограничений, это из документации по триггерам

Триггер может быть указан для срабатывания до того, как будет предпринята попытка выполнить операцию в строке (до проверки ограничений и попытки INSERT, UPDATE или DELETE); или после завершения операции (после проверки ограничений и завершения INSERT, UPDATE или DELETE); или вместо операции (в случае вставки, обновления или удаления в представлении). Если триггер срабатывает до или вместо события, он может пропустить операцию для текущей строки или изменить вставляемую строку (только для операций INSERT и UPDATE).

Это немного неясно, если триггер, происходящий до проверки ограничения, применяется к проверке ограничения других транзакций. В любом случае, эта проблема является либо ошибкой, либо плохо документирована.