From: Manu <manuelreyesbravo@gmail.com>
To: shihao zhong <zhong950419@gmail.com>
Cc: Alexandre Felipe <o.alexandre.felipe@gmail.com>,
 Andres Freund <andres@anarazel.de>, Tom Lane <tgl@sss.pgh.pa.us>,
 Michael Paquier <michael@paquier.xyz>,
 Zsolt Parragi <zsolt.parragi@percona.com>, pgsql-hackers@lists.postgresql.org
Subject: Re: BUG #19686: Rolling back SET TABLESPACE
Date: Sun, 18 Oct 2026 13:45:31 -0300
Message-ID: <179234193175.237803.1636724777877426621@gmail.com>
In-Reply-To: 
 <CAGRkXqRKCNG1LKBV7d98QSdZKf1iT8Qb7LM9jTDKofn33hdsyw@mail.gmail.com>
References: 
 <CAGRkXqRKCNG1LKBV7d98QSdZKf1iT8Qb7LM9jTDKofn33hdsyw@mail.gmail.com>
Content-Type: text/plain; charset="utf-8"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/179234193175.237803.1636724777877426621%40gmail.com>
Precedence: bulk

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 table=
space, 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