Hi Shihao, > One correction. v7 copies the indexes in their own tablespace, so the > room is needed on the old volume, not the destination.
You're right -- thanks for catching it. My earlier "destination tablespace" was wrong, and I measured it. v7 copies each index into the index's own tablespace, so that is where the extra room is needed; the destination only has to fit the heap, the same as master. The extra is the size of the indexes, within about 32 kB: 6.45 MB for one index, 12.89 MB for two, 25.78 MB for four in my test. A single ALTER through the extended protocol then fails with ENOSPC on a nearly-full index volume where master succeeds; inside a transaction block v6 copies too, so that cost is v7's only for the single extended-protocol statement. This is the cost the deferred copy (my v4) avoided -- it copied nothing for an ALTER with no later write, 0 copies across the 15 cases I re-ran. Since the cost is back, I went back to the two objections that led me to drop it. Andres, on the old index being pinned: I measured it in 13 scenarios where the deferred copy runs with a buffer of the old index file still pinned, verified each time with pg_buffercache before the write. (A plain MVCC btree scan drops the leaf pin, so the outer scans are index-only or hash, which keep it, plus a nested loop, a BEFORE trigger, a scrollable cursor, a generic prepared plan, a partial index and writes under savepoints.) All 13 are correct after COMMIT and ROLLBACK; master fails every one on ROLLBACK. The oracle is index-vs-seq, by row md5 over 8 query shapes and index-only scans after VACUUM to surface stale entries, since amcheck's heapallindexed only checks heap->index. One v4 case does fail -- a REINDEX before a savepoint whose move is then rolled back -- but that is the savepoint gap v5 already closed, not a pin issue, so a deferred v8 would keep v5's per-subtransaction rule. This was btree and hash, one backend. On the other objection, the performance being harder to reason about, I have no number that settles it: the copy happens only when a later write makes it necessary, and the first such write pays for all the indexes, which is a trade-off to weigh rather than something I can measure away. For the back branches the deferred copy is a behavior change and, as Michael noted, not backpatchable, so there the eager copy (v7) is the fix regardless; the question is only 19 and master. So, given the space cost: is the deferred copy worth another look for 19, or should we take the eager copy everywhere? I'm happy to prepare a proper v8 of it if you'd like to see one, and otherwise v7 stands. regards, Manu
