Re: [BUG] pg_upgrade test fails from older versions. - Mailing list pgsql-hackers

Sorry, didn't get to see the last letter!

On 23.12.2022 11:51, Michael Paquier wrote:
> 
> FWIW, I find the use of a FOR loop with a DO block much cleaner to
> follow in this context, so something like the attached would be able
> to group the two queries and address your point on O(N^2).  Do you
> like that?
> --
> Michael

The query:

  DO $$
    DECLARE
      rec record;
    BEGIN
    FOR rec in
     SELECT oid::regclass::text as rel, attname as col
      FROM pg_class c, pg_attribute a
      WHERE c.relname !~ '^pg_'
       AND c.relkind IN ('r')
        AND a.attrelid = c.oid
        AND a.atttypid = 'aclitem'::regtype
      ORDER BY 1
    LOOP
     EXECUTE 'ALTER TABLE ' || quote_ident(rec.rel) || ' ALTER COLUMN ' ||
        quote_ident(rec.col) || ' SET DATA TYPE text';
    END LOOP;
    END; $$;

gives the average time of 36 ms at the same conditions.


With the best wishes!

-- 
Anton A. Melnikov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company