Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1p8YiZ-0006LV-QH for pgsql-hackers@arkaria.postgresql.org; Fri, 23 Dec 2022 03:27:31 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1p8YiY-0005HP-FN for pgsql-hackers@arkaria.postgresql.org; Fri, 23 Dec 2022 03:27:30 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1p8YiY-0005HF-1R for pgsql-hackers@lists.postgresql.org; Fri, 23 Dec 2022 03:27:30 +0000 Received: from mail-il1-x12d.google.com ([2607:f8b0:4864:20::12d]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1p8YiU-0003GK-Or for pgsql-hackers@lists.postgresql.org; Fri, 23 Dec 2022 03:27:28 +0000 Received: by mail-il1-x12d.google.com with SMTP id y3so2012524ilq.0 for ; Thu, 22 Dec 2022 19:27:26 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=telsasoft-com.20210112.gappssmtp.com; s=20210112; h=user-agent:in-reply-to:content-disposition:mime-version:references :message-id:subject:cc:to:from:date:from:to:cc:subject:date :message-id:reply-to; bh=XTRkKQ4OuRHXNK+hSDxFcSaKZhnbIchBQlpvG6fa3aI=; b=ov47qzVqb/p/r/K9r7TsK/Us+TY/lGEKTVoOzSzRKymS82iqXG3X8UxesmSqbQEJOV Jmy4w4IcEiThJ0iAos9kP7u4EThleYIzoZd3Qw+qA2aaUK4r5TFE4KcvYaejC61P8IaG SKva+T5P29BNe0K9bx4ACgZbXcoNOlJ9LFTA5LWgL7Y/7rcIW7IrZw42GdvO3y0krvTt 4UL6eb5vy3Pf8LKFrw2opB4Dxxnm1t58rwChW3jB+nIgh8K92uIbj+PN6b3ElzO9qAh2 Dd4+o+CXLu/kcWuzb8RsSphKOWoDnWvANHIZZePTIDkpjKMnbuiEM8JuiPjlpeCUXdXg BPRg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=user-agent:in-reply-to:content-disposition:mime-version:references :message-id:subject:cc:to:from:date:x-gm-message-state:from:to:cc :subject:date:message-id:reply-to; bh=XTRkKQ4OuRHXNK+hSDxFcSaKZhnbIchBQlpvG6fa3aI=; b=D2KaDoCmeV3it84aAif2uYV3uF7xB6aVq+eqQ9zZxjMY9jk+mUzhXKequVbg1NOWs5 jXAp/tsmOAw93egnWV1qA6I1YJ2SBmTW614uSPM1ZQ/x32CaTBQZzyIw79tWVGUaZLA5 e46jnReOHdKWqPm9JxJBkdCQkocM5cnaJ5naw1U9Bz4U2NPhnPUESOa9Bkmm1clVcauF /20rVmRAv7L1F1LdEIx8+TjqI4fjIrLWv+2FQRWo+fLOpAthCNSWACzrooBvipMSqyVJ 3xjhqlfiMCnvVqHedAPiAOh9oJ2NwiWDshDhDIiPu74lovG3bXVtaOUHq29SK6ZwdgUZ erYg== X-Gm-Message-State: AFqh2krps5j13H1m7Hojcxl/nX2GEW6gXlfkpg/8G5CtoR5pE+mDANgv D+t6HfhAoo2U2Vct7CURcM1qzQ== X-Google-Smtp-Source: AMrXdXtDnFfwz2pq819U9SLV4rh5mMBri+twNSDwD3kMbPHBYDhq5sHn2vq6OCqCS2AaR3LNrep5Ng== X-Received: by 2002:a92:c04e:0:b0:300:bcdd:919 with SMTP id o14-20020a92c04e000000b00300bcdd0919mr5973883ilf.22.1671766045781; Thu, 22 Dec 2022 19:27:25 -0800 (PST) Received: from pryzbyj.telsasoft (charmander.telsasoft.com. [50.244.222.1]) by smtp.gmail.com with ESMTPSA id t4-20020a922c04000000b002f9f7d24d78sm699577ile.76.2022.12.22.19.27.25 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Thu, 22 Dec 2022 19:27:25 -0800 (PST) Received: by pryzbyj.telsasoft (Postfix, from userid 1000) id 411B3800DCC; Thu, 22 Dec 2022 21:27:24 -0600 (CST) Date: Thu, 22 Dec 2022 21:27:24 -0600 From: Justin Pryzby To: Michael Paquier Cc: "Anton A. Melnikov" , Tom Lane , pgsql-hackers@lists.postgresql.org Subject: Re: [BUG] pg_upgrade test fails from older versions. Message-ID: <20221223032724.GQ1153@telsasoft.com> References: <49f389ba-95ce-8a9b-09ae-f60650c0e7c7@inbox.ru> <819272.1671415008@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.9.4 (2018-02-28) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 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