Re: Support for 8-byte TOAST values (aka the TOAST infinite loop problem) - Mailing list pgsql-hackers

On Tue, Jul 22, 2025 at 02:56:23PM +0200, Hannu Krosing wrote:
> The high-level idea would be to any actual rewrite -- as opposed to
> plain vacuum which frees empty space within the TOAST relation -- as
> part of the vacuum of the main relation.
> Another option would be to store a back-pointer to the heap tuple
> inside the toast tuple and use that when rewriting, though it has its
> own set of complexities.

Well.  I would suggest to begin with simpler to begin with, finding
building pieces that can be relied on, and I tend to think that I've
sorted out the first basic one of these because we want to be able to
give the backend the possibility to understand more formats of
external on-dist TOAST pointers.  A simple whole rewrite of the TOAST
engine is not something that can just happen in one day: that's a very
risky move, and we need to worry about backward-compatibility while
maintaining the legacy code.  FWIW, I think that we should still move
on with the 8-byte implementation anyway with specific vartag_external
that can lead to variable-sized pointers (shorter if value is less
than UINT32_MAX, still stored in a int8 TOAST table), extending the
code so as it's possible to think about more on top of that.  That's
basically risk-free if done right while taking the problem at its
root.  That's also what Tom and Andres meant back in 2022 before the
problem drifted away to different issues, and that should allow the
addition of more compression methods if done correctly (quoted at the
top of this thread).

>>> You have some interesting points around
>>> detoast_external_attr() and detoast_attr_slice(), as far as I can see.
>>> One point of the on-disk TOAST refactoring is that we should be able
>>> to entirely avoid this level of redirection.  I get that this is a
>>> POC, of course, but it provides pointers that what I've done may not
>>> be sufficient in terms of extensibility so that seems worth digging
>>> into.
>>
>> I'm currently re-visiting our TOAST API patch set, there are some
>> good (in terms of simplicity and lightweightness) proposals, will mail
>> later.
>
> Sounds interesting.

If you have refactoring proposals, that could be considered
separately, yes.

Now, the reason why nothing has been done about the original problem
is the same reason as what's happening now on this thread: I am seeing
a lot of designs and assumptions for new solutions, without knowing
all the problems we are trying to solve or even if they actually make
sense at this level of the baclend engine.  And there is little to no
argument made about backward-compatibility, which is also something
I've tried to design (single GUC for dump/restore/upgrade with TOAST
type based on attribute data type of the TOAST table, which could be
also a tid later on).

> But this goes way beyond the current patch's scope. Though my design
> should accommodate it nicely.

If you see independent useful pieces that are worth their own, nobody
is going to object to that.  The trick is to find incremental pieces
good enough to be able to build upon with individual evaluations and
reviews, at least that's how I want to tackle the problem because I
would be the one who would be responsible for its maintenance by
default.  Finding out these "simple" independent relevant pieces is
the hard part.
--
Michael