Re: Support for 8-byte TOAST values, round two

On Wed, Sep 16, 2026 at 12:20:28AM +0800, Rui Zhao wrote:
>> One thing that neither you nor Greg pointed out is a disturbance I
>> have found in amcheck. The code relied on a vartag to decide the max
>> chunk size of a TOAST blob:

(Cut some of the contents, some are clearly AI-generated, which is
slightly annoying to argue with.)

> 1. Once 0004 and 0005 are in, amcheck is the one consumer of oid8
> pointers left that no test runs with IDs past 2^32. 0006 adds a TAP
> test for it.
>
> If I cast the value ID that
> check_toasted_attribute() passes to toast_valueid_scankey_init() to
> Oid by hand, the test fails, with verify_heapam() reporting every
> value:

I'm wondering if we should think a bit more wildly here. For example,
we could have a new PG_TEST_EXTRA value that enforces an initial OID
beyong the 2^32 limit. This is a bit more general than the statement
you are making here. Just to say that I'm not much a fan of the
cycles spend on a new TAP test for this purpose, where installcheck
could do the same using the SQL queries. So I don't think that your
0006 buys much by just adding more cycles and spawning a new node for
what's limited and duplicated coverage compared to the SQLs of
amcheck/sql/. An advantage of dumping the OID up in the pg_upgrade
test is that it's basically free.

> 2. The "compressed out-of-line values" of check_heap.sql are not out of
> line. 0007 makes them larger.
>
> so rows 6 to 10 of test_toast_oid and test_toast_oid8 hold no TOAST
> pointer, and verify_heapam(check_toast := true) has nothing to look up
> for them. With repeat('xyzzy', 100000) the compressed value is 5735
> bytes and goes out of line; 0007 also adds a SELECT showing that all
> ten values of each table are out of line and five of them compressed.

Hmpf, yeah. These tuples are not chunked into TOAST as they compress
too well. There are a few ways to make that predictible and cheaper:
- Force default_toast_compression to pglz, like in portals.sql, and to
force a check based on pg_column_compression().
- Use the same trick as test_decoding's toast.sql, for a much faster
query and the same coverage and even less tuples.

There is a similar mistake in test_decoding/sql/toast.sql, for the
same assumptions. Will adjust both the same way, both tests want the
same data patterns. Thanks for the report.

>> To me, applying the value set in the reloption at the moment of the
>> dump and not the chunk_id type feels like a natural thing to do.

(Note: this is a point of design I've had in mind for close to 2
years, so it's not like I gave it no thoughts.)

> a) Keep SET and RESET as they are and let a rewrite apply the value,
> as fillfactor does. A rewrite and a restore would then build the TOAST
> table the same way, which is what the reloption already says it does.

And you're missing a very fancy point here. A user could be trapped
under a oid8->oid switch. Create an oid8 table, downgrade the
reloption to oid, do a VACUUM FULL and enjoy the infinite loop.
That's no different from somebody currently seeing the infinite 4
billion limitation with an oid table, but it surely hurts when
happening across a rewrite. toast_tuple_target and fillfactor impact
the heap pages and are per-write targets for future tuples written,
something like a VACUUM feeds on. Shaping a TOAST table based on a
reloption is an entirely new design space. It just does not have to
fit in these rules (this does not mean that I don't mind changing some
of the rules of HEAD, depending on how things go in this release
cycle).

> b) Reject RESET and have SET rewrite the table, as SET ACCESS METHOD
> does. The reloption then always describes the type in use, whether it
> is set or absent, which is why pg_dump can stay as it is. It puts a
> rewrite behind an ALTER TABLE ... SET, which no other reloption does
> today.

First. ALTER TABLE SET (reloption=foo) should never trigger a
rewrite. It's cheap currently as it just touches catalogs, and it
should stay as such. SET ACCESS METHOD is a different sub-command in
the ALTER TABLE set of options available. Whether it triggers a
rewrite is up to it because there is a user intention behind the cost.
A similar argument exists for SET LOGGED.

Anyway, this line of thought discards the fact that toast_value_type
defines the definition of an underlying table. Having a rewrite
enforced by a plain ALTER TABLE SET can also be surprising, especially
if you are dealing with a 10TB table that needs to be WAL-logged.

FWIW, we've had exactly a very similar kind of discussion in the v14
development when lz4 compression has been introduced for VACUUM
(outremembering a bunch of folks on this thread, I guess). The
original feature enforced a rewrite of the TOAST values based on the
default_toast_compression if the default was switched to lz4 while
values could be written as pglz, and the conclusion was that it was
not VACUUM's job to enforce new compression policies.

> Either one settles what Bharath's 0001 reports: a dump follows the
> reloption because that is what the reloption means. If the restore
> side should be able to choose the type as well, the way to do that
> is the one used for default_table_access_method, but I do not see
> the need for it now.

Among the options presented, preventing a RESET of the new reloption
if the reloption has been explicitely set may be OK, but it can be
disruptive with dump and upgrade scenarios in general, which is a
reason why it's originally pretty loose. Bharath's suggestion of
adding a WITH clause in the dumps based on the type of chunk_id when
taking the dump sounds like the most tempting semantic to me, because
it sticks the TOAST definition within the CREATE TABLE of the parent.
The devil is in the details, and there's still plenty of time to tweak
the semantics.

A GUC was shut down two years ago by Andres and Tom, see this thread
for a summary (feeding that to an agent would be faster, I guess):
https://www.postgresql.org/message-id/aFOnKHG7Wn-Srnpv@paquier.xyz
So I'm not going back to anything like that.

The rewrite of the TOAST table is something that I think would fit
much better on an option-basis in one of the existing commands, not
enforced upon users, *if and only if* explicitely requested. REPACK
would be a nice fit, in spirit, to be able to perform the job,
combined with an update of the reloption. VACUUM and CLUSTER are
there for different purposes. There would be value for such a thing
for a oid8->oid as much as an oid->oid8. At the end, it's also up to
users to be very careful. Not sure there is much a point to outsmart
them with the backend.
--
Michael