MIME-Version: 1.0
References: 
 <CA+3i_M8eT6j8_cBHkYykV-SXCxbmAxpVSKptjDVq+MFtpT-Paw@mail.gmail.com>
 <CAJpy0uD3Tre0jEBKZoVsxgdHQGTVTXW5hGmiOxB7TG=eWAABGw@mail.gmail.com>
In-Reply-To: 
 <CAJpy0uD3Tre0jEBKZoVsxgdHQGTVTXW5hGmiOxB7TG=eWAABGw@mail.gmail.com>
From: Masahiko Sawada <sawada.mshk@gmail.com>
Date: Wed, 22 Apr 2026 01:02:20 -0700
Message-ID: 
 <CAD21AoA2=3M+wAntjqTAyFUbXyHiB17Q4VU8NjZWDf+X5hXxcg@mail.gmail.com>
Subject: Re: Fix pg_upgrade to detect invalid logical replication slots on
 PG19
To: shveta malik <shveta.malik@gmail.com>
Cc: Lakshmi N <lakshmin.jhs@gmail.com>,
	"pgsql-hackers@lists.postgresql.org" <pgsql-hackers@lists.postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAD21AoA2%3D3M%2BwAntjqTAyFUbXyHiB17Q4VU8NjZWDf%2BX5hXxcg%40mail.gmail.com>
Precedence: bulk

On Tue, Apr 21, 2026 at 9:27=E2=80=AFPM shveta malik <shveta.malik@gmail.co=
m> wrote:
>
> On Mon, Apr 20, 2026 at 2:28=E2=80=AFPM Lakshmi N <lakshmin.jhs@gmail.com=
> wrote:
> >
> > Hi Hackers,
> >
> > 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.
> >
> > 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.

Thank you for the report.

> >
> > 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.
> >
>
> I agree with the problem here.

I've confirmed the bug and the patch fixes it. The patch looks good to
me, so I'm going to push it tomorrow barring any objections.

Regards,

--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com