Re: Conflict detection for update_deleted in logical replication - Mailing list pgsql-hackers

On Fri, Aug 29, 2025 at 11:49 AM Zhijie Hou (Fujitsu)
<houzj.fnst@fujitsu.com> wrote:
>
> Here is the new version patch set which also addressed Shveta's comments[1].
>

Thanks for the patches here, I tested the v68-001 patch alone, please
find review comments -

1) If a sub is created with retain_dead_tuples=on but disabled, e.g.
postgres=# create subscription sub3 CONNECTION 'dbname=postgres
host=localhost port=8841' PUBLICATION pub3 WITH
(enabled=false,retain_dead_tuples=on);
WARNING:  deleted rows to detect conflicts would not be removed until
the subscription is enabled
HINT:  Consider setting retain_dead_tuples to false.
NOTICE:  created replication slot "sub3" on publisher
CREATE SUBSCRIPTION

In this case, the conflict slot is not created until the sub is
enabled. Also, if the slot already exists but all other subscriptions
have stopped retaining (slot.xmin=NULL), the dead tuple retention will
not start until the slot is recreated.
To me, the above warning seems misleading in this case.

2) A similar situation can happen with ALTER SUBSCRIPTION. For
example, consider two subscriptions where retention has stopped for
both and slot.xmin is NULL:

 subname | subenabled | subretaindeadtuples | submaxretention |
subretentionactive
---------+------------+---------------------+-----------------+--------------------
 sub2    | t          | t                   |             100 | f
 sub1    | t          | t                   |             100 | f

postgres=# select slot_name,active,xmin from pg_replication_slots ;
       slot_name       | active | xmin
-----------------------+--------+------
 pg_conflict_detection | t      |

If we try to resume retention only for sub1 by toggling retain_dead_tuples:

postgres=# alter subscription sub1 set (retain_dead_tuples =off);
NOTICE:  max_retention_duration is ineffective when retain_dead_tuples
is disabled
ALTER SUBSCRIPTION
postgres=# alter subscription sub1 set (retain_dead_tuples =on);
NOTICE:  deleted rows to detect conflicts would not be removed until
the subscription is enabled
ALTER SUBSCRIPTION

2a) Here also the retention NOTICE is ambiguous as slot.xmin remains
NULL. Though, the above steps don't strictly follow the docs (i.e.
slot should be recreated to resume the retention), still the notice
can be confusing for users.

2b) Also, the retention is not resumed for sub1(expected), but still
the subretentionactive is changed to true.

 subname | subenabled | subretaindeadtuples | submaxretention |
subretentionactive
---------+------------+---------------------+-----------------+--------------------
 sub1    | f          | t                   |             100 | t
 sub2    | t          | t                   |             100 | f

I think we should avoid changing subretentionactive to true in such
cases until the slot is recreated and retention is actually resumed.
Thoughts?

--
Thanks,
Nisha