While prototyping in-place upgrade and testing some tooling against
pg_upgrade, I found a corner case that sails through pg_upgrade and
only shows breakage afterwards. Here's a not-totally-unrealistic
reproducer going from PG18 to master:
-- On the old cluster
CREATE TABLE orders (id int PRIMARY KEY,
customer text,
total numeric);
CREATE TABLE schema_snapshot AS
SELECT table_name,
array_agg(column_name ORDER BY ordinal_position) AS cols
FROM information_schema.columns
WHERE table_schema = 'public' GROUP BY table_name;
=# \d schema_snapshot
Table "public.schema_snapshot"
Column | Type |
------------+-------------------------------------+...
table_name | information_schema.sql_identifier |
cols | information_schema.sql_identifier[] |
-- pg_upgrade to the new version. Then:
SELECT cols[1] FROM schema_snapshot LIMIT 1;
cols
------
id
(1 row)
SELECT cols FROM schema_snapshot;
ERROR: cache lookup failed for type 14351
pg_dump of the upgraded cluster also fails:
pg_dump: error: Dumping the contents of table "schema_snapshot"
failed: PQgetResult() failed.
pg_dump: detail: Error message from server: ERROR: cache lookup
failed for type 14351
pg_dump: detail: Command was: COPY public.schema_snapshot (table_name,
cols) TO stdout;
To fix, we could add a new entry in data_types_usage_checks() whose base
query returns arrays over elements with unstable OIDs, so the upgrade is
refused up front like the sibling checks, as in the attached.
--
John Naylor
Amazon Web Services
From 06ac3d0ebc1a212ec022640a29beae39136a955c Mon Sep 17 00:00:00 2001
From: John Naylor <[email protected]>
Date: Wed, 22 Jul 2026 09:32:56 -0400
Subject: [PATCH v1] pg_upgrade: check for arrays over system types with
unstable OIDs
Array values embed their element type's OID, and pg_upgrade does not
rewrite user data. The information_schema domains get new OIDs in
every major version, so a stored array over one -- created, for
example, by aggregating an information_schema query into a table --
comes through the upgrade with a dangling element OID. Whole-array
reads and pg_dump of the upgraded cluster then fail with "cache
lookup failed for type N"; if the OID has meanwhile been reassigned,
the values are misread instead.
The data type checks miss this because nothing flags the domain
itself: domain storage is just the base type, so the recursive
expansion that catches arrays over flagged types never reaches it.
Add a check for arrays whose element type lacks a hand-assigned OID
(10000 and up, not stable across major versions), including anything
in information_schema in case it was dropped and reloaded.
Back-patch to all supported branches.
---
src/bin/pg_upgrade/check.c | 25 +++++++++++++++++++++++++
1 file changed, 25 insertions(+)
diff --git a/src/bin/pg_upgrade/check.c b/src/bin/pg_upgrade/check.c
index 2155b01b11f..0a00a339012 100644
--- a/src/bin/pg_upgrade/check.c
+++ b/src/bin/pg_upgrade/check.c
@@ -127,6 +127,31 @@ static DataTypesUsageChecks data_types_usage_checks[] =
.threshold_version = ALL_VERSIONS
},
+ /*
+ * Array values embed their element type's OID (ARR_ELEMTYPE). System
+ * types without hand-assigned OIDs (10000 and up) do not keep the same
+ * OID across major versions, so stored arrays over them would point at
+ * the wrong type after an upgrade. As above, the information_schema test
+ * covers a dropped-and-reloaded information_schema.
+ */
+ {
+ .status = gettext_noop("Checking for arrays over system types with unstable OIDs in user tables"),
+ .report_filename = "tables_using_system_arrays.txt",
+ .base_query =
+ "SELECT t.oid FROM pg_catalog.pg_type t "
+ "JOIN pg_catalog.pg_type e ON t.typelem = e.oid "
+ "LEFT JOIN pg_catalog.pg_namespace n ON e.typnamespace = n.oid "
+ " WHERE t.typtype = 'b' AND "
+ " ((e.oid >= 10000 AND e.oid < 16384) "
+ " OR n.nspname = 'information_schema')",
+ .report_text =
+ gettext_noop("Your installation contains arrays over system types with unstable OIDs\n"
+ "in user tables. Array values embed the element type's OID, so this\n"
+ "cluster cannot currently be upgraded. You can drop the problem\n"
+ "columns, or change them to another data type, and restart the upgrade.\n"),
+ .threshold_version = ALL_VERSIONS
+ },
+
/*
* pg_upgrade only preserves these system values: pg_class.oid pg_type.oid
* pg_enum.oid
--
2.55.0
