Re: unexpected rowlock mode when trigger is on the table

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
>
>
>
>