[PATCH] Clarify the behavior of the system when approaching XID wraparound

From 03e6f42077e25de71ceffeb839c09f4530bc1aa8 Mon Sep 17 00:00:00 2001
From: Aleksander Alekseev <aleksander@timescale.com>
Date: Mon, 16 Jan 2023 12:23:30 +0300
Subject: [PATCH v2 1/4] Correct the docs about preventing XID wraparound

Prior to this commit the documentation was somewhat misleading. It stated that
the system will "shutdown" in order to prevent XID wraparound. This is not
what actually happens though.

Actually the system refuses to allocate new XIDs. XIDs are allocated lazily
and not needed for checking the visibility rules. Thus read-only transaction
will continues to execute. Additionally, read-write transactions that are
in progress and have an allocated XID will continue to execute and can be
committed.

Author: Aleksander Alekseev
Reviewed-by: TODO FIXME
Discussion: https://postgr.es/m/CAJ7c6TM2D277U2wH8X78kg8pH3tdUqebV3_JCJqAkYQFHCFzeg@mail.gmail.com
---
 doc/src/sgml/maintenance.sgml | 19 ++++++++++---------
 1 file changed, 10 insertions(+), 9 deletions(-)

diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index 759ea5ac9c..2aa072ba89 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -662,24 +662,25 @@ HINT:  To avoid a database shutdown, execute a database-wide VACUUM in that data
     hint; but note that the <command>VACUUM</command> must be performed by a
     superuser, else it will fail to process system catalogs and thus not
     be able to advance the database's <structfield>datfrozenxid</structfield>.)
-    If these warnings are
-    ignored, the system will shut down and refuse to start any new
-    transactions once there are fewer than three million transactions left
-    until wraparound:
+    If these warnings are ignored, the system will refuse to allocate new XIDs
+    once there are fewer than three million transactions left until wraparound:
 
 <programlisting>
 ERROR:  database is not accepting commands to avoid wraparound data loss in database "mydb"
 HINT:  Stop the postmaster and vacuum that database in single-user mode.
 </programlisting>
 
+    In this condition the system can still execute read-only transactions.
+    The active transactions will continue to execute and will be able to
+    commit.
+
     The three-million-transaction safety margin exists to let the
     administrator recover without data loss, by manually executing the
-    required <command>VACUUM</command> commands.  However, since the system will not
-    execute commands once it has gone into the safety shutdown mode,
+    required <command>VACUUM</command> commands.  However
     the only way to do this is to stop the server and start the server in single-user
-    mode to execute <command>VACUUM</command>.  The shutdown mode is not enforced
-    in single-user mode.  See the <xref linkend="app-postgres"/> reference
-    page for details about using single-user mode.
+    mode to execute <command>VACUUM</command>. See the
+    <xref linkend="app-postgres"/> reference page for details about using
+    single-user mode.
    </para>
 
    <sect3 id="vacuum-for-multixact-wraparound">
-- 
2.39.0

From 5ce785a5617aa9ab38d4a414f1a927d8de292abf Mon Sep 17 00:00:00 2001
From: Aleksander Alekseev <aleksander@timescale.com>
Date: Mon, 16 Jan 2023 12:49:39 +0300
Subject: [PATCH v2 3/4] Fix the message in case of exceeding xidWarnLimit

Previously the message said that the user should execute VACCUM in order to
prevent the database shutdown. This gave a wrong impression that the entire
database may become unavailable.

This is not what happens though. Rather the system refuses to allocate
new XIDs. From the user perspective it looks like entering a read-only mode,
similarly to what the user sees on hot standby replicas.

This patch changes the message accordingly.

Author: Aleksander Alekseev
Reviewed-by: TODO FIXME
Discussion: https://postgr.es/m/CAJ7c6TM2D277U2wH8X78kg8pH3tdUqebV3_JCJqAkYQFHCFzeg@mail.gmail.com
---
 doc/src/sgml/maintenance.sgml       | 2 +-
 src/backend/access/transam/varsup.c | 8 ++++----
 2 files changed, 5 insertions(+), 5 deletions(-)

diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index 48330605c7..d21675fac9 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -655,7 +655,7 @@ SELECT datname, age(datfrozenxid) FROM pg_database;
 
 <programlisting>
 WARNING:  database "mydb" must be vacuumed within 39985967 transactions
