Re: Resetting spilled txn statistics in pg_stat_replication

On Tue, 7 Jul 2020 at 17:50, Magnus Hagander <magnus(at)hagander(dot)net> wrote:
>
>
>
> On Tue, Jul 7, 2020 at 5:10 AM Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>>
>> On Tue, Jul 7, 2020 at 7:07 AM Masahiko Sawada
>> <masahiko(dot)sawada(at)2ndquadrant(dot)com> wrote:
>> >
>> > On Mon, 6 Jul 2020 at 20:45, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
>> > >
>> > > > Second, as long as the unique identifier is the slot name there is no
>> > > > convenient way to distinguish between the same name old and new
>> > > > replication slots, so the backend process or wal sender process sends
>> > > > a message to the stats collector to drop the replication slot at
>> > > > ReplicationSlotDropPtr(). This strategy differs from what we do for
>> > > > table, index, and function statistics. It might not be a problem but
>> > > > I’m thinking a better way.
>> > > >
>> > >
>> > > Can we rely on message ordering in the transmission mechanism (UDP)
>> > > for stats? The wiki suggests [1] we can't. If so, then this might
>> > > not work.
>> >
>> > Yeah, I'm also concerned about this. Another idea would be to have
>> > another unique identifier to distinguish old and new replication slots
>> > with the same name. For example, creation timestamp. And then we
>> > reclaim the stats of unused slots later like table and function
>> > statistics.
>> >
>>
>> So, we need to have 'creation timestamp' as persistent data for slots
>> to achieve this? I am not sure of adding creation_time as a parameter
>> to identify for this case because users can change timings on systems
>> so it might not be a bullet-proof method but I agree that it can work
>> in general.
>
>
> If we need them to be persistent across time like that, perhaps we simply need to assign oids to replication slots? That might simplify this problem quite a bit?

Yeah, I guess assigning oids to replication slots in the same way of
oids in system catalogs might not work because physical replication
slot can be created even during recovery. But using a
monotonically-increasing integer as id seems better and straight
forward. This id is not necessarily displayed in pg_repliation_slots
view because the user already can use slot name as a unique
identifier.

Regards,

--
Masahiko Sawada http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services