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 1kfC7H-0001fG-Bo for pgsql-bugs@arkaria.postgresql.org; Wed, 18 Nov 2020 01:18:35 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kfC7G-0007uE-3k for pgsql-bugs@arkaria.postgresql.org; Wed, 18 Nov 2020 01:18:34 +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 1kfC7F-0007u6-Ti for pgsql-bugs@lists.postgresql.org; Wed, 18 Nov 2020 01:18:33 +0000 Received: from oss.nttdata.com ([49.212.34.109]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kfC78-0003SV-4V for pgsql-bugs@lists.postgresql.org; Wed, 18 Nov 2020 01:18:32 +0000 Received: from hnk.local (p1295030-ipngn4902funabasi.chiba.ocn.ne.jp [153.166.85.30]) by oss.nttdata.com (Postfix) with ESMTPSA id BE853626CE; Wed, 18 Nov 2020 10:18:20 +0900 (JST) X-Virus-Status: Clean X-Virus-Scanned: clamav-milter 0.102.3 at oss.nttdata.com Subject: Re: BUG #16722: PG hanging on COPY when table has close to 2^32 toasts in the table. To: skoposov@ed.ac.uk, pgsql-bugs@lists.postgresql.org References: <16722-93043fb459a41073@postgresql.org> From: Fujii Masao Message-ID: <95bfb4b5-2e0e-8ab7-12cf-03fae3367c52@oss.nttdata.com> Date: Wed, 18 Nov 2020 10:18:20 +0900 User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.14; rv:78.0) Gecko/20100101 Thunderbird/78.4.3 MIME-Version: 1.0 In-Reply-To: <16722-93043fb459a41073@postgresql.org> Content-Type: text/plain; charset=utf-8; format=flowed Content-Language: en-US Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk On 2020/11/17 3:15, PG Bug reporting form wrote: > The following bug has been logged on the website: > > Bug reference: 16722 > Logged by: Sergey Koposov > Email address: skoposov@ed.ac.uk > PostgreSQL version: 11.9 > Operating system: debian > Description: > > Hi, > > When ingesting a billion or so rows in the table that has some array columns > (see schema below), at some point all the backends doing the ingestion hang > with 100% CPU usage. When investigating, I think I can trace this to the > limit of 2^32 toasted records per table. Yes, this is the limitation of the number of out-of-line values in toast. https://wiki.postgresql.org/wiki/TOAST#Total_table_size_limit > See the gdb full backtrace of > hanged backends in the bottom. When the problem occurs, it has 272 mill > records. > Basically the hanging happens in GetNewOidWithIndex called by > toast_save_datum. > While I understand the limit for the toast number is there to stay, but the > behaviour of PG next to the limit is IMO a bug (or at least non-trivial) . > > I would rather prefer to see an error-message as opposed to backends > hanging. To emit an error, we need to check that there is no unused OID for the toast and it would take very long to do that. So I'm not sure if to emit an error message really improves the current situation or not. OTOH it might be good idea to emit a warning message (every time we cannot find unused OID in recent 1 million OIDs, for example) or report the issue as wait event, or something while the record insertion is hanging because of toast limit, so that we can easily detect the issue. Regards, -- Fujii Masao Advanced Computing Technology Center Research and Development Headquarters NTT DATA CORPORATION