unexpected rowlock mode when trigger is on the table - Mailing list pgsql-hackers

Hi pgsql hackers,

I'd like to ask you for kind advice with problem I'm pretty long stuck on. I simplified it just to two tables and
trigger.I described it thoroughly on Stack Overflow a week ago but nobody responded so far, so I was advised by Czech
PGexpert Pavel Stěhule to use this forum.
 

Database setup:

------------
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 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');
insert into detail (code, id) values ('a', 1);

create extension pgrowlocks;
------------

In psql console, I open first transaction and run:

postgres=# begin;
BEGIN
postgres=# update master set detail_id=null, name='y' where id=1000;
UPDATE 1

In another psql console, I run:

postgres=# select * from pgrowlocks('master');
 locked_row | locker | multi | xids  |  modes   | pids
------------+--------+-------+-------+----------+-------
 (0,3)      |    564 | f     | {564} | {Update} | {138}
(1 row)

Note the mode is Update, which means the attempt of taking FOR KEY SHARE lock is not successful:

postgres=# set statement_timeout = 4000;
SET
postgres=# SELECT 1 FROM ONLY "public"."master" x WHERE "id" OPERATOR(pg_catalog.=) 1000 FOR KEY SHARE OF x;
ERROR:  canceling statement due to statement timeout
CONTEXT:  while locking tuple (0,3) in relation "master"

This is ok. What is weird: this behaviour disappears when whole experiment is performed without trigger trg set up on
mastertable. Mode is then No Key Update, which lets second transaction to acquire FOR KEY SHARE lock and perform select
correctly.(Actually there's another table representing many-to-many relation between master and detail, at first I
obtainedtimeout during attempt of insert binding row.)
 

So what I can't understand is:

1. Why the rowlock mode is only No Key Update in case without trigger? According to
https://www.postgresql.org/docs/9.6/explicit-locking.html#LOCKING-ROWS, thanks to unique constraint on master.detail_id
column,the rowlock mode should be Update anyway, shouldn't it? Why is it Update only after adding trigger?
 

2. How to make this case working with trigger on the table? I need it to be there, it worked before trigger addition.

I reproduced it on Postgres 9.6.12 (embedded), 9.6.15 (in Docker) and 11.5 (in Docker).
I dockerized database with setup above to DockerHub image tomaszalusky/trig-example , Dockerfile here:
https://gist.github.com/tomaszalusky/4b953c678c806408025d05d984d30ed3
 
Original SO question: https://stackoverflow.com/q/57681970/653539 (captures some history of my investigations which I
considerunnecessary to state here)
 

Thank you for all the effort.

Tomáš Záluský