Re: pgsql: Track last_inactive_time in pg_replication_slots.

On Tue, Mar 26, 2024 at 9:10 PM Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> wrote:
>
> On 2024-Mar-26, Nathan Bossart wrote:
>
> > FWIW I'd really prefer to have something like max_slot_xid_age for this. A
> > time-based parameter would likely help with most cases, but transaction ID
> > usage will vary widely from server to server, so it'd be nice to have
> > something to protect against wraparound more directly.
>
> Yeah, I tend to agree that an XID-based limit makes more sense than a
> time-based one.
>

So, do we want the time-based parameter or just max_slot_xid_age
considering both will be GUC's? Yes, the xid_age based parameter
sounds to be directly helpful for transaction ID wraparound or dead
row cleanup, OTOH having a lot of inactive slots (which is possible in
use cases where a tool creates a lot of slots and forgets to remove
them, or the tool exits without cleaning up slots (say due to server
shutdown)) also prohibit removing dead space which is not nice either?

The one example that comes to mind is the pg_createsubscriber
(committed for PG17) which creates one slot per database to convert
standby to subscriber, now say it exits due to power shutdown then
there could be a lot of dangling slots on the primary server. Also,
say there is some bug in such a tool that doesn't allow proper cleanup
of slots, the same problem can happen; yeah, this would be a problem
of the tool but I think there is no harm in giving a way to avoid
problems at the server end due to such slots.

--
With Regards,
Amit Kapila.