Hi John, > On Jul 22, 2026, at 21:46, John Naylor <[email protected]> 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
