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


Reply via email to