Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3864.400.21\))
Subject: Re: Fix pg_upgrade to detect invalid logical replication slots on
 PG19
From: Chao Li <li.evan.chao@gmail.com>
In-Reply-To: 
 <CA+3i_M8eT6j8_cBHkYykV-SXCxbmAxpVSKptjDVq+MFtpT-Paw@mail.gmail.com>
Date: Wed, 22 Apr 2026 17:13:01 +0800
Cc: "pgsql-hackers@lists.postgresql.org" <pgsql-hackers@lists.postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <91589FA5-0AB6-4A71-BD6A-F206866FDA93@gmail.com>
References: 
 <CA+3i_M8eT6j8_cBHkYykV-SXCxbmAxpVSKptjDVq+MFtpT-Paw@mail.gmail.com>
To: Lakshmi N <lakshmin.jhs@gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/91589FA5-0AB6-4A71-BD6A-F206866FDA93%40gmail.com>
Precedence: bulk


> On Apr 20, 2026, at 16:57, Lakshmi N <lakshmin.jhs@gmail.com> wrote:
>=20
> Hi Hackers,
>=20
> The PG19-optimized slot catchup query uses a CTE that filters on
> invalidation_reason IS NULL, then cross-joins it with the main slot
> query.  When ALL logical slots in a database are invalid, the CTE
> returns zero rows, and the cross join produces an empty result set.
> This causes pg_upgrade to silently skip those slots entirely --
> neither detecting them as invalid (which should block the upgrade)
> nor attempting to migrate them.
>=20
> The pre-PG19 query path does not have this problem because it queries
> pg_replication_slots directly without a cross join. This may not =
impact
> upgrade to PG19 but will change the behavior for PG20 upgrade.
>=20
> Fix by changing the cross join to a LEFT JOIN,
> so that invalid slots still appear in the result set with NULL
> caught_up values.=20
>=20
> Regards,
> Lakshmi
> <0001-Fix-pg_upgrade-to-detect-invalid-logical-replication.patch>

This changes the inner join to a left join, which preserves the rows =
from pg_replication_slots. As check_caught_up is intentionally a =
singleton CTE because ORDER BY =E2=80=A6 LIMIT 1, so there is no row =
multiplication risk.

So, the patch looks good to me.

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/