public inbox for [email protected]
help / color / mirror / Atom feedRe: When UPDATE a row in a table with BEFORE ROW UPDATE trigger, the XMAX of new tuple is set to current XID
5+ messages / 3 participants
[nested] [flat]
* Re: When UPDATE a row in a table with BEFORE ROW UPDATE trigger, the XMAX of new tuple is set to current XID
@ 2025-08-06 18:22 Adrian Klaver <[email protected]>
2025-08-08 01:20 ` Re: When UPDATE a row in a table with BEFORE ROW UPDATE trigger, the XMAX of new tuple is set to current XID Charles Qi <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Adrian Klaver @ 2025-08-06 18:22 UTC (permalink / raw)
To: Charles Qi <[email protected]>; [email protected]
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 protected]
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: When UPDATE a row in a table with BEFORE ROW UPDATE trigger, the XMAX of new tuple is set to current XID
2025-08-06 18:22 Re: When UPDATE a row in a table with BEFORE ROW UPDATE trigger, the XMAX of new tuple is set to current XID Adrian Klaver <[email protected]>
@ 2025-08-08 01:20 ` Charles Qi <[email protected]>
2025-08-10 19:34 ` Re: When UPDATE a row in a table with BEFORE ROW UPDATE trigger, the XMAX of new tuple is set to current XID Laurenz Albe <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Charles Qi @ 2025-08-08 01:20 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; +Cc: [email protected]
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.
On Thu, Aug 7, 2025 at 2:22 AM Adrian Klaver <[email protected]> wrote:
>
> 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 protected]
Attachments:
[application/octet-stream] no_multi.sql (1.6K, 2-no_multi.sql)
download
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: When UPDATE a row in a table with BEFORE ROW UPDATE trigger, the XMAX of new tuple is set to current XID
2025-08-06 18:22 Re: When UPDATE a row in a table with BEFORE ROW UPDATE trigger, the XMAX of new tuple is set to current XID Adrian Klaver <[email protected]>
2025-08-08 01:20 ` Re: When UPDATE a row in a table with BEFORE ROW UPDATE trigger, the XMAX of new tuple is set to current XID Charles Qi <[email protected]>
@ 2025-08-10 19:34 ` Laurenz Albe <[email protected]>
2025-08-11 03:34 ` Re: When UPDATE a row in a table with BEFORE ROW UPDATE trigger, the XMAX of new tuple is set to current XID Charles Qi <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Laurenz Albe @ 2025-08-10 19:34 UTC (permalink / raw)
To: Charles Qi <[email protected]>; Adrian Klaver <[email protected]>; +Cc: [email protected]
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).
> 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: When UPDATE a row in a table with BEFORE ROW UPDATE trigger, the XMAX of new tuple is set to current XID
2025-08-06 18:22 Re: When UPDATE a row in a table with BEFORE ROW UPDATE trigger, the XMAX of new tuple is set to current XID Adrian Klaver <[email protected]>
2025-08-08 01:20 ` Re: When UPDATE a row in a table with BEFORE ROW UPDATE trigger, the XMAX of new tuple is set to current XID Charles Qi <[email protected]>
2025-08-10 19:34 ` Re: When UPDATE a row in a table with BEFORE ROW UPDATE trigger, the XMAX of new tuple is set to current XID Laurenz Albe <[email protected]>
@ 2025-08-11 03:34 ` Charles Qi <[email protected]>
2025-08-13 05:14 ` Re: When UPDATE a row in a table with BEFORE ROW UPDATE trigger, the XMAX of new tuple is set to current XID Laurenz Albe <[email protected]>
0 siblings, 1 reply; 5+ messages in thread
From: Charles Qi @ 2025-08-11 03:34 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; +Cc: Adrian Klaver <[email protected]>; [email protected]
On Mon, Aug 11, 2025 at 3:34 AM Laurenz Albe <[email protected]> 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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: When UPDATE a row in a table with BEFORE ROW UPDATE trigger, the XMAX of new tuple is set to current XID
2025-08-06 18:22 Re: When UPDATE a row in a table with BEFORE ROW UPDATE trigger, the XMAX of new tuple is set to current XID Adrian Klaver <[email protected]>
2025-08-08 01:20 ` Re: When UPDATE a row in a table with BEFORE ROW UPDATE trigger, the XMAX of new tuple is set to current XID Charles Qi <[email protected]>
2025-08-10 19:34 ` Re: When UPDATE a row in a table with BEFORE ROW UPDATE trigger, the XMAX of new tuple is set to current XID Laurenz Albe <[email protected]>
2025-08-11 03:34 ` Re: When UPDATE a row in a table with BEFORE ROW UPDATE trigger, the XMAX of new tuple is set to current XID Charles Qi <[email protected]>
@ 2025-08-13 05:14 ` Laurenz Albe <[email protected]>
0 siblings, 0 replies; 5+ messages in thread
From: Laurenz Albe @ 2025-08-13 05:14 UTC (permalink / raw)
To: Charles Qi <[email protected]>; +Cc: Adrian Klaver <[email protected]>; [email protected]
On Mon, 2025-08-11 at 11:34 +0800, Charles Qi wrote:
> 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.
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2025-08-13 05:14 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-08-06 18:22 Re: When UPDATE a row in a table with BEFORE ROW UPDATE trigger, the XMAX of new tuple is set to current XID Adrian Klaver <[email protected]>
2025-08-08 01:20 ` Charles Qi <[email protected]>
2025-08-10 19:34 ` Laurenz Albe <[email protected]>
2025-08-11 03:34 ` Charles Qi <[email protected]>
2025-08-13 05:14 ` Laurenz Albe <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox