Re: unexpected rowlock mode when trigger is on the table

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(at)entry=0x7eff2157b4d8,
tuple=tuple(at)entry=0x7ffe570db3e0, cid=0,
mode=mode(at)entry=LockTupleExclusive,
wait_policy=wait_policy(at)entry=LockWaitBlock,
follow_updates=follow_updates(at)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(at)entry=0x5648b3894110,
epqstate=epqstate(at)entry=0x5648b3894750, tid=tid(at)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(at)entry=0x5648b3894110,
epqstate=epqstate(at)entry=0x5648b3894750,
relinfo=relinfo(at)entry=0x5648b3894260,
tupleid=tupleid(at)entry=0x7ffe570db674,
fdw_trigtuple=fdw_trigtuple(at)entry=0x0, slot=slot(at)entry=0x5648b3896670)
at /pgsql/source/REL9_6_STABLE/src/backend/commands/trigger.c:2432
#3 0x00005648b1d8ddc2 in ExecUpdate (tupleid=tupleid(at)entry=0x7ffe570db674,
oldtuple=oldtuple(at)entry=0x0, slot=slot(at)entry=0x5648b3896670,
planSlot=planSlot(at)entry=0x5648b3895998,
epqstate=epqstate(at)entry=0x5648b3894750,
estate=estate(at)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