We should stop telling users to "vacuum that database in single-user mode"

It looks like we are unnecessarily instructing our usiers to vacuum their
databases in single-user mode when just vacuuming would be enough.

We should fix the error message to be less misleading.

== The story

I think most of us have at some time seen the following message, if not in their
own database, then at some client.

ERROR: database is not accepting commands to avoid wraparound data
loss in database "<dbname>"
HINT: Stop the postmaster and vacuum that database in single-user mode.
You might also need to commit or roll back old prepared transactions.

But "vacuum that database in single-user mode" is the very last thing
one wants to
do, because

* it is single-user mode, so nothing else works ...
* CHECKPOINTs are not running, so all the WAL segments can not be rotated and
reused
* Replication does not work, so after vacuum is done and database is started
in normal mode, there is huge backlog to replicate
* pg_stat_progress_vacuum is not available so you have no idea when the
command is going to complete
* VACUUM VERBOSE isn't - there is absolutely no output from single-user mode
vacuum with or without VERBOSE, so you *really* do not know what is going on
and how much progress is made (if you are locky you can guess something from
IO and CPU monitoring, but it is inexact at best )

When I started looking at improving the situation I discovered, that there
already is no need to run VACUUM in single user mode in any currently supported
PostgreSQL version as you can run VACUUM perfectly well when the wraparound
protection is active.

It worked in all PG versions from v9.6 to v13.

I also tested v 8.3 as this is where we added virtual transactions, but there
VACUUM really fails to run successfully without single-user mode..

So my proposal is to change the error message [*] to something that does not
suggest the single-user mode as the requirement for running VACUUM.
Also COMMIT PREPARED still works ok in this situation.

Single-user mode still may be needed in case one needs to drop a
replication slot
or something similar.

[*] The message is in src/backend/access/transam/varsup.c around line 120

=== How to test

The following instructions let you run into wraparound in about an hour,
depending on your setup (was 1.2 hours on my laptop)

==== First, set some flags

To allow PREPARE TRANSACTION to block VACUUM cleanup

```
alter system set max_prepared_transactions = 10;
```

Also set *_min_messages to errors, unless you want to get 10M of
WARNINGs (~4GB) to
logs and the same amount sent to client, slowing down the last 10M transactions
significantly.

```
alter system set log_min_messages = error;
alter system set client_min_messages = error;
```

==== Restart the system to activate the settings

==== Block Vacuum from cleaning up transactions

Create a database `wraptest` and connect to it, then

```
create table t (i int);

BEGIN;
insert into t values(1);
PREPARE TRANSACTION 'trx_id_pin';
```

Now you have a prepared transaction, which makes sure that even well-tuned
autovacuum does not prevent running into the wraparound protection.

```
[local]:5096 hannu(at)wraptest=# SELECT * FROM pg_prepared_xacts;
transaction | gid | prepared | owner | database
-------------+------------+-------------------------------+-------+----------
593 | trx_id_pin | 2021-03-01 08:57:27.024777+01 | hannu | wraptest
(1 row)
```

==== Create a function to consume transaction ids as fast as possible:

```
CREATE OR REPLACE FUNCTION trx_eater(n int) RETURNS void
LANGUAGE plpgsql
AS $plpgsql$
BEGIN
FOR i IN 0..n LOOP
BEGIN
INSERT INTO t values(i);
EXCEPTION WHEN OTHERS THEN
RAISE; -- raise it again, so that we actually err out on wraparound
END;
END LOOP;
END;
$plpgsql$;
```

==== Use pgbench to drive this function

Make a pgbench command file

$ echo 'select trx_eater(100000);' > trx_eater.pgbench

and start pgbench to run this function in a few backends in parallel

$ pgbench -c 16 -T 20000 -P 60 -n wraptest -f trx_eater.pgbench

==== Wait 1-2 hours

In about an hour or two this should error out with

ERROR: database is not accepting commands to avoid wraparound data
loss in database "postgres"
HINT: Stop the postmaster and vacuum that database in single-user mode.
You might also need to commit or roll back old prepared transactions.

After this just do

COMMIT PREPARED 'trx_id_pin';

==== Verify that VACUUM still works

to release the blocket 2PC transaction and you can verify yourself that

* you can run VACUUM on any table, and
* Autovacuum is working, and will eventually clear up the situation

If you have not tuned autovacuum_vacuum_cost_* at all, especially in earlier
versions where it is 20ms by default the autovacuum-started vacuum is
running really
slowly, and it will take about 8 hours to clean up the table, but this
can be sped up
if you set autovacuum_vacuum_cost_delay=0 and then either restart the database
or just kill the vacuum process after reloading flags. After this it
should complete in
15-30 min, after which the database is available for writes again.

Cheers,
Hannu Krosing