vacuum_freeze_terminate_blockers_pid.patch

diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index 99d0db82ed7..49844fe0b5a 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -50,6 +50,7 @@
 #include "postmaster/interrupt.h"
 #include "storage/bufmgr.h"
 #include "storage/lmgr.h"
+#include "storage/lock.h"
 #include "storage/pmsignal.h"
 #include "storage/proc.h"
 #include "storage/procarray.h"
@@ -58,6 +59,7 @@
 #include "utils/guc.h"
 #include "utils/guc_hooks.h"
 #include "utils/injection_point.h"
+#include "utils/lsyscache.h"
 #include "utils/memutils.h"
 #include "utils/snapmgr.h"
 #include "utils/syscache.h"
@@ -80,6 +82,7 @@ int			vacuum_multixact_freeze_table_age;
 int			vacuum_failsafe_age;
 int			vacuum_multixact_failsafe_age;
 double		vacuum_max_eager_freeze_failure_rate;
+bool		vacuum_freeze_terminate_blockers_pid;
 bool		track_cost_delay_timing;
 bool		vacuum_truncate;
 
@@ -128,6 +131,10 @@ static void vac_truncate_clog(TransactionId frozenXID,
 							  MultiXactId lastSaneMinMulti);
 static bool vacuum_rel(Oid relid, RangeVar *relation, VacuumParams params,
 					   BufferAccessStrategy bstrategy, bool isTopLevel);
+static void vacuum_maybe_terminate_freeze_pid(Relation rel,
+											  struct VacuumCutoffs *cutoffs,
+											  TransactionId freezeLimit,
+											  TransactionId nextXID);
 static double compute_parallel_delay(void);
 static VacOptValue get_vacoptval_from_boolean(DefElem *def);
 static bool vac_tid_reaped(ItemPointer itemptr, void *state);
