public inbox for [email protected]
help / color / mirror / Atom feedFrom: Lakshmi N <[email protected]>
To: [email protected] <[email protected]>
Subject: Fix pg_upgrade to detect invalid logical replication slots on PG19
Date: Mon, 20 Apr 2026 01:57:43 -0700
Message-ID: <CA+3i_M8eT6j8_cBHkYykV-SXCxbmAxpVSKptjDVq+MFtpT-Paw@mail.gmail.com> (raw)
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
Attachments:
[application/octet-stream] 0001-Fix-pg_upgrade-to-detect-invalid-logical-replication.patch (965B, 3-0001-Fix-pg_upgrade-to-detect-invalid-logical-replication.patch)
download | inline diff:
From 6a80958204364c94b511e7728f83281013cdbf06 Mon Sep 17 00:00:00 2001
From: Lakshmi N <[email protected]>
Date: Mon, 20 Apr 2026 01:47:50 -0700
Subject: [PATCH] Fix pg_upgrade to detect invalid logical replication slots on
PG19
---
src/bin/pg_upgrade/info.c | 3 ++-
1 file changed, 2 insertions(+), 1 deletion(-)
diff --git a/src/bin/pg_upgrade/info.c b/src/bin/pg_upgrade/info.c
index 8c5679b8097..37fff93892f 100644
--- a/src/bin/pg_upgrade/info.c
+++ b/src/bin/pg_upgrade/info.c
@@ -743,7 +743,8 @@ get_old_cluster_logical_slot_infos_query(ClusterInfo *cluster)
" confirmed_flush_lsn > last_pending_wal "
"END as caught_up, "
"invalidation_reason IS NOT NULL as invalid "
- "FROM pg_catalog.pg_replication_slots, check_caught_up "
+ "FROM pg_catalog.pg_replication_slots "
+ "LEFT JOIN check_caught_up ON true "
"WHERE slot_type = 'logical' AND "
"database = current_database() AND "
"temporary IS FALSE ";
--
2.43.0
view thread (5+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: Fix pg_upgrade to detect invalid logical replication slots on PG19
In-Reply-To: <CA+3i_M8eT6j8_cBHkYykV-SXCxbmAxpVSKptjDVq+MFtpT-Paw@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox