v5-0003-Modify-the-hints-about-preventing-XID-wraparound.patch

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)