Re: configurable the threshold for warning due to run out of transaction ID

On Tue, 2021-01-19 at 11:44 +0900, Masahiro Ikeda wrote:
> PostgreSQL has the feature to warn about running out of transaction ID.
> The following message is an example.
>
> 2021-01-19 10:59:27 JST [client backend] WARNING: database "postgres" must be vacuumed within xxx transactions
> 2021-01-19 10:59:27 JST [client backend] HINT: To avoid a database shutdown, execute a database-wide VACUUM in that database.
> You might also need to commit or roll back old prepared transactions, or drop stale replication slots.
>
> But, the threshold for the warning is not configurable.
> The value is hard-coded to 40M.
>
> varsup.c
>
> /*
> * We'll start complaining loudly when we get within 40M transactions of
> * data loss. This is kind of arbitrary, but if you let your gas gauge
> * get down to 2% of full, would you be looking for the next gas station?
> * We need to be fairly liberal about this number because there are lots
> * of scenarios where most transactions are done by automatic clients that
> * won't pay attention to warnings. (No, we're not gonna make this
> * configurable. If you know enough to configure it, you know enough to
> * not get in this kind of trouble in the first place.)
> */
>
> I think it's useful to configure the threshold for warning due to run out of
> transaction ID like "checkpoint_warning" parameter.

I think the argument in the comment is a good one: people who know enough to
increase the number because they consume lots of transactions and want to be
warned earlier are probably people who care enough about their database to have
some monitoring in place that warns them about approaching transaction wraparound
("datfrozenxid" and "datminmxid" in "pg_database").

People who lower the limit to get rid of the warning are hopeless, and there is
no need to support such activity.

So I don't see much point in making this configurable.
We have enough parameters as it is.

Yours,
Laurenz Albe