Horton

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

Justin Pryzby <pryzby@telsasoft.com>

View thread View on postgr.es
From: Justin Pryzby <pryzby@telsasoft.com>
To: Michael Paquier <michael@paquier.xyz>
Cc: "Anton A. Melnikov" <aamelnikov@inbox.ru>, Tom Lane <tgl@sss.pgh.pa.us>, pgsql-hackers@lists.postgresql.org
Date: 2022-12-23T03:27:24Z
Lists: pgsql-hackers 
On Fri, Dec 23, 2022 at 11:42:39AM +0900, Michael Paquier wrote:
> Hmm.  0001 does a direct check on aclitem as data type used in an
> attribute,

> For now, I have fixed the most pressing part for tables to match with
> the buildfarm

+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; $$;

This will do a seq scan around pg_attribute for each relation (currently
~600)...

Here, that takes a few seconds in a debug build, and I guess it'll be
more painful when running under valgrind/discard_caches/antiquated
hardware/etc.

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

-- 
Justin

Commits

  1. Add custom filtering rules to the TAP tests of pg_upgrade

  2. Switch query fixing aclitems in ~15 from O(N^2) to O(N) in upgrade_adapt.sql

  3. Update upgrade_adapt.sql to handle tables using aclitem as data type

  4. Fix some incorrectness in upgrade_adapt.sql on query for WITH OIDS