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

Hi Michael,

The tests of my last mail (standby promotion, pg_upgrade with the counter
past 2^32, rewrites after ALTER TABLE SET (toast_value_type), logical
replication) give the same results on master at fb60892f40, and
check-world passes there with 0006 and 0007 applied.

> 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:

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.

check_heap.sql runs on the regression instance, whose counter is young,
and the pg_upgrade test, where the counter is moved, does not run
amcheck. 0006 moves the counter past 2^32 before the first start, fills
an oid8 table with uncompressed and compressed out-of-line values,
replaces and deletes some of them, and runs verify_heapam(check_toast
:= true) on the table, verify_heapam() on the TOAST table and
bt_index_parent_check(heapallindexed => true) on the TOAST index,
before and after VACUUM. 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:

toast value 4295067314 not found in toast table

2. The "compressed out-of-line values" of check_heap.sql are not out of
line. 0007 makes them larger.

The rows come from these statements of check_heap.sql:

ALTER TABLE test_toast_oid8 ALTER COLUMN b SET STORAGE EXTENDED;
INSERT INTO test_toast_oid8 (a, b)
(SELECT gs, repeat('xyzzy', 20000) FROM generate_series(6,10) gs);

and repeat('xyzzy', 20000) compresses to 1161 bytes and stays in the
heap:

SELECT a, length(b), pg_column_size(b), pg_column_compression(b),
pg_column_toast_chunk_id(b)
FROM test_toast_oid8 WHERE a = 6;

a | length | pg_column_size | pg_column_compression | pg_column_toast_chunk_id
6 | 100000 | 1161 | pglz |

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.

> 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.

3. That works with either of two behaviors for ALTER TABLE, and the
current one is neither. I prefer a) and can write it.

Today SET and RESET (toast_value_type) change pg_class.reloptions and
nothing else, and no rewrite applies the new value: VACUUM FULL,
CLUSTER, REPACK and ALTER TABLE SET ACCESS METHOD rebuild the TOAST
table with the type it already has, since create_toast_table() takes
that type from the old TOAST table. A dump and restore is the only
thing that applies the reloption. Other heap reloptions are applied by
a rewrite; a table of 20000 rows with fillfactor:

default, 167 pages
SET (fillfactor = 20), 167 pages
VACUUM FULL, 870 pages
RESET (fillfactor) and VACUUM FULL, 167 pages

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.

Going to oid8 costs nothing there. Going to oid has to assign the
value IDs by probing for free ones, which slows down as the TOAST
table fills, so it is a usable operation for a table that fits with
room to spare and not for one that had a reason to be oid8. A restore
into an oid TOAST table makes the same assignment, so this is not
something a rewrite would introduce; neither route avoids it.

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.

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.

Say which one you prefer and I will send a patch for it.

Regards,
Rui