Обсуждение: unexpected rowlock mode when trigger is on the table
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ý
On 2019-Sep-03, Tomáš Záluský wrote:
> 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)
Hmm, so I'm guessing that this tuple lock comes from GetTupleForTrigger
called from ExecBRUpdateTriggers, which uses ExecUpdateLockMode() in
order to figure out the lockmode to use, depending on whether the
modified columns by the update overlap columns indexed by any unique
index. I think there should be no overlap (PK is column "id", not modified)
but I may be missing something.
(gdb) bt
#0 heap_lock_tuple (relation=relation@entry=0x7eff2157b4d8,
tuple=tuple@entry=0x7ffe570db3e0, cid=0,
mode=mode@entry=LockTupleExclusive,
wait_policy=wait_policy@entry=LockWaitBlock,
follow_updates=follow_updates@entry=0 '\000', buffer=0x7ffe570db3cc,
hufd=0x7ffe570db3d0)
at /pgsql/source/REL9_6_STABLE/src/backend/access/heap/heapam.c:4577
#1 0x00005648b1d52f15 in GetTupleForTrigger (
estate=estate@entry=0x5648b3894110,
epqstate=epqstate@entry=0x5648b3894750, tid=tid@entry=0x7ffe570db674,
lockmode=LockTupleExclusive, newSlot=0x7ffe570db498,
relinfo=<optimized out>, relinfo=<optimized out>)
at /pgsql/source/REL9_6_STABLE/src/backend/commands/trigger.c:2709
#2 0x00005648b1d579a0 in ExecBRUpdateTriggers (
estate=estate@entry=0x5648b3894110,
epqstate=epqstate@entry=0x5648b3894750,
relinfo=relinfo@entry=0x5648b3894260,
tupleid=tupleid@entry=0x7ffe570db674,
fdw_trigtuple=fdw_trigtuple@entry=0x0, slot=slot@entry=0x5648b3896670)
at /pgsql/source/REL9_6_STABLE/src/backend/commands/trigger.c:2432
#3 0x00005648b1d8ddc2 in ExecUpdate (tupleid=tupleid@entry=0x7ffe570db674,
oldtuple=oldtuple@entry=0x0, slot=slot@entry=0x5648b3896670,
planSlot=planSlot@entry=0x5648b3895998,
epqstate=epqstate@entry=0x5648b3894750,
estate=estate@entry=0x5648b3894110, canSetTag=1 '\001')
at /pgsql/source/REL9_6_STABLE/src/backend/executor/nodeModifyTable.c:850
Maybe we're passing an argument wrong somewhere. Unclear ...
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Thanks for response.
> I think there should be no overlap (PK is column "id", not modified)
The update command sets the detail_id column which has unique constraint.
If I read documentation correctly (13.3.2. Row-level Locks), unique columns also count to columns whose presence in
updatestatement causes choosing FOR UPDATE lock.
What is unclear to me, why FOR NO KEY UPDATE is chosen when there is no trigger.
Perhaps the execution path to ExecUpdateLockMode is somehow different?
And if FOR NO KEY UPDATE is correct, how to achieve it also with trigger?
Tomáš
______________________________________________________________
> Od: "Alvaro Herrera" <alvherre@2ndquadrant.com>
> Komu: "Tomáš Záluský" <zalusky@centrum.cz>
> Datum: 05.09.2019 00:52
> Předmět: Re: unexpected rowlock mode when trigger is on the table
>
> CC: <pgsql-hackers@postgresql.org>
>On 2019-Sep-03, Tomáš Záluský wrote:
>
>> 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)
>
>Hmm, so I'm guessing that this tuple lock comes from GetTupleForTrigger
>called from ExecBRUpdateTriggers, which uses ExecUpdateLockMode() in
>order to figure out the lockmode to use, depending on whether the
>modified columns by the update overlap columns indexed by any unique
>index. I think there should be no overlap (PK is column "id", not modified)
>but I may be missing something.
>
>(gdb) bt
>#0 heap_lock_tuple (relation=relation@entry=0x7eff2157b4d8,
> tuple=tuple@entry=0x7ffe570db3e0, cid=0,
> mode=mode@entry=LockTupleExclusive,
> wait_policy=wait_policy@entry=LockWaitBlock,
> follow_updates=follow_updates@entry=0 '\000', buffer=0x7ffe570db3cc,
> hufd=0x7ffe570db3d0)
> at /pgsql/source/REL9_6_STABLE/src/backend/access/heap/heapam.c:4577
>#1 0x00005648b1d52f15 in GetTupleForTrigger (
> estate=estate@entry=0x5648b3894110,
> epqstate=epqstate@entry=0x5648b3894750, tid=tid@entry=0x7ffe570db674,
> lockmode=LockTupleExclusive, newSlot=0x7ffe570db498,
> relinfo=<optimized out>, relinfo=<optimized out>)
> at /pgsql/source/REL9_6_STABLE/src/backend/commands/trigger.c:2709
>#2 0x00005648b1d579a0 in ExecBRUpdateTriggers (
> estate=estate@entry=0x5648b3894110,
> epqstate=epqstate@entry=0x5648b3894750,
> relinfo=relinfo@entry=0x5648b3894260,
> tupleid=tupleid@entry=0x7ffe570db674,
> fdw_trigtuple=fdw_trigtuple@entry=0x0, slot=slot@entry=0x5648b3896670)
> at /pgsql/source/REL9_6_STABLE/src/backend/commands/trigger.c:2432
>#3 0x00005648b1d8ddc2 in ExecUpdate (tupleid=tupleid@entry=0x7ffe570db674,
> oldtuple=oldtuple@entry=0x0, slot=slot@entry=0x5648b3896670,
> planSlot=planSlot@entry=0x5648b3895998,
> epqstate=epqstate@entry=0x5648b3894750,
> estate=estate@entry=0x5648b3894110, canSetTag=1 '\001')
> at /pgsql/source/REL9_6_STABLE/src/backend/executor/nodeModifyTable.c:850
>
>Maybe we're passing an argument wrong somewhere. Unclear ...
>
>--
>Álvaro Herrera https://www.2ndQuadrant.com/
>PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>
>
>
>
On 2019-Sep-05, Tomáš Záluský wrote: > Thanks for response. > > > I think there should be no overlap (PK is column "id", not modified) > > The update command sets the detail_id column which has unique constraint. Oh, I see, yeah that explains it. > What is unclear to me, why FOR NO KEY UPDATE is chosen when there is no trigger. > Perhaps the execution path to ExecUpdateLockMode is somehow different? heap_update on its own uses a slightly different method to determine which columns are modified -- see HeapDetermineModifiedColumns. In this case, since the old value is NULL and the updated value is NULL, that function decides that the column has not changed and thus it doesn't need the stronger lock. I bet it would work differently if you had a different detail_id originally, or if you set it to a different value afterwards. > And if FOR NO KEY UPDATE is correct, how to achieve it also with trigger? Not sure that's feasible, short of patching the Pg source. -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
I confirm the update statement with new value distinct from old value causes taking FOR UPDATE lock. In my original example, the `set detail_id=null` clause is actually generated by Hibernate and was preserved during exampleminification. So I'll have to either find a way how to stop generating unnecessary clauses or refactor database to avoid detail_id columnat all. Patching PG source is not option for me at this moment, however, chapter 13.3.2 in documentation could be improved to explicitlystate 1. the unchanged value of key/unique column causes FOR NO KEY UPDATE lock and 2. the sole usage of key/uniquecolumn in statement causes FOR UPDATE lock when table has trigger. Thank you for clarification. ______________________________________________________________ > Od: "Alvaro Herrera" <alvherre@2ndquadrant.com> > Komu: "Tomáš Záluský" <zalusky@centrum.cz> > Datum: 05.09.2019 16:00 > Předmět: Re: unexpected rowlock mode when trigger is on the table > > CC: <pgsql-hackers@postgresql.org> >On 2019-Sep-05, Tomáš Záluský wrote: > >> Thanks for response. >> >> > I think there should be no overlap (PK is column "id", not modified) >> >> The update command sets the detail_id column which has unique constraint. > >Oh, I see, yeah that explains it. > >> What is unclear to me, why FOR NO KEY UPDATE is chosen when there is no trigger. >> Perhaps the execution path to ExecUpdateLockMode is somehow different? > >heap_update on its own uses a slightly different method to determine >which columns are modified -- see HeapDetermineModifiedColumns. In this >case, since the old value is NULL and the updated value is NULL, that >function decides that the column has not changed and thus it doesn't >need the stronger lock. I bet it would work differently if you had a >different detail_id originally, or if you set it to a different value >afterwards. > >> And if FOR NO KEY UPDATE is correct, how to achieve it also with trigger? > >Not sure that's feasible, short of patching the Pg source. > >-- >Álvaro Herrera https://www.2ndQuadrant.com/ >PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services > > > >