Subject: Fix pg_upgrade to detect invalid logical replication slots on PG19

From: Lakshmi N <[email-masked]>
Date: Mon, 20 Apr 2026 01:57:43 -0700

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

---

From: shveta malik <[email-masked]>
Date: Wed, 22 Apr 2026 09:57:10 +0530

> so that invalid slots still appear in the result set with NULL

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

---

From: Masahiko Sawada <[email-masked]>
Date: Wed, 22 Apr 2026 01:02:20 -0700

> > upgrade to PG19 but will change the behavior for PG20 upgrade.

Thank you for the report.

> 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

---

From: Chao Li <[email-masked]>
Date: Wed, 22 Apr 2026 17:13:01 +0800

> On Apr 20, 2026, at 16:57, Lakshmi N <[email-masked]> wrote:
> 
> Hi Hackers,
> 
> <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/

---

From: Masahiko Sawada <[email-masked]>
Date: Wed, 22 Apr 2026 10:01:01 -0700

> me, so I'm going to push it tomorrow barring any objections.

Pushed.

Regards,