RE: Conflict detection for update_deleted in logical replication

On Monday, August 18, 2025 2:32 PM Dilip Kumar <dilipbalaut@gmail.com> wrote:
> 
> On Mon, Aug 18, 2025 at 10:36 AM Amit Kapila <amit.kapila16@gmail.com>
> wrote:
> >
> > > ---
> > > Even if an apply worker disables retaining dead tuples due to
> > > max_conflict_retention_duration, it enables again after the server
> > > restarts.
> > >
> >
> > I also find this behaviour questionable because this also means that
> > it is possible that before restart one would deduce that the
> > update_deleted conflict won't be reliably detected for a particular
> > subscription but after restart it could lead to the opposite
> > conclusion. But note that to make it behave similarly we need to store
> > this value persistently in pg_subscription unless you have better
> > ideas for this. Theoretically, there are two places where we can
> > persist this information, one is with pg_subscription, and other in
> > origin. I find it is closer to pg_subscription.
> 
> I think it makes sense to store this in pg_subscription to preserve the decision
> across restart.

Thanks for sharing the opinion!

Regarding this, I'd like to clarify some implementation details for persisting the
retention status in pg_subscription.

Since the logical launcher does not connect to a specific database, it cannot
update the catalog, as this would trigger a FATAL error (e.g.,
CatalogTupleUpdate -> ... -> ScanPgRelation -> FATAL: cannot read pg_class
without having selected a database). Therefore, the apply worker should take
responsibility for updating the catalog.

To achieve that, ideally, the apply worker should update pg_subscription in a
separate transaction, rather than using the transaction started during the
application of changes. This implies that we must wait for the current
transaction to complete before proceeding with the catalog update. So I think we
could an additional phase, RDT_MARK_RETENTION_INACTIVE, to manage the
catalog update once the existing transaction finishes.

If we proceed in this manner, it suggests that the apply worker could set the
shared memory flag first and then catalog flag. So, if the apply worker
encounters an error after setting the shared memory flag but before updating the
catalog, it may lead to issues similar to the one mentioned by Sawada-San,
e.g., the apply worker restart but would retain the dead tuples again because
the status had not persisted. This seems like a rare case, so I'm not sure
whether it's necessary to address it. If we do decide to handle it, we could
update the catalog upon an ERROR using a PG_CATCH block, similar to
DisableSubscriptionAndExit().

Another way could be remove the shared flag and simply depend on the catalog
flag. The launcher will only check the retention in the catalog to decide
whether to invalidate the slot or skip collecting oldest_xid for a apply worker.
However, this approach could retain the dead tuples for more time than the
specified value in max_retention option, due to waiting for a large transaction
to finish before updating the catalog.

What do you think ?

Best Regards,
Hou zj