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 1kfYaY-00040s-Fp for pgsql-bugs@arkaria.postgresql.org; Thu, 19 Nov 2020 01:18:18 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kfYaX-0000ju-EM for pgsql-bugs@arkaria.postgresql.org; Thu, 19 Nov 2020 01:18:17 +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 1kfYaX-0000jn-8X for pgsql-bugs@lists.postgresql.org; Thu, 19 Nov 2020 01:18:17 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kfYaV-0006jZ-4i for pgsql-bugs@lists.postgresql.org; Thu, 19 Nov 2020 01:18:16 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 0AJ1Ht5T1013619; Wed, 18 Nov 2020 20:17:55 -0500 From: Tom Lane To: Andres Freund cc: Magnus Hagander , Fujii Masao , skoposov@ed.ac.uk, PostgreSQL mailing lists Subject: Re: BUG #16722: PG hanging on COPY when table has close to 2^32 toasts in the table. In-reply-to: <20201119010637.uu5ub4qjnywytpt4@alap3.anarazel.de> References: <16722-93043fb459a41073@postgresql.org> <95bfb4b5-2e0e-8ab7-12cf-03fae3367c52@oss.nttdata.com> <20201118192743.kr43onoxci5gexjx@alap3.anarazel.de> <20201118230529.6u7fyl6trnepekgc@alap3.anarazel.de> <1010421.1605744154@sss.pgh.pa.us> <20201119010637.uu5ub4qjnywytpt4@alap3.anarazel.de> Comments: In-reply-to Andres Freund message dated "Wed, 18 Nov 2020 17:06:37 -0800" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <1013617.1605748675.1@sss.pgh.pa.us> Date: Wed, 18 Nov 2020 20:17:55 -0500 Message-ID: <1013618.1605748675@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk Andres Freund writes: > On 2020-11-18 19:02:34 -0500, Tom Lane wrote: >> Yeah. If we're going to put work into this, widening the IDs used >> to identify toast values seems like the right work to be doing. > To outline, here's what I think the two major pieces to get there are: > 1) Make toast oid assignment independent of the oid counter. The easiest > way likely is to also create a serial and use that. That alone > improves the situation considerably, because it takes much longer to > to wrap around in each toast table. I'm not really on board with creating an actual sequence object for each toast table. I don't think they are efficient enough. I had imagined internally widening the OID counter to 64 bits, of which ordinary OID usage would just discard the high 32 bits, but for TOAST chunk IDs we could use the whole value. Now, compared to your idea that would result in using the wider toast-pointer format more often, but I think there are ways around that. For example, we could do (1) Grab the next 64-bit OID. (2) Probe the toast table to see if the low 32 bits correspond to a value in use in that toast table. If not, just use the low 32 bits. (3) Otherwise, use the whole 64 bits and the wider pointer format. (We could do another uniqueness probe here, but I think it's unnecessary, since the 64-bit counter will never wrap around.) Since the existing OID assignment mechanism requires a uniqueness probe already, this isn't any more expensive than what we do now. regards, tom lane