Re: Introduce XID age and inactive timeout based replication slot invalidation

On Wed, Mar 20, 2024 at 7:08 PM Bertrand Drouvot
<bertranddrouvot(dot)pg(at)gmail(dot)com> wrote:
>
> Regarding v12-0004: "Allow setting inactive_timeout in the replication command",
> shouldn't we also add an new SQL API say: pg_alter_replication_slot() that would
> allow to change the timeout property?
>
> That would allow users to alter this property without the need to make a
> replication connection.

+1 to add a new SQL function pg_alter_replication_slot(). It helps
first create the slots and then later decide the appropriate
inactive_timeout. It might grow into altering other slot parameters
such as failover (I'm not sure if altering failover property on the
primary after a while makes it the right candidate for syncing on the
standby). Perhaps, we can add it for altering just inactive_timeout
for now and be done with it.

FWIW, ALTER_REPLICATION_SLOT was added keeping in mind just the
failover property for logical slots, that's why it emits an error
"cannot use ALTER_REPLICATION_SLOT with a physical replication slot"

> But the issue is that it would make it inconsistent with the new inactivetimeout
> in the subscription that is added in "v12-0005".

Can you please elaborate what the inconsistency it causes with inactivetimeout?

> But do we need to display
> subinactivetimeout in pg_subscription (and even allow it at subscription creation
> / alter) after all? (I've the feeling there is less such a need as compare to
> subfailover, subtwophasestate for example).

Maybe we don't need to. One can always trace down to the replication
slot associated with the subscription on the publisher, and get to
know what the slot's inactive_timeout setting is. However, it looks to
me that it avoids one going to the publisher to know the
inactive_timeout value for a subscription. Moreover, we are allowing
the inactive_timeout to be set via CREATE/ALTER SUBSCRIPTION command,
I believe there's nothing wrong if it's also part of the
pg_subscription catalog.

--
Bharath Rupireddy
PostgreSQL Contributors Team
RDS Open Source Databases
Amazon Web Services: https://aws.amazon.com