The on disk format for the type information_schema.sql_identifier has changed in PostgreSQL version 12. Tables with this data type will be unable to upgrade to version 12 and above until those tables are altered to use a safe, compatible type.
The PostgreSQL Global Development Group has identified a rare data corruption bug in version 12.0. If you have tables with columns of type information_schema.sql_identifier and use pg_upgrade to upgrade the instance to version 12.0, those tables will be unreadable in the new cluster. This type is not used often, but can arise if, for example, a user performed a query like
CREATE TABLE ... AS SELECT * FROM information_schema.tables;The problem will manifest when attempting to read one of those tables (which normally first happens when running analyze_new_cluster after upgrade), at which point the server will crash with an error such as:
vacuumdb: error: vacuuming of table "ABC" in database "XYZ"
failed: ERROR: compressed data is corrupted
In version 12.1 and up, pg_upgrade will detect when the old instance has columns of type information_schema.sql_identifier, and stop the upgrade from proceeding. In the meantime, you can find such columns with the following query:
WITH RECURSIVE oids AS (
/* the sql_identifier type itself */
SELECT 'information_schema.sql_identifier'::pg_catalog.regtype AS oid
UNION ALL
SELECT * FROM (
/* domains on the type */
WITH x AS (SELECT oid FROM oids)
SELECT t.oid FROM pg_catalog.pg_type t, x WHERE typbasetype = x.oid AND typtype = 'd'
UNION
/* composite types containing the type */
SELECT t.oid FROM pg_catalog.pg_type t, pg_catalog.pg_class c, pg_catalog.pg_attribute a, x
WHERE t.typtype = 'c' AND
t.oid = c.reltype AND
c.oid = a.attrelid AND
NOT a.attisdropped AND
a.atttypid = x.oid
) foo
)
SELECT n.nspname, c.relname, a.attname
FROM pg_catalog.pg_class c,
pg_catalog.pg_namespace n,
pg_catalog.pg_attribute a
WHERE c.oid = a.attrelid AND
NOT a.attisdropped AND
a.atttypid IN (SELECT oid FROM oids) AND
c.relkind IN ('r', 'm', 'i') AND
c.relnamespace = n.oid AND
/* exclude possible orphaned temp tables */
n.nspname !~ '^pg_temp_' AND
n.nspname !~ '^pg_toast_temp_' AND
n.nspname NOT IN ('pg_catalog', 'information_schema'));However the columns are identified, the solution is the same: Before starting the upgrade, you must change the data type to text, for example:
ALTER TABLE mytable
ALTER COLUMN col1 SET DATA TYPE text,
ALTER COLUMN col2 SET DATA TYPE text;Note you can specify multiple columns so that the command only needs to be run once per table. Once all affected columns have been changed to the text type, pg_upgrade can proceed.