@@ -1108,6 +1115,7 @@ vacuum_get_cutoffs(Relation rel, const VacuumParams *params,
 				effective_multixact_freeze_max_age;
 	TransactionId nextXID,
 				safeOldestXmin,
+				unconstrainedFreezeLimit,
 				aggressiveXIDCutoff;
 	MultiXactId nextMXID,
 				safeOldestMxact,
@@ -1186,9 +1194,14 @@ vacuum_get_cutoffs(Relation rel, const VacuumParams *params,
 	Assert(freeze_min_age >= 0);
 
 	/* Compute FreezeLimit, being careful to generate a normal XID */
-	cutoffs->FreezeLimit = nextXID - freeze_min_age;
-	if (!TransactionIdIsNormal(cutoffs->FreezeLimit))
-		cutoffs->FreezeLimit = FirstNormalTransactionId;
+	unconstrainedFreezeLimit = nextXID - freeze_min_age;
+	if (!TransactionIdIsNormal(unconstrainedFreezeLimit))
+		unconstrainedFreezeLimit = FirstNormalTransactionId;
+
+	vacuum_maybe_terminate_freeze_pid(rel, cutoffs,
+									  unconstrainedFreezeLimit, nextXID);
+
+	cutoffs->FreezeLimit = unconstrainedFreezeLimit;
 	/* FreezeLimit must always be <= OldestXmin */
 	if (TransactionIdPrecedes(cutoffs->OldestXmin, cutoffs->FreezeLimit))
 		cutoffs->FreezeLimit = cutoffs->OldestXmin;
@@ -1258,6 +1271,100 @@ vacuum_get_cutoffs(Relation rel, const VacuumParams *params,
 	return false;
 }
 
+/*
+ * Terminate active backends that are holding back VACUUM's ability to advance
+ * FreezeLimit, when explicitly enabled by the user.
+ */
+static void
+vacuum_maybe_terminate_freeze_pid(Relation rel,
+								  struct VacuumCutoffs *cutoffs,
+								  TransactionId freezeLimit,
+								  TransactionId nextXID)
+{
+	VirtualTransactionId *vxids;
+	VirtualTransactionId *vxid;
+	TransactionId freezeTerminateLimit;
+	TransactionId freezeTerminateAgeXids;
+	double		freezeTerminateAge;
+	int			terminated = 0;
+	int			i;
+	Oid			dbOid;
+
+	if (!vacuum_freeze_terminate_blockers_pid)
+		return;
+
+	if (vacuum_failsafe_age <= 0)
+		return;
+
+	/*
+	 * Determine the freeze termination age to use.  Normally this scales
+	 * vacuum_failsafe_age by autovacuum_freeze_score_weight.  When the weight
+	 * is zero, use vacuum_failsafe_age directly.
+	 */
+	if (likely(autovacuum_freeze_score_weight > 0.0))
+		freezeTerminateAge =
+			(double) vacuum_failsafe_age / autovacuum_freeze_score_weight;
+	else
+		freezeTerminateAge = (double) vacuum_failsafe_age;
+
+	if (freezeTerminateAge >= (double) MaxTransactionId)
+		return;
+
+	freezeTerminateAgeXids = (TransactionId) floor(freezeTerminateAge);
+	freezeTerminateLimit = nextXID - freezeTerminateAgeXids;
+	if (!TransactionIdIsNormal(freezeTerminateLimit))
+		freezeTerminateLimit = FirstNormalTransactionId;
+
+	/* Only act once the table age has passed the termination age. */
+	if (!TransactionIdPrecedes(cutoffs->relfrozenxid, freezeTerminateLimit))
+		return;
+
+	/* If OldestXmin is not holding back FreezeLimit, nobody blocks freeze. */
+	if (!TransactionIdPrecedes(cutoffs->OldestXmin, freezeLimit))
+		return;
+
+	dbOid = rel->rd_rel->relisshared ? InvalidOid : MyDatabaseId;
+	vxids = GetVirtualXIDsBlockingVacuumFreeze(freezeLimit, dbOid);
+
+	for (vxid = vxids; VirtualTransactionIdIsValid(*vxid); vxid++)
+	{
+		int			pid = 0;
+
+		if (TerminateBackendWithVirtualXID(*vxid, &pid))
+		{
+			char	   *nspname;
+
+			vxids[terminated++] = *vxid;
+			nspname = get_namespace_name(RelationGetNamespace(rel));
+
+			ereport(LOG,
+						(errmsg("terminating backend with PID %d because it blocks vacuum freeze of table \"%s.%s\"",
+								pid, nspname, RelationGetRelationName(rel)),
+					 errdetail("The table age is greater than the freeze termination age derived from vacuum_failsafe_age and autovacuum_freeze_score_weight."),
+					 errhint("Disable configuration parameter \"vacuum_freeze_terminate_blockers_pid\" to prevent VACUUM from terminating blocking sessions.")));
+
+			pfree(nspname);
+		}
+	}
+
+	/*
+	 * Terminate blockers before waiting, matching the recovery-conflict
+	 * pattern of identifying blockers by VXID and then waiting for each
+	 * signaled VXID to disappear.  Once they are gone, recompute OldestXmin so
+	 * this VACUUM can use the less conservative freeze cutoff immediately.
+	 */
+	if (terminated > 0)
+	{
+		for (i = 0; i < terminated; i++)
+			VirtualXactLock(vxids[i], true);
+
+		cutoffs->OldestXmin = GetOldestNonRemovableTransactionId(rel);
+		Assert(TransactionIdIsNormal(cutoffs->OldestXmin));
+	}
+
+	pfree(vxids);
+}
+
 /*
  * vacuum_xid_failsafe_check() -- Used by VACUUM's wraparound failsafe
  * mechanism to determine if its table's relfrozenxid and relminmxid are now
diff --git a/src/backend/storage/ipc/procarray.c b/src/backend/storage/ipc/procarray.c
index 9299bcebbda..a71ce8c6e45 100644
--- a/src/backend/storage/ipc/procarray.c
+++ b/src/backend/storage/ipc/procarray.c
@@ -3350,6 +3350,92 @@ GetCurrentVirtualXIDs(TransactionId limitXmin, bool excludeXmin0,
 	return vxids;
 }
 
+/*
+ * GetVirtualXIDsBlockingVacuumFreeze -- returns active VXIDs holding back
+ * VACUUM's freeze horizon.
+ *
+ * The caller supplies the freeze cutoff that it wanted to use before it was
+ * constrained by OldestXmin.  We return regular client backends whose xmin/xid
+ * horizon is older than that cutoff and whose database scope matches the
+ * relation being vacuumed.
+ *
+ * Replication slots, hot standby feedback, and prepared transactions can also
+ * hold back horizons, but they are not ordinary long-running client
+ * transactions, so this routine deliberately ignores them.
+ *
+ * The result is palloc'd and terminated with an invalid VXID.
+ */
+VirtualTransactionId *
+GetVirtualXIDsBlockingVacuumFreeze(TransactionId limitXmin, Oid dbOid)
+{
+	VirtualTransactionId *vxids;
+	ProcArrayStruct *arrayP = procArray;
+	TransactionId *other_xids = ProcGlobal->xids;
+	int			count = 0;
+	int			index;
+
+	Assert(TransactionIdIsValid(limitXmin));
+
+	vxids = palloc_array(VirtualTransactionId, arrayP->maxProcs + 1);
+
+	LWLockAcquire(ProcArrayLock, LW_SHARED);
+
+	for (index = 0; index < arrayP->numProcs; index++)
+	{
+		int			pgprocno = arrayP->pgprocnos[index];
+		PGPROC	   *proc = &allProcs[pgprocno];
+		uint8		statusFlags = ProcGlobal->statusFlags[index];
+		TransactionId xid;
+		TransactionId xmin;
+
+		if (proc == MyProc)
+			continue;
+
+		/* Prepared transactions can block horizons, but have no session PID. */
+		if (proc->pid == 0)
+			continue;
+
+		/* Only ordinary client backends are actionable here. */
+		if (proc->backendType != B_BACKEND)
+			continue;
+
+		/* Hot standby feedback affects all horizons, but is not a client xact. */
+		if (statusFlags & PROC_AFFECTS_ALL_HORIZONS)
+			continue;
+
+		/*
+		 * Match ComputeXidHorizons(): lazy VACUUMs and logical decoding
+		 * backends do not hold back VACUUM's non-removable horizon here.
+		 */
+		if (statusFlags & (PROC_IN_VACUUM | PROC_IN_LOGICAL_DECODING))
+			continue;
+
+		if (OidIsValid(dbOid) && proc->databaseId != dbOid)
+			continue;
+
+		xid = UINT32_ACCESS_ONCE(other_xids[index]);
+		xmin = UINT32_ACCESS_ONCE(proc->xmin);
+		xmin = TransactionIdOlder(xmin, xid);
+
+		if (TransactionIdIsValid(xmin) &&
+			TransactionIdPrecedes(xmin, limitXmin))
+		{
+			VirtualTransactionId vxid;
+
+			GET_VXID_FROM_PGPROC(vxid, *proc);
+			if (VirtualTransactionIdIsValid(vxid))
+				vxids[count++] = vxid;
+		}
+	}
+
+	LWLockRelease(ProcArrayLock);
+
+	vxids[count].procNumber = INVALID_PROC_NUMBER;
+	vxids[count].localTransactionId = InvalidLocalTransactionId;
+
+	return vxids;
+}
+
 /*
  * GetConflictingVirtualXIDs -- returns an array of currently active VXIDs.
  *
@@ -3454,6 +3540,61 @@ GetConflictingVirtualXIDs(TransactionId limitXmin, Oid dbOid)
 	return vxids;
 }
 
+/*
+ * TerminateBackendWithVirtualXID -- terminate the backend still owning a VXID.
+ *
+ * This follows the recovery-conflict convention of resolving the target by
+ * VXID under ProcArrayLock before signaling it.  The target PID is returned
+ * to the caller for reporting.
+ */
+bool
+TerminateBackendWithVirtualXID(VirtualTransactionId vxid, int *pid)
+{
+	ProcArrayStruct *arrayP = procArray;
+	pid_t		target_pid = 0;
+	int			index;
+
+	Assert(VirtualTransactionIdIsValid(vxid));
+
+	LWLockAcquire(ProcArrayLock, LW_SHARED);
+
+	for (index = 0; index < arrayP->numProcs; index++)
+	{
+		int			pgprocno = arrayP->pgprocnos[index];
+		PGPROC	   *proc = &allProcs[pgprocno];
+		uint8		statusFlags = ProcGlobal->statusFlags[index];
+		VirtualTransactionId procvxid;
+
+		GET_VXID_FROM_PGPROC(procvxid, *proc);
+
+		if (procvxid.procNumber == vxid.procNumber &&
+			procvxid.localTransactionId == vxid.localTransactionId)
+		{
+			if (proc->backendType == B_BACKEND &&
+				!(statusFlags & PROC_AFFECTS_ALL_HORIZONS))
+				target_pid = proc->pid;
+			break;
+		}
+	}
+
+	LWLockRelease(ProcArrayLock);
+
+	if (pid)
+		*pid = target_pid;
+
+	if (target_pid == 0)
+		return false;
+
+#ifdef HAVE_SETSID
+	if (kill(-target_pid, SIGTERM))
+#else
+	if (kill(target_pid, SIGTERM))
+#endif
+		return false;
+
+	return true;
+}
+
 /*
  * SignalRecoveryConflict -- signal that a process is blocking recovery
  *
diff --git a/src/backend/utils/misc/guc_parameters.dat b/src/backend/utils/misc/guc_parameters.dat
index 83af594d4af..796d21b639b 100644
--- a/src/backend/utils/misc/guc_parameters.dat
+++ b/src/backend/utils/misc/guc_parameters.dat
@@ -3385,6 +3385,13 @@
   max => '2000000000',
 },
 
+{ name => 'vacuum_freeze_terminate_blockers_pid', type => 'bool', context => 'PGC_SUSET', group => 'VACUUM_FREEZING',
+  short_desc => 'Terminates client sessions that block VACUUM from advancing its freeze cutoff.',
+  long_desc => 'When enabled, VACUUM terminates regular client sessions whose transaction horizon blocks freezing once the table age is greater than vacuum_failsafe_age divided by autovacuum_freeze_score_weight, or greater than vacuum_failsafe_age when autovacuum_freeze_score_weight is zero.',
+  variable => 'vacuum_freeze_terminate_blockers_pid',
+  boot_val => 'false',
+},
+
 { name => 'vacuum_max_eager_freeze_failure_rate', type => 'real', context => 'PGC_USERSET', group => 'VACUUM_FREEZING',
   short_desc => 'Fraction of pages in a relation vacuum can scan and fail to freeze before disabling eager scanning.',
   long_desc => 'A value of 0.0 disables eager scanning and a value of 1.0 will eagerly scan up to 100 percent of the all-visible pages in the relation. If vacuum successfully freezes these pages, the cap is lower than 100 percent, because the goal is to amortize page freezing across multiple vacuums.',
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index ac38cddaaf9..1ca1d6ce6c5 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -774,6 +774,7 @@
 #vacuum_freeze_table_age = 150000000
 #vacuum_freeze_min_age = 50000000
 #vacuum_failsafe_age = 1600000000
+#vacuum_freeze_terminate_blockers_pid = off
 #vacuum_multixact_freeze_table_age = 150000000
 #vacuum_multixact_freeze_min_age = 5000000
 #vacuum_multixact_failsafe_age = 1600000000
diff --git a/src/include/commands/vacuum.h b/src/include/commands/vacuum.h
index 956d9cea36d..a48f3aea7f8 100644
--- a/src/include/commands/vacuum.h
+++ b/src/include/commands/vacuum.h
@@ -329,6 +329,7 @@ extern PGDLLIMPORT int vacuum_multixact_freeze_min_age;
 extern PGDLLIMPORT int vacuum_multixact_freeze_table_age;
 extern PGDLLIMPORT int vacuum_failsafe_age;
 extern PGDLLIMPORT int vacuum_multixact_failsafe_age;
+extern PGDLLIMPORT bool vacuum_freeze_terminate_blockers_pid;
 extern PGDLLIMPORT bool track_cost_delay_timing;
 extern PGDLLIMPORT bool vacuum_truncate;
 
diff --git a/src/include/storage/procarray.h b/src/include/storage/procarray.h
index ec89c448220..e84218b5d61 100644
--- a/src/include/storage/procarray.h
+++ b/src/include/storage/procarray.h
@@ -73,8 +73,11 @@ extern bool IsBackendPid(int pid);
 extern VirtualTransactionId *GetCurrentVirtualXIDs(TransactionId limitXmin,
 												   bool excludeXmin0, bool allDbs, int excludeVacuum,
 												   int *nvxids);
+extern VirtualTransactionId *GetVirtualXIDsBlockingVacuumFreeze(TransactionId limitXmin,
+																Oid dbOid);
 extern VirtualTransactionId *GetConflictingVirtualXIDs(TransactionId limitXmin, Oid dbOid);
 
+extern bool TerminateBackendWithVirtualXID(VirtualTransactionId vxid, int *pid);
 extern bool SignalRecoveryConflict(PGPROC *proc, pid_t pid, RecoveryConflictReason reason);
 extern bool SignalRecoveryConflictWithVirtualXID(VirtualTransactionId vxid, RecoveryConflictReason reason);
 extern void SignalRecoveryConflictWithDatabase(Oid databaseid, RecoveryConflictReason reason);
diff --git a/src/test/isolation/isolation_schedule b/src/test/isolation/isolation_schedule
index 1578ba191c8..3447d467e77 100644
--- a/src/test/isolation/isolation_schedule
+++ b/src/test/isolation/isolation_schedule
@@ -98,6 +98,7 @@ test: create-trigger
 test: sequence-ddl
 test: async-notify
 test: vacuum-no-cleanup-lock
+test: vacuum-freeze-terminate-blockers
 test: timeouts
 test: vacuum-concurrent-drop
 test: vacuum-conflict
diff --git a/src/test/isolation/specs/vacuum-freeze-terminate-blockers.spec b/src/test/isolation/specs/vacuum-freeze-terminate-blockers.spec
new file mode 100644
index 00000000000..5e7d5814dd1
--- /dev/null
+++ b/src/test/isolation/specs/vacuum-freeze-terminate-blockers.spec
@@ -0,0 +1,85 @@
+# Test vacuum_freeze_terminate_blockers_pid.
+#
+# A transaction with an old XID can hold back VACUUM's freeze cutoff.  Once
+# table age passes the freeze termination age derived from vacuum_failsafe_age
+# and autovacuum_freeze_score_weight, enabling the GUC should make VACUUM
+# terminate the backend that owns that blocker XID.
+
+setup
+{
+	CREATE TABLE vacuum_freeze_blocker_tab (id int)
+		WITH (autovacuum_enabled = off);
+	INSERT INTO vacuum_freeze_blocker_tab VALUES (1);
+	CREATE TABLE vacuum_freeze_blocker_pid (pid int);
+	CREATE TABLE vacuum_freeze_xid_burner (id int);
+}
+
+# Unsafe xid_wraparound tests can consume billions of XIDs.  This default
+# isolation test keeps runtime practical by using a small vacuum_failsafe_age
+# and burning enough XIDs to cross the same age threshold formula.
+# Each setup block runs separately.
+setup { INSERT INTO vacuum_freeze_xid_burner DEFAULT VALUES; }
+setup { INSERT INTO vacuum_freeze_xid_burner DEFAULT VALUES; }
+setup { INSERT INTO vacuum_freeze_xid_burner DEFAULT VALUES; }
+setup { INSERT INTO vacuum_freeze_xid_burner DEFAULT VALUES; }
+setup { INSERT INTO vacuum_freeze_xid_burner DEFAULT VALUES; }
+setup { INSERT INTO vacuum_freeze_xid_burner DEFAULT VALUES; }
+
+teardown
+{
+	DROP TABLE IF EXISTS vacuum_freeze_blocker_tab;
+	DROP TABLE IF EXISTS vacuum_freeze_blocker_pid;
+	DROP TABLE IF EXISTS vacuum_freeze_xid_burner;
+}
+
+session blocker
+step blocker_record_pid
+{
+	INSERT INTO vacuum_freeze_blocker_pid SELECT pg_backend_pid();
+}
+step blocker_begin
+{
+	BEGIN;
+}
+step blocker_assign_xid
+{
+	SELECT txid_current() IS NOT NULL AS xid_assigned;
+}
+
+session vacuumer
+setup
+{
+	SET client_min_messages = error;
+	SET vacuum_failsafe_age = 6;
+	SET vacuum_freeze_min_age = 0;
+	SET vacuum_freeze_terminate_blockers_pid = on;
+}
+step vacuum_run
+{
+	VACUUM vacuum_freeze_blocker_tab;
+}
+step vacuum_check_age_past_threshold
+{
+	SELECT age(relfrozenxid)::float8 >
+		CASE WHEN current_setting('autovacuum_freeze_score_weight')::float8 > 0.0
+			THEN current_setting('vacuum_failsafe_age')::float8 /
+				current_setting('autovacuum_freeze_score_weight')::float8
+			ELSE current_setting('vacuum_failsafe_age')::float8
+		END AS past_termination_age
+	FROM pg_class
+	WHERE oid = 'vacuum_freeze_blocker_tab'::regclass;
+}
+step vacuum_check_blocker_gone
+{
+	SELECT count(*) = 0 AS blocker_gone
+	FROM pg_stat_activity
+	WHERE pid = (SELECT pid FROM vacuum_freeze_blocker_pid);
+}
+
+permutation
+	blocker_record_pid
+	blocker_begin
+	blocker_assign_xid
+	vacuum_check_age_past_threshold
+	vacuum_run
+	vacuum_check_blocker_gone
diff --git a/src/test/isolation/expected/vacuum-freeze-terminate-blockers.out b/src/test/isolation/expected/vacuum-freeze-terminate-blockers.out
new file mode 100644
index 00000000000..2f8d3851639
--- /dev/null
+++ b/src/test/isolation/expected/vacuum-freeze-terminate-blockers.out
@@ -0,0 +1,45 @@
+Parsed test spec with 2 sessions
+
+starting permutation: blocker_record_pid blocker_begin blocker_assign_xid vacuum_check_age_past_threshold vacuum_run vacuum_check_blocker_gone
+step blocker_record_pid: 
+	INSERT INTO vacuum_freeze_blocker_pid SELECT pg_backend_pid();
+
+step blocker_begin: 
+	BEGIN;
+
+step blocker_assign_xid: 
+	SELECT txid_current() IS NOT NULL AS xid_assigned;
+
+xid_assigned
+------------
+t           
+(1 row)
+
+step vacuum_check_age_past_threshold: 
+	SELECT age(relfrozenxid)::float8 >
+		CASE WHEN current_setting('autovacuum_freeze_score_weight')::float8 > 0.0
+			THEN current_setting('vacuum_failsafe_age')::float8 /
+				current_setting('autovacuum_freeze_score_weight')::float8
+			ELSE current_setting('vacuum_failsafe_age')::float8
+		END AS past_termination_age
+	FROM pg_class
+	WHERE oid = 'vacuum_freeze_blocker_tab'::regclass;
+
+past_termination_age
+--------------------
+t                   
+(1 row)
+
+step vacuum_run: 
+	VACUUM vacuum_freeze_blocker_tab;
+
+step vacuum_check_blocker_gone: 
+	SELECT count(*) = 0 AS blocker_gone
+	FROM pg_stat_activity
+	WHERE pid = (SELECT pid FROM vacuum_freeze_blocker_pid);
+
+blocker_gone
+------------
+t           
+(1 row)
+