From a850be2fe653b3b529969946c1cefe0fd9e34a8d Mon Sep 17 00:00:00 2001 From: Amit Kapila Date: Tue, 2 Sep 2025 03:20:18 +0000 Subject: Add max_retention_duration option to subscriptions. This commit introduces a new subscription parameter, max_retention_duration, aimed at mitigating excessive accumulation of dead tuples when retain_dead_tuples is enabled and the apply worker lags behind the publisher. When the time spent advancing a non-removable transaction ID exceeds the max_retention_duration threshold, the apply worker will stop retaining conflict detection information. In such cases, the conflict slot's xmin will be set to InvalidTransactionId, provided that all apply workers associated with the subscription (with retain_dead_tuples enabled) confirm the retention duration has been exceeded. To ensure retention status persists across server restarts, a new column subretentionactive has been added to the pg_subscription catalog. This prevents unnecessary reactivation of retention logic after a restart. The conflict detection slot will not be automatically re-initialized unless a new subscription is created with retain_dead_tuples = true, or the user manually re-enables retain_dead_tuples. A future patch will introduce support for automatic slot re-initialization once at least one apply worker confirms that the retention duration is within the configured max_retention_duration. Author: Zhijie Hou Reviewed-by: shveta malik Reviewed-by: Nisha Moond Reviewed-by: Masahiko Sawada Reviewed-by: Dilip Kumar Reviewed-by: Amit Kapila Discussion: https://postgr.es/m/OS0PR01MB5716BE80DAEB0EE2A6A5D1F5949D2@OS0PR01MB5716.jpnprd01.prod.outlook.com --- doc/src/sgml/catalogs.sgml | 25 ++++++++++++++++++ doc/src/sgml/ref/alter_subscription.sgml | 5 ++-- doc/src/sgml/ref/create_subscription.sgml | 43 ++++++++++++++++++++++++++++++- 3 files changed, 70 insertions(+), 3 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index da8a7882580..e9095bedf21 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -8094,6 +8094,31 @@ SCRAM-SHA-256$<iteration count>:&l + + + submaxretention int4 + + + The maximum duration (in milliseconds) for which information (e.g., dead + tuples, commit timestamps, and origins) useful for conflict detection can + be retained. + + + + + + subretentionactive bool + + + The retention status of information (e.g., dead tuples, commit + timestamps, and origins) useful for conflict detection. True if + retain_dead_tuples + is enabled, and the retention duration has not exceeded + max_retention_duration, + when defined. + + + subconninfo text diff --git a/doc/src/sgml/ref/alter_subscription.sgml b/doc/src/sgml/ref/alter_subscription.sgml index d48cdc76bd3..12f72ba3167 100644 --- a/doc/src/sgml/ref/alter_subscription.sgml +++ b/doc/src/sgml/ref/alter_subscription.sgml @@ -236,8 +236,9 @@ ALTER SUBSCRIPTION name RENAME TO < run_as_owner, origin, failover, - two_phase, and - retain_dead_tuples. + two_phase, + retain_dead_tuples, and + max_retention_duration. Only a superuser can set password_required = false. diff --git a/doc/src/sgml/ref/create_subscription.sgml b/doc/src/sgml/ref/create_subscription.sgml index 247c5bd2604..fc314437311 100644 --- a/doc/src/sgml/ref/create_subscription.sgml +++ b/doc/src/sgml/ref/create_subscription.sgml @@ -448,7 +448,7 @@ CREATE SUBSCRIPTION subscription_nametrue, the detection of is enabled, and a physical replication slot named pg_conflict_detection - created on the subscriber to prevent the information for detecting + is created on the subscriber to prevent the information for detecting conflicts from being removed. @@ -521,6 +521,47 @@ CREATE SUBSCRIPTION subscription_name + + + max_retention_duration (integer) + + + Maximum duration in milliseconds for which this subscription's apply worker + is allowed to retain the information useful for conflict detection when + retain_dead_tuples is enabled. The default value + is 0, indicating that the information is retained + until it is no longer needed for detection purposes. + + + The information useful for conflict detection is no longer retained if + all apply workers associated with the subscriptions, where + retain_dead_tuples is enabled, confirm that the + retention duration has exceeded the + max_retention_duration set within the corresponding + subscription. The retention will not be automatically resumed unless a + new subscription is created with retain_dead_tuples = + true, or the user manually re-enables + retain_dead_tuples. + + + Note that overall retention will not stop if other subscriptions that + have a value greater than 0 for this parameter have not exceeded it, + or if they set this option to 0. + + + This option is effective only when + retain_conflict_info is enabled and the apply + worker associated with the subscription is active. + + + + Note that setting a non-zero value for this option could lead to + information for conflict detection being removed prematurely, + potentially resulting in incorrect conflict detection. + + + + -- cgit v1.2.3