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 PG expert 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 master table. 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 obtained timeout 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 consider unnecessary to state here)
Thank you for all the effort.
Tomáš Záluský
