Обсуждение: configurable the threshold for warning due to run out of transaction ID

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

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