Re: UNDO with FILEOPS, and the FLUX table AM

Hello Manu, Matthias, Shihao, hackers,

v8 is attached, rebased onto current master. Some significant changes and the
last series with the FLUX table AM and core changes required to enable it to
work. The series is too long/large and I'd like to get some/any momentum for
the idea and FILEOPS in place or put the effort aside.

One interesting point for those working on TOAST alternatives is that FLUX in
this version has its own overflow management that doesn't use TOAST at all
(maybe interesting to you Hannu).

This is experimental, draft stage posted for design review of:

0001-0003 the UNDO and FILEOPS layers
0004-0008 are only useful to an UNDO-based table AM
0009-0018 are small heap-neutral hooks, and
0019-0020 FLUX exists to exercise all of it, not proposed for core.

Fixed in v7's UNDO and FILEOPS code (0001, 0004, 0005):

- The undo launcher could hang for good.

- The undo workers' round robin could skip the xid queue.

- An abort that defers its UNDO to the ATM in some cases leaked
its "Inline UNDO Apply" memory context.

- The FILEOPS WRITE inverse treated a failed stat() of its file like a
missing file and reported success, so a crash loser's overwritten
bytes were never restored.

- The logical revert launcher gave up on a database whose worker could
not be registered (no free background-worker slot): it recorded the
database as served anyway, so its ATM entries were never reverted and
their WAL was kept for good.

- When the revert worker's 64-slot retry table was full, a chain that
failed to apply was given up on its first failure, marked reverted
and forgotten.

and more.

More fixes in FLUX code (0019):

- Relation extension race.

- An in-place UPDATE's WAL diff was taken against the wrong old image.

- The FSM can return a block at or past the end of the relation, which a
plain ReadBuffer() rejects.

- A DELETE or UPDATE reserved its UNDO record before pinning the
visibility-map page, and that read can fail.

- FLUX row locks keyed their lock tag on the relation's relfilenumber,
where heap uses its OID.

and more.

FLUX-native overflow (0020, new):

In v7, FLUX pushed every wide value to an ordinary heap TOAST table; the
overflow pointer type and its redo existed but were never wired up. 0020
stores a wide value on FLUX's own pages, so it goes through the relation's own
buffers, WAL and UNDO. In outline (the commit message has the details):

- A value over a quarter page becomes data chunks on ordinary FLUX pages plus
a root directory chunk listing every data chunk; one level of indirect
directories covers values up to the varlena limit. The tuple keeps a
20-byte pointer to the root. Values of 2 MB and more are read through a
read_stream, since the directory names every block up front.

- Each chunk is written under its own buffer lock and its own WAL record, so a
writer never holds two chunk-page locks. Holding several, acquired in FSM
order by many backends, deadlocked at the buffer-lock level, where there is
no deadlock detector (recovery/164).

- Chunks carry no MVCC state. A chunk lives while any version that names it
is visible to someone, on the page or in UNDO. VACUUM's orphan sweep marks
what is reachable, walking each row's UNDO chain for superseded values, and
frees the rest; it fails safe, abandoning reclaim for the relation rather
than freeing on uncertainty. Freeing is offset-stable and carries a standby
conflict horizon. Every reader checks a chunk's recorded lengths before
following them, so a damaged page is reported as data corruption
(recovery/171) instead of overrunning a buffer. The same goes for a
superseded value's image rebuilt from UNDO for that walk: one shorter than a
tuple header is reported, where an earlier version of this commit read its
xmin from past its end and gave an old snapshot a wrong answer
(recovery/173), and a directory entry's offset is checked against its page's
line pointers before it is followed (recovery/171). Redo of a chunk write
also sets the page commit timestamp the primary set, which a write to an
existing chunk page, logged without a full-page image, missed
(recovery/175).

- Logically logged relations keep heap TOAST for wide values: reorderbuffer
needs an oversized value as an external heap TOAST pointer, and the decoder
has no FLUX relation to reassemble chunks from.

Measured against heap TOAST on a c6id.metal (128 vCPU, 4x NVMe RAID0), release
build of this series, fsync and full_page_writes on. Values are uncompressible
md5-hex text, about 6 GB per size, 3 reps, a fresh table per rep. In every rep
the md5 of every value matched between FLUX and heap, after the load and after
a 10% update.

Medians, FLUX/heap (below 1 is better for FLUX):

4 KB 32 KB 256 KB 1 MB
total size 1.47 1.00 0.97 0.97
load time 0.99 0.94 0.98 0.98
load WAL 1.00 1.02 0.96 0.96
full scan, cold 1.04 0.35 0.39 0.37
full scan, warm 0.95 0.30 0.34 0.35
10% UPDATE time 0.58 0.85 1.03 1.04
10% UPDATE WAL 0.71 0.59 0.50 0.48
VACUUM time 1.83 1.35 1.31 1.30
point read time, 32 clients, warm

- Full scans of wide values are about three times faster: the chunks
sit in the relation's own pages instead of being reached through a
TOAST index.

- An in-place UPDATE writes about half the WAL.

- Point reads of very wide single values are slower. A 1 MB value is
about 129 chunks, read one at a time because that is below the 2 MB
read_stream threshold, while TOAST fetches in index order.

- At 4 KB, FLUX is 1.47x larger. Each chunk carries per-record and
per-page overhead that is large next to a 4 KB value. Heap also
compressed about 23% of the 4 KB values, md5 text though they are,
and FLUX compressed none, so that column favours heap.

- VACUUM is 1.3-1.8x slower. Part of that is the orphan sweep, which
reads the whole relation again; an earlier, separate measurement on
a narrow-row table put it at about 3-4 s per 4 GB of cold reads.

- Next steps: make the sweep skip pages that hold no chunks, and add a
per-chunk point-read path.

Under wal_level = logical, wide FLUX values go to heap TOAST as described
above; they decode through test_decoding, and scans match heap's (the
native-overflow advantage disappears, as expected).

V8 Series
---------

0001 cluster-wide UNDO engine (records in the WAL stream)
0002 transactional filesystem operations (FILEOPS) backed by UNDO
0003 route filesystem mutations through FILEOPS
0004 per-backend UNDO logs
0005 the ATM, sLog, and logical revert worker
0006 nbtree: mark aborted index inserts dead via UNDO
0007 hash: mark aborted index inserts dead via UNDO
0008 nbtree: in-place delete-marking for stable-TID UPDATE
0009 tableam: let an AM declare that UPDATE keeps a row's TID
0010 executor: keep the pre-update row of an in-place UPDATE
0011 EvalPlanQual: fetch an in-place AM's reference rows with the query snapshot
0012 AFTER triggers: queue an in-place AM's row images with the event
0013 logical replication: pre-check unique keys an in-place UPDATE changes
0014 tableam: SO_RETURN_DELETED, and update_deleted detection for any AM
0015 tableam: let a block AM's size estimate use its own default fillfactor
0016 tableam: let REPACK (CONCURRENTLY) accept any AM whose changes are decoded
0017 vacuum: add parallel_vacuum_set_index_stats() and a bulkdel injection point
0018 pg_visibility: refuse tables of other table AMs in the tuple checks
0019 the FLUX in-place-MVCC table access method
0020 flux: store wide values in FLUX-native overflow (new)
0021 benchmarks: heap vs FLUX

Next post will be 0001, 0002, and 0005 only. The rest are interesting
only if the first three, and the idea of adding UNDO, gain traction.

best.

-greg