MIME-Version: 1.0
From: Lakshmi N <lakshmin.jhs@gmail.com>
Date: Mon, 20 Apr 2026 01:57:43 -0700
Message-ID: 
 <CA+3i_M8eT6j8_cBHkYykV-SXCxbmAxpVSKptjDVq+MFtpT-Paw@mail.gmail.com>
Subject: Fix pg_upgrade to detect invalid logical replication slots on PG19
To: "pgsql-hackers@lists.postgresql.org" <pgsql-hackers@lists.postgresql.org>
Content-Type: multipart/mixed; boundary="000000000000924c37064fe07eca"
Archived-At: 
 <https://www.postgresql.org/message-id/CA%2B3i_M8eT6j8_cBHkYykV-SXCxbmAxpVSKptjDVq%2BMFtpT-Paw%40mail.gmail.com>
Precedence: bulk

--000000000000924c37064fe07eca
Content-Type: multipart/alternative; boundary="000000000000924c34064fe07ec8"

--000000000000924c34064fe07ec8
Content-Type: text/plain; charset="UTF-8"

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

--000000000000924c34064fe07ec8
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Hi Hackers,</div><div><br></div>The PG19-optimized sl=
ot catchup query uses a CTE that filters on<br>invalidation_reason IS NULL,=
 then cross-joins it with the main slot<br>query.=C2=A0 When ALL logical sl=
ots in a database are invalid, the CTE<br>returns zero rows, and the cross =
join produces an empty result set.<br>This causes pg_upgrade to silently sk=
ip those slots entirely --<br>neither detecting them as invalid (which shou=
ld block the upgrade)<br>nor attempting to migrate them.<br><br>The pre-PG1=
9 query path does not have this problem because it queries<br>pg_replicatio=
n_slots directly without a cross join. This may not impact<div>upgrade to P=
G19 but will change the behavior for PG20 upgrade.<br><br>Fix by changing t=
he cross join to a LEFT JOIN,<br>so that invalid slots still appear in the =
result set with NULL<br>caught_up values.=C2=A0<br><div><br></div><div>Rega=
rds,</div><div>Lakshmi</div></div></div>

--000000000000924c34064fe07ec8--

--000000000000924c37064fe07eca
Content-Type: application/octet-stream;
	name="0001-Fix-pg_upgrade-to-detect-invalid-logical-replication.patch"
Content-Disposition: attachment;
	filename="0001-Fix-pg_upgrade-to-detect-invalid-logical-replication.patch"
Content-Transfer-Encoding: base64
Content-ID: <f_mo6yhdni0>
X-Attachment-Id: f_mo6yhdni0

RnJvbSA2YTgwOTU4MjA0MzY0Yzk0YjUxMWU3NzI4ZjgzMjgxMDEzY2RiZjA2IE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBMYWtzaG1pIE4gPGxha3NobWluLmpoc0BnbWFpbC5jb20+CkRh
dGU6IE1vbiwgMjAgQXByIDIwMjYgMDE6NDc6NTAgLTA3MDAKU3ViamVjdDogW1BBVENIXSBGaXgg
cGdfdXBncmFkZSB0byBkZXRlY3QgaW52YWxpZCBsb2dpY2FsIHJlcGxpY2F0aW9uIHNsb3RzIG9u
CiAgUEcxOQoKLS0tCiBzcmMvYmluL3BnX3VwZ3JhZGUvaW5mby5jIHwgMyArKy0KIDEgZmlsZSBj
aGFuZ2VkLCAyIGluc2VydGlvbnMoKyksIDEgZGVsZXRpb24oLSkKCmRpZmYgLS1naXQgYS9zcmMv
YmluL3BnX3VwZ3JhZGUvaW5mby5jIGIvc3JjL2Jpbi9wZ191cGdyYWRlL2luZm8uYwppbmRleCA4
YzU2NzliODA5Ny4uMzdmZmY5Mzg5MmYgMTAwNjQ0Ci0tLSBhL3NyYy9iaW4vcGdfdXBncmFkZS9p
bmZvLmMKKysrIGIvc3JjL2Jpbi9wZ191cGdyYWRlL2luZm8uYwpAQCAtNzQzLDcgKzc0Myw4IEBA
IGdldF9vbGRfY2x1c3Rlcl9sb2dpY2FsX3Nsb3RfaW5mb3NfcXVlcnkoQ2x1c3RlckluZm8gKmNs
dXN0ZXIpCiAJCQkiICBjb25maXJtZWRfZmx1c2hfbHNuID4gbGFzdF9wZW5kaW5nX3dhbCAiCiAJ
CQkiRU5EIGFzIGNhdWdodF91cCwgIgogCQkJImludmFsaWRhdGlvbl9yZWFzb24gSVMgTk9UIE5V
TEwgYXMgaW52YWxpZCAiCi0JCQkiRlJPTSBwZ19jYXRhbG9nLnBnX3JlcGxpY2F0aW9uX3Nsb3Rz
LCBjaGVja19jYXVnaHRfdXAgIgorCQkJIkZST00gcGdfY2F0YWxvZy5wZ19yZXBsaWNhdGlvbl9z
bG90cyAiCisJCQkiTEVGVCBKT0lOIGNoZWNrX2NhdWdodF91cCBPTiB0cnVlICIKIAkJCSJXSEVS
RSBzbG90X3R5cGUgPSAnbG9naWNhbCcgQU5EICIKIAkJCSJkYXRhYmFzZSA9IGN1cnJlbnRfZGF0
YWJhc2UoKSBBTkQgIgogCQkJInRlbXBvcmFyeSBJUyBGQUxTRSAiOwotLSAKMi40My4wCgo=
--000000000000924c37064fe07eca--