postgres.git / summary / log / commit / refs

commit    f4f2f2b84a0bbf9edbfc4a8684040a941cd6d085
Author:   Michael Paquier <michael@paquier.xyz>
Date:     Fri Dec 23 02:36:57 2022 +0000
First released in: 16.0

    Update upgrade_adapt.sql to handle tables using aclitem as data type
    
    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


src/bin/pg_upgrade/upgrade_adapt.sql | 30 +++++++++++++++++++++++++++++- 1 file changed, 29 insertions(+), 1 deletion(-) diff --git a/src/bin/pg_upgrade/upgrade_adapt.sql b/src/bin/pg_upgrade/upgrade_adapt.sql index d0580e42823..54920f54f51 100644 --- 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 [parent: 3022cb143396]