Re: physical slot xmin dependency on logical slot?

On Tue, 19 Nov 2019 at 05:37, Jeremy Finzel <finzelj(at)gmail(dot)com> wrote:

> We had a scenario today that was new to us. We had a logical replication
> slot that was severely far behind. Before dropping this logical slot, we
> made a physical point-in-time-recovery snapshot of the system with this
> logical slot.
>
> This logical slot was causing severe catalog bloat. We proceeded to drop
> the logical slot which was over 12000 WAL segments behind. The physical
> slot was only a few 100 segments behind and still in place.
>
> But now proceeding to VAC FULL the catalog tables did not recover any
> bloat beyond the now-dropped logical slot. Eventually to our surprise, we
> found that dropping the physical slot allowed us to recover the bloat.
>
> We saw in forensics after the fact that xmin of the physical slot equaled
> the catalog_xmin of the logical slot. Is there some dependency here where
> physical slots made of a system retain all transactions of logical slots it
> contains as well? If so, could someone help us understand this, and is
> there documentation around this? Is this by design?
>

I expect that you created the replica in a manner that preserved the
logical replication slot on it. You also had hot_standby_feedback enabled.

PostgreSQL standbys send the global xmin and (in Pg10+) catalog_xmin to the
upstream when hot_standby_feedback is enabled. If there's a slot holding
the catalog_xmin on the replica down, that'll be passed on via
hot_standby_feedback to the upstream. On Pg 9.6 or older, or if the replica
isn't using a physical replication slot, the catalog_xmin is treated as a
regular xmin since there's nowhere in PGPROC or PGXACT to track the
separate catalog_xmin. If the standby uses a physical slot, then on pg10+
the catalog_xmin sent by the replica is stored as the catalog_xmin on the
physical slot instead.

Either way, if you have hot_standby_feedback enabled on a standby, that
feedback includes the requirements of any replication slots on the standby.

--
Craig Ringer http://www.2ndQuadrant.com/
2ndQuadrant - PostgreSQL Solutions for the Enterprise