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

On 8/7/25 18:20, Charles Qi wrote:
> 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.

Yeah, I saw that in your previous example. I would say it is evidence 
that this due to the trigger/function combination, unfortunately an 
exact answer to this is beyond my present knowledge. I will dig into 
this when I get a chance, but in the meantime hopefully someone with 
more expertise will provide the answer.

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

The information here:

https://www.postgresql.org/docs/current/routine-vacuuming.html#VACUUM-FOR-MULTIXACT-WRAPAROUND

shows that multixact system can handle fairly high levels of activity.

Do you think your normal operations are going to exceed the values shown 
below?

Following the configuration links gets you:

vacuum_multixact_freeze_min_age (integer)

     Specifies the cutoff age (in multixacts) that VACUUM should use to 
decide whether to trigger freezing of pages with an older multixact ID. 
The default is 5 million multixacts. Although users can set this value 
anywhere from zero to one billion, VACUUM will silently limit the 
effective value to half the value of 
autovacuum_multixact_freeze_max_age, so that there is not an 
unreasonably short time between forced autovacuums. For more information 
see Section 24.1.5.1.


vacuum_multixact_freeze_table_age (integer)

     VACUUM performs an aggressive scan if the table's 
pg_class.relminmxid field has reached the age specified by this setting. 
An aggressive scan differs from a regular VACUUM in that it visits every 
page that might contain unfrozen XIDs or MXIDs, not just those that 
might contain dead tuples. The default is 150 million multixacts. 
Although users can set this value anywhere from zero to two billion, 
VACUUM will silently limit the effective value to 95% of 
autovacuum_multixact_freeze_max_age, so that a periodic manual VACUUM 
has a chance to run before an anti-wraparound is launched for the table. 
For more information see Section 24.1.5.1.


autovacuum_multixact_freeze_max_age (integer)

     Specifies the maximum age (in multixacts) that a table's 
pg_class.relminmxid field can attain before a VACUUM operation is forced 
to prevent multixact ID wraparound within the table. Note that the 
system will launch autovacuum processes to prevent wraparound even when 
autovacuum is otherwise disabled.

     Vacuuming multixacts also allows removal of old files from the 
pg_multixact/members and pg_multixact/offsets subdirectories, which is 
why the default is a relatively low 400 million multixacts. This 
parameter can only be set at server start, but the setting can be 
reduced for individual tables by changing table storage parameters. For 
more information see Section 24.1.5.1.

> 
> 
> On Thu, Aug 7, 2025 at 2:22 AM Adrian Klaver <adrian.klaver@aklaver.com> 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
>> adrian.klaver@aklaver.com


-- 
Adrian Klaver
adrian.klaver@aklaver.com