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
1) Time: 53,699 ms
Time: 60,146 ms
Time: 60,594 ms
Avg: 58,1 ms
2) Time: 1020,832 ms
Time: 1061,554 ms (00:01,062)
Time: 1148,029 ms (00:01,148)
Avg: 1076 ms
3) Time: 20,972 ms
regression=# \gexec
ALTER TABLE
Time: 12,601 ms
Time: 3,106 ms
sum = 36,67
Time: 22,087 ms
regression=# \gexec
ALTER TABLE
Time: 40,768 ms
Time: 3,154 ms
sum = 66,01
Time: 13,865 ms
regression=# \gexec
ALTER TABLE
Time: 34,619 ms
Time: 3,063 ms
sum = 51,55
Avg: 51,4 ms
4) Time: 25,518 ms
ime: 35,746 ms
Time: 39,232 ms
Avg: 33,4 ms
