Subject: Re: When UPDATE a row in a table with BEFORE ROW UPDATE trigger, the XMAX of new tuple is set to current XID

From: Adrian Klaver <[email-masked]>
Date: Wed, 6 Aug 2025 11:22:28 -0700

On 8/6/25 05:37, Charles Qi wrote:
> And if we do the updates in multiple subtransactions, multixact will be 
> created, which is not created when the BEFORE ROW UPDATE trigger is absent.
> 
> Is this behavior by design? If so, what is the purpose for the behavior?

I would say this is by design. My reasoning is that the savepoints are 
essentially rollback points and the state of the tuple would need to be 
saved for each potential rollback. Hence a different transaction id for 
each savepoint.

> 
> Tested version:
> PostgreSQL 14.18 (Ubuntu 14.18-0ubuntu0.22.04.1) on x86_64-pc-linux-gnu, 
> compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit
> 
> The attached file reproduce.sql can be used to reproduce the behavior.

-- 
Adrian Klaver
[email-masked]

---

From: Charles Qi <[email-masked]>
Date: Fri, 8 Aug 2025 09:20:00 +0800

As I stated before, when the BEFORE ROW UPDATE trigger is absent, even
if we update the same row in multiple subtransactions inside one top
transaction, no multixact will be created.
Check the attached no_multi.sql for example.

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?
which risks piling up multixacts quickly in savepoint/exception block
scenarios.

---

From: Laurenz Albe <[email-masked]>
Date: Sun, 10 Aug 2025 21:34:19 +0200

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

> which risks piling up multixacts quickly in savepoint/exception block

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

---

From: Charles Qi <[email-masked]>
Date: Mon, 11 Aug 2025 11:34:08 +0800

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

> if a subtransaction needs to take a stronger lock on the row than what

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
We are doing two no key updates in example, and should not need a
stronger lock on the same row.

---

From: Laurenz Albe <[email-masked]>
Date: Wed, 13 Aug 2025 07:14:48 +0200

> stronger lock on the same row.

Still, you could explicitly lock the row in the trigger function with
a high enough lock level to avoid a multixact being created later on.

Yours,
Laurenz Albe