Update upgrade_adapt.sql to handle tables using aclitem as data type

michaelpq · michaelpq · commit f4f2f2b84a0b · 2022-12-23T11:36:57.000+09:00
The regression test suite includes a table called "tab_core_types" that has one attribute based on the type "aclitem". Keeping this attribute as-is causes hard failures when running pg_upgrade with an origin on ~15. This commit updates upgrade_adapt.sql to automatically detect the tables with such attributes and switch them to text so as pg_upgrade is able to go through its run. This does not provide the same detection coverage as pg_upgrade, where we are able to find out aclitems used in arrays, domains or even composite types, but this is (I guess) enough for most things like an instance that had installcheck run on before the upgrade with a dump generated from it. Note that the buildfarm code has taken the simplest approach of just dropping "tab_core_types", so what we have here is more modular. Author: Anton A. Melnikov Discussion: https://postgr.es/m/49f389ba-95ce-8a9b-09ae-f60650c0e7c7@inbox.ru
diff --git a/src/bin/pg_upgrade/upgrade_adapt.sql b/src/bin/pg_upgrade/upgrade_adapt.sql
@@ -19,7 +19,8 @@ SELECT
   ver <= 906 AS oldpgversion_le96,
   ver <= 1000 AS oldpgversion_le10,
   ver <= 1100 AS oldpgversion_le11,
-  ver <= 1300 AS oldpgversion_le13
+  ver <= 1300 AS oldpgversion_le13,
+  ver <= 1500 AS oldpgversion_le15
   FROM (SELECT current_setting('server_version_num')::int / 100 AS ver) AS v;
 \gset
 
@@ -89,3 +90,30 @@ DROP OPERATOR public.#%# (pg_catalog.int8, NONE);
 DROP OPERATOR public.!=- (pg_catalog.int8, NONE);
 DROP OPERATOR public.#@%# (pg_catalog.int8, NONE);
 \endif
+
+-- Objects last appearing in 15.
+-- The internal format of "aclitem" has changed in 16, so replace it with
+-- text type in tables.
+\if :oldpgversion_le15
+DO $$
+  DECLARE
+    rec text;
+	col text;
+  BEGIN
+  FOR rec in
+    SELECT oid::regclass::text
+    FROM pg_class
+    WHERE relname !~ '^pg_'
+      AND relkind IN ('r')
+    ORDER BY 1
+  LOOP
+    FOR col in SELECT attname FROM pg_attribute
+      WHERE attrelid::regclass::text = rec
+      AND atttypid = 'aclitem'::regtype
+    LOOP
+      EXECUTE 'ALTER TABLE ' || quote_ident(rec) || ' ALTER COLUMN ' ||
+        quote_ident(col) || ' SET DATA TYPE text';
+    END LOOP;
+  END LOOP;
+  END; $$;
+\endif
