Horton
Menu

Thread

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. pg_upgrade: Fix detection of invalid logical replication slots.

  1. Fix pg_upgrade to detect invalid logical replication slots on PG19

    Lakshmi N <lakshmin.jhs@gmail.com> — 2026-04-20T08:57:43Z

    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.

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.

Regards,
Lakshmi

  2. Re: Fix pg_upgrade to detect invalid logical replication slots on PG19

    shveta malik <shveta.malik@gmail.com> — 2026-04-22T04:27:10Z

    On Mon, Apr 20, 2026 at 2:28 PM 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.
>
> 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.

Another way to solve this would be using a scalar subquery(see [1]),
but that would reduce readability. Thus, I prefer a LEFT OUTER JOIN on
TRUE here. There should also be no performance impact, since the
right-hand side query will always return at most one row due to the
LIMIT 1 clause. So IMO, the proposed solution is good. Copying
Sawada-san, as he was the author of the original patch.


[1]:
SELECT slot_name, plugin, two_phase, failover,
CASE
  WHEN invalidation_reason IS NOT NULL THEN FALSE
  ELSE (
    (SELECT last_pending_wal FROM check_caught_up) IS NULL
    OR confirmed_flush_lsn > (SELECT last_pending_wal FROM check_caught_up)
  )
END as caught_up,
invalidation_reason IS NOT NULL as invalid
FROM pg_catalog.pg_replication_slots
WHERE slot_type = 'logical'
  AND database = current_database()
  AND temporary IS FALSE;

thanks
Shveta

  3. Re: Fix pg_upgrade to detect invalid logical replication slots on PG19

    Masahiko Sawada <sawada.mshk@gmail.com> — 2026-04-22T08:02:20Z

    On Tue, Apr 21, 2026 at 9:27 PM shveta malik <shveta.malik@gmail.com> wrote:
>
> On Mon, Apr 20, 2026 at 2:28 PM 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

  4. Re: Fix pg_upgrade to detect invalid logical replication slots on PG19

    Chao Li <li.evan.chao@gmail.com> — 2026-04-22T09:13:01Z

    
> On Apr 20, 2026, at 16:57, 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.
> 
> 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. 
> 
> 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 … 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/

  5. Re: Fix pg_upgrade to detect invalid logical replication slots on PG19

    Masahiko Sawada <sawada.mshk@gmail.com> — 2026-04-22T17:01:01Z

    On Wed, Apr 22, 2026 at 1:02 AM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
>
> On Tue, Apr 21, 2026 at 9:27 PM shveta malik <shveta.malik@gmail.com> wrote:
> >
> > On Mon, Apr 20, 2026 at 2:28 PM 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.

Pushed.

Regards,

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