-HINT:  To avoid a database shutdown, execute a database-wide VACUUM in that database.
+HINT:  To prevent entering read-only mode, execute a database-wide VACUUM in that database.
 </programlisting>
 
     (A manual <command>VACUUM</command> should fix the problem, as suggested by the
diff --git a/src/backend/access/transam/varsup.c b/src/backend/access/transam/varsup.c
index fece6cf31b..485d8ebf81 100644
--- a/src/backend/access/transam/varsup.c
+++ b/src/backend/access/transam/varsup.c
@@ -148,14 +148,14 @@ GetNewTransactionId(bool isSubXact)
 						(errmsg("database \"%s\" must be vacuumed within %u transactions",
 								oldest_datname,
 								xidWrapLimit - xid),
-						 errhint("To avoid a database shutdown, execute a database-wide VACUUM in that database.\n"
+						 errhint("To prevent entering read-only mode, execute a database-wide VACUUM in that database.\n"
 								 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 			else
 				ereport(WARNING,
 						(errmsg("database with OID %u must be vacuumed within %u transactions",
 								oldest_datoid,
 								xidWrapLimit - xid),
-						 errhint("To avoid a database shutdown, execute a database-wide VACUUM in that database.\n"
+						 errhint("To prevent entering read-only mode, execute a database-wide VACUUM in that database.\n"
 								 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 		}
 
@@ -463,14 +463,14 @@ SetTransactionIdLimit(TransactionId oldest_datfrozenxid, Oid oldest_datoid)
 					(errmsg("database \"%s\" must be vacuumed within %u transactions",
 							oldest_datname,
 							xidWrapLimit - curXid),
-					 errhint("To avoid a database shutdown, execute a database-wide VACUUM in that database.\n"
+					 errhint("To prevent entering read-only mode, execute a database-wide VACUUM in that database.\n"
 							 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 		else
 			ereport(WARNING,
 					(errmsg("database with OID %u must be vacuumed within %u transactions",
 							oldest_datoid,
 							xidWrapLimit - curXid),
-					 errhint("To avoid a database shutdown, execute a database-wide VACUUM in that database.\n"
+					 errhint("To prevent entering read-only mode, execute a database-wide VACUUM in that database.\n"
 							 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 	}
 }
-- 
2.39.0

From 7656a445b24914d402e00465de8491453c83c89d Mon Sep 17 00:00:00 2001
From: Aleksander Alekseev <aleksander@timescale.com>
Date: Mon, 16 Jan 2023 15:37:12 +0300
Subject: [PATCH v2 4/4] Don't recommend running VACUUM in a single-user mode
 when reaching xidStopLimit

This recommendation is outdated for some time now. A regular VACUUM or
VACUUM FREEZE works just fine.

Author: Aleksander Alekseev
Reported-by: Hannu Krosing
Reviewed-by: TODO FIXME
Discussion: https://postgr.es/m/CAJ7c6TM2D277U2wH8X78kg8pH3tdUqebV3_JCJqAkYQFHCFzeg@mail.gmail.com
Discussion: https://postgr.es/m/CAMT0RQTmRj_Egtmre6fbiMA9E2hM3BsLULiV8W00stwa3URvzA%40mail.gmail.com
---
 doc/src/sgml/maintenance.sgml       | 8 ++------
 src/backend/access/transam/varsup.c | 8 +++-----
 2 files changed, 5 insertions(+), 11 deletions(-)

diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index d21675fac9..3b78098232 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -667,7 +667,7 @@ HINT:  To prevent entering read-only mode, execute a database-wide VACUUM in tha
 
 <programlisting>
 ERROR:  database is not accepting commands that generate new XIDs to avoid wraparound data loss in database "mydb"
-HINT:  Stop the postmaster and vacuum that database in single-user mode.
+HINT:  VACUUM or VACUUM FREEZE that database.
 </programlisting>
 
     In this condition the system can still execute read-only transactions.
@@ -676,11 +676,7 @@ HINT:  Stop the postmaster and vacuum that database in single-user mode.
 
     The three-million-transaction safety margin exists to let the
     administrator recover without data loss, by manually executing the
-    required <command>VACUUM</command> commands.  However
-    the only way to do this is to stop the server and start the server in single-user
-    mode to execute <command>VACUUM</command>. See the
-    <xref linkend="app-postgres"/> reference page for details about using
-    single-user mode.
+    required <command>VACUUM</command> commands.
    </para>
 
    <sect3 id="vacuum-for-multixact-wraparound">
diff --git a/src/backend/access/transam/varsup.c b/src/backend/access/transam/varsup.c
index 485d8ebf81..a79144d445 100644
--- a/src/backend/access/transam/varsup.c
+++ b/src/backend/access/transam/varsup.c
@@ -86,9 +86,7 @@ GetNewTransactionId(bool isSubXact)
 	 *
 	 * If we're past xidVacLimit, start trying to force autovacuum cycles.
 	 * If we're past xidWarnLimit, start issuing warnings.
-	 * If we're past xidStopLimit, refuse to execute transactions, unless
-	 * we are running in single-user mode (which gives an escape hatch
-	 * to the DBA who somehow got past the earlier defenses).
+	 * If we're past xidStopLimit, refuse to allocate new XIDs.
 	 *
 	 * Note that this coding also appears in GetNewMultiXactId.
 	 *----------
@@ -128,14 +126,14 @@ GetNewTransactionId(bool isSubXact)
 						(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
 						 errmsg("database is not accepting commands that generate new XIDs to avoid wraparound data loss in database \"%s\"",
 								oldest_datname),
-						 errhint("Stop the postmaster and vacuum that database in single-user mode.\n"
+						 errhint("VACUUM or VACUUM FREEZE that database.\n"
 								 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 			else
 				ereport(ERROR,
 						(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
 						 errmsg("database is not accepting commands that generate new XIDs to avoid wraparound data loss in database with OID %u",
 								oldest_datoid),
-						 errhint("Stop the postmaster and vacuum that database in single-user mode.\n"
+						 errhint("VACUUM or VACUUM FREEZE that database.\n"
 								 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 		}
 		else if (TransactionIdFollowsOrEquals(xid, xidWarnLimit))
-- 
2.39.0

From 8de345838a987de5e0ac74c7bd70dc0d746a4574 Mon Sep 17 00:00:00 2001
From: Aleksander Alekseev <aleksander@timescale.com>
Date: Mon, 16 Jan 2023 12:34:18 +0300
Subject: [PATCH v2 2/4] Fix the message in case of approaching xidWrapLimit

Prior to this commit the error message said that the database doesn't accept
new commands when it approaches xidWrapLimit. This is not true. The commands
that don't allocate new XIDs can still be executed. E.g. read-only
transactions continue to work.

This commit clarifies this. Additionally it makes the error messages consistent
with the similar messages for MultiXactIds which interestingly were accurate.

Author: Aleksander Alekseev
Reviewed-by: TODO FIXME
Discussion: https://postgr.es/m/CAJ7c6TM2D277U2wH8X78kg8pH3tdUqebV3_JCJqAkYQFHCFzeg@mail.gmail.com
---
 doc/src/sgml/maintenance.sgml       | 2 +-
 src/backend/access/transam/varsup.c | 4 ++--
 2 files changed, 3 insertions(+), 3 deletions(-)

diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index 2aa072ba89..48330605c7 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -666,7 +666,7 @@ HINT:  To avoid a database shutdown, execute a database-wide VACUUM in that data
     once there are fewer than three million transactions left until wraparound:
 
 <programlisting>
-ERROR:  database is not accepting commands to avoid wraparound data loss in database "mydb"
+ERROR:  database is not accepting commands that generate new XIDs to avoid wraparound data loss in database "mydb"
 HINT:  Stop the postmaster and vacuum that database in single-user mode.
 </programlisting>
 
diff --git a/src/backend/access/transam/varsup.c b/src/backend/access/transam/varsup.c
index 334adac09e..fece6cf31b 100644
--- a/src/backend/access/transam/varsup.c
+++ b/src/backend/access/transam/varsup.c
@@ -126,14 +126,14 @@ GetNewTransactionId(bool isSubXact)
 			if (oldest_datname)
 				ereport(ERROR,
 						(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
-						 errmsg("database is not accepting commands to avoid wraparound data loss in database \"%s\"",
+						 errmsg("database is not accepting commands that generate new XIDs to avoid wraparound data loss in database \"%s\"",
 								oldest_datname),
 						 errhint("Stop the postmaster and vacuum that database in single-user mode.\n"
 								 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 			else
 				ereport(ERROR,
 						(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
-						 errmsg("database is not accepting commands to avoid wraparound data loss in database with OID %u",
+						 errmsg("database is not accepting commands that generate new XIDs to avoid wraparound data loss in database with OID %u",
 								oldest_datoid),
 						 errhint("Stop the postmaster and vacuum that database in single-user mode.\n"
 								 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
-- 
2.39.0

From 7f534bdb15e89ca3ed88712c457cee7d69892da3 Mon Sep 17 00:00:00 2001
From: Aleksander Alekseev <aleksander@timescale.com>
Date: Mon, 16 Jan 2023 12:23:30 +0300
Subject: [PATCH v3 1/4] Correct the docs about preventing XID wraparound

Prior to this commit the documentation was somewhat misleading. It stated that
the system will "shutdown" in order to prevent XID wraparound. This is not
what actually happens though.

Actually the system refuses to allocate new XIDs. XIDs are allocated lazily
and not needed for checking the visibility rules. Thus read-only transaction
will continues to execute. Additionally, read-write transactions that are
in progress and have an allocated XID will continue to execute and can be
committed.

Author: Aleksander Alekseev
Reviewed-by: John Naylor
Discussion: https://postgr.es/m/CAJ7c6TM2D277U2wH8X78kg8pH3tdUqebV3_JCJqAkYQFHCFzeg@mail.gmail.com
---
 doc/src/sgml/maintenance.sgml | 18 +++++++++---------
 1 file changed, 9 insertions(+), 9 deletions(-)

diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index 9cf9d030a8..92d99a8f8c 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -663,24 +663,24 @@ HINT:  To avoid a database shutdown, execute a database-wide VACUUM in that data
     hint; but note that the <command>VACUUM</command> must be performed by a
     superuser, else it will fail to process system catalogs and thus not
     be able to advance the database's <structfield>datfrozenxid</structfield>.)
-    If these warnings are
-    ignored, the system will shut down and refuse to start any new
-    transactions once there are fewer than three million transactions left
-    until wraparound:
+    If these warnings are ignored, the system will refuse to allocate new XIDs
+    once there are fewer than three million transactions left until wraparound:
 
 <programlisting>
 ERROR:  database is not accepting commands to avoid wraparound data loss in database "mydb"
 HINT:  Stop the postmaster and vacuum that database in single-user mode.
 </programlisting>
 
+    In this condition any transactions already started can continue.
+    Only new read-only transactions can be started.
+
     The three-million-transaction safety margin exists to let the
     administrator recover without data loss, by manually executing the
-    required <command>VACUUM</command> commands.  However, since the system will not
-    execute commands once it has gone into the safety shutdown mode,
+    required <command>VACUUM</command> commands.  However
     the only way to do this is to stop the server and start the server in single-user
-    mode to execute <command>VACUUM</command>.  The shutdown mode is not enforced
-    in single-user mode.  See the <xref linkend="app-postgres"/> reference
-    page for details about using single-user mode.
+    mode to execute <command>VACUUM</command>. See the
+    <xref linkend="app-postgres"/> reference page for details about using
+    single-user mode.
    </para>
 
    <sect3 id="vacuum-for-multixact-wraparound">
-- 
2.39.2

From 669709ad943149b39ec9db06e4603dadf2dd146c Mon Sep 17 00:00:00 2001
From: Aleksander Alekseev <aleksander@timescale.com>
Date: Mon, 16 Jan 2023 12:34:18 +0300
Subject: [PATCH v3 2/4] Fix the message in case of approaching xidWrapLimit

Prior to this commit the error message said that the database doesn't accept
new commands when it approaches xidWrapLimit. This is not true. The commands
that don't allocate new XIDs can still be executed. E.g. read-only
transactions continue to work.

This commit clarifies this. Additionally it makes the error messages consistent
with the similar messages for MultiXactIds which interestingly were accurate.

Author: Aleksander Alekseev
Reviewed-by: John Naylor
Discussion: https://postgr.es/m/CAJ7c6TM2D277U2wH8X78kg8pH3tdUqebV3_JCJqAkYQFHCFzeg@mail.gmail.com
---
 doc/src/sgml/maintenance.sgml       | 2 +-
 src/backend/access/transam/varsup.c | 4 ++--
 2 files changed, 3 insertions(+), 3 deletions(-)

diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index 92d99a8f8c..b0336b12e5 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -667,7 +667,7 @@ HINT:  To avoid a database shutdown, execute a database-wide VACUUM in that data
     once there are fewer than three million transactions left until wraparound:
 
 <programlisting>
-ERROR:  database is not accepting commands to avoid wraparound data loss in database "mydb"
+ERROR:  database is not accepting commands that generate new XIDs to avoid wraparound data loss in database "mydb"
 HINT:  Stop the postmaster and vacuum that database in single-user mode.
 </programlisting>
 
diff --git a/src/backend/access/transam/varsup.c b/src/backend/access/transam/varsup.c
index 334adac09e..fece6cf31b 100644
--- a/src/backend/access/transam/varsup.c
+++ b/src/backend/access/transam/varsup.c
@@ -126,14 +126,14 @@ GetNewTransactionId(bool isSubXact)
 			if (oldest_datname)
 				ereport(ERROR,
 						(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
-						 errmsg("database is not accepting commands to avoid wraparound data loss in database \"%s\"",
+						 errmsg("database is not accepting commands that generate new XIDs to avoid wraparound data loss in database \"%s\"",
 								oldest_datname),
 						 errhint("Stop the postmaster and vacuum that database in single-user mode.\n"
 								 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 			else
 				ereport(ERROR,
 						(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
-						 errmsg("database is not accepting commands to avoid wraparound data loss in database with OID %u",
+						 errmsg("database is not accepting commands that generate new XIDs to avoid wraparound data loss in database with OID %u",
 								oldest_datoid),
 						 errhint("Stop the postmaster and vacuum that database in single-user mode.\n"
 								 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
-- 
2.39.2

From 23f01af9c2e807f4d4bb175a25ef9273f38388b9 Mon Sep 17 00:00:00 2001
From: Aleksander Alekseev <aleksander@timescale.com>
Date: Mon, 16 Jan 2023 12:49:39 +0300
Subject: [PATCH v3 3/4] Fix the message in case of exceeding xidWarnLimit

Previously the message said that the user should execute VACCUM in order to
prevent the database shutdown. This gave a wrong impression that the entire
database may become unavailable.

This is not what happens though. Rather the system refuses to allocate
new XIDs. From the user perspective it looks like entering a read-only mode,
similarly to what the user sees on hot standby replicas.

This patch changes the message accordingly.

Author: Aleksander Alekseev
Reviewed-by: John Naylor
Discussion: https://postgr.es/m/CAJ7c6TM2D277U2wH8X78kg8pH3tdUqebV3_JCJqAkYQFHCFzeg@mail.gmail.com
---
 doc/src/sgml/maintenance.sgml       | 2 +-
 src/backend/access/transam/varsup.c | 8 ++++----
 2 files changed, 5 insertions(+), 5 deletions(-)

diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index b0336b12e5..2a902e422c 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -656,7 +656,7 @@ SELECT datname, age(datfrozenxid) FROM pg_database;
 
 <programlisting>
 WARNING:  database "mydb" must be vacuumed within 39985967 transactions
-HINT:  To avoid a database shutdown, execute a database-wide VACUUM in that database.
+HINT:  To prevent entering read-only mode, execute a database-wide VACUUM in that database.
 </programlisting>
 
     (A manual <command>VACUUM</command> should fix the problem, as suggested by the
diff --git a/src/backend/access/transam/varsup.c b/src/backend/access/transam/varsup.c
index fece6cf31b..485d8ebf81 100644
--- a/src/backend/access/transam/varsup.c
+++ b/src/backend/access/transam/varsup.c
@@ -148,14 +148,14 @@ GetNewTransactionId(bool isSubXact)
 						(errmsg("database \"%s\" must be vacuumed within %u transactions",
 								oldest_datname,
 								xidWrapLimit - xid),
-						 errhint("To avoid a database shutdown, execute a database-wide VACUUM in that database.\n"
+						 errhint("To prevent entering read-only mode, execute a database-wide VACUUM in that database.\n"
 								 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 			else
 				ereport(WARNING,
 						(errmsg("database with OID %u must be vacuumed within %u transactions",
 								oldest_datoid,
 								xidWrapLimit - xid),
-						 errhint("To avoid a database shutdown, execute a database-wide VACUUM in that database.\n"
+						 errhint("To prevent entering read-only mode, execute a database-wide VACUUM in that database.\n"
 								 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 		}
 
@@ -463,14 +463,14 @@ SetTransactionIdLimit(TransactionId oldest_datfrozenxid, Oid oldest_datoid)
 					(errmsg("database \"%s\" must be vacuumed within %u transactions",
 							oldest_datname,
 							xidWrapLimit - curXid),
-					 errhint("To avoid a database shutdown, execute a database-wide VACUUM in that database.\n"
+					 errhint("To prevent entering read-only mode, execute a database-wide VACUUM in that database.\n"
 							 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 		else
 			ereport(WARNING,
 					(errmsg("database with OID %u must be vacuumed within %u transactions",
 							oldest_datoid,
 							xidWrapLimit - curXid),
-					 errhint("To avoid a database shutdown, execute a database-wide VACUUM in that database.\n"
+					 errhint("To prevent entering read-only mode, execute a database-wide VACUUM in that database.\n"
 							 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 	}
 }
-- 
2.39.2

From 66465d20dcd68b168f4510b64edc48acf7229dfd Mon Sep 17 00:00:00 2001
From: Aleksander Alekseev <aleksander@timescale.com>
Date: Mon, 16 Jan 2023 15:37:12 +0300
Subject: [PATCH v3 4/4] Don't recommend running VACUUM in a single-user mode
 when reaching xidStopLimit

This recommendation is outdated for some time now. A regular VACUUM
works just fine. Additionally add an explicit note against using a single-user
mode for people who may potentially miss this change.

Author: Aleksander Alekseev
Reported-by: Hannu Krosing
Reviewed-by: John Naylor
Discussion: https://postgr.es/m/CAJ7c6TM2D277U2wH8X78kg8pH3tdUqebV3_JCJqAkYQFHCFzeg@mail.gmail.com
Discussion: https://postgr.es/m/CAMT0RQTmRj_Egtmre6fbiMA9E2hM3BsLULiV8W00stwa3URvzA%40mail.gmail.com
---
 doc/src/sgml/maintenance.sgml       | 15 +++++++++------
 src/backend/access/transam/varsup.c |  4 ++--
 2 files changed, 11 insertions(+), 8 deletions(-)

diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index 2a902e422c..12bc7af01f 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -668,7 +668,7 @@ HINT:  To prevent entering read-only mode, execute a database-wide VACUUM in tha
 
 <programlisting>
 ERROR:  database is not accepting commands that generate new XIDs to avoid wraparound data loss in database "mydb"
-HINT:  Stop the postmaster and vacuum that database in single-user mode.
+HINT:  VACUUM that database.
 </programlisting>
 
     In this condition any transactions already started can continue.
@@ -676,13 +676,16 @@ HINT:  Stop the postmaster and vacuum that database in single-user mode.
 
     The three-million-transaction safety margin exists to let the
     administrator recover without data loss, by manually executing the
-    required <command>VACUUM</command> commands.  However
-    the only way to do this is to stop the server and start the server in single-user
-    mode to execute <command>VACUUM</command>. See the
-    <xref linkend="app-postgres"/> reference page for details about using
-    single-user mode.
+    required <command>VACUUM</command> commands.
    </para>
 
+   <note>
+    <para>
+     Previously it was required to stop the postmaster and VACUUM the database
+     in a single-user mode. There is no need to use a single-user mode anymore.
+    </para>
+   </note>
+
    <sect3 id="vacuum-for-multixact-wraparound">
     <title>Multixacts and Wraparound</title>
 
diff --git a/src/backend/access/transam/varsup.c b/src/backend/access/transam/varsup.c
index 485d8ebf81..ef1f211d32 100644
--- a/src/backend/access/transam/varsup.c
+++ b/src/backend/access/transam/varsup.c
@@ -128,14 +128,14 @@ GetNewTransactionId(bool isSubXact)
 						(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
 						 errmsg("database is not accepting commands that generate new XIDs to avoid wraparound data loss in database \"%s\"",
 								oldest_datname),
-						 errhint("Stop the postmaster and vacuum that database in single-user mode.\n"
+						 errhint("VACUUM that database.\n"
 								 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 			else
 				ereport(ERROR,
 						(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
 						 errmsg("database is not accepting commands that generate new XIDs to avoid wraparound data loss in database with OID %u",
 								oldest_datoid),
-						 errhint("Stop the postmaster and vacuum that database in single-user mode.\n"
+						 errhint("VACUUM that database.\n"
 								 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 		}
 		else if (TransactionIdFollowsOrEquals(xid, xidWarnLimit))
-- 
2.39.2

From 9c3b184331c734c0178bd0e7e8e507477023dd54 Mon Sep 17 00:00:00 2001
From: Aleksander Alekseev <aleksander@timescale.com>
Date: Mon, 16 Jan 2023 12:23:30 +0300
Subject: [PATCH v4 1/3] Correct the docs and messages about preventing XID
 wraparound

Prior to this commit the documentation was somewhat misleading. It stated that
the system will "shutdown" in order to prevent XID wraparound. This is not
what actually happens though.

Actually the system refuses to allocate new XIDs. XIDs are allocated lazily
and not needed for checking the visibility rules. Thus read-only transaction
will continues to execute. Additionally, read-write transactions that are
in progress and have an allocated XID will continue to execute and can be
committed.

Additionally, fix the messages shown when approaching xidWrapLimit,
xidWarnLimit and multiWarnLimit accordingly.

Author: Aleksander Alekseev
Reviewed-by: John Naylor
Discussion: https://postgr.es/m/CAJ7c6TM2D277U2wH8X78kg8pH3tdUqebV3_JCJqAkYQFHCFzeg@mail.gmail.com
---
 doc/src/sgml/maintenance.sgml          | 22 +++++++++++-----------
 src/backend/access/transam/multixact.c |  4 ++--
 src/backend/access/transam/varsup.c    | 12 ++++++------
 3 files changed, 19 insertions(+), 19 deletions(-)

diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index 9cf9d030a8..2a902e422c 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -656,31 +656,31 @@ SELECT datname, age(datfrozenxid) FROM pg_database;
 
 <programlisting>
 WARNING:  database "mydb" must be vacuumed within 39985967 transactions
-HINT:  To avoid a database shutdown, execute a database-wide VACUUM in that database.
+HINT:  To prevent entering read-only mode, execute a database-wide VACUUM in that database.
 </programlisting>
 
     (A manual <command>VACUUM</command> should fix the problem, as suggested by the
     hint; but note that the <command>VACUUM</command> must be performed by a
     superuser, else it will fail to process system catalogs and thus not
     be able to advance the database's <structfield>datfrozenxid</structfield>.)
-    If these warnings are
-    ignored, the system will shut down and refuse to start any new
-    transactions once there are fewer than three million transactions left
-    until wraparound:
+    If these warnings are ignored, the system will refuse to allocate new XIDs
+    once there are fewer than three million transactions left until wraparound:
 
 <programlisting>
-ERROR:  database is not accepting commands to avoid wraparound data loss in database "mydb"
+ERROR:  database is not accepting commands that generate new XIDs to avoid wraparound data loss in database "mydb"
 HINT:  Stop the postmaster and vacuum that database in single-user mode.
 </programlisting>
 
+    In this condition any transactions already started can continue.
+    Only new read-only transactions can be started.
+
     The three-million-transaction safety margin exists to let the
     administrator recover without data loss, by manually executing the
-    required <command>VACUUM</command> commands.  However, since the system will not
-    execute commands once it has gone into the safety shutdown mode,
+    required <command>VACUUM</command> commands.  However
     the only way to do this is to stop the server and start the server in single-user
-    mode to execute <command>VACUUM</command>.  The shutdown mode is not enforced
-    in single-user mode.  See the <xref linkend="app-postgres"/> reference
-    page for details about using single-user mode.
+    mode to execute <command>VACUUM</command>. See the
+    <xref linkend="app-postgres"/> reference page for details about using
+    single-user mode.
    </para>
 
    <sect3 id="vacuum-for-multixact-wraparound">
diff --git a/src/backend/access/transam/multixact.c b/src/backend/access/transam/multixact.c
index fe6698d5ff..1dd29c02cc 100644
--- a/src/backend/access/transam/multixact.c
+++ b/src/backend/access/transam/multixact.c
@@ -2335,7 +2335,7 @@ SetMultiXactIdLimit(MultiXactId oldest_datminmxid, Oid oldest_datoid,
 								   multiWrapLimit - curMulti,
 								   oldest_datname,
 								   multiWrapLimit - curMulti),
-					 errhint("To avoid a database shutdown, execute a database-wide VACUUM in that database.\n"
+					 errhint("To prevent entering read-only mode, execute a database-wide VACUUM in that database.\n"
 							 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 		else
 			ereport(WARNING,
@@ -2344,7 +2344,7 @@ SetMultiXactIdLimit(MultiXactId oldest_datminmxid, Oid oldest_datoid,
 								   multiWrapLimit - curMulti,
 								   oldest_datoid,
 								   multiWrapLimit - curMulti),
-					 errhint("To avoid a database shutdown, execute a database-wide VACUUM in that database.\n"
+					 errhint("To prevent entering read-only mode, execute a database-wide VACUUM in that database.\n"
 							 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 	}
 }
diff --git a/src/backend/access/transam/varsup.c b/src/backend/access/transam/varsup.c
index 334adac09e..485d8ebf81 100644
--- a/src/backend/access/transam/varsup.c
+++ b/src/backend/access/transam/varsup.c
@@ -126,14 +126,14 @@ GetNewTransactionId(bool isSubXact)
 			if (oldest_datname)
 				ereport(ERROR,
 						(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
-						 errmsg("database is not accepting commands to avoid wraparound data loss in database \"%s\"",
+						 errmsg("database is not accepting commands that generate new XIDs to avoid wraparound data loss in database \"%s\"",
 								oldest_datname),
 						 errhint("Stop the postmaster and vacuum that database in single-user mode.\n"
 								 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 			else
 				ereport(ERROR,
 						(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
-						 errmsg("database is not accepting commands to avoid wraparound data loss in database with OID %u",
+						 errmsg("database is not accepting commands that generate new XIDs to avoid wraparound data loss in database with OID %u",
 								oldest_datoid),
 						 errhint("Stop the postmaster and vacuum that database in single-user mode.\n"
 								 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
@@ -148,14 +148,14 @@ GetNewTransactionId(bool isSubXact)
 						(errmsg("database \"%s\" must be vacuumed within %u transactions",
 								oldest_datname,
 								xidWrapLimit - xid),
-						 errhint("To avoid a database shutdown, execute a database-wide VACUUM in that database.\n"
+						 errhint("To prevent entering read-only mode, execute a database-wide VACUUM in that database.\n"
 								 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 			else
 				ereport(WARNING,
 						(errmsg("database with OID %u must be vacuumed within %u transactions",
 								oldest_datoid,
 								xidWrapLimit - xid),
-						 errhint("To avoid a database shutdown, execute a database-wide VACUUM in that database.\n"
+						 errhint("To prevent entering read-only mode, execute a database-wide VACUUM in that database.\n"
 								 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 		}
 
@@ -463,14 +463,14 @@ SetTransactionIdLimit(TransactionId oldest_datfrozenxid, Oid oldest_datoid)
 					(errmsg("database \"%s\" must be vacuumed within %u transactions",
 							oldest_datname,
 							xidWrapLimit - curXid),
-					 errhint("To avoid a database shutdown, execute a database-wide VACUUM in that database.\n"
+					 errhint("To prevent entering read-only mode, execute a database-wide VACUUM in that database.\n"
 							 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 		else
 			ereport(WARNING,
 					(errmsg("database with OID %u must be vacuumed within %u transactions",
 							oldest_datoid,
 							xidWrapLimit - curXid),
-					 errhint("To avoid a database shutdown, execute a database-wide VACUUM in that database.\n"
+					 errhint("To prevent entering read-only mode, execute a database-wide VACUUM in that database.\n"
 							 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 	}
 }
-- 
2.39.2

From dce05503162c274b5496cde5b2e7cefcb78452ba Mon Sep 17 00:00:00 2001
From: Aleksander Alekseev <aleksander@timescale.com>
Date: Mon, 16 Jan 2023 15:37:12 +0300
Subject: [PATCH v4 2/3] Don't recommend running VACUUM in a single-user mode
 when reaching xidStopLimit

This recommendation is outdated for some time now. A regular VACUUM
works just fine. Additionally add an explicit note against using a single-user
mode for people who may potentially miss this change.

Author: Aleksander Alekseev
Reported-by: Hannu Krosing
Reviewed-by: John Naylor
Discussion: https://postgr.es/m/CAJ7c6TM2D277U2wH8X78kg8pH3tdUqebV3_JCJqAkYQFHCFzeg@mail.gmail.com
Discussion: https://postgr.es/m/CAMT0RQTmRj_Egtmre6fbiMA9E2hM3BsLULiV8W00stwa3URvzA%40mail.gmail.com
---
 doc/src/sgml/maintenance.sgml       | 20 ++++++++++++++------
 src/backend/access/transam/varsup.c |  4 ++--
 2 files changed, 16 insertions(+), 8 deletions(-)

diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index 2a902e422c..f0d913c94f 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -668,7 +668,7 @@ HINT:  To prevent entering read-only mode, execute a database-wide VACUUM in tha
 
 <programlisting>
 ERROR:  database is not accepting commands that generate new XIDs to avoid wraparound data loss in database "mydb"
-HINT:  Stop the postmaster and vacuum that database in single-user mode.
+HINT:  VACUUM that database.
 </programlisting>
 
     In this condition any transactions already started can continue.
@@ -676,13 +676,21 @@ HINT:  Stop the postmaster and vacuum that database in single-user mode.
 
     The three-million-transaction safety margin exists to let the
     administrator recover without data loss, by manually executing the
-    required <command>VACUUM</command> commands.  However
-    the only way to do this is to stop the server and start the server in single-user
-    mode to execute <command>VACUUM</command>. See the
-    <xref linkend="app-postgres"/> reference page for details about using
-    single-user mode.
+    required <command>VACUUM</command> commands.
    </para>
 
+   <note>
+    <para>
+     Previously it was required to stop the postmaster and
+     <command>VACUUM</command> the database in a single-user mode. There is no
+     need to use a single-user mode anymore unless the resolution needs to be
+     speeded up by performing <command>TRUNCATE</command> or
+     <command>DROP</command>. Except for this scenario it's strongly advised
+     against using a single-user mode: it requires downtime, can't be monitored,
+     disables replication, disables safeguards against wraparound, etc.
+    </para>
+   </note>
+
    <sect3 id="vacuum-for-multixact-wraparound">
     <title>Multixacts and Wraparound</title>
 
diff --git a/src/backend/access/transam/varsup.c b/src/backend/access/transam/varsup.c
index 485d8ebf81..ef1f211d32 100644
--- a/src/backend/access/transam/varsup.c
+++ b/src/backend/access/transam/varsup.c
@@ -128,14 +128,14 @@ GetNewTransactionId(bool isSubXact)
 						(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
 						 errmsg("database is not accepting commands that generate new XIDs to avoid wraparound data loss in database \"%s\"",
 								oldest_datname),
-						 errhint("Stop the postmaster and vacuum that database in single-user mode.\n"
+						 errhint("VACUUM that database.\n"
 								 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 			else
 				ereport(ERROR,
 						(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
 						 errmsg("database is not accepting commands that generate new XIDs to avoid wraparound data loss in database with OID %u",
 								oldest_datoid),
-						 errhint("Stop the postmaster and vacuum that database in single-user mode.\n"
+						 errhint("VACUUM that database.\n"
 								 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 		}
 		else if (TransactionIdFollowsOrEquals(xid, xidWarnLimit))
-- 
2.39.2

From 1f804c86796be37ce6154b9235ae64e7ed8f0196 Mon Sep 17 00:00:00 2001
From: Aleksander Alekseev <aleksander@timescale.com>
Date: Mon, 3 Apr 2023 14:00:38 +0300
Subject: [PATCH v4 3/3] Modify the hints about preventing XID wraparound

The new hint messages provide a comprehensive list of actionable items that
help preventing XID wraparound. Previously the list was incomplete.

Per suggestion by Robert Haas.

Author: Aleksander Alekseev
Reviewed-by: John Naylor
Discussion: https://postgr.es/m/CAJ7c6TM2D277U2wH8X78kg8pH3tdUqebV3_JCJqAkYQFHCFzeg@mail.gmail.com
---
 src/backend/access/transam/multixact.c | 12 ++++++------
 src/backend/access/transam/varsup.c    | 12 ++++++------
 src/backend/commands/vacuum.c          |  4 ++--
 3 files changed, 14 insertions(+), 14 deletions(-)

diff --git a/src/backend/access/transam/multixact.c b/src/backend/access/transam/multixact.c
index 1dd29c02cc..5f922f3afd 100644
--- a/src/backend/access/transam/multixact.c
+++ b/src/backend/access/transam/multixact.c
@@ -1023,14 +1023,14 @@ GetNewMultiXactId(int nmembers, MultiXactOffset *offset)
 						 errmsg("database is not accepting commands that generate new MultiXactIds to avoid wraparound data loss in database \"%s\"",
 								oldest_datname),
 						 errhint("Execute a database-wide VACUUM in that database.\n"
-								 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
+								 "You might also need to commit or roll back old prepared transactions, drop stale replication slots, or kill long-running sessions. Ensure that autovacuum is progressing, or run a manual database-wide VACUUM.")));
 			else
 				ereport(ERROR,
 						(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
 						 errmsg("database is not accepting commands that generate new MultiXactIds to avoid wraparound data loss in database with OID %u",
 								oldest_datoid),
 						 errhint("Execute a database-wide VACUUM in that database.\n"
-								 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
+								 "You might also need to commit or roll back old prepared transactions, drop stale replication slots, or kill long-running sessions. Ensure that autovacuum is progressing, or run a manual database-wide VACUUM.")));
 		}
 
 		/*
@@ -1054,7 +1054,7 @@ GetNewMultiXactId(int nmembers, MultiXactOffset *offset)
 									   oldest_datname,
 									   multiWrapLimit - result),
 						 errhint("Execute a database-wide VACUUM in that database.\n"
-								 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
+								 "You might also need to commit or roll back old prepared transactions, drop stale replication slots, or kill long-running sessions. Ensure that autovacuum is progressing, or run a manual database-wide VACUUM.")));
 			else
 				ereport(WARNING,
 						(errmsg_plural("database with OID %u must be vacuumed before %u more MultiXactId is used",
@@ -1063,7 +1063,7 @@ GetNewMultiXactId(int nmembers, MultiXactOffset *offset)
 									   oldest_datoid,
 									   multiWrapLimit - result),
 						 errhint("Execute a database-wide VACUUM in that database.\n"
-								 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
+								 "You might also need to commit or roll back old prepared transactions, drop stale replication slots, or kill long-running sessions. Ensure that autovacuum is progressing, or run a manual database-wide VACUUM.")));
 		}
 
 		/* Re-acquire lock and start over */
@@ -2336,7 +2336,7 @@ SetMultiXactIdLimit(MultiXactId oldest_datminmxid, Oid oldest_datoid,
 								   oldest_datname,
 								   multiWrapLimit - curMulti),
 					 errhint("To prevent entering read-only mode, execute a database-wide VACUUM in that database.\n"
-							 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
+							 "You might also need to commit or roll back old prepared transactions, drop stale replication slots, or kill long-running sessions. Ensure that autovacuum is progressing, or run a manual database-wide VACUUM.")));
 		else
 			ereport(WARNING,
 					(errmsg_plural("database with OID %u must be vacuumed before %u more MultiXactId is used",
@@ -2345,7 +2345,7 @@ SetMultiXactIdLimit(MultiXactId oldest_datminmxid, Oid oldest_datoid,
 								   oldest_datoid,
 								   multiWrapLimit - curMulti),
 					 errhint("To prevent entering read-only mode, execute a database-wide VACUUM in that database.\n"
-							 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
+							 "You might also need to commit or roll back old prepared transactions, drop stale replication slots, or kill long-running sessions. Ensure that autovacuum is progressing, or run a manual database-wide VACUUM.")));
 	}
 }
 
diff --git a/src/backend/access/transam/varsup.c b/src/backend/access/transam/varsup.c
index ef1f211d32..186d394df2 100644
--- a/src/backend/access/transam/varsup.c
+++ b/src/backend/access/transam/varsup.c
@@ -129,14 +129,14 @@ GetNewTransactionId(bool isSubXact)
 						 errmsg("database is not accepting commands that generate new XIDs to avoid wraparound data loss in database \"%s\"",
 								oldest_datname),
 						 errhint("VACUUM that database.\n"
-								 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
+								 "You might also need to commit or roll back old prepared transactions, drop stale replication slots, or kill long-running sessions. Ensure that autovacuum is progressing, or run a manual database-wide VACUUM.")));
 			else
 				ereport(ERROR,
 						(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
 						 errmsg("database is not accepting commands that generate new XIDs to avoid wraparound data loss in database with OID %u",
 								oldest_datoid),
 						 errhint("VACUUM that database.\n"
-								 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
+								 "You might also need to commit or roll back old prepared transactions, drop stale replication slots, or kill long-running sessions. Ensure that autovacuum is progressing, or run a manual database-wide VACUUM.")));
 		}
 		else if (TransactionIdFollowsOrEquals(xid, xidWarnLimit))
 		{
@@ -149,14 +149,14 @@ GetNewTransactionId(bool isSubXact)
 								oldest_datname,
 								xidWrapLimit - xid),
 						 errhint("To prevent entering read-only mode, execute a database-wide VACUUM in that database.\n"
-								 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
+								 "You might also need to commit or roll back old prepared transactions, drop stale replication slots, or kill long-running sessions. Ensure that autovacuum is progressing, or run a manual database-wide VACUUM.")));
 			else
 				ereport(WARNING,
 						(errmsg("database with OID %u must be vacuumed within %u transactions",
 								oldest_datoid,
 								xidWrapLimit - xid),
 						 errhint("To prevent entering read-only mode, execute a database-wide VACUUM in that database.\n"
-								 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
+								 "You might also need to commit or roll back old prepared transactions, drop stale replication slots, or kill long-running sessions. Ensure that autovacuum is progressing, or run a manual database-wide VACUUM.")));
 		}
 
 		/* Re-acquire lock and start over */
@@ -464,14 +464,14 @@ SetTransactionIdLimit(TransactionId oldest_datfrozenxid, Oid oldest_datoid)
 							oldest_datname,
 							xidWrapLimit - curXid),
 					 errhint("To prevent entering read-only mode, execute a database-wide VACUUM in that database.\n"
-							 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
+							 "You might also need to commit or roll back old prepared transactions, drop stale replication slots, or kill long-running sessions. Ensure that autovacuum is progressing, or run a manual database-wide VACUUM.")));
 		else
 			ereport(WARNING,
 					(errmsg("database with OID %u must be vacuumed within %u transactions",
 							oldest_datoid,
 							xidWrapLimit - curXid),
 					 errhint("To prevent entering read-only mode, execute a database-wide VACUUM in that database.\n"
-							 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
+							 "You might also need to commit or roll back old prepared transactions, drop stale replication slots, or kill long-running sessions. Ensure that autovacuum is progressing, or run a manual database-wide VACUUM.")));
 	}
 }
 
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index 1c3437336d..e9162f0534 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -1047,12 +1047,12 @@ vacuum_get_cutoffs(Relation rel, const VacuumParams *params,
 		ereport(WARNING,
 				(errmsg("cutoff for removing and freezing tuples is far in the past"),
 				 errhint("Close open transactions soon to avoid wraparound problems.\n"
-						 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
+						 "You might also need to commit or roll back old prepared transactions, drop stale replication slots, or kill long-running sessions. Ensure that autovacuum is progressing, or run a manual database-wide VACUUM.")));
 	if (MultiXactIdPrecedes(cutoffs->OldestMxact, safeOldestMxact))
 		ereport(WARNING,
 				(errmsg("cutoff for freezing multixacts is far in the past"),
 				 errhint("Close open transactions soon to avoid wraparound problems.\n"
-						 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
+						 "You might also need to commit or roll back old prepared transactions, drop stale replication slots, or kill long-running sessions. Ensure that autovacuum is progressing, or run a manual database-wide VACUUM.")));
 
 	/*
 	 * Determine the minimum freeze age to use: as specified by the caller, or
-- 
2.39.2

From 4633ec986fce07c22ca094212c86ca16d5c9c887 Mon Sep 17 00:00:00 2001
From: Pavel Borisov <pashkin.elfe@gmail.com>
Date: Tue, 4 Apr 2023 13:26:14 +0400
Subject: [PATCH v5 2/3] This recommendation is outdated for some time now. A
 regular VACUUM works just fine. Additionally add an explicit note against
 using a single-user mode for people who may potentially miss this change.

Author: Aleksander Alekseev
Reported-by: Hannu Krosing
Reviewed-by: John Naylor
Discussion: https://postgr.es/m/CAJ7c6TM2D277U2wH8X78kg8pH3tdUqebV3_JCJqAkYQFHCFzeg@mail.gmail.com
Discussion: https://postgr.es/m/CAMT0RQTmRj_Egtmre6fbiMA9E2hM3BsLULiV8W00stwa3URvzA%40mail.gmail.com
---
 doc/src/sgml/maintenance.sgml       | 20 ++++++++++++++------
 src/backend/access/transam/varsup.c |  4 ++--
 2 files changed, 16 insertions(+), 8 deletions(-)

diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index b7254344ee0..66456b5e462 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -668,7 +668,7 @@ HINT:  To prevent entering read-only mode, execute a database-wide VACUUM in tha
 
 <programlisting>
 ERROR:  database can't generate new XIDs to avoid wraparound data loss in database "mydb"
-HINT:  Stop the postmaster and vacuum that database in single-user mode.
+HINT:  VACUUM that database.
 </programlisting>
 
     In this condition any transactions already started can continue.
@@ -676,13 +676,21 @@ HINT:  Stop the postmaster and vacuum that database in single-user mode.
 
     The three-million-transaction safety margin exists to let the
     administrator recover without data loss, by manually executing the
-    required <command>VACUUM</command> commands.  However
-    the only way to do this is to stop the server and start the server in single-user
-    mode to execute <command>VACUUM</command>. See the
-    <xref linkend="app-postgres"/> reference page for details about using
-    single-user mode.
+    required <command>VACUUM</command> commands.
    </para>
 
+   <note>
+    <para>
+     Previously it was required to stop the postmaster and
+     <command>VACUUM</command> the database in a single-user mode. There is no
+     need to use a single-user mode anymore unless the resolution needs to be
+     speeded up by performing <command>TRUNCATE</command> or
+     <command>DROP</command>. Except for this scenario it's strongly advised
+     against using a single-user mode: it requires downtime, can't be monitored,
+     disables replication, disables safeguards against wraparound, etc.
+    </para>
+   </note>
+
    <sect3 id="vacuum-for-multixact-wraparound">
     <title>Multixacts and Wraparound</title>
 
diff --git a/src/backend/access/transam/varsup.c b/src/backend/access/transam/varsup.c
index bd27576bd58..f039c98cd09 100644
--- a/src/backend/access/transam/varsup.c
+++ b/src/backend/access/transam/varsup.c
@@ -128,14 +128,14 @@ GetNewTransactionId(bool isSubXact)
 						(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
 						 errmsg("database can't generate new XIDs to avoid wraparound data loss in database \"%s\"",
 								oldest_datname),
-						 errhint("Stop the postmaster and vacuum that database in single-user mode.\n"
+						 errhint("VACUUM that database.\n"
 								 "You might also need to commit or roll back old prepared transactions and drop stale replication slots.")));
 			else
 				ereport(ERROR,
 						(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
 						 errmsg("database can't generate new XIDs to avoid wraparound data loss in database with OID %u",
 								oldest_datoid),
-						 errhint("Stop the postmaster and vacuum that database in single-user mode.\n"
+						 errhint("VACUUM that database.\n"
 								 "You might also need to commit or roll back old prepared transactions and drop stale replication slots.")));
 		}
 		else if (TransactionIdFollowsOrEquals(xid, xidWarnLimit))
-- 
2.37.1 (Apple Git-137.1)

From f04e0c576c1415f04fc32d8af792ccfc28527869 Mon Sep 17 00:00:00 2001
From: Aleksander Alekseev <aleksander@timescale.com>
Date: Mon, 16 Jan 2023 12:23:30 +0300
Subject: [PATCH v5 1/3] Correct the docs and messages about preventing XID
 wraparound

Prior to this commit the documentation was somewhat misleading. It stated that
the system will "shutdown" in order to prevent XID wraparound. This is not
what actually happens though.

Actually the system refuses to allocate new XIDs. XIDs are allocated lazily
and not needed for checking the visibility rules. Thus read-only transaction
will continues to execute. Additionally, read-write transactions that are
in progress and have an allocated XID will continue to execute and can be
committed.

Additionally, fix the messages shown when approaching xidWrapLimit,
xidWarnLimit and multiWarnLimit accordingly.

Author: Aleksander Alekseev
Reviewed-by: John Naylor
Discussion: https://postgr.es/m/CAJ7c6TM2D277U2wH8X78kg8pH3tdUqebV3_JCJqAkYQFHCFzeg@mail.gmail.com
---
 doc/src/sgml/maintenance.sgml          | 22 +++++++++++-----------
 src/backend/access/transam/multixact.c |  4 ++--
 src/backend/access/transam/varsup.c    | 24 ++++++++++++------------
 3 files changed, 25 insertions(+), 25 deletions(-)

diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index 9cf9d030a8a..b7254344ee0 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -656,31 +656,31 @@ SELECT datname, age(datfrozenxid) FROM pg_database;
 
 <programlisting>
 WARNING:  database "mydb" must be vacuumed within 39985967 transactions
-HINT:  To avoid a database shutdown, execute a database-wide VACUUM in that database.
+HINT:  To prevent entering read-only mode, execute a database-wide VACUUM in that database.
 </programlisting>
 
     (A manual <command>VACUUM</command> should fix the problem, as suggested by the
     hint; but note that the <command>VACUUM</command> must be performed by a
     superuser, else it will fail to process system catalogs and thus not
     be able to advance the database's <structfield>datfrozenxid</structfield>.)
-    If these warnings are
-    ignored, the system will shut down and refuse to start any new
-    transactions once there are fewer than three million transactions left
-    until wraparound:
+    If these warnings are ignored, the system will refuse to allocate new XIDs
+    once there are fewer than three million transactions left until wraparound:
 
 <programlisting>
-ERROR:  database is not accepting commands to avoid wraparound data loss in database "mydb"
+ERROR:  database can't generate new XIDs to avoid wraparound data loss in database "mydb"
 HINT:  Stop the postmaster and vacuum that database in single-user mode.
 </programlisting>
 
+    In this condition any transactions already started can continue.
+    Only new read-only transactions can be started.
+
     The three-million-transaction safety margin exists to let the
     administrator recover without data loss, by manually executing the
-    required <command>VACUUM</command> commands.  However, since the system will not
-    execute commands once it has gone into the safety shutdown mode,
+    required <command>VACUUM</command> commands.  However
     the only way to do this is to stop the server and start the server in single-user
-    mode to execute <command>VACUUM</command>.  The shutdown mode is not enforced
-    in single-user mode.  See the <xref linkend="app-postgres"/> reference
-    page for details about using single-user mode.
+    mode to execute <command>VACUUM</command>. See the
+    <xref linkend="app-postgres"/> reference page for details about using
+    single-user mode.
    </para>
 
    <sect3 id="vacuum-for-multixact-wraparound">
diff --git a/src/backend/access/transam/multixact.c b/src/backend/access/transam/multixact.c
index fe6698d5ffa..1dd29c02cc5 100644
--- a/src/backend/access/transam/multixact.c
+++ b/src/backend/access/transam/multixact.c
@@ -2335,7 +2335,7 @@ SetMultiXactIdLimit(MultiXactId oldest_datminmxid, Oid oldest_datoid,
 								   multiWrapLimit - curMulti,
 								   oldest_datname,
 								   multiWrapLimit - curMulti),
-					 errhint("To avoid a database shutdown, execute a database-wide VACUUM in that database.\n"
+					 errhint("To prevent entering read-only mode, execute a database-wide VACUUM in that database.\n"
 							 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 		else
 			ereport(WARNING,
@@ -2344,7 +2344,7 @@ SetMultiXactIdLimit(MultiXactId oldest_datminmxid, Oid oldest_datoid,
 								   multiWrapLimit - curMulti,
 								   oldest_datoid,
 								   multiWrapLimit - curMulti),
-					 errhint("To avoid a database shutdown, execute a database-wide VACUUM in that database.\n"
+					 errhint("To prevent entering read-only mode, execute a database-wide VACUUM in that database.\n"
 							 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 	}
 }
diff --git a/src/backend/access/transam/varsup.c b/src/backend/access/transam/varsup.c
index 334adac09e8..bd27576bd58 100644
--- a/src/backend/access/transam/varsup.c
+++ b/src/backend/access/transam/varsup.c
@@ -126,17 +126,17 @@ GetNewTransactionId(bool isSubXact)
 			if (oldest_datname)
 				ereport(ERROR,
 						(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
-						 errmsg("database is not accepting commands to avoid wraparound data loss in database \"%s\"",
+						 errmsg("database can't generate new XIDs to avoid wraparound data loss in database \"%s\"",
 								oldest_datname),
 						 errhint("Stop the postmaster and vacuum that database in single-user mode.\n"
-								 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
+								 "You might also need to commit or roll back old prepared transactions and drop stale replication slots.")));
 			else
 				ereport(ERROR,
 						(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
-						 errmsg("database is not accepting commands to avoid wraparound data loss in database with OID %u",
+						 errmsg("database can't generate new XIDs to avoid wraparound data loss in database with OID %u",
 								oldest_datoid),
 						 errhint("Stop the postmaster and vacuum that database in single-user mode.\n"
-								 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
+								 "You might also need to commit or roll back old prepared transactions and drop stale replication slots.")));
 		}
 		else if (TransactionIdFollowsOrEquals(xid, xidWarnLimit))
 		{
@@ -148,15 +148,15 @@ GetNewTransactionId(bool isSubXact)
 						(errmsg("database \"%s\" must be vacuumed within %u transactions",
 								oldest_datname,
 								xidWrapLimit - xid),
-						 errhint("To avoid a database shutdown, execute a database-wide VACUUM in that database.\n"
-								 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
+						 errhint("To prevent entering read-only mode, execute a database-wide VACUUM in that database.\n"
+								 "You might also need to commit or roll back old prepared transactions and drop stale replication slots.")));
 			else
 				ereport(WARNING,
 						(errmsg("database with OID %u must be vacuumed within %u transactions",
 								oldest_datoid,
 								xidWrapLimit - xid),
-						 errhint("To avoid a database shutdown, execute a database-wide VACUUM in that database.\n"
-								 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
+						 errhint("To prevent entering read-only mode, execute a database-wide VACUUM in that database.\n"
+								 "You might also need to commit or roll back old prepared transactions, and drop stale replication slots.")));
 		}
 
 		/* Re-acquire lock and start over */
@@ -463,15 +463,15 @@ SetTransactionIdLimit(TransactionId oldest_datfrozenxid, Oid oldest_datoid)
 					(errmsg("database \"%s\" must be vacuumed within %u transactions",
 							oldest_datname,
 							xidWrapLimit - curXid),
-					 errhint("To avoid a database shutdown, execute a database-wide VACUUM in that database.\n"
-							 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
+					 errhint("To prevent entering read-only mode, execute a database-wide VACUUM in that database.\n"
+							 "You might also need to commit or roll back old prepared transactions and drop stale replication slots.")));
 		else
 			ereport(WARNING,
 					(errmsg("database with OID %u must be vacuumed within %u transactions",
 							oldest_datoid,
 							xidWrapLimit - curXid),
-					 errhint("To avoid a database shutdown, execute a database-wide VACUUM in that database.\n"
-							 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
+					 errhint("To prevent entering read-only mode, execute a database-wide VACUUM in that database.\n"
+							 "You might also need to commit or roll back old prepared transactions and drop stale replication slots.")));
 	}
 }
 
-- 
2.37.1 (Apple Git-137.1)

From 2c450d9a21761fcbd3d54b9e88967c901e779c0d Mon Sep 17 00:00:00 2001
From: Aleksander Alekseev <aleksander@timescale.com>
Date: Mon, 3 Apr 2023 14:00:38 +0300
Subject: [PATCH v5 3/3] Modify the hints about preventing XID wraparound

The new hint messages provide a comprehensive list of actionable items that
help preventing XID wraparound. Previously the list was incomplete.

Per suggestion by Robert Haas.

Author: Aleksander Alekseev
Reviewed-by: John Naylor
Discussion: https://postgr.es/m/CAJ7c6TM2D277U2wH8X78kg8pH3tdUqebV3_JCJqAkYQFHCFzeg@mail.gmail.com
---
 src/backend/access/transam/multixact.c | 12 ++++++------
 src/backend/access/transam/varsup.c    | 12 ++++++------
 src/backend/commands/vacuum.c          |  4 ++--
 3 files changed, 14 insertions(+), 14 deletions(-)

diff --git a/src/backend/access/transam/multixact.c b/src/backend/access/transam/multixact.c
index 1dd29c02cc5..5f922f3afd5 100644
--- a/src/backend/access/transam/multixact.c
+++ b/src/backend/access/transam/multixact.c
@@ -1023,14 +1023,14 @@ GetNewMultiXactId(int nmembers, MultiXactOffset *offset)
 						 errmsg("database is not accepting commands that generate new MultiXactIds to avoid wraparound data loss in database \"%s\"",
 								oldest_datname),
 						 errhint("Execute a database-wide VACUUM in that database.\n"
-								 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
+								 "You might also need to commit or roll back old prepared transactions, drop stale replication slots, or kill long-running sessions. Ensure that autovacuum is progressing, or run a manual database-wide VACUUM.")));
 			else
 				ereport(ERROR,
 						(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
 						 errmsg("database is not accepting commands that generate new MultiXactIds to avoid wraparound data loss in database with OID %u",
 								oldest_datoid),
 						 errhint("Execute a database-wide VACUUM in that database.\n"
-								 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
+								 "You might also need to commit or roll back old prepared transactions, drop stale replication slots, or kill long-running sessions. Ensure that autovacuum is progressing, or run a manual database-wide VACUUM.")));
 		}
 
 		/*
@@ -1054,7 +1054,7 @@ GetNewMultiXactId(int nmembers, MultiXactOffset *offset)
 									   oldest_datname,
 									   multiWrapLimit - result),
 						 errhint("Execute a database-wide VACUUM in that database.\n"
-								 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
+								 "You might also need to commit or roll back old prepared transactions, drop stale replication slots, or kill long-running sessions. Ensure that autovacuum is progressing, or run a manual database-wide VACUUM.")));
 			else
 				ereport(WARNING,
 						(errmsg_plural("database with OID %u must be vacuumed before %u more MultiXactId is used",
@@ -1063,7 +1063,7 @@ GetNewMultiXactId(int nmembers, MultiXactOffset *offset)
 									   oldest_datoid,
 									   multiWrapLimit - result),
 						 errhint("Execute a database-wide VACUUM in that database.\n"
-								 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
+								 "You might also need to commit or roll back old prepared transactions, drop stale replication slots, or kill long-running sessions. Ensure that autovacuum is progressing, or run a manual database-wide VACUUM.")));
 		}
 
 		/* Re-acquire lock and start over */
@@ -2336,7 +2336,7 @@ SetMultiXactIdLimit(MultiXactId oldest_datminmxid, Oid oldest_datoid,
 								   oldest_datname,
 								   multiWrapLimit - curMulti),
 					 errhint("To prevent entering read-only mode, execute a database-wide VACUUM in that database.\n"
-							 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
+							 "You might also need to commit or roll back old prepared transactions, drop stale replication slots, or kill long-running sessions. Ensure that autovacuum is progressing, or run a manual database-wide VACUUM.")));
 		else
 			ereport(WARNING,
 					(errmsg_plural("database with OID %u must be vacuumed before %u more MultiXactId is used",
@@ -2345,7 +2345,7 @@ SetMultiXactIdLimit(MultiXactId oldest_datminmxid, Oid oldest_datoid,
 								   oldest_datoid,
 								   multiWrapLimit - curMulti),
 					 errhint("To prevent entering read-only mode, execute a database-wide VACUUM in that database.\n"
-							 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
+							 "You might also need to commit or roll back old prepared transactions, drop stale replication slots, or kill long-running sessions. Ensure that autovacuum is progressing, or run a manual database-wide VACUUM.")));
 	}
 }
 
diff --git a/src/backend/access/transam/varsup.c b/src/backend/access/transam/varsup.c
index f039c98cd09..52e456b70c7 100644
--- a/src/backend/access/transam/varsup.c
+++ b/src/backend/access/transam/varsup.c
@@ -129,14 +129,14 @@ GetNewTransactionId(bool isSubXact)
 						 errmsg("database can't generate new XIDs to avoid wraparound data loss in database \"%s\"",
 								oldest_datname),
 						 errhint("VACUUM that database.\n"
-								 "You might also need to commit or roll back old prepared transactions and drop stale replication slots.")));
+								 "You might also need to commit or roll back old prepared transactions, drop stale replication slots and kill long-running sessions.")));
 			else
 				ereport(ERROR,
 						(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
 						 errmsg("database can't generate new XIDs to avoid wraparound data loss in database with OID %u",
 								oldest_datoid),
 						 errhint("VACUUM that database.\n"
-								 "You might also need to commit or roll back old prepared transactions and drop stale replication slots.")));
+								 "You might also need to commit or roll back old prepared transactions, drop stale replication slots and kill long-running sessions.")));
 		}
 		else if (TransactionIdFollowsOrEquals(xid, xidWarnLimit))
 		{
@@ -149,14 +149,14 @@ GetNewTransactionId(bool isSubXact)
 								oldest_datname,
 								xidWrapLimit - xid),
 						 errhint("To prevent entering read-only mode, execute a database-wide VACUUM in that database.\n"
-								 "You might also need to commit or roll back old prepared transactions and drop stale replication slots.")));
+								 "You might also need to commit or roll back old prepared transactions, drop stale replication slots and kill long-running sessions.")));
 			else
 				ereport(WARNING,
 						(errmsg("database with OID %u must be vacuumed within %u transactions",
 								oldest_datoid,
 								xidWrapLimit - xid),
 						 errhint("To prevent entering read-only mode, execute a database-wide VACUUM in that database.\n"
-								 "You might also need to commit or roll back old prepared transactions, and drop stale replication slots.")));
+								 "You might also need to commit or roll back old prepared transactions, drop stale replication slots and kill long-running sessions.")));
 		}
 
 		/* Re-acquire lock and start over */
@@ -464,14 +464,14 @@ SetTransactionIdLimit(TransactionId oldest_datfrozenxid, Oid oldest_datoid)
 							oldest_datname,
 							xidWrapLimit - curXid),
 					 errhint("To prevent entering read-only mode, execute a database-wide VACUUM in that database.\n"
-							 "You might also need to commit or roll back old prepared transactions and drop stale replication slots.")));
+							 "You might also need to commit or roll back old prepared transactions, drop stale replication slots and kill long-running sessions.")));
 		else
 			ereport(WARNING,
 					(errmsg("database with OID %u must be vacuumed within %u transactions",
 							oldest_datoid,
 							xidWrapLimit - curXid),
 					 errhint("To prevent entering read-only mode, execute a database-wide VACUUM in that database.\n"
-							 "You might also need to commit or roll back old prepared transactions and drop stale replication slots.")));
+							 "You might also need to commit or roll back old prepared transactions, drop stale replication slots and kill long-running sessions.")));
 	}
 }
 
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index da85330ef40..e8f9fa052a6 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -1049,12 +1049,12 @@ vacuum_get_cutoffs(Relation rel, const VacuumParams *params,
 		ereport(WARNING,
 				(errmsg("cutoff for removing and freezing tuples is far in the past"),
 				 errhint("Close open transactions soon to avoid wraparound problems.\n"
-						 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
+						 "You might also need to commit or roll back old prepared transactions, drop stale replication slots, or kill long-running sessions. Ensure that autovacuum is progressing, or run a manual database-wide VACUUM.")));
 	if (MultiXactIdPrecedes(cutoffs->OldestMxact, safeOldestMxact))
 		ereport(WARNING,
 				(errmsg("cutoff for freezing multixacts is far in the past"),
 				 errhint("Close open transactions soon to avoid wraparound problems.\n"
-						 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
+						 "You might also need to commit or roll back old prepared transactions, drop stale replication slots, or kill long-running sessions. Ensure that autovacuum is progressing, or run a manual database-wide VACUUM.")));
 
 	/*
 	 * Determine the minimum freeze age to use: as specified by the caller, or
-- 
2.37.1 (Apple Git-137.1)

From ebb5b0278d82bcfb566703d78d3e6f08c7b70c02 Mon Sep 17 00:00:00 2001
From: Aleksander Alekseev <aleksander@timescale.com>
Date: Mon, 16 Jan 2023 12:23:30 +0300
Subject: [PATCH v6 1/3] Correct the docs and messages about preventing XID
 wraparound

Prior to this commit the documentation was somewhat misleading. It stated that
the system will "shutdown" in order to prevent XID wraparound. This is not
what actually happens though.

Actually the system refuses to allocate new XIDs. XIDs are allocated lazily
and not needed for checking the visibility rules. Thus read-only transaction
will continues to execute. Additionally, read-write transactions that are
in progress and have an allocated XID will continue to execute and can be
committed.

Additionally, fix the messages shown when approaching xidWrapLimit,
xidWarnLimit and multiWarnLimit accordingly.

Author: Aleksander Alekseev
Reviewed-by: John Naylor
Discussion: https://postgr.es/m/CAJ7c6TM2D277U2wH8X78kg8pH3tdUqebV3_JCJqAkYQFHCFzeg@mail.gmail.com
---
 doc/src/sgml/maintenance.sgml          | 22 +++++++++++-----------
 src/backend/access/transam/multixact.c |  4 ++--
 src/backend/access/transam/varsup.c    | 12 ++++++------
 3 files changed, 19 insertions(+), 19 deletions(-)

diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index 9cf9d030a8..2a902e422c 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -656,31 +656,31 @@ SELECT datname, age(datfrozenxid) FROM pg_database;
 
 <programlisting>
 WARNING:  database "mydb" must be vacuumed within 39985967 transactions
-HINT:  To avoid a database shutdown, execute a database-wide VACUUM in that database.
+HINT:  To prevent entering read-only mode, execute a database-wide VACUUM in that database.
 </programlisting>
 
     (A manual <command>VACUUM</command> should fix the problem, as suggested by the
     hint; but note that the <command>VACUUM</command> must be performed by a
     superuser, else it will fail to process system catalogs and thus not
     be able to advance the database's <structfield>datfrozenxid</structfield>.)
-    If these warnings are
-    ignored, the system will shut down and refuse to start any new
-    transactions once there are fewer than three million transactions left
-    until wraparound:
+    If these warnings are ignored, the system will refuse to allocate new XIDs
+    once there are fewer than three million transactions left until wraparound:
 
 <programlisting>
-ERROR:  database is not accepting commands to avoid wraparound data loss in database "mydb"
+ERROR:  database is not accepting commands that generate new XIDs to avoid wraparound data loss in database "mydb"
 HINT:  Stop the postmaster and vacuum that database in single-user mode.
 </programlisting>
 
+    In this condition any transactions already started can continue.
+    Only new read-only transactions can be started.
+
     The three-million-transaction safety margin exists to let the
     administrator recover without data loss, by manually executing the
-    required <command>VACUUM</command> commands.  However, since the system will not
-    execute commands once it has gone into the safety shutdown mode,
+    required <command>VACUUM</command> commands.  However
     the only way to do this is to stop the server and start the server in single-user
-    mode to execute <command>VACUUM</command>.  The shutdown mode is not enforced
-    in single-user mode.  See the <xref linkend="app-postgres"/> reference
-    page for details about using single-user mode.
+    mode to execute <command>VACUUM</command>. See the
+    <xref linkend="app-postgres"/> reference page for details about using
+    single-user mode.
    </para>
 
    <sect3 id="vacuum-for-multixact-wraparound">
diff --git a/src/backend/access/transam/multixact.c b/src/backend/access/transam/multixact.c
index fe6698d5ff..1dd29c02cc 100644
--- a/src/backend/access/transam/multixact.c
+++ b/src/backend/access/transam/multixact.c
@@ -2335,7 +2335,7 @@ SetMultiXactIdLimit(MultiXactId oldest_datminmxid, Oid oldest_datoid,
 								   multiWrapLimit - curMulti,
 								   oldest_datname,
 								   multiWrapLimit - curMulti),
-					 errhint("To avoid a database shutdown, execute a database-wide VACUUM in that database.\n"
+					 errhint("To prevent entering read-only mode, execute a database-wide VACUUM in that database.\n"
 							 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 		else
 			ereport(WARNING,
@@ -2344,7 +2344,7 @@ SetMultiXactIdLimit(MultiXactId oldest_datminmxid, Oid oldest_datoid,
 								   multiWrapLimit - curMulti,
 								   oldest_datoid,
 								   multiWrapLimit - curMulti),
-					 errhint("To avoid a database shutdown, execute a database-wide VACUUM in that database.\n"
+					 errhint("To prevent entering read-only mode, execute a database-wide VACUUM in that database.\n"
 							 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 	}
 }
diff --git a/src/backend/access/transam/varsup.c b/src/backend/access/transam/varsup.c
index 334adac09e..485d8ebf81 100644
--- a/src/backend/access/transam/varsup.c
+++ b/src/backend/access/transam/varsup.c
@@ -126,14 +126,14 @@ GetNewTransactionId(bool isSubXact)
 			if (oldest_datname)
 				ereport(ERROR,
 						(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
-						 errmsg("database is not accepting commands to avoid wraparound data loss in database \"%s\"",
+						 errmsg("database is not accepting commands that generate new XIDs to avoid wraparound data loss in database \"%s\"",
 								oldest_datname),
 						 errhint("Stop the postmaster and vacuum that database in single-user mode.\n"
 								 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 			else
 				ereport(ERROR,
 						(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
-						 errmsg("database is not accepting commands to avoid wraparound data loss in database with OID %u",
+						 errmsg("database is not accepting commands that generate new XIDs to avoid wraparound data loss in database with OID %u",
 								oldest_datoid),
 						 errhint("Stop the postmaster and vacuum that database in single-user mode.\n"
 								 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
@@ -148,14 +148,14 @@ GetNewTransactionId(bool isSubXact)
 						(errmsg("database \"%s\" must be vacuumed within %u transactions",
 								oldest_datname,
 								xidWrapLimit - xid),
-						 errhint("To avoid a database shutdown, execute a database-wide VACUUM in that database.\n"
+						 errhint("To prevent entering read-only mode, execute a database-wide VACUUM in that database.\n"
 								 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 			else
 				ereport(WARNING,
 						(errmsg("database with OID %u must be vacuumed within %u transactions",
 								oldest_datoid,
 								xidWrapLimit - xid),
-						 errhint("To avoid a database shutdown, execute a database-wide VACUUM in that database.\n"
+						 errhint("To prevent entering read-only mode, execute a database-wide VACUUM in that database.\n"
 								 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 		}
 
@@ -463,14 +463,14 @@ SetTransactionIdLimit(TransactionId oldest_datfrozenxid, Oid oldest_datoid)
 					(errmsg("database \"%s\" must be vacuumed within %u transactions",
 							oldest_datname,
 							xidWrapLimit - curXid),
-					 errhint("To avoid a database shutdown, execute a database-wide VACUUM in that database.\n"
+					 errhint("To prevent entering read-only mode, execute a database-wide VACUUM in that database.\n"
 							 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 		else
 			ereport(WARNING,
 					(errmsg("database with OID %u must be vacuumed within %u transactions",
 							oldest_datoid,
 							xidWrapLimit - curXid),
-					 errhint("To avoid a database shutdown, execute a database-wide VACUUM in that database.\n"
+					 errhint("To prevent entering read-only mode, execute a database-wide VACUUM in that database.\n"
 							 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 	}
 }
-- 
2.40.0

From 7ff56f0b9b68541a2403175a29f2a179d4ecb3c8 Mon Sep 17 00:00:00 2001
From: Aleksander Alekseev <aleksander@timescale.com>
Date: Mon, 16 Jan 2023 15:37:12 +0300
Subject: [PATCH v6 2/3] Don't recommend running VACUUM in a single-user mode
 when reaching xidStopLimit

This recommendation is outdated for some time now. A regular VACUUM
works just fine. Additionally add an explicit note against using a single-user
mode for people who may potentially miss this change.

Author: Aleksander Alekseev
Reported-by: Hannu Krosing
Reviewed-by: John Naylor, Pavel Borisov
Discussion: https://postgr.es/m/CAJ7c6TM2D277U2wH8X78kg8pH3tdUqebV3_JCJqAkYQFHCFzeg@mail.gmail.com
Discussion: https://postgr.es/m/CAMT0RQTmRj_Egtmre6fbiMA9E2hM3BsLULiV8W00stwa3URvzA%40mail.gmail.com
---
 doc/src/sgml/maintenance.sgml       | 20 ++++++++++++++------
 src/backend/access/transam/varsup.c |  4 ++--
 2 files changed, 16 insertions(+), 8 deletions(-)

diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index 2a902e422c..e6b24f3258 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -668,7 +668,7 @@ HINT:  To prevent entering read-only mode, execute a database-wide VACUUM in tha
 
 <programlisting>
 ERROR:  database is not accepting commands that generate new XIDs to avoid wraparound data loss in database "mydb"
-HINT:  Stop the postmaster and vacuum that database in single-user mode.
+HINT:  Execute a database-wide VACUUM in that database.
 </programlisting>
 
     In this condition any transactions already started can continue.
@@ -676,13 +676,21 @@ HINT:  Stop the postmaster and vacuum that database in single-user mode.
 
     The three-million-transaction safety margin exists to let the
     administrator recover without data loss, by manually executing the
-    required <command>VACUUM</command> commands.  However
-    the only way to do this is to stop the server and start the server in single-user
-    mode to execute <command>VACUUM</command>. See the
-    <xref linkend="app-postgres"/> reference page for details about using
-    single-user mode.
+    required <command>VACUUM</command> commands.
    </para>
 
+   <note>
+    <para>
+     Previously it was required to stop the postmaster and
+     <command>VACUUM</command> the database in a single-user mode. There is no
+     need to use a single-user mode anymore unless the resolution needs to be
+     speeded up by performing <command>TRUNCATE</command> or
+     <command>DROP</command>. Except for this scenario it's strongly advised
+     against using a single-user mode: it requires downtime, can't be monitored,
+     disables replication, disables safeguards against wraparound, etc.
+    </para>
+   </note>
+
    <sect3 id="vacuum-for-multixact-wraparound">
     <title>Multixacts and Wraparound</title>
 
diff --git a/src/backend/access/transam/varsup.c b/src/backend/access/transam/varsup.c
index 485d8ebf81..667da0fd64 100644
--- a/src/backend/access/transam/varsup.c
+++ b/src/backend/access/transam/varsup.c
@@ -128,14 +128,14 @@ GetNewTransactionId(bool isSubXact)
 						(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
 						 errmsg("database is not accepting commands that generate new XIDs to avoid wraparound data loss in database \"%s\"",
 								oldest_datname),
-						 errhint("Stop the postmaster and vacuum that database in single-user mode.\n"
+						 errhint("Execute a database-wide VACUUM in that database.\n"
 								 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 			else
 				ereport(ERROR,
 						(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
 						 errmsg("database is not accepting commands that generate new XIDs to avoid wraparound data loss in database with OID %u",
 								oldest_datoid),
-						 errhint("Stop the postmaster and vacuum that database in single-user mode.\n"
+						 errhint("Execute a database-wide VACUUM in that database.\n"
 								 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 		}
 		else if (TransactionIdFollowsOrEquals(xid, xidWarnLimit))
-- 
2.40.0

From 228d4d10bf555f9e40b86fbce805a7bf9b6e9b8b Mon Sep 17 00:00:00 2001
From: Aleksander Alekseev <aleksander@timescale.com>
Date: Mon, 3 Apr 2023 14:00:38 +0300
Subject: [PATCH v6 3/3] Modify the hints about preventing XID wraparound

Per suggestion by Robert Haas.

Author: Aleksander Alekseev
Reviewed-by: John Naylor, Pavel Borisov
Discussion: https://postgr.es/m/CAJ7c6TM2D277U2wH8X78kg8pH3tdUqebV3_JCJqAkYQFHCFzeg@mail.gmail.com
---
 src/backend/access/transam/multixact.c | 12 ++++++------
 src/backend/access/transam/varsup.c    | 12 ++++++------
 2 files changed, 12 insertions(+), 12 deletions(-)

diff --git a/src/backend/access/transam/multixact.c b/src/backend/access/transam/multixact.c
index 1dd29c02cc..5321719d92 100644
--- a/src/backend/access/transam/multixact.c
+++ b/src/backend/access/transam/multixact.c
@@ -1023,14 +1023,14 @@ GetNewMultiXactId(int nmembers, MultiXactOffset *offset)
 						 errmsg("database is not accepting commands that generate new MultiXactIds to avoid wraparound data loss in database \"%s\"",
 								oldest_datname),
 						 errhint("Execute a database-wide VACUUM in that database.\n"
-								 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
+								 "You might also need to commit or roll back old prepared transactions, drop stale replication slots, or kill long-running sessions.")));
 			else
 				ereport(ERROR,
 						(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
 						 errmsg("database is not accepting commands that generate new MultiXactIds to avoid wraparound data loss in database with OID %u",
 								oldest_datoid),
 						 errhint("Execute a database-wide VACUUM in that database.\n"
-								 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
+								 "You might also need to commit or roll back old prepared transactions, drop stale replication slots, or kill long-running sessions.")));
 		}
 
 		/*
@@ -1054,7 +1054,7 @@ GetNewMultiXactId(int nmembers, MultiXactOffset *offset)
 									   oldest_datname,
 									   multiWrapLimit - result),
 						 errhint("Execute a database-wide VACUUM in that database.\n"
-								 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
+								 "You might also need to commit or roll back old prepared transactions, drop stale replication slots, or kill long-running sessions.")));
 			else
 				ereport(WARNING,
 						(errmsg_plural("database with OID %u must be vacuumed before %u more MultiXactId is used",
@@ -1063,7 +1063,7 @@ GetNewMultiXactId(int nmembers, MultiXactOffset *offset)
 									   oldest_datoid,
 									   multiWrapLimit - result),
 						 errhint("Execute a database-wide VACUUM in that database.\n"
-								 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
+								 "You might also need to commit or roll back old prepared transactions, drop stale replication slots, or kill long-running sessions.")));
 		}
 
 		/* Re-acquire lock and start over */
@@ -2336,7 +2336,7 @@ SetMultiXactIdLimit(MultiXactId oldest_datminmxid, Oid oldest_datoid,
 								   oldest_datname,
 								   multiWrapLimit - curMulti),
 					 errhint("To prevent entering read-only mode, execute a database-wide VACUUM in that database.\n"
-							 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
+							 "You might also need to commit or roll back old prepared transactions, drop stale replication slots, or kill long-running sessions.")));
 		else
 			ereport(WARNING,
 					(errmsg_plural("database with OID %u must be vacuumed before %u more MultiXactId is used",
@@ -2345,7 +2345,7 @@ SetMultiXactIdLimit(MultiXactId oldest_datminmxid, Oid oldest_datoid,
 								   oldest_datoid,
 								   multiWrapLimit - curMulti),
 					 errhint("To prevent entering read-only mode, execute a database-wide VACUUM in that database.\n"
-							 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
+							 "You might also need to commit or roll back old prepared transactions, drop stale replication slots, or kill long-running sessions.")));
 	}
 }
 
diff --git a/src/backend/access/transam/varsup.c b/src/backend/access/transam/varsup.c
index 667da0fd64..fd93793e0b 100644
--- a/src/backend/access/transam/varsup.c
+++ b/src/backend/access/transam/varsup.c
@@ -129,14 +129,14 @@ GetNewTransactionId(bool isSubXact)
 						 errmsg("database is not accepting commands that generate new XIDs to avoid wraparound data loss in database \"%s\"",
 								oldest_datname),
 						 errhint("Execute a database-wide VACUUM in that database.\n"
-								 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
+								 "You might also need to commit or roll back old prepared transactions, drop stale replication slots, or kill long-running sessions.")));
 			else
 				ereport(ERROR,
 						(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
 						 errmsg("database is not accepting commands that generate new XIDs to avoid wraparound data loss in database with OID %u",
 								oldest_datoid),
 						 errhint("Execute a database-wide VACUUM in that database.\n"
-								 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
+								 "You might also need to commit or roll back old prepared transactions, drop stale replication slots, or kill long-running sessions.")));
 		}
 		else if (TransactionIdFollowsOrEquals(xid, xidWarnLimit))
 		{
@@ -149,14 +149,14 @@ GetNewTransactionId(bool isSubXact)
 								oldest_datname,
 								xidWrapLimit - xid),
 						 errhint("To prevent entering read-only mode, execute a database-wide VACUUM in that database.\n"
-								 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
+								 "You might also need to commit or roll back old prepared transactions, drop stale replication slots, or kill long-running sessions.")));
 			else
 				ereport(WARNING,
 						(errmsg("database with OID %u must be vacuumed within %u transactions",
 								oldest_datoid,
 								xidWrapLimit - xid),
 						 errhint("To prevent entering read-only mode, execute a database-wide VACUUM in that database.\n"
-								 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
+								 "You might also need to commit or roll back old prepared transactions, drop stale replication slots, or kill long-running sessions.")));
 		}
 
 		/* Re-acquire lock and start over */
@@ -464,14 +464,14 @@ SetTransactionIdLimit(TransactionId oldest_datfrozenxid, Oid oldest_datoid)
 							oldest_datname,
 							xidWrapLimit - curXid),
 					 errhint("To prevent entering read-only mode, execute a database-wide VACUUM in that database.\n"
-							 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
+							 "You might also need to commit or roll back old prepared transactions, drop stale replication slots, or kill long-running sessions.")));
 		else
 			ereport(WARNING,
 					(errmsg("database with OID %u must be vacuumed within %u transactions",
 							oldest_datoid,
 							xidWrapLimit - curXid),
 					 errhint("To prevent entering read-only mode, execute a database-wide VACUUM in that database.\n"
-							 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
+							 "You might also need to commit or roll back old prepared transactions, drop stale replication slots, or kill long-running sessions.")));
 	}
 }
 
-- 
2.40.0

From f5fa3605effd624b263ff3e5dbb8b3e5c8992dba Mon Sep 17 00:00:00 2001
From: John Naylor <john.naylor@postgresql.org>
Date: Sat, 29 Apr 2023 14:23:50 +0700
Subject: [PATCH v7 2/2] Stop telling users to run VACUUM in a single-user mode

Single-user mode is almost always the worst thing to reach for in a
VACUUM emergency:

* Restarting in single user mode requires a shutdown checkpoint
* The user interface is completely different, and awful
* The buffer cache is completely cold
* The checkpointer, background writer and WAL writer are not running
* Without checkpoints WAL segments can not be rotated and reused
* Replication is not running, so after VACUUM is done and database
  is started in normal mode, there is a huge backlog to replicate
* pg_stat_progress_vacuum is not available so there is no indication
  of when the command will complete
* VACUUM VERBOSE doesn't work - there is no output from single-user
  mode vacuum, with or without VERBOSE

If that weren't enough, it's also unsafe because the wraparound
limits are not enforced. It is by no means impossible to corrupt the
database by mistake, such as by a user running VACUUM FULL because it
sounds better.

As mentioned in commit XXXXXXXXX, the system is perfectly capable of
accepting commands when reaching xidStopLimit. Most VACUUM operations
will work normally, with one exception: A new XID is required when
truncating the relation if wal_level is above "minimal". As of v14
the failsafe mechanism disables truncation some time before reaching
xidStopLimit, so this is not an issue in practice.

By remaining in multi-user mode, users still have read-only access to
their database, they can use parallelism, they can use command line
utilities like vacuumdb, and they can remotely access the database
using normal clients.

The only reason to restart in single-user mode is to DROP or TRUNCATE
a table, when it is suspected that doing that would be faster than
vacuuming.

Also add an explicit note warning against using single-user mode.

Backpatch to v14, which is the first version with the VACUUM failsafe.

XXX We should consider v12-13 with "VACUUM (TRUNCATE off, INDEX_CLEANUP
off);", but it's not yet convenient to get to xidStopLimit before v14.

XXX We could consider v11 with careful instructions about redoing
VACUUMs in single-user mode when truncation is necessary.

Aleksander Alekseev, with some adjustments by me

Reported as various times by (at least) Hannu Krosing, Robert Haas,
and Andres Freund

Discussion: https://postgr.es/m/CAJ7c6TM2D277U2wH8X78kg8pH3tdUqebV3_JCJqAkYQFHCFzeg@mail.gmail.com
Discussion: https://www.postgresql.org/message-id/CA%2BTgmob1QCMJrHwRBK8HZtGsr%2B6cJANRQw2mEgJ9e%3DD%2Bz7cOsw%40mail.gmail.com
Discussion: https://www.postgresql.org/message-id/CAMT0RQTmRj_Egtmre6fbiMA9E2hM3BsLULiV8W00stwa3URvzA%40mail.gmail.com
Discussion: https://www.postgresql.org/message-id/CA%2BTgmoYPfofQmRtUan%3DA3aWE9wFsJaOFr%2BW_ys2pPkNPr-2FZw%40mail.gmail.com
---
 doc/src/sgml/maintenance.sgml       | 32 +++++++++++++++++++++--------
 src/backend/access/transam/varsup.c |  4 ++--
 2 files changed, 25 insertions(+), 11 deletions(-)

diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index 105a9900cb..ae9a35fb73 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -669,22 +669,36 @@ HINT:  To prevent entering read-only mode, execute a database-wide VACUUM in tha
 
 <programlisting>
 ERROR:  database is not accepting commands that generate new XIDs to avoid wraparound data loss in database "mydb"
-HINT:  Stop the postmaster and vacuum that database in single-user mode.
+HINT:  Execute a database-wide VACUUM in that database.
 </programlisting>
 
     In this condition any transactions already in progress can continue,
     but only read-only transactions can be started. Operations that
     modify database records or truncate relations will fail.
-
-    The three-million-transaction safety margin exists to let the
-    administrator recover without data loss, by manually executing the
-    required <command>VACUUM</command> commands.  However
-    the only way to do this is to stop the server and start the server in single-user
-    mode to execute <command>VACUUM</command>. See the
-    <xref linkend="app-postgres"/> reference page for details about using
-    single-user mode.
+    The <command>VACUUM</command> command can still be run normally to recover.
+         <!-- v12 and v13 need VACUUM (TRUNCATE off, INDEX_CLEANUP off); >
+         <!-- v11 maybe mention that it will often succed, but will need S-U if truncation happens >
    </para>
 
+   <note>
+    <para>
+     In earlier versions it was required to stop the postmaster and
+     <command>VACUUM</command> the database in a single-user mode. There is no
+     need to use single-user mode anymore, and in fact it's strongly
+     discouraged: It increases downtime, makes monitoring impossible,
+     disables replication, bypasses safeguards against wraparound, etc.
+    </para>
+
+    <para>
+     The only reason to use single-user mode in an emergency is to e.g.
+     <command>TRUNCATE</command> or <command>DROP</command> unneeded tables
+     to avoid needing to <command>VACUUM</command> them.
+     The three-million-transaction safety margin exists to let the
+     administrator do this. See the <xref linkend="app-postgres"/>
+     reference page for details about using single-user mode.
+    </para>
+   </note>
+
    <sect3 id="vacuum-for-multixact-wraparound">
     <title>Multixacts and Wraparound</title>
 
diff --git a/src/backend/access/transam/varsup.c b/src/backend/access/transam/varsup.c
index 485d8ebf81..667da0fd64 100644
--- a/src/backend/access/transam/varsup.c
+++ b/src/backend/access/transam/varsup.c
@@ -128,14 +128,14 @@ GetNewTransactionId(bool isSubXact)
 						(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
 						 errmsg("database is not accepting commands that generate new XIDs to avoid wraparound data loss in database \"%s\"",
 								oldest_datname),
-						 errhint("Stop the postmaster and vacuum that database in single-user mode.\n"
+						 errhint("Execute a database-wide VACUUM in that database.\n"
 								 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 			else
 				ereport(ERROR,
 						(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
 						 errmsg("database is not accepting commands that generate new XIDs to avoid wraparound data loss in database with OID %u",
 								oldest_datoid),
-						 errhint("Stop the postmaster and vacuum that database in single-user mode.\n"
+						 errhint("Execute a database-wide VACUUM in that database.\n"
 								 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 		}
 		else if (TransactionIdFollowsOrEquals(xid, xidWarnLimit))
-- 
2.39.2

From fe9ebc1f89e946f2d9e47fdb4f889a3872532338 Mon Sep 17 00:00:00 2001
From: John Naylor <john.naylor@postgresql.org>
Date: Fri, 28 Apr 2023 16:08:33 +0700
Subject: [PATCH v7 1/2] Correct outdated docs and messages regarding XID
 limits

Previously, when approaching xidStopLimit or xidWrapLimit, log messages
would warn against a "database shutdown", and when it reached those
limits claimed that it "is not accepting commands". This language
originated in commit 60b2444cc when the xidStopLimit was added in
2005. At that time, even a trivial SELECT would have failed.

Commit 295e63983d in 2007 introduced virtual transaction IDs, which
allowed actual XIDs to be allocated lazily when it is necessary
to do so, such as when modifying database records. Since then, the
behavior at these limits is merely to refuse to allocate new XIDs,
so read-only queries can continue to be initiated.

This has been wrong for a very long time, so backpatch to all
supported branches.

Aleksander Alekseev, with some editing by me

Reviewed by Pavel Borisov
Discussion: https://postgr.es/m/CAJ7c6TM2D277U2wH8X78kg8pH3tdUqebV3_JCJqAkYQFHCFzeg@mail.gmail.com
---
 doc/src/sgml/maintenance.sgml          | 22 ++++++++++++----------
 src/backend/access/transam/multixact.c |  4 ++--
 src/backend/access/transam/varsup.c    | 12 ++++++------
 3 files changed, 20 insertions(+), 18 deletions(-)

diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index 9cf9d030a8..105a9900cb 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -656,7 +656,7 @@ SELECT datname, age(datfrozenxid) FROM pg_database;
 
 <programlisting>
 WARNING:  database "mydb" must be vacuumed within 39985967 transactions
-HINT:  To avoid a database shutdown, execute a database-wide VACUUM in that database.
+HINT:  To prevent entering read-only mode, execute a database-wide VACUUM in that database.
 </programlisting>
 
     (A manual <command>VACUUM</command> should fix the problem, as suggested by the
@@ -664,23 +664,25 @@ HINT:  To avoid a database shutdown, execute a database-wide VACUUM in that data
     superuser, else it will fail to process system catalogs and thus not
     be able to advance the database's <structfield>datfrozenxid</structfield>.)
     If these warnings are
-    ignored, the system will shut down and refuse to start any new
-    transactions once there are fewer than three million transactions left
-    until wraparound:
+    ignored, the system will refuse to allocate new XIDs once there are
+    fewer than three million transactions left until wraparound:
 
 <programlisting>
-ERROR:  database is not accepting commands to avoid wraparound data loss in database "mydb"
+ERROR:  database is not accepting commands that generate new XIDs to avoid wraparound data loss in database "mydb"
 HINT:  Stop the postmaster and vacuum that database in single-user mode.
 </programlisting>
 
+    In this condition any transactions already in progress can continue,
+    but only read-only transactions can be started. Operations that
+    modify database records or truncate relations will fail.
+
     The three-million-transaction safety margin exists to let the
     administrator recover without data loss, by manually executing the
-    required <command>VACUUM</command> commands.  However, since the system will not
-    execute commands once it has gone into the safety shutdown mode,
+    required <command>VACUUM</command> commands.  However
     the only way to do this is to stop the server and start the server in single-user
-    mode to execute <command>VACUUM</command>.  The shutdown mode is not enforced
-    in single-user mode.  See the <xref linkend="app-postgres"/> reference
-    page for details about using single-user mode.
+    mode to execute <command>VACUUM</command>. See the
+    <xref linkend="app-postgres"/> reference page for details about using
+    single-user mode.
    </para>
 
    <sect3 id="vacuum-for-multixact-wraparound">
diff --git a/src/backend/access/transam/multixact.c b/src/backend/access/transam/multixact.c
index fe6698d5ff..1dd29c02cc 100644
--- a/src/backend/access/transam/multixact.c
+++ b/src/backend/access/transam/multixact.c
@@ -2335,7 +2335,7 @@ SetMultiXactIdLimit(MultiXactId oldest_datminmxid, Oid oldest_datoid,
 								   multiWrapLimit - curMulti,
 								   oldest_datname,
 								   multiWrapLimit - curMulti),
-					 errhint("To avoid a database shutdown, execute a database-wide VACUUM in that database.\n"
+					 errhint("To prevent entering read-only mode, execute a database-wide VACUUM in that database.\n"
 							 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 		else
 			ereport(WARNING,
@@ -2344,7 +2344,7 @@ SetMultiXactIdLimit(MultiXactId oldest_datminmxid, Oid oldest_datoid,
 								   multiWrapLimit - curMulti,
 								   oldest_datoid,
 								   multiWrapLimit - curMulti),
-					 errhint("To avoid a database shutdown, execute a database-wide VACUUM in that database.\n"
+					 errhint("To prevent entering read-only mode, execute a database-wide VACUUM in that database.\n"
 							 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 	}
 }
diff --git a/src/backend/access/transam/varsup.c b/src/backend/access/transam/varsup.c
index 334adac09e..485d8ebf81 100644
--- a/src/backend/access/transam/varsup.c
+++ b/src/backend/access/transam/varsup.c
@@ -126,14 +126,14 @@ GetNewTransactionId(bool isSubXact)
 			if (oldest_datname)
 				ereport(ERROR,
 						(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
-						 errmsg("database is not accepting commands to avoid wraparound data loss in database \"%s\"",
+						 errmsg("database is not accepting commands that generate new XIDs to avoid wraparound data loss in database \"%s\"",
 								oldest_datname),
 						 errhint("Stop the postmaster and vacuum that database in single-user mode.\n"
 								 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 			else
 				ereport(ERROR,
 						(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
-						 errmsg("database is not accepting commands to avoid wraparound data loss in database with OID %u",
+						 errmsg("database is not accepting commands that generate new XIDs to avoid wraparound data loss in database with OID %u",
 								oldest_datoid),
 						 errhint("Stop the postmaster and vacuum that database in single-user mode.\n"
 								 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
@@ -148,14 +148,14 @@ GetNewTransactionId(bool isSubXact)
 						(errmsg("database \"%s\" must be vacuumed within %u transactions",
 								oldest_datname,
 								xidWrapLimit - xid),
-						 errhint("To avoid a database shutdown, execute a database-wide VACUUM in that database.\n"
+						 errhint("To prevent entering read-only mode, execute a database-wide VACUUM in that database.\n"
 								 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 			else
 				ereport(WARNING,
 						(errmsg("database with OID %u must be vacuumed within %u transactions",
 								oldest_datoid,
 								xidWrapLimit - xid),
-						 errhint("To avoid a database shutdown, execute a database-wide VACUUM in that database.\n"
+						 errhint("To prevent entering read-only mode, execute a database-wide VACUUM in that database.\n"
 								 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 		}
 
@@ -463,14 +463,14 @@ SetTransactionIdLimit(TransactionId oldest_datfrozenxid, Oid oldest_datoid)
 					(errmsg("database \"%s\" must be vacuumed within %u transactions",
 							oldest_datname,
 							xidWrapLimit - curXid),
-					 errhint("To avoid a database shutdown, execute a database-wide VACUUM in that database.\n"
+					 errhint("To prevent entering read-only mode, execute a database-wide VACUUM in that database.\n"
 							 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 		else
 			ereport(WARNING,
 					(errmsg("database with OID %u must be vacuumed within %u transactions",
 							oldest_datoid,
 							xidWrapLimit - curXid),
-					 errhint("To avoid a database shutdown, execute a database-wide VACUUM in that database.\n"
+					 errhint("To prevent entering read-only mode, execute a database-wide VACUUM in that database.\n"
 							 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 	}
 }
-- 
2.39.2

From 469d0e7123a16386e300a85a6bb08109e283b65c Mon Sep 17 00:00:00 2001
From: John Naylor <john.naylor@postgresql.org>
Date: Sat, 29 Apr 2023 14:23:50 +0700
Subject: [PATCH v8 2/3] Stop telling users to run VACUUM in a single-user mode

Single-user mode is almost always the worst thing to reach for in a
VACUUM emergency:

* Restarting in single user mode requires a shutdown checkpoint
* The user interface is completely different, and awful
* The buffer cache is completely cold
* The checkpointer, background writer and WAL writer are not running
* Without checkpoints WAL segments can not be rotated and reused
* Replication is not running, so after VACUUM is done and database
  is started in normal mode, there is a huge backlog to replicate
* pg_stat_progress_vacuum is not available so there is no indication
  of when the command will complete
* VACUUM VERBOSE doesn't work - there is no output from single-user
  mode vacuum, with or without VERBOSE

If that weren't enough, it's also unsafe because the wraparound
limits are not enforced. It is by no means impossible to corrupt the
database by mistake, such as by a user running VACUUM FULL because it
sounds better.

As mentioned in commit XXXXXXXXX, the system is perfectly capable of
accepting commands when reaching xidStopLimit. Most VACUUM operations
will work normally, with one exception: A new XID is required when
truncating the relation if wal_level is above "minimal". As of v14
the failsafe mechanism disables truncation some time before reaching
xidStopLimit, so this is not an issue in practice.

By remaining in multi-user mode, users still have read-only access to
their database, they can use parallelism, they can use command line
utilities like vacuumdb, and they can remotely access the database
using normal clients.

The only reason to restart in single-user mode is to DROP or TRUNCATE
a table, when it is suspected that doing that would be faster than
vacuuming.

Also add an explicit note warning against using single-user mode.

Backpatch to v14, which is the first version with the VACUUM failsafe.

XXX We should consider v12-13 with "VACUUM (TRUNCATE off, INDEX_CLEANUP
off);", but it's not yet convenient to get to xidStopLimit before v14.

XXX We could consider v11 with careful instructions about redoing
VACUUMs in single-user mode when truncation is necessary.

Aleksander Alekseev, with some adjustments by me

Reviewed by Peter Geoghegan

Reported at various times by (at least) Hannu Krosing, Robert Haas,
and Andres Freund

Discussion: https://postgr.es/m/CAJ7c6TM2D277U2wH8X78kg8pH3tdUqebV3_JCJqAkYQFHCFzeg@mail.gmail.com
Discussion: https://postgr.es/m/CA%2BTgmob1QCMJrHwRBK8HZtGsr%2B6cJANRQw2mEgJ9e%3DD%2Bz7cOsw%40mail.gmail.com
Discussion: https://postgr.es/m/CAMT0RQTmRj_Egtmre6fbiMA9E2hM3BsLULiV8W00stwa3URvzA%40mail.gmail.com
Discussion: https://postgr.es/m/CA%2BTgmoYPfofQmRtUan%3DA3aWE9wFsJaOFr%2BW_ys2pPkNPr-2FZw%40mail.gmail.com
---
 doc/src/sgml/maintenance.sgml       | 32 +++++++++++++++++++++--------
 src/backend/access/transam/varsup.c |  4 ++--
 2 files changed, 25 insertions(+), 11 deletions(-)

diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index 48d43cb339..116d6187cd 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -669,22 +669,36 @@ HINT:  To prevent XID generation failure, execute a database-wide VACUUM in that
 
 <programlisting>
 ERROR:  database is not accepting commands that generate new XIDs to avoid wraparound data loss in database "mydb"
-HINT:  Stop the postmaster and vacuum that database in single-user mode.
+HINT:  Execute a database-wide VACUUM in that database.
 </programlisting>
 
     In this condition any transactions already in progress can continue,
     but only read-only transactions can be started. Operations that
     modify database records or truncate relations will fail.
-
-    The three-million-transaction safety margin exists to let the
-    administrator recover without data loss, by manually executing the
-    required <command>VACUUM</command> commands.  However
-    the only way to do this is to stop the server and start the server in single-user
-    mode to execute <command>VACUUM</command>. See the
-    <xref linkend="app-postgres"/> reference page for details about using
-    single-user mode.
+    The <command>VACUUM</command> command can still be run normally to recover.
+         <!-- v12 and v13 need VACUUM (TRUNCATE off, INDEX_CLEANUP off); -->
+         <!-- v11 maybe mention that it will often succed, but will need S-U if truncation happens -->
    </para>
 
+   <note>
+    <para>
+     In earlier versions it was required to stop the postmaster and
+     <command>VACUUM</command> the database in a single-user mode. There is no
+     need to use single-user mode anymore, and in fact it's strongly
+     discouraged: It increases downtime, makes monitoring impossible,
+     disables replication, bypasses safeguards against wraparound, etc.
+    </para>
+
+    <para>
+     The only reason to use single-user mode in an emergency is to e.g.
+     <command>TRUNCATE</command> or <command>DROP</command> unneeded tables
+     to avoid needing to <command>VACUUM</command> them.
+     The three-million-transaction safety margin exists to let the
+     administrator do this. See the <xref linkend="app-postgres"/>
+     reference page for details about using single-user mode.
+    </para>
+   </note>
+
    <sect3 id="vacuum-for-multixact-wraparound">
     <title>Multixacts and Wraparound</title>
 
diff --git a/src/backend/access/transam/varsup.c b/src/backend/access/transam/varsup.c
index 672e6f0196..0d6c4925d5 100644
--- a/src/backend/access/transam/varsup.c
+++ b/src/backend/access/transam/varsup.c
@@ -128,14 +128,14 @@ GetNewTransactionId(bool isSubXact)
 						(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
 						 errmsg("database is not accepting commands that generate new XIDs to avoid wraparound data loss in database \"%s\"",
 								oldest_datname),
-						 errhint("Stop the postmaster and vacuum that database in single-user mode.\n"
+						 errhint("Execute a database-wide VACUUM in that database.\n"
 								 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 			else
 				ereport(ERROR,
 						(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
 						 errmsg("database is not accepting commands that generate new XIDs to avoid wraparound data loss in database with OID %u",
 								oldest_datoid),
-						 errhint("Stop the postmaster and vacuum that database in single-user mode.\n"
+						 errhint("Execute a database-wide VACUUM in that database.\n"
 								 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 		}
 		else if (TransactionIdFollowsOrEquals(xid, xidWarnLimit))
-- 
2.39.2

From 313019305d78a82b816fcfcbd79dc5c570dd31af Mon Sep 17 00:00:00 2001
From: John Naylor <john.naylor@postgresql.org>
Date: Fri, 28 Apr 2023 16:08:33 +0700
Subject: [PATCH v8 1/3] Correct outdated docs and messages regarding XID
 limits

Previously, when approaching xidStopLimit or xidWrapLimit, log messages
would warn against a "database shutdown", and when it reached those
limits claimed that it "is not accepting commands". This language
originated in commit 60b2444cc when the xidStopLimit was added in
2005. At that time, even a trivial SELECT would have failed.

Commit 295e63983d in 2007 introduced virtual transaction IDs, which
allowed actual XIDs to be allocated lazily when it is necessary
to do so, such as when modifying database records. Since then, the
behavior at these limits is merely to refuse to allocate new XIDs,
so read-only queries can continue to be initiated.

The "database shutdown" message was also copied to the message
warning for multiWarnLimit when it was added.

This has been wrong for a very long time, so backpatch to all
supported branches.

Aleksander Alekseev, with some editing by me

Reviewed by Pavel Borisov and Peter Geoghegan
Discussion: https://postgr.es/m/CAJ7c6TM2D277U2wH8X78kg8pH3tdUqebV3_JCJqAkYQFHCFzeg@mail.gmail.com
---
 doc/src/sgml/maintenance.sgml          | 22 ++++++++++++----------
 src/backend/access/transam/multixact.c |  4 ++--
 src/backend/access/transam/varsup.c    | 12 ++++++------
 3 files changed, 20 insertions(+), 18 deletions(-)

diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index 9cf9d030a8..48d43cb339 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -656,7 +656,7 @@ SELECT datname, age(datfrozenxid) FROM pg_database;
 
 <programlisting>
 WARNING:  database "mydb" must be vacuumed within 39985967 transactions
-HINT:  To avoid a database shutdown, execute a database-wide VACUUM in that database.
+HINT:  To prevent XID generation failure, execute a database-wide VACUUM in that database.
 </programlisting>
 
     (A manual <command>VACUUM</command> should fix the problem, as suggested by the
@@ -664,23 +664,25 @@ HINT:  To avoid a database shutdown, execute a database-wide VACUUM in that data
     superuser, else it will fail to process system catalogs and thus not
     be able to advance the database's <structfield>datfrozenxid</structfield>.)
     If these warnings are
-    ignored, the system will shut down and refuse to start any new
-    transactions once there are fewer than three million transactions left
-    until wraparound:
+    ignored, the system will refuse to generate new XIDs once there are
+    fewer than three million transactions left until wraparound:
 
 <programlisting>
-ERROR:  database is not accepting commands to avoid wraparound data loss in database "mydb"
+ERROR:  database is not accepting commands that generate new XIDs to avoid wraparound data loss in database "mydb"
 HINT:  Stop the postmaster and vacuum that database in single-user mode.
 </programlisting>
 
+    In this condition any transactions already in progress can continue,
+    but only read-only transactions can be started. Operations that
+    modify database records or truncate relations will fail.
+
     The three-million-transaction safety margin exists to let the
     administrator recover without data loss, by manually executing the
-    required <command>VACUUM</command> commands.  However, since the system will not
-    execute commands once it has gone into the safety shutdown mode,
+    required <command>VACUUM</command> commands.  However
     the only way to do this is to stop the server and start the server in single-user
-    mode to execute <command>VACUUM</command>.  The shutdown mode is not enforced
-    in single-user mode.  See the <xref linkend="app-postgres"/> reference
-    page for details about using single-user mode.
+    mode to execute <command>VACUUM</command>. See the
+    <xref linkend="app-postgres"/> reference page for details about using
+    single-user mode.
    </para>
 
    <sect3 id="vacuum-for-multixact-wraparound">
diff --git a/src/backend/access/transam/multixact.c b/src/backend/access/transam/multixact.c
index fe6698d5ff..ca0e038c36 100644
--- a/src/backend/access/transam/multixact.c
+++ b/src/backend/access/transam/multixact.c
@@ -2335,7 +2335,7 @@ SetMultiXactIdLimit(MultiXactId oldest_datminmxid, Oid oldest_datoid,
 								   multiWrapLimit - curMulti,
 								   oldest_datname,
 								   multiWrapLimit - curMulti),
-					 errhint("To avoid a database shutdown, execute a database-wide VACUUM in that database.\n"
+					 errhint("To prevent MultiXactId generation failure, execute a database-wide VACUUM in that database.\n"
 							 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 		else
 			ereport(WARNING,
@@ -2344,7 +2344,7 @@ SetMultiXactIdLimit(MultiXactId oldest_datminmxid, Oid oldest_datoid,
 								   multiWrapLimit - curMulti,
 								   oldest_datoid,
 								   multiWrapLimit - curMulti),
-					 errhint("To avoid a database shutdown, execute a database-wide VACUUM in that database.\n"
+					 errhint("To prevent MultiXactId generation failure, execute a database-wide VACUUM in that database.\n"
 							 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 	}
 }
diff --git a/src/backend/access/transam/varsup.c b/src/backend/access/transam/varsup.c
index 334adac09e..672e6f0196 100644
--- a/src/backend/access/transam/varsup.c
+++ b/src/backend/access/transam/varsup.c
@@ -126,14 +126,14 @@ GetNewTransactionId(bool isSubXact)
 			if (oldest_datname)
 				ereport(ERROR,
 						(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
-						 errmsg("database is not accepting commands to avoid wraparound data loss in database \"%s\"",
+						 errmsg("database is not accepting commands that generate new XIDs to avoid wraparound data loss in database \"%s\"",
 								oldest_datname),
 						 errhint("Stop the postmaster and vacuum that database in single-user mode.\n"
 								 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 			else
 				ereport(ERROR,
 						(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
-						 errmsg("database is not accepting commands to avoid wraparound data loss in database with OID %u",
+						 errmsg("database is not accepting commands that generate new XIDs to avoid wraparound data loss in database with OID %u",
 								oldest_datoid),
 						 errhint("Stop the postmaster and vacuum that database in single-user mode.\n"
 								 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
@@ -148,14 +148,14 @@ GetNewTransactionId(bool isSubXact)
 						(errmsg("database \"%s\" must be vacuumed within %u transactions",
 								oldest_datname,
 								xidWrapLimit - xid),
-						 errhint("To avoid a database shutdown, execute a database-wide VACUUM in that database.\n"
+						 errhint("To prevent XID generation failure, execute a database-wide VACUUM in that database.\n"
 								 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 			else
 				ereport(WARNING,
 						(errmsg("database with OID %u must be vacuumed within %u transactions",
 								oldest_datoid,
 								xidWrapLimit - xid),
-						 errhint("To avoid a database shutdown, execute a database-wide VACUUM in that database.\n"
+						 errhint("To prevent XID generation failure, execute a database-wide VACUUM in that database.\n"
 								 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 		}
 
@@ -463,14 +463,14 @@ SetTransactionIdLimit(TransactionId oldest_datfrozenxid, Oid oldest_datoid)
 					(errmsg("database \"%s\" must be vacuumed within %u transactions",
 							oldest_datname,
 							xidWrapLimit - curXid),
-					 errhint("To avoid a database shutdown, execute a database-wide VACUUM in that database.\n"
+					 errhint("To prevent entering read-only mode, execute a database-wide VACUUM in that database.\n"
 							 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 		else
 			ereport(WARNING,
 					(errmsg("database with OID %u must be vacuumed within %u transactions",
 							oldest_datoid,
 							xidWrapLimit - curXid),
-					 errhint("To avoid a database shutdown, execute a database-wide VACUUM in that database.\n"
+					 errhint("To prevent entering read-only mode, execute a database-wide VACUUM in that database.\n"
 							 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 	}
 }
-- 
2.39.2

From ef5448479cea756c349ab2b46138477250cbac8e Mon Sep 17 00:00:00 2001
From: John Naylor <john.naylor@postgresql.org>
Date: Mon, 1 May 2023 19:07:37 +0700
Subject: [PATCH v8 3/3] Rough draft of complete steps to recover from (M)XID
 generation failure

---
 doc/src/sgml/maintenance.sgml | 53 +++++++++++++++++++++++++++++++++++
 1 file changed, 53 insertions(+)

diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index 116d6187cd..8b9f34074b 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -678,6 +678,20 @@ HINT:  Execute a database-wide VACUUM in that database.
     The <command>VACUUM</command> command can still be run normally to recover.
          <!-- v12 and v13 need VACUUM (TRUNCATE off, INDEX_CLEANUP off); -->
          <!-- v11 maybe mention that it will often succed, but will need S-U if truncation happens -->
+
+    However, it is first necessary to remove obstacles to advancing the XID horizon:
+
+    <orderedlist>
+     <listitem>
+      <simpara>Commit or rollback any prepared transactions</simpara>
+     </listitem>
+     <listitem>
+      <simpara>Terminate any sessions that might have open transactions</simpara>
+     </listitem>
+     <listitem>
+      <simpara>Drop any old replication slots</simpara>
+     </listitem>
+    </orderedlist>
    </para>
 
    <note>
@@ -763,6 +777,45 @@ HINT:  Execute a database-wide VACUUM in that database.
      have the oldest multixact-age.  Both of these kinds of aggressive
      scans will occur even if autovacuum is nominally disabled.
     </para>
+
+<!-- WIP: Lot's of stuff identical to the XID case, can we abbreviate? -->
+   <para>
+    If for some reason autovacuum fails to clear old MXIDs from a table, the
+    system will begin to emit warning messages like this when the database's
+    oldest XIDs reach forty million transactions from the wraparound point:
+
+<programlisting>
+WARNING:  database "mydb" must be vacuumed within 39985967 transactions
+HINT:  To prevent MultiXactId generation failure, execute a database-wide VACUUM in that database.
+</programlisting>
+
+<!-- WIP: not sure about system catalogs -->
+    (A manual <command>VACUUM</command> should fix the problem, as suggested by the
+    hint; but note that the <command>VACUUM</command> must be performed by a
+    superuser, else it will fail to process system catalogs and thus not
+    be able to advance the database's <structfield>datfrozenxid</structfield>.)
+    If these warnings are
+    ignored, the system will refuse to generate new MXIDs once there are
+    fewer than three million left until wraparound:
+
+<programlisting>
+ERROR:  database is not accepting commands that generate new MultiXactIds to avoid wraparound data loss in database "mydb"
+HINT:  Execute a database-wide VACUUM in that database.
+</programlisting>
+   </para>
+
+   <para>
+    To restore normal operation, it is first necessary to remove obstacles to advancing the MXID horizon:
+    <orderedlist>
+     <listitem>
+      <simpara>Commit or rollback each prepared transaction that might appear in a multixact</simpara>
+     </listitem>
+     <listitem>
+      <simpara>Resolve each transaction that might appear in a multixact</simpara>
+     </listitem>
+    </orderedlist>
+   </para>
+
    </sect3>
   </sect2>
 
-- 
2.39.2

From 0e9d6ea72216b196d37de4629736c0cf34e7cd5c Mon Sep 17 00:00:00 2001
From: John Naylor <john.naylor@postgresql.org>
Date: Sat, 13 May 2023 11:03:40 +0700
Subject: [PATCH v9 3/3] Rough draft of complete steps to recover from (M)XID
 generation failure

TODO: squash with previous
---
 doc/src/sgml/maintenance.sgml | 61 ++++++++++++++++++++++++++++++++++-
 1 file changed, 60 insertions(+), 1 deletion(-)

diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index 45d6cd1815..fee88cb647 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -675,7 +675,25 @@ HINT:  Execute a database-wide VACUUM in that database.
     In this condition any transactions already in progress can continue,
     but only read-only transactions can be started. Operations that
     modify database records or truncate relations will fail.
-    The <command>VACUUM</command> command can still be run normally to recover.
+    The <command>VACUUM</command> command can still be run normally.
+    To restore normal operation:
+
+    <orderedlist>
+     <listitem>
+      <simpara>Commit or rollback any prepared transactions.</simpara>
+     </listitem>
+     <listitem>
+      <simpara>Terminate any sessions that might have open transactions.</simpara>
+     </listitem>
+     <listitem>
+      <simpara>Drop any old replication slots.</simpara>
+     </listitem>
+     <listitem>
+      <simpara>Ensure autovacuum is running, and execute a database-wide <command>VACUUM</command>.
+        To reduce the time required, it as also possible to issue manual <command>VACUUM</command>
+        commands on the tables where <structfield>relminxid</structfield> is oldest.</simpara>
+     </listitem>
+    </orderedlist>
    </para>
 
    <note>
@@ -761,6 +779,47 @@ HINT:  Execute a database-wide VACUUM in that database.
      have the oldest multixact-age.  Both of these kinds of aggressive
      scans will occur even if autovacuum is nominally disabled.
     </para>
+
+   <para>
+    Similar to the XID case, if autovacuum fails to clear old MXIDs from a table, the
+    system will begin to emit warning messages like this when the database's
+    oldest MXIDs reach forty million transactions from the wraparound point:
+
+<programlisting>
+WARNING:  database "mydb" must be vacuumed within 39985967 transactions
+HINT:  To prevent MultiXactId generation failure, execute a database-wide VACUUM in that database.
+</programlisting>
+
+    (A manual <command>VACUUM</command> should fix the problem, as suggested by the
+    hint; but note that the <command>VACUUM</command> must be performed by a
+    superuser, else it will fail to process system catalogs and thus not
+    be able to advance the database's <structfield>datfrozenxid</structfield>.)
+    If these warnings are ignored, the system will refuse to generate new
+    MXIDs once there are fewer than three million left until wraparound:
+
+<programlisting>
+ERROR:  database is not accepting commands that generate new MultiXactIds to avoid wraparound data loss in database "mydb"
+HINT:  Execute a database-wide VACUUM in that database.
+</programlisting>
+   </para>
+
+   <para>
+    To restore normal operation:
+    <orderedlist>
+     <listitem>
+      <simpara>Commit or rollback each prepared transaction that might appear in a multixact.</simpara>
+     </listitem>
+     <listitem>
+      <simpara>Resolve each transaction that might appear in a multixact.</simpara>
+     </listitem>
+     <listitem>
+      <simpara>Ensure autovacuum is running, and execute a database-wide <command>VACUUM</command>.
+        To reduce the time required, it as also possible to issue manual <command>VACUUM</command>
+        commands on the tables where <structfield>relminmxid</structfield> is oldest.</simpara>
+     </listitem>
+    </orderedlist>
+   </para>
+
    </sect3>
   </sect2>
 
-- 
2.39.2

From 267609882a0be2764bc33fc289c0a962d47643c4 Mon Sep 17 00:00:00 2001
From: John Naylor <john.naylor@postgresql.org>
Date: Fri, 28 Apr 2023 16:08:33 +0700
Subject: [PATCH v9 1/3] Correct outdated docs and messages regarding XID/MXID
 limits

Previously, when approaching xidStopLimit or xidWrapLimit, log messages
would warn against a "database shutdown", and when it reached those
limits claimed that it "is not accepting commands". This language
originated in commit 60b2444cc when the xidStopLimit was added in
2005. At that time, even a trivial SELECT would have failed.

Commit 295e63983d in 2007 introduced virtual transaction IDs, which
allowed actual XIDs to be allocated lazily when it is necessary
to do so, such as when modifying database records. Since then, the
behavior at these limits is merely to refuse to allocate new XIDs,
so read-only queries can continue to be initiated.

The "database shutdown" message was also copied to the message
warning for multiWarnLimit when it was added.

This has been wrong for a very long time, so backpatch to all
supported branches.

Aleksander Alekseev, with some editing by me

Reviewed by Pavel Borisov and Peter Geoghegan
Discussion: https://postgr.es/m/CAJ7c6TM2D277U2wH8X78kg8pH3tdUqebV3_JCJqAkYQFHCFzeg@mail.gmail.com
---
 doc/src/sgml/maintenance.sgml          | 22 ++++++++++++----------
 src/backend/access/transam/multixact.c |  4 ++--
 src/backend/access/transam/varsup.c    | 12 ++++++------
 3 files changed, 20 insertions(+), 18 deletions(-)

diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index 9cf9d030a8..48d43cb339 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -656,7 +656,7 @@ SELECT datname, age(datfrozenxid) FROM pg_database;
 
 <programlisting>
 WARNING:  database "mydb" must be vacuumed within 39985967 transactions
-HINT:  To avoid a database shutdown, execute a database-wide VACUUM in that database.
+HINT:  To prevent XID generation failure, execute a database-wide VACUUM in that database.
 </programlisting>
 
     (A manual <command>VACUUM</command> should fix the problem, as suggested by the
@@ -664,23 +664,25 @@ HINT:  To avoid a database shutdown, execute a database-wide VACUUM in that data
     superuser, else it will fail to process system catalogs and thus not
     be able to advance the database's <structfield>datfrozenxid</structfield>.)
     If these warnings are
-    ignored, the system will shut down and refuse to start any new
-    transactions once there are fewer than three million transactions left
-    until wraparound:
+    ignored, the system will refuse to generate new XIDs once there are
+    fewer than three million transactions left until wraparound:
 
 <programlisting>
-ERROR:  database is not accepting commands to avoid wraparound data loss in database "mydb"
+ERROR:  database is not accepting commands that generate new XIDs to avoid wraparound data loss in database "mydb"
 HINT:  Stop the postmaster and vacuum that database in single-user mode.
 </programlisting>
 
+    In this condition any transactions already in progress can continue,
+    but only read-only transactions can be started. Operations that
+    modify database records or truncate relations will fail.
+
     The three-million-transaction safety margin exists to let the
     administrator recover without data loss, by manually executing the
-    required <command>VACUUM</command> commands.  However, since the system will not
-    execute commands once it has gone into the safety shutdown mode,
+    required <command>VACUUM</command> commands.  However
     the only way to do this is to stop the server and start the server in single-user
-    mode to execute <command>VACUUM</command>.  The shutdown mode is not enforced
-    in single-user mode.  See the <xref linkend="app-postgres"/> reference
-    page for details about using single-user mode.
+    mode to execute <command>VACUUM</command>. See the
+    <xref linkend="app-postgres"/> reference page for details about using
+    single-user mode.
    </para>
 
    <sect3 id="vacuum-for-multixact-wraparound">
diff --git a/src/backend/access/transam/multixact.c b/src/backend/access/transam/multixact.c
index fe6698d5ff..ca0e038c36 100644
--- a/src/backend/access/transam/multixact.c
+++ b/src/backend/access/transam/multixact.c
@@ -2335,7 +2335,7 @@ SetMultiXactIdLimit(MultiXactId oldest_datminmxid, Oid oldest_datoid,
 								   multiWrapLimit - curMulti,
 								   oldest_datname,
 								   multiWrapLimit - curMulti),
-					 errhint("To avoid a database shutdown, execute a database-wide VACUUM in that database.\n"
+					 errhint("To prevent MultiXactId generation failure, execute a database-wide VACUUM in that database.\n"
 							 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 		else
 			ereport(WARNING,
@@ -2344,7 +2344,7 @@ SetMultiXactIdLimit(MultiXactId oldest_datminmxid, Oid oldest_datoid,
 								   multiWrapLimit - curMulti,
 								   oldest_datoid,
 								   multiWrapLimit - curMulti),
-					 errhint("To avoid a database shutdown, execute a database-wide VACUUM in that database.\n"
+					 errhint("To prevent MultiXactId generation failure, execute a database-wide VACUUM in that database.\n"
 							 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 	}
 }
diff --git a/src/backend/access/transam/varsup.c b/src/backend/access/transam/varsup.c
index 334adac09e..672e6f0196 100644
--- a/src/backend/access/transam/varsup.c
+++ b/src/backend/access/transam/varsup.c
@@ -126,14 +126,14 @@ GetNewTransactionId(bool isSubXact)
 			if (oldest_datname)
 				ereport(ERROR,
 						(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
-						 errmsg("database is not accepting commands to avoid wraparound data loss in database \"%s\"",
+						 errmsg("database is not accepting commands that generate new XIDs to avoid wraparound data loss in database \"%s\"",
 								oldest_datname),
 						 errhint("Stop the postmaster and vacuum that database in single-user mode.\n"
 								 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 			else
 				ereport(ERROR,
 						(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
-						 errmsg("database is not accepting commands to avoid wraparound data loss in database with OID %u",
+						 errmsg("database is not accepting commands that generate new XIDs to avoid wraparound data loss in database with OID %u",
 								oldest_datoid),
 						 errhint("Stop the postmaster and vacuum that database in single-user mode.\n"
 								 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
@@ -148,14 +148,14 @@ GetNewTransactionId(bool isSubXact)
 						(errmsg("database \"%s\" must be vacuumed within %u transactions",
 								oldest_datname,
 								xidWrapLimit - xid),
-						 errhint("To avoid a database shutdown, execute a database-wide VACUUM in that database.\n"
+						 errhint("To prevent XID generation failure, execute a database-wide VACUUM in that database.\n"
 								 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 			else
 				ereport(WARNING,
 						(errmsg("database with OID %u must be vacuumed within %u transactions",
 								oldest_datoid,
 								xidWrapLimit - xid),
-						 errhint("To avoid a database shutdown, execute a database-wide VACUUM in that database.\n"
+						 errhint("To prevent XID generation failure, execute a database-wide VACUUM in that database.\n"
 								 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 		}
 
@@ -463,14 +463,14 @@ SetTransactionIdLimit(TransactionId oldest_datfrozenxid, Oid oldest_datoid)
 					(errmsg("database \"%s\" must be vacuumed within %u transactions",
 							oldest_datname,
 							xidWrapLimit - curXid),
-					 errhint("To avoid a database shutdown, execute a database-wide VACUUM in that database.\n"
+					 errhint("To prevent entering read-only mode, execute a database-wide VACUUM in that database.\n"
 							 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 		else
 			ereport(WARNING,
 					(errmsg("database with OID %u must be vacuumed within %u transactions",
 							oldest_datoid,
 							xidWrapLimit - curXid),
-					 errhint("To avoid a database shutdown, execute a database-wide VACUUM in that database.\n"
+					 errhint("To prevent entering read-only mode, execute a database-wide VACUUM in that database.\n"
 							 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 	}
 }
-- 
2.39.2

From aecffc481503f471d8bc1ab0025bc243d126cb47 Mon Sep 17 00:00:00 2001
From: John Naylor <john.naylor@postgresql.org>
Date: Sat, 29 Apr 2023 14:23:50 +0700
Subject: [PATCH v9 2/3] Stop telling users to run VACUUM in a single-user mode

Single-user mode is almost always the worst thing to reach for in a
VACUUM emergency:

* Restarting in single user mode requires a shutdown checkpoint
* The user interface is completely different, and awful
* The buffer cache is completely cold
* The checkpointer, background writer and WAL writer are not running
* Without checkpoints WAL segments can not be rotated and reused
* Replication is not running, so after VACUUM is done and database
  is started in normal mode, there is a huge backlog to replicate
* pg_stat_progress_vacuum is not available so there is no indication
  of when the command will complete
* VACUUM VERBOSE doesn't work - there is no output from single-user
  mode vacuum, with or without VERBOSE

If that weren't enough, it's also unsafe because the wraparound
limits are not enforced. It is by no means impossible to corrupt the
database by mistake, such as by a user running VACUUM FULL because it
sounds better.

As mentioned in commit XXXXXXXXX, the system is perfectly capable of
accepting commands when reaching xidStopLimit. Most VACUUM operations
will work normally, with one exception: A new XID is required when
truncating the relation if wal_level is above "minimal". As of v14
the failsafe mechanism disables truncation some time before reaching
xidStopLimit, so this is not an issue in practice.

By remaining in multi-user mode, users still have read-only access to
their database, they can use parallelism, they can use command line
utilities like vacuumdb, and they can remotely access the database
using normal clients.

The only reason to restart in single-user mode is to DROP or TRUNCATE
a table, when it is suspected that doing that would be faster than
vacuuming.

Also add an explicit note warning against using single-user mode.

Backpatch to v14, which is the first version with the VACUUM failsafe.

XXX We should consider v12-13 with "VACUUM (TRUNCATE off, INDEX_CLEANUP
off);", but it's not yet convenient to get to xidStopLimit before v14.

XXX We could consider v11 with careful instructions about redoing
VACUUMs in single-user mode when truncation is necessary.

Aleksander Alekseev, with some adjustments by me

Reviewed by Peter Geoghegan

Reported at various times by (at least) Hannu Krosing, Robert Haas,
and Andres Freund

Discussion: https://postgr.es/m/CAJ7c6TM2D277U2wH8X78kg8pH3tdUqebV3_JCJqAkYQFHCFzeg@mail.gmail.com
Discussion: https://postgr.es/m/CA%2BTgmob1QCMJrHwRBK8HZtGsr%2B6cJANRQw2mEgJ9e%3DD%2Bz7cOsw%40mail.gmail.com
Discussion: https://postgr.es/m/CAMT0RQTmRj_Egtmre6fbiMA9E2hM3BsLULiV8W00stwa3URvzA%40mail.gmail.com
Discussion: https://postgr.es/m/CA%2BTgmoYPfofQmRtUan%3DA3aWE9wFsJaOFr%2BW_ys2pPkNPr-2FZw%40mail.gmail.com
---
 doc/src/sgml/maintenance.sgml       | 30 ++++++++++++++++++++---------
 src/backend/access/transam/varsup.c |  4 ++--
 2 files changed, 23 insertions(+), 11 deletions(-)

diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index 48d43cb339..45d6cd1815 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -669,22 +669,34 @@ HINT:  To prevent XID generation failure, execute a database-wide VACUUM in that
 
 <programlisting>
 ERROR:  database is not accepting commands that generate new XIDs to avoid wraparound data loss in database "mydb"
-HINT:  Stop the postmaster and vacuum that database in single-user mode.
+HINT:  Execute a database-wide VACUUM in that database.
 </programlisting>
 
     In this condition any transactions already in progress can continue,
     but only read-only transactions can be started. Operations that
     modify database records or truncate relations will fail.
-
-    The three-million-transaction safety margin exists to let the
-    administrator recover without data loss, by manually executing the
-    required <command>VACUUM</command> commands.  However
-    the only way to do this is to stop the server and start the server in single-user
-    mode to execute <command>VACUUM</command>. See the
-    <xref linkend="app-postgres"/> reference page for details about using
-    single-user mode.
+    The <command>VACUUM</command> command can still be run normally to recover.
    </para>
 
+   <note>
+    <para>
+     In earlier versions it was required to stop the postmaster and
+     <command>VACUUM</command> the database in a single-user mode. There is no
+     need to use single-user mode anymore, and in fact it's strongly
+     discouraged: It increases downtime, makes monitoring impossible,
+     disables replication, bypasses safeguards against wraparound, etc.
+    </para>
+
+    <para>
+     The only reason to use single-user mode in an emergency is to e.g.
+     <command>TRUNCATE</command> or <command>DROP</command> unneeded tables
+     to avoid needing to <command>VACUUM</command> them.
+     The three-million-transaction safety margin exists to let the
+     administrator do this. See the <xref linkend="app-postgres"/>
+     reference page for details about using single-user mode.
+    </para>
+   </note>
+
    <sect3 id="vacuum-for-multixact-wraparound">
     <title>Multixacts and Wraparound</title>
 
diff --git a/src/backend/access/transam/varsup.c b/src/backend/access/transam/varsup.c
index 672e6f0196..0d6c4925d5 100644
--- a/src/backend/access/transam/varsup.c
+++ b/src/backend/access/transam/varsup.c
@@ -128,14 +128,14 @@ GetNewTransactionId(bool isSubXact)
 						(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
 						 errmsg("database is not accepting commands that generate new XIDs to avoid wraparound data loss in database \"%s\"",
 								oldest_datname),
-						 errhint("Stop the postmaster and vacuum that database in single-user mode.\n"
+						 errhint("Execute a database-wide VACUUM in that database.\n"
 								 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 			else
 				ereport(ERROR,
 						(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
 						 errmsg("database is not accepting commands that generate new XIDs to avoid wraparound data loss in database with OID %u",
 								oldest_datoid),
-						 errhint("Stop the postmaster and vacuum that database in single-user mode.\n"
+						 errhint("Execute a database-wide VACUUM in that database.\n"
 								 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 		}
 		else if (TransactionIdFollowsOrEquals(xid, xidWarnLimit))
-- 
2.39.2

From e37ba699adc42b7cdd568a575558b68d6288bbad Mon Sep 17 00:00:00 2001
From: Robert Haas <rhaas@postgresql.org>
Date: Thu, 12 Oct 2023 16:01:57 -0400
Subject: [PATCH v10] Update the documentation on recovering from (M)XID
 exhaustion.

The old documentation encourages entering single-user mode for no
reason, which is a bad plan in most cases. Instead, discourage users
from doing that, and explain the limited cases in which it may be
desirable.

The old documentation claims that running VACUUM as anyone but the
superuser can't possibly work, which is not really true, because it
might be that some other user has enough permissions to VACUUM all
the tables that matter. Weaken the language just a bit.

The old documentation claims that you can't run any commands
when near XID exhaustion, which is false because you can still
run commands that don't require an XID, like a SELECT without a
locking clause.

The old documentation doesn't clearly explain that it's a good idea
to get rid of prepared transactons, long-running transactions, and
replication slots that are preventing (M)XID horizon advancement.
Spell out the steps to do that.

Also, discourage the use of VACUUM FULL and VACUUM FREEZE in
this type of scenario.

Alexander Alekseev, John Naylor, Robert Haas, reviewed at various
times by Peter Geoghegan, Hannu Krosing, and Andres Freund.
---
 doc/src/sgml/maintenance.sgml | 112 +++++++++++++++++++++++++++++-----
 1 file changed, 97 insertions(+), 15 deletions(-)

diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index 3018d8f64c..66f2c6a02e 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -660,29 +660,79 @@ HINT:  To avoid a database shutdown, execute a database-wide VACUUM in that data
 </programlisting>
 
     (A manual <command>VACUUM</command> should fix the problem, as suggested by the
-    hint; but note that the <command>VACUUM</command> must be performed by a
-    superuser, else it will fail to process system catalogs and thus not
-    be able to advance the database's <structfield>datfrozenxid</structfield>.)
-    If these warnings are
-    ignored, the system will shut down and refuse to start any new
-    transactions once there are fewer than three million transactions left
-    until wraparound:
+    hint; but note that the <command>VACUUM</command> should be performed by a
+    superuser, else it will fail to process system catalogs, which prevent it from
+    being able to advance the database's <structfield>datfrozenxid</structfield>.)
+    If these warnings are ignored, the system will refuse to assign new XIDs once
+    there are fewer than three million transactions left until wraparound:
 
 <programlisting>
 ERROR:  database is not accepting commands to avoid wraparound data loss in database "mydb"
 HINT:  Stop the postmaster and vacuum that database in single-user mode.
 </programlisting>
 
-    The three-million-transaction safety margin exists to let the
-    administrator recover without data loss, by manually executing the
-    required <command>VACUUM</command> commands.  However, since the system will not
-    execute commands once it has gone into the safety shutdown mode,
-    the only way to do this is to stop the server and start the server in single-user
-    mode to execute <command>VACUUM</command>.  The shutdown mode is not enforced
-    in single-user mode.  See the <xref linkend="app-postgres"/> reference
-    page for details about using single-user mode.
+    In this condition any transactions already in progress can continue,
+    but only read-only transactions can be started. Operations that
+    modify database records or truncate relations will fail.
+    The <command>VACUUM</command> command can still be run normally.
+    Contrary to what the hint states, it is not necessary or desirable to stop the
+    postmaster or enter single user-mode in order to restore normal operation.
+    Instead, follow these steps:
+
+    <orderedlist>
+     <listitem>
+      <simpara>Resolve old prepared transactions. You can find these by checking
+       <link linkend="view-pg-prepared-xacts">pg_prepared_xacts</link> for rows where
+       <literal>age(transactionid)</literal> is large. Such transactions should be
+       committed or rolled back.</simpara>
+     </listitem>
+     <listitem>
+      <simpara>End long-running open transactions. You can find these by checking
+       <link linkend="monitoring-pg-stat-activity-view">pg_stat_activity</link> for rows where
+       <literal>age(backend_xid)</literal> or <literal>age(backend_xmin)</literal> is
+       large. Such transactions should be committed or rolled back, or the session
+       can be terminated using <literal>pg_terminate_backend</literal>.</simpara>
+     </listitem>
+     <listitem>
+      <simpara>Drop any old replication slots. Use
+       <link linkend="monitoring-pg-stat-replication-view">pg_stat_replication</link> to
+       find slots where <literal>age(xmin)</literal> or <literal>age(catalog_xmin)</literal>
+       is large. In many cases, such slots were created for replication to servers that no
+       longer exist, or that have been down for a long time. If you drop a slot for a server
+       that still exists and might still try to connect to that slot, that replica may
+       need to be rebuilt.</simpara>
+     </listitem>
+     <listitem>
+      <simpara>Execute <command>VACUUM</command> in the target database. A database-wide
+       <literal>VACUUM</literal> is simplest; to reduce the time required, it as also possible
+       to issue manual <command>VACUUM</command> commands on the tables where
+       <structfield>relminxid</structfield> is oldest. Do not use <literal>VACUUM FULL</literal>
+       in this scenario, because it requires an XID and will therefore fail, except in super-user
+       mode, where it will instead consume an XID and thus increase the risk of transaction ID
+       wraparound. Do not use <literal>VACUUM FREEZE</literal> either, because it will do
+       more than the minimum amount of work required to restore normal operation.</simpara>
+     </listitem>
+     <listitem>
+      <simpara>Once normal operation is restored, ensure that autovacuum is properly configured
+       in the target database in order to avoid future problems.</simpara>
+     </listitem>
+    </orderedlist>
    </para>
 
+   <note>
+    <para>
+     In earlier versions, it was sometimes necessary to stop the postmaster and
+     <command>VACUUM</command> the database in a single-user mode. In typical scenarios, this
+     is no longer necessary, and should be avoided whenever possible, since it involves taking
+     the system down. It is also riskier, since it disables transaction ID wraparound safeguards
+     that are designed to prevent data loss.  The only reason to use single-user mode in this
+     scenario is if you wish to <command>TRUNCATE</command> or <command>DROP</command> unneeded
+     tables to avoid needing to <command>VACUUM</command> them.  The three-million-transaction
+     safety margin exists to let the administrator do this. See the
+     <xref linkend="app-postgres"/> reference page for details about using single-user mode.
+    </para>
+   </note>
+
    <sect3 id="vacuum-for-multixact-wraparound">
     <title>Multixacts and Wraparound</title>
 
@@ -747,6 +797,38 @@ HINT:  Stop the postmaster and vacuum that database in single-user mode.
      have the oldest multixact-age.  Both of these kinds of aggressive
      scans will occur even if autovacuum is nominally disabled.
     </para>
+
+    <para>
+     Similar to the XID case, if autovacuum fails to clear old MXIDs from a table, the
+     system will begin to emit warning messages when the database's oldest MXIDs reach forty
+     million transactions from the wraparound point.  And, just as an the XID case, if these
+     warnings are ignored, the system will refuse to generate new MXIDs once there are fewer
+     than three million left until wraparound.
+    </para>
+
+    <para>
+     Normal operation when MXIDs are exhausted can be restored in much the same way as
+     when XIDs are exhausted. Follow the same steps in the previous section, but with the
+     following differences:
+
+    <orderedlist>
+     <listitem>
+      <simpara>Running transactions and prepared transactions can be ignored if there
+       is no chance that they might appear in a multixact.</simpara>
+     </listitem>
+     <listitem>
+      <simpara>MXID information is not directly visible in system views such as
+       <literal>pg_stat_activity</literal>; however, looking for old XIDs is still a good
+       way of determining which transactions are causing MXID wraparound problems.</simpara>
+     </listitem>
+     <listitem>
+      <simpara>XID exhaustion will block all write transactions, but MXID exhaustion will
+       only block a subset of write transactions, specifically those that involve
+       row locks that require an MXID.</simpara>
+     </listitem>
+    </orderedlist>
+   </para>
+
    </sect3>
   </sect2>
 
-- 
2.37.1 (Apple Git-137.1)

From 94875d3213df0c60f74bc4ffce6ac13a9ab2350f Mon Sep 17 00:00:00 2001
From: Robert Haas <rhaas@postgresql.org>
Date: Mon, 16 Oct 2023 13:46:01 -0400
Subject: [PATCH v11 2/2] Reword messages about impending (M)XID exhaustion.

First, we shouldn't recommend switching to single-user mode, because
that's terrible advice. Especially on newer versions where VACUUM
will enter emergency mode when nearing (M)XID exhaustion, it's
perfectly fine to just VACUUM in multi-user mode. Doing it that way
is less disruptive and avoids disabling the safeguards that prevent
actual wraparound, so recommend that instead.

Second, be more precise about what is going to happen (when we're
nearing the limits) or what is happening (when we actually hit them).
The database doesn't shut down, nor does it refuse all commands. It
refuses commands that assign whichever of XIDs and MXIDs are nearly
exhausted.

No back-patch. The existing hint that advises going to single-user
mode is sufficiently awful advice that removing it or changing it
might be justifiable even though we normally avoid changing
user-facing messages in back-branches, but I (rhaas) felt that it
was better to be more conservative and limit this fix to master
only. Aside from the usual risk of breaking transalations, people
might be used to the existing message, or even have monitoring
scripts that look for it.

Alexander Alekseev, John Naylor, Robert Haas, reviewed at various
times by Peter Geoghegan, Hannu Krosing, and Andres Freund.
---
 doc/src/sgml/maintenance.sgml          | 11 ++++++-----
 src/backend/access/transam/multixact.c |  4 ++--
 src/backend/access/transam/varsup.c    | 16 ++++++++--------
 3 files changed, 16 insertions(+), 15 deletions(-)

diff --git a/doc/src/sgml/maintenance.sgml b/doc/src/sgml/maintenance.sgml
index 66f2c6a02e..9389eef973 100644
--- a/doc/src/sgml/maintenance.sgml
+++ b/doc/src/sgml/maintenance.sgml
@@ -656,7 +656,7 @@ SELECT datname, age(datfrozenxid) FROM pg_database;
 
 <programlisting>
 WARNING:  database "mydb" must be vacuumed within 39985967 transactions
-HINT:  To avoid a database shutdown, execute a database-wide VACUUM in that database.
+HINT:  To avoid XID assignment failures, execute a database-wide VACUUM in that database.
 </programlisting>
 
     (A manual <command>VACUUM</command> should fix the problem, as suggested by the
@@ -667,16 +667,17 @@ HINT:  To avoid a database shutdown, execute a database-wide VACUUM in that data
     there are fewer than three million transactions left until wraparound:
 
 <programlisting>
-ERROR:  database is not accepting commands to avoid wraparound data loss in database "mydb"
-HINT:  Stop the postmaster and vacuum that database in single-user mode.
+ERROR:  database is not accepting commands that assign new XIDs to avoid wraparound data loss in database "mydb"
+HINT:  Execute a database-wide VACUUM in that database.
 </programlisting>
 
     In this condition any transactions already in progress can continue,
     but only read-only transactions can be started. Operations that
     modify database records or truncate relations will fail.
     The <command>VACUUM</command> command can still be run normally.
-    Contrary to what the hint states, it is not necessary or desirable to stop the
-    postmaster or enter single user-mode in order to restore normal operation.
+    Note that, contrary to what was sometimes recommended in earlier releases,
+    it is not necessary or desirable to stop the postmaster or enter single
+    user-mode in order to restore normal operation.
     Instead, follow these steps:
 
     <orderedlist>
diff --git a/src/backend/access/transam/multixact.c b/src/backend/access/transam/multixact.c
index 546d411610..57ed34c0a8 100644
--- a/src/backend/access/transam/multixact.c
+++ b/src/backend/access/transam/multixact.c
@@ -2335,7 +2335,7 @@ SetMultiXactIdLimit(MultiXactId oldest_datminmxid, Oid oldest_datoid,
 								   multiWrapLimit - curMulti,
 								   oldest_datname,
 								   multiWrapLimit - curMulti),
-					 errhint("To avoid a database shutdown, execute a database-wide VACUUM in that database.\n"
+					 errhint("To avoid MultiXactId assignment failures, execute a database-wide VACUUM in that database.\n"
 							 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 		else
 			ereport(WARNING,
@@ -2344,7 +2344,7 @@ SetMultiXactIdLimit(MultiXactId oldest_datminmxid, Oid oldest_datoid,
 								   multiWrapLimit - curMulti,
 								   oldest_datoid,
 								   multiWrapLimit - curMulti),
-					 errhint("To avoid a database shutdown, execute a database-wide VACUUM in that database.\n"
+					 errhint("To avoid MultiXactId assignment failures, execute a database-wide VACUUM in that database.\n"
 							 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 	}
 }
diff --git a/src/backend/access/transam/varsup.c b/src/backend/access/transam/varsup.c
index 334adac09e..abfee48317 100644
--- a/src/backend/access/transam/varsup.c
+++ b/src/backend/access/transam/varsup.c
@@ -126,16 +126,16 @@ GetNewTransactionId(bool isSubXact)
 			if (oldest_datname)
 				ereport(ERROR,
 						(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
-						 errmsg("database is not accepting commands to avoid wraparound data loss in database \"%s\"",
+						 errmsg("database is not accepting commands that assign new XIDs to avoid wraparound data loss in database \"%s\"",
 								oldest_datname),
-						 errhint("Stop the postmaster and vacuum that database in single-user mode.\n"
+						 errhint("Execute a database-wide VACUUM in that database.\n"
 								 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 			else
 				ereport(ERROR,
 						(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
-						 errmsg("database is not accepting commands to avoid wraparound data loss in database with OID %u",
+						 errmsg("database is not accepting commands that assign new XIDs to avoid wraparound data loss in database with OID %u",
 								oldest_datoid),
-						 errhint("Stop the postmaster and vacuum that database in single-user mode.\n"
+						 errhint("Execute a database-wide VACUUM in that database.\n"
 								 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 		}
 		else if (TransactionIdFollowsOrEquals(xid, xidWarnLimit))
@@ -148,14 +148,14 @@ GetNewTransactionId(bool isSubXact)
 						(errmsg("database \"%s\" must be vacuumed within %u transactions",
 								oldest_datname,
 								xidWrapLimit - xid),
-						 errhint("To avoid a database shutdown, execute a database-wide VACUUM in that database.\n"
+						 errhint("To avoid XID assignment failures, execute a database-wide VACUUM in that database.\n"
 								 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 			else
 				ereport(WARNING,
 						(errmsg("database with OID %u must be vacuumed within %u transactions",
 								oldest_datoid,
 								xidWrapLimit - xid),
-						 errhint("To avoid a database shutdown, execute a database-wide VACUUM in that database.\n"
+						 errhint("To avoid XID assignment failures, execute a database-wide VACUUM in that database.\n"
 								 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 		}
 
@@ -463,14 +463,14 @@ SetTransactionIdLimit(TransactionId oldest_datfrozenxid, Oid oldest_datoid)
 					(errmsg("database \"%s\" must be vacuumed within %u transactions",
 							oldest_datname,
 							xidWrapLimit - curXid),
-					 errhint("To avoid a database shutdown, execute a database-wide VACUUM in that database.\n"
+					 errhint("To avoid XID assignment failures, execute a database-wide VACUUM in that database.\n"
 							 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 		else
 			ereport(WARNING,
 					(errmsg("database with OID %u must be vacuumed within %u transactions",
 							oldest_datoid,
 							xidWrapLimit - curXid),
-					 errhint("To avoid a database shutdown, execute a database-wide VACUUM in that database.\n"
+					 errhint("To avoid XID assignment failures, execute a database-wide VACUUM in that database.\n"
 							 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 	}
 }
-- 
2.37.1 (Apple Git-137.1)

From 569238c86ebb97ce96ae12e47c84e7ce7f81c68b Mon Sep 17 00:00:00 2001
From: Robert Haas <rhaas@postgresql.org>
Date: Thu, 12 Oct 2023 14:50:15 -0400
Subject: [PATCH v11 1/2] Talk about assigning, rather than generating, new
 MultiXactIds.

The word "assign" is used in various places internally to describe what
GetNewMultiXactId does, but the user-facing messages have previously
said "generate". For consistency, standardize on "assign," which seems
(at least to me) to be slightly clearer.
---
 src/backend/access/transam/multixact.c | 4 ++--
 1 file changed, 2 insertions(+), 2 deletions(-)

diff --git a/src/backend/access/transam/multixact.c b/src/backend/access/transam/multixact.c
index abb022e067..546d411610 100644
--- a/src/backend/access/transam/multixact.c
+++ b/src/backend/access/transam/multixact.c
@@ -1020,14 +1020,14 @@ GetNewMultiXactId(int nmembers, MultiXactOffset *offset)
 			if (oldest_datname)
 				ereport(ERROR,
 						(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
-						 errmsg("database is not accepting commands that generate new MultiXactIds to avoid wraparound data loss in database \"%s\"",
+						 errmsg("database is not accepting commands that assign new MultiXactIds to avoid wraparound data loss in database \"%s\"",
 								oldest_datname),
 						 errhint("Execute a database-wide VACUUM in that database.\n"
 								 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
 			else
 				ereport(ERROR,
 						(errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
-						 errmsg("database is not accepting commands that generate new MultiXactIds to avoid wraparound data loss in database with OID %u",
+						 errmsg("database is not accepting commands that assign new MultiXactIds to avoid wraparound data loss in database with OID %u",
 								oldest_datoid),
 						 errhint("Execute a database-wide VACUUM in that database.\n"
 								 "You might also need to commit or roll back old prepared transactions, or drop stale replication slots.")));
-- 
2.37.1 (Apple Git-137.1)