On Thu, Jul 30, 2026 at 11:24 AM Chengpeng Yan
<[email protected]> wrote:
> > On Jul 22, 2026, at 21:46, John Naylor <[email protected]> wrote:
> Thanks for working on this. I agree that `pg_upgrade` should reject
> stored arrays with unstable element type OIDs, since it does not rewrite
> array Datums.
>
> I think the v1 query is a little too broad, though. `pg_type.typelem`
> is also used by fixed-length raw types such as `point`, whose Datums
> have no `ArrayType` header. A user-defined type can likewise set
> `ELEMENT` to `information_schema.sql_identifier`, so v1 could reject it
> even though its Datum contains no element type OID.
Thanks for taking a look! Hmm, yeah.
> As a minimal improvement, perhaps adding `t.typlen = -1` to the `WHERE`
> clause could at least exclude fixed-length raw types and reduce false
> positives. This might not be an exact test either, and I am not sure
> whether there is a better catalog-only test that works across all
> supported source versions.
I think it would work to restrict to true array types by adding "AND
e.typarray = t.oid" to the WHERE clause:
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.typarray = t.oid
AND ((e.oid >= 10000 AND e.oid < 16384)
OR n.nspname = 'information_schema')
> Should we add a TAP test for this as well?
I don't see any existing TAP tests for type checks.
--
John Naylor
Amazon Web Services
From 899669cb93b3f123b2d48264021edb65eca658f0 Mon Sep 17 00:00:00 2001
From: John Naylor <[email protected]>
Date: Wed, 22 Jul 2026 09:32:56 -0400
Subject: [PATCH v2] 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 OIDs of information_schema domains can vary
between major versions, so a stored array over one (e.g. by aggregating
an information_schema query into a table) comes through the upgrade
with at best a dangling element OID. Whole-array reads and pg_dump
of the upgraded cluster then fail with "cache lookup failed for type
N". Worst case, the OID has been reassigned, leading to values being
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 in
data_type_check_query() never reaches the array type.
Add a check for arrays whose element type OID was assigned by genbki.pl
or initdb, including anything in information_schema in case it was
dropped and reloaded.
Backpatch to all supported versions.
Reviewed-by: Chengpeng Yan <[email protected]>
Discussion: https://postgr.es/m/CANWCAZbusXHWWSq_9NpxtOGvR=m5mh4uj+wo0f1_rtc0ume...@mail.gmail.com
Backpatch-through: 14
---
src/bin/pg_upgrade/check.c | 34 ++++++++++++++++++++++++++++++++++
1 file changed, 34 insertions(+)
diff --git a/src/bin/pg_upgrade/check.c b/src/bin/pg_upgrade/check.c
index 1fedf63c6dd..7e56e979592 100644
--- a/src/bin/pg_upgrade/check.c
+++ b/src/bin/pg_upgrade/check.c
@@ -127,6 +127,40 @@ 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 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. Since typelem also appears on
+ * fixed-length types such as point, whose values embed no type OID,
+ * require the element's typarray back-link to restrict the match to true
+ * array types.
+ *
+ * The query below hardcodes FirstGenbkiObjectId as 10000 and
+ * FirstNormalObjectId as 16384 rather than interpolating those C
+ * #defines into the query because, if either #define is ever changed,
+ * the cutoffs we want to use are the values used by pre-version 14
+ * servers, not those of some future version.
+ */
+ {
+ .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.typarray = t.oid 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
