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

On Thu, Mar 21, 2024 at 9:07 AM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>
> I also don't see any obvious problem with such an API. However, this
> is not a good time to invent new APIs. Let's keep the feature simple
> and then we can extend it in the next version after more discussion
> and probably by that time we will get some feedback from the field as
> well.

I couldn't agree more.

> > > 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?
> >
> I think the inconsistency can arise from the fact that on publisher
> one can change the inactive_timeout for the slot corresponding to a
> subscription but the subscriber won't know, so it will still show the
> old value.

Understood.

> If we want we can document this as a limitation and let
> users be aware of it. However, I feel at this stage, let's not even
> expose this from the subscription or maybe we can discuss it once/if
> we are done with other patches. Anyway, if one wants to use this
> feature with a subscription, she can create a slot first on the
> publisher with inactive_timeout value and then associate such a slot
> with a required subscription.

If we are not exposing it via subscription (meaning, we don't consider
v13-0004 and v13-0005 patches), I feel we can have a new SQL API
pg_alter_replication_slot(int inactive_timeout) for now just altering
the inactive_timeout of a given slot.

With this approach, one can do either of the following:
1) Create a slot with SQL API with inactive_timeout set, and use it
for subscriptions or for streaming standbys.
2) Create a slot with SQL API without inactive_timeout set, use it for
subscriptions or for streaming standbys, and set inactive_timeout
later via pg_alter_replication_slot() depending on how the slot is
consumed
3) Create a subscription with create_slot=true, and set
inactive_timeout via pg_alter_replication_slot() depending on how the
slot is consumed.

This approach seems consistent and minimal to start with.

If we agree on this, I'll drop both 0004 and 0005 that are allowing
inactive_timeout to be set via replication commands and via
create/alter subscription respectively, and implement
pg_alter_replication_slot().

FWIW, adding the new SQL API pg_alter_replication_slot() isn't that hard.

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