From: | "Anton A(dot) Melnikov" <aamelnikov(at)inbox(dot)ru> |
---|---|
To: | Justin Pryzby <pryzby(at)telsasoft(dot)com>, Michael Paquier <michael(at)paquier(dot)xyz> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-hackers(at)lists(dot)postgresql(dot)org |
Subject: | Re: [BUG] pg_upgrade test fails from older versions. |
Date: | 2022-12-23 09:17:18 |
Message-ID: | 36d7cbf4-f6f8-e32d-b9c2-314b0f4c530c@inbox.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
Attachment | Content-Type | Size |
---|---|---|
timing.txt | text/plain | 537 bytes |
From | Date | Subject | |
---|---|---|---|
Next Message | houzj.fnst@fujitsu.com | 2022-12-23 09:20:01 | RE: Perform streaming logical transactions by background workers and parallel apply |
Previous Message | Amit Kapila | 2022-12-23 09:15:28 | Re: Timeout when changes are filtered out by the core during logical replication |