Hi,
While stress testing REPACK (CONCURRENTLY), I noticed that the current
"initializing" phase contains multiple operations that can wait indefinitely.
The transaction wait can be prolonged by long-running or prepared
transactions, or by another REPACK (CONCURRENTLY) operation that already has
an XID. Describing the whole phase as "expected to be very brief" [1] is
misleading.
To make these waits visible and distinguish the work immediately before and
after the old transaction wait, three progress phases between
"initializing" and
the heap scan should be added.
1/ enabling logical decoding
When wal_level is logical, or logical decoding is already enabled, this phase
is effectively instantaneous. Otherwise, REPACK waits for every running
process to acknowledge a ProcSignalBarrier. This normally finishes quickly,
but the barrier has no timeout and can take longer if a process does not
respond promptly.
2/ waiting for old transactions
The snapshot builder waits for transactions with assigned XIDs to finish
before it can reach a consistent point.
3/ building initial snapshot
REPACK obtains the initial snapshot from the decoding worker and prepares to
start copying the table. This separates the end of the transaction wait
from the start of the heap scan.
This gives the following sequence:
initializing (default, expected to be brief)
enabling logical decoding
waiting for old transactions
building initial snapshot
seq scanning heap / index scanning heap
The attached patch has the worker publish its setup stage through shared
memory and signal the existing condition variable.
Thoughts?
[1] https://www.postgresql.org/docs/19/progress-reporting.html
--
Sami Imseih
Amazon Web Services (AWS)
