Re: [BUG] pg_upgrade test fails from older versions.

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