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 example minification. So I'll have to either find a way how to stop generating unnecessary clauses or refactor database to avoid detail_id column at all.
Patching PG source is not option for me at this moment, however, chapter 13.3.2 in documentation could be improved to explicitly state 1. the unchanged value of key/unique column causes FOR NO KEY UPDATE lock and 2. the sole usage of key/unique column in statement causes FOR UPDATE lock when table has trigger. Thank you for clarification. ______________________________________________________________ > Od: "Alvaro Herrera" <alvhe...@2ndquadrant.com> > Komu: "Tomáš Záluský" <zalu...@centrum.cz> > Datum: 05.09.2019 16:00 > Předmět: Re: unexpected rowlock mode when trigger is on the table > > CC: <pgsql-hack...@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 > > > >
