arrays over initdb-created types are broken after pg_upgrade

Hi John,

On Jul 22, 2026, at 21:46, John Naylor <johncnaylorls@gmail.com> wrote:

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
<v1-0001-pg_upgrade-check-for-arrays-over-system-types-wit.patch>

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. That said, such
types are likely uncommon, and the consequence would be a conservative
false positive rather than unsafe post-upgrade behavior.

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.

Should we add a TAP test for this as well?

--
Best regards,
Chengpeng Yan

On Thu, Jul 30, 2026 at 11:24 AM Chengpeng Yan
<chengpeng_yan@outlook.com> wrote:

On Jul 22, 2026, at 21:46, John Naylor <johncnaylorls@gmail.com> 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

Hi John,

On Aug 6, 2026, at 18:34, John Naylor <johncnaylorls@gmail.com> wrote:

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')

That seems reasonable for ordinary associated array types. One caveat
is that a custom variable-length type can use `array_in`/`array_out` and
store an `ArrayType` datum without being the element type's associated
array. `e.typarray = t.oid` would not detect its embedded element OID.
That said, such a type is probably very uncommon and may not need
special handling.

Should we add a TAP test for this as well?

I don't see any existing TAP tests for type checks.

Thanks, good to know.

--
Best regards,
Chengpeng Yan

On Sun, Aug 9, 2026 at 1:41 PM Chengpeng Yan <chengpeng_yan@outlook.com> wrote:

On Aug 6, 2026, at 18:34, John Naylor <johncnaylorls@gmail.com> wrote:

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')

That seems reasonable for ordinary associated array types. One caveat
is that a custom variable-length type can use `array_in`/`array_out` and
store an `ArrayType` datum without being the element type's associated
array. `e.typarray = t.oid` would not detect its embedded element OID.
That said, such a type is probably very uncommon and may not need
special handling.

That case doesn't make any sense to me -- can you construct a concrete
example and show what happens? In any case, it's more important here
to avoid false positives here than false negatives, especially given
the lack of field reports. For that reason, I'm also inclined to only
backpatch to v15, since v14 will be on it's last release with next
minor release this can affect.

--
John Naylor
Amazon Web Services

Hi,

On Aug 11, 2026, at 13:35, John Naylor <johncnaylorls@gmail.com> wrote:

That case doesn't make any sense to me -- can you construct a concrete
example and show what happens? In any case, it's more important here
to avoid false positives here than false negatives, especially given
the lack of field reports.

I initially thought that a custom variable-length type could use
`array_in`/`array_out` while setting `ELEMENT` to
`information_schema.sql_identifier`, without being its associated array.
I had overlooked that user-defined types are not allowed to use
`array_subscript_handler` directly.

I agree that "AND e.typarray = t.oid" is a reasonable criterion here.
LGTM.

--
Best regards,
Chengpeng Yan