Re: arrays over initdb-created types are broken after pg_upgrade

Hi John,

> On Jul 22, 2026, at 21:46, John Naylor <johncnaylorls(at)gmail(dot)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