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

Hello!

On 23.12.2022 06:27, Justin Pryzby wrote:
> 
> This would do a single seqscan:
> SELECT format('ALTER TABLE %I ALTER COLUMN %I TYPE TEXT', attrelid::regclass, attname) FROM pg_attribute WHERE atttypid='aclitem'::regtype; -- AND ...
> \gexec
> 

Touched a bit on how long it takes to execute different types of queries on my PC.
At each measurement, the server restarted with a freshly copied regression database.
1)
DO $$
DECLARE
     change_aclitem_type TEXT;
BEGIN
     FOR change_aclitem_type IN
         SELECT 'ALTER TABLE ' || table_schema || '.' ||
         table_name || ' ALTER COLUMN ' ||
		column_name || ' SET DATA TYPE text;'
         AS change_aclitem_type
         FROM information_schema.columns
         WHERE data_type = 'aclitem' and table_schema != 'pg_catalog'
     LOOP
         EXECUTE change_aclitem_type;
     END LOOP;
END;
$$;

2)
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; $$;

3)
SELECT format('ALTER TABLE %I ALTER COLUMN %I TYPE TEXT', attrelid::regclass, attname) FROM pg_attribute WHERE atttypid='aclitem'::regtype;
\gexec

4) The same as 3) but in the DO block
DO $$
DECLARE
     change_aclitem_type TEXT;
BEGIN
     FOR change_aclitem_type IN
         SELECT 'ALTER TABLE ' || attrelid::regclass || ' ALTER COLUMN ' ||
		attname || ' TYPE TEXT;'
         AS change_aclitem_type
         FROM pg_attribute
         WHERE atttypid = 'aclitem'::regtype
     LOOP
         EXECUTE change_aclitem_type;
     END LOOP;
END;
$$;

Average execution time for three times:
_____________________________________
|N of query:   |  1 |   2  | 3  |  4 |
|____________________________________
|Avg time, ms: | 58 | 1076 | 51 | 33 |
|____________________________________

Raw results in timing.txt

Best wishes,

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