Re: BUG #16722: PG hanging on COPY when table has close to 2^32 toasts in the table.

On Wed, 2020-11-18 at 19:02 -0500, Tom Lane wrote:
> This email was sent to you by someone outside the University.
> You should only click on links or attachments if you are certain that the email is genuine and the content is safe.
>
> Andres Freund <andres(at)anarazel(dot)de> writes:
> > On 2020-11-18 23:12:10 +0100, Magnus Hagander wrote:
> > > That said, I'd really prefer to see *some* kind of counter that could
> > > get people noticing this a bit earlier, rather than just bugging them
> > > in the logfile once it's gone over a threshold. A statistics counter
> > > maybe, but I guess that'd have to be tracked at a per-table level to
> > > be really useful, which would make it potentially fairly expensive to
> > > keep around...
> > I'd rather spend effort to solve the underlying issue. It's a bit more
> > work, but not crazily so, I think. Even the pg_upgrade pieces aren't
> > that hard to solve.
>
> 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.
>
> Having said that, I had imagined that we might never have to fix it,
> because if your table is big enough that it has a problem of this
> ilk then likely you want to partition it anyway. And partitioning
> solves the problem since each partition has its own toast table.

I can say that for my case of very large static clustered tables the
partitioning doesn't really offer much benefits, only overhead (To the best
of my understanding at least), so I'm not using it.

Sergey

PS And the current workaround that I used for this particular dataset that I reported
was to just expand a few arrays that I have in the table into columns
(as all arrays had static lengths) to not have any toasts at all.

The University of Edinburgh is a charitable body, registered in Scotland, with registration number SC005336.