pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Andres Freund <andres@anarazel.de>
Cc: Magnus Hagander <magnus@hagander.net>
Cc: Fujii Masao <masao.fujii@oss.nttdata.com>
Cc: skoposov@ed.ac.uk, PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Subject: Re: BUG #16722: PG hanging on COPY when table has close to 2^32 toasts in the table.
Date: Wed, 18 Nov 2020 20:17:55 -0500
Message-ID: <1013618.1605748675@sss.pgh.pa.us> (raw)
In-Reply-To: <20201119010637.uu5ub4qjnywytpt4@alap3.anarazel.de>
References: <16722-93043fb459a41073@postgresql.org>
	<95bfb4b5-2e0e-8ab7-12cf-03fae3367c52@oss.nttdata.com>
	<CABUevExw6AHqC_U7nH83=rvF-xY_U8AUDbR94kBDqDR0hGqKXw@mail.gmail.com>
	<20201118192743.kr43onoxci5gexjx@alap3.anarazel.de>
	<CABUevEw69Es-s7fcvrNfZx9SWoPt-ZX+GJNFn_wNPoMtYAvNCw@mail.gmail.com>
	<20201118230529.6u7fyl6trnepekgc@alap3.anarazel.de>
	<1010421.1605744154@sss.pgh.pa.us>
	<20201119010637.uu5ub4qjnywytpt4@alap3.anarazel.de>

Andres Freund <andres@anarazel.de> 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





view thread (28+ messages)  latest in thread

Message-ID: <1013618.1605748675@sss.pgh.pa.us>
Permalink:  ../1013618.1605748675@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/1013618.1605748675@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-bugs@postgresql.org
  Cc: tgl@sss.pgh.pa.us, andres@anarazel.de, magnus@hagander.net, masao.fujii@oss.nttdata.com, pgsql-bugs@lists.postgresql.org
  Subject: Re: BUG #16722: PG hanging on COPY when table has close to 2^32 toasts in the table.
  In-Reply-To: <1013618.1605748675@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox