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

On Thu, Jul 30, 2026 at 11:24 AM Chengpeng Yan
<chengpeng_yan(at)outlook(dot)com> wrote:
> > On Jul 22, 2026, at 21:46, John Naylor <johncnaylorls(at)gmail(dot)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