Skip to content

Commit a850be2

Browse files
author
Amit Kapila
committed
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 <houzj.fnst@fujitsu.com> Reviewed-by: shveta malik <shveta.malik@gmail.com> Reviewed-by: Nisha Moond <nisha.moond412@gmail.com> Reviewed-by: Masahiko Sawada <sawada.mshk@gmail.com> Reviewed-by: Dilip Kumar <dilipbalaut@gmail.com> Reviewed-by: Amit Kapila <amit.kapila16@gmail.com> Discussion: https://postgr.es/m/OS0PR01MB5716BE80DAEB0EE2A6A5D1F5949D2@OS0PR01MB5716.jpnprd01.prod.outlook.com
1 parent 36aed19 commit a850be2

File tree

20 files changed

+779
-218
lines changed

20 files changed

+779
-218
lines changed

‎doc/src/sgml/catalogs.sgml

Lines changed: 25 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -8094,6 +8094,31 @@ SCRAM-SHA-256$<replaceable>&lt;iteration count&gt;</replaceable>:<replaceable>&l
80948094
</para></entry>
80958095
</row>
80968096

8097+
<row>
8098+
<entry role="catalog_table_entry"><para role="column_definition">
8099+
<structfield>submaxretention</structfield> <type>int4</type>
8100+
</para>
8101+
<para>
8102+
The maximum duration (in milliseconds) for which information (e.g., dead
8103+
tuples, commit timestamps, and origins) useful for conflict detection can
8104+
be retained.
8105+
</para></entry>
8106+
</row>
8107+
8108+
<row>
8109+
<entry role="catalog_table_entry"><para role="column_definition">
8110+
<structfield>subretentionactive</structfield> <type>bool</type>
8111+
</para>
8112+
<para>
8113+
The retention status of information (e.g., dead tuples, commit
8114+
timestamps, and origins) useful for conflict detection. True if
8115+
<link linkend="sql-createsubscription-params-with-retain-dead-tuples"><literal>retain_dead_tuples</literal></link>
8116+
is enabled, and the retention duration has not exceeded
8117+
<link linkend="sql-createsubscription-params-with-max-retention-duration"><literal>max_retention_duration</literal></link>,
8118+
when defined.
8119+
</para></entry>
8120+
</row>
8121+
80978122
<row>
80988123
<entry role="catalog_table_entry"><para role="column_definition">
80998124
<structfield>subconninfo</structfield> <type>text</type>

‎doc/src/sgml/ref/alter_subscription.sgml

Lines changed: 3 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -236,8 +236,9 @@ ALTER SUBSCRIPTION <replaceable class="parameter">name</replaceable> RENAME TO <
236236
<link linkend="sql-createsubscription-params-with-run-as-owner"><literal>run_as_owner</literal></link>,
237237
<link linkend="sql-createsubscription-params-with-origin"><literal>origin</literal></link>,
238238
<link linkend="sql-createsubscription-params-with-failover"><literal>failover</literal></link>,
239-
<link linkend="sql-createsubscription-params-with-two-phase"><literal>two_phase</literal></link>, and
240-
<link linkend="sql-createsubscription-params-with-retain-dead-tuples"><literal>retain_dead_tuples</literal></link>.
239+
<link linkend="sql-createsubscription-params-with-two-phase"><literal>two_phase</literal></link>,
240+
<link linkend="sql-createsubscription-params-with-retain-dead-tuples"><literal>retain_dead_tuples</literal></link>, and
241+
<link linkend="sql-createsubscription-params-with-max-retention-duration"><literal>max_retention_duration</literal></link>.
241242
Only a superuser can set <literal>password_required = false</literal>.
242243
</para>
243244

‎doc/src/sgml/ref/create_subscription.sgml

Lines changed: 42 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -448,7 +448,7 @@ CREATE SUBSCRIPTION <replaceable class="parameter">subscription_name</replaceabl
448448
If set to <literal>true</literal>, the detection of
449449
<xref linkend="conflict-update-deleted"/> is enabled, and a physical
450450
replication slot named <quote><literal>pg_conflict_detection</literal></quote>
451-
created on the subscriber to prevent the information for detecting
451+
is created on the subscriber to prevent the information for detecting
452452
conflicts from being removed.
453453
</para>
454454

