Re: unexpected rowlock mode when trigger is on the table

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