Horton
Menu

Re: Adding REPACK [concurrently]

Alvaro Herrera <alvherre@alvh.no-ip.org>

View thread
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Srinath Reddy Sadipiralla <srinath2133@gmail.com>
Cc: Mihail Nikalayeu <mihailnikalayeu@gmail.com>, Antonin Houska <ah@cybertec.at>, Matthias van de Meent <boekewurm+postgres@gmail.com>, Pg Hackers <pgsql-hackers@lists.postgresql.org>, Robert Treat <rob@xzilla.net>
Date: 2026-03-25T20:12:48Z
Lists: pgsql-hackers

Attachments

On 2026-Mar-25, Srinath Reddy Sadipiralla wrote:

> Then as suggested by Alvaro off-list I checked the lock upgrade
> behavior during the table swap phase. I observed that if another
> transaction holds a conflicting lock on the table when the swap is
> attempted, it can lead to “transient table” data loss during a manual
> or timeout abort.  when a REPACK (concurrent) waits for a conflicting
> lock to be released and eventually hits a lock_timeout (or is
> cancelled via ctrl+c), the transaction aborts. During this abort, the
> cleanup process triggers smgrDoPendingDeletes. This results in the
> removal of all transient table relfiles and decoder worker files
> created during the process.  This effectively wipes out the work done
> by the transient table creation before the swap could successfully
> complete, this happens because during transient table creation we add
> the table to the PendingRelDelete list.

I think we certainly need to make the files be deleted in some
reasonable fashion if repack fails partway through.

As for lock upgrade, I wonder if the best way to handle this isn't to
hack the deadlock detector so that it causes any *other* process to die,
if they detect that they would block on REPACK.  Arguably there's
nothing that you can do to a table while its undergoing REPACK
CONCURRENTLY; any alterations would have to wait until the repacking is
compelted.  We can implement that idea simply enough, as shown in this
crude prototype.  (I omitted the last three patches in the series, and
squashed my proposed changes into 0003, as announced in my previous
posting.)

The isolation test file is also a bit crude; I just copied repack.spec
to a new file and removed the uninteresting bits.

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/
"I am amazed at [the pgsql-sql] mailing list for the wonderful support, and
lack of hesitasion in answering a lost soul's question, I just wished the rest
of the mailing list could be like this."                               (Fotis)
              https://postgr.es/m/200606261359.k5QDxE2p004593@auth-smtp.hol.gr

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Allow old WAL recycling during REPACK CONCURRENTLY

  2. Advance restart_lsn more eagerly in LogicalConfirmReceivedLocation

  3. Remove unnecessary signal handler change

  4. Improve REPACK (CONCURRENTLY) error messages some more

  5. Revert "Allow logical replication snapshots to be database-specific"

  6. Move REPACK (CONCURRENTLY) test out of stock regression tests

  7. REPACK: do not require REPLICATION or LOGIN

  8. Add missing initialization

  9. Simplify declaration of memcpy target

  10. Reserve replication slots specifically for REPACK

  11. doc: Add an example of REPACK (CONCURRENTLY)

  12. Allow logical replication snapshots to be database-specific

  13. Avoid different-size pointer-to-integer cast

  14. Fix valgrind failure

  15. Add CONCURRENTLY option to REPACK

  16. Rename cluster.c to repack.c (and corresponding .h)

  17. Allow index_create to suppress index_build progress reporting

  18. Make index_concurrently_create_copy more general

  19. Document the 'command' column of pg_stat_progress_repack

  20. Introduce the REPACK command

  21. Toggle logical decoding dynamically based on logical slot presence.

  22. Split vacuumdb to create vacuuming.c/h

  23. Remove ReorderBufferTupleBuf structure.

  24. Revert changes to CONCURRENTLY that "sped up" Xmin advance

  25. VACUUM: ignore indexing operations with CONCURRENTLY