unexpected rowlock mode when trigger is on the table

From: Tomáš Záluský <zalusky(at)centrum(dot)cz>
To: <pgsql-hackers(at)postgresql(dot)org>
Subject: unexpected rowlock mode when trigger is on the table
Date: 2019-09-03 12:56:02
Message-ID: 20190903145602.F566C141@centrum.cz
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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 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ý

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Robert Haas 2019-09-03 12:59:53 Re: block-level incremental backup
Previous Message Fujii Masao 2019-09-03 12:44:25 Re: [PATCH] Speedup truncates of relation forks