Hi,
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.
Actually, when a user's workload is too write-heavy,
there was a case we want to get the warning message earlier.
I understood that there is another way to handle it.
For example, to monitor frozen transaction ID to execute the following
query
and check to see if the custom threshold is exceeded.
```
SELECT max(age(datfrozenxid)) FROM pg_database;
```
But, I think to warn to a server log is a simpler way.
I would like to know your opinion.
If it's useful for us, I'll make patches.
Regards,
--
Masahiro Ikeda
NTT DATA CORPORATION
