Re: When UPDATE a row in a table with BEFORE ROW UPDATE trigger, the XMAX of new tuple is set to current XID - Mailing list pgsql-general

On Mon, Aug 11, 2025 at 3:34 AM Laurenz Albe <laurenz.albe@cybertec.at> wrote:
>
> On Fri, 2025-08-08 at 09:20 +0800, Charles Qi wrote:
> > Let me clarify the question, when the BEFORE ROW UPDATE trigger is presented
> > Q. Why do we need to set the XMAX of the new tuple to the current xid?
>
> Because the row gets locked, I'd say (without looking at your example).
>

With or without the trigger, the row gets locked and unlocked while
the update is doing its thing.
The problem here is that HEAP_XMAX_KEYSHR_LOCK and XMAX are set with
the trigger even if the update transaction is finished, while both are
not set without the trigger.

> > which risks piling up multixacts quickly in savepoint/exception block
> > scenarios.
>
> Why is that a problem for you?
>
> Perhaps the trigger could use SELECT ... FOR ... to lock the row in the
> strongest level your transaction needs.  A multixact is only necessary
> if a subtransaction needs to take a stronger lock on the row than what
> was there before.
>
> Yours,
> Laurenz Albe

The piling up of multixacts are related to the performance topic,
which is not in the scope of this mail.

The trigger function in example is doing nothing but return new, the
row is actually locked by the trigger itself (trigger.c >
ExecBRUpdateTriggers > GetTupleForTrigger)
You mentioned a very important behavior:
> A multixact is only necessary
> if a subtransaction needs to take a stronger lock on the row than what
> was there before.
We are doing two no key updates in example, and should not need a
stronger lock on the same row.