arrays over initdb-created types are broken after pg_upgrade - Mailing list pgsql-hackers

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