Re: enhance wraparound warnings

On Sat, Nov 15, 2025 at 2:05 AM Nathan Bossart <nathandbossart(at)gmail(dot)com> wrote:
> I don't know about you, but I start getting antsy around a quarter tank.
> In any case, I'm told that even 40M transactions aren't enough time to
> react these days. Attached are a few patches to enhance the wraparound
> warnings.

Thank you for the patch!

> * 0001 adds a "percent remaining" detail message to the existing WARNING.
> The idea is that "1.86% of transaction IDs" is both easier to understand
> and better indicates urgency than "39985967 transactions".

I like this idea and this is helpful information for DBA. 0001 looks good to me.

> * 0002 bumps the warning limit from 40M to 100M to give folks some more
> time to react.

I don't have a strong opinion on whether 100M is the right value, but
I noticed a documentation issue in 0002.

<programlisting>
WARNING: database "mydb" must be vacuumed within 39985967 transactions
DETAIL: Approximately 1.86% of transaction IDs are available for use.
HINT: To avoid XID assignment failures, execute a database-wide
VACUUM in that database.
</programlisting>

In maintenance.sgml, above "39985967" and "1.86%" should be updated.

> * 0003 adds an early warning system for when fewer than 500M transactions
> remain. This system sends a LOG only to the server log every 1M
> transactions. The hope is that this gets someone's attention sooner
> without flooding the application and server log.

I'm not sure 0003 is worth the added complexity. It adds a new field
to TransamVariablesData and a modulo check in GetNewTransactionId(),
which is a hot path. DBAs who need early warning can already monitor
age(datfrozenxid) with more flexible thresholds.

--
Best regards,
Shinya Kato
NTT OSS Center