@@ -521,6 +521,47 @@ CREATE SUBSCRIPTION <replaceable class="parameter">subscription_name</replaceabl
521521
</para>
522522
</listitem>
523523
</varlistentry>
524+
525+
<varlistentry id="sql-createsubscription-params-with-max-retention-duration">
526+
<term><literal>max_retention_duration</literal> (<type>integer</type>)</term>
527+
<listitem>
528+
<para>
529+
Maximum duration in milliseconds for which this subscription's apply worker
530+
is allowed to retain the information useful for conflict detection when
531+
<literal>retain_dead_tuples</literal> is enabled. The default value
532+
is <literal>0</literal>, indicating that the information is retained
533+
until it is no longer needed for detection purposes.
534+
</para>
535+
<para>
536+
The information useful for conflict detection is no longer retained if
537+
all apply workers associated with the subscriptions, where
538+
<literal>retain_dead_tuples</literal> is enabled, confirm that the
539+
retention duration has exceeded the
540+
<literal>max_retention_duration</literal> set within the corresponding
541+
subscription. The retention will not be automatically resumed unless a
542+
new subscription is created with <literal>retain_dead_tuples =
543+
true</literal>, or the user manually re-enables
544+
<literal>retain_dead_tuples</literal>.
545+
</para>
546+
<para>
547+
Note that overall retention will not stop if other subscriptions that
548+
have a value greater than 0 for this parameter have not exceeded it,
549+
or if they set this option to 0.
550+
</para>
551+
<para>
552+
This option is effective only when
553+
<literal>retain_conflict_info</literal> is enabled and the apply
554+
worker associated with the subscription is active.
555+
</para>
556+
<warning>
557+
<para>
558+
Note that setting a non-zero value for this option could lead to
559+
information for conflict detection being removed prematurely,
560+
potentially resulting in incorrect conflict detection.
561+
</para>
562+
</warning>
563+
</listitem>
564+
</varlistentry>
524565
</variablelist></para>
525566

526567
</listitem>

‎src/backend/catalog/pg_subscription.c

Lines changed: 41 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -104,6 +104,8 @@ GetSubscription(Oid subid, bool missing_ok)
104104
sub->runasowner = subform->subrunasowner;
105105
sub->failover = subform->subfailover;
106106
sub->retaindeadtuples = subform->subretaindeadtuples;
107+
sub->maxretention = subform->submaxretention;
108+
sub->retentionactive = subform->subretentionactive;
107109

108110
/* Get conninfo */
109111
datum = SysCacheGetAttrNotNull(SUBSCRIPTIONOID,
@@ -598,3 +600,42 @@ GetSubscriptionRelations(Oid subid, bool not_ready)
598600

599601
return res;
600602
}
603+
604+
/*
605+
* Update the dead tuple retention status for the given subscription.
606+
*/
607+
void
608+
UpdateDeadTupleRetentionStatus(Oid subid, bool active)
609+
{
610+
Relation rel;
611+
bool nulls[Natts_pg_subscription];
612+
bool replaces[Natts_pg_subscription];
613+
Datum values[Natts_pg_subscription];
614+
HeapTuple tup;
615+
616+
/* Look up the subscription in the catalog */
617+
rel = table_open(SubscriptionRelationId, RowExclusiveLock);
618+
tup = SearchSysCacheCopy1(SUBSCRIPTIONOID, ObjectIdGetDatum(subid));
619+
620+
if (!HeapTupleIsValid(tup))
621+
elog(ERROR, "cache lookup failed for subscription %u", subid);
622+
623+
LockSharedObject(SubscriptionRelationId, subid, 0, AccessShareLock);
624+
625+
/* Form a new tuple. */
626+
memset(values, 0, sizeof(values));
627+
memset(nulls, false, sizeof(nulls));
628+
memset(replaces, false, sizeof(replaces));
629+
630+
/* Set the subscription to disabled. */
631+
values[Anum_pg_subscription_subretentionactive - 1] = active;
632+
replaces[Anum_pg_subscription_subretentionactive - 1] = true;
633+
634+
/* Update the catalog */
635+
tup = heap_modify_tuple(tup, RelationGetDescr(rel), values, nulls,
636+
replaces);
637+
CatalogTupleUpdate(rel, &tup->t_self, tup);
638+
heap_freetuple(tup);
639+
640+
table_close(rel, NoLock);
641+
}

‎src/backend/catalog/system_views.sql

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1389,8 +1389,8 @@ REVOKE ALL ON pg_subscription FROM public;
13891389
GRANT SELECT (oid, subdbid, subskiplsn, subname, subowner, subenabled,
13901390
subbinary, substream, subtwophasestate, subdisableonerr,
13911391
subpasswordrequired, subrunasowner, subfailover,
1392-
subretaindeadtuples, subslotname, subsynccommit,
1393-
subpublications, suborigin)
1392+
subretaindeadtuples, submaxretention, subretentionactive,
1393+
subslotname, subsynccommit, subpublications, suborigin)
13941394
ON pg_subscription TO public;
13951395

13961396
CREATE VIEW pg_stat_subscription_stats AS

0 commit comments

Comments
 (0)