Re: unexpected rowlock mode when trigger is on the table

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