Horton
Menu

Re: Adding REPACK [concurrently]

Andres Freund <andres@anarazel.de>

View thread
From: Andres Freund <andres@anarazel.de>
To: Amit Kapila <amit.kapila16@gmail.com>
Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>, Mihail Nikalayeu <mihailnikalayeu@gmail.com>, Antonin Houska <ah@cybertec.at>, Srinath Reddy Sadipiralla <srinath2133@gmail.com>, Matthias van de Meent <boekewurm+postgres@gmail.com>, Pg Hackers <pgsql-hackers@lists.postgresql.org>, Robert Treat <rob@xzilla.net>
Date: 2026-04-08T17:22:41Z
Lists: pgsql-hackers 
Hi,

On 2026-04-08 14:05:49 +0530, Amit Kapila wrote:
> On Wed, Apr 8, 2026 at 1:54 AM Andres Freund <andres@anarazel.de> wrote:
> > On 2026-04-07 00:22:32 +0200, Alvaro Herrera wrote:
> > > From 4303eea0a72408183f9f5afcf8d2801df20f8ffe Mon Sep 17 00:00:00 2001
> > > From: Antonin Houska <ah@cybertec.at>
> > > Date: Wed, 1 Apr 2026 17:35:47 +0200
> > > Subject: [PATCH v56 3/3] Error out any process that would block at REPACK
> > >
> > > Any process waiting on REPACK to release its lock would actually cause
> > > it to deadlock when it tries to upgrade its lock to AEL, losing all work
> > > done to that point.  We avoid this by teaching the deadlock detector to
> > > raise an error when this condition is detected.
> >
> > I'm rather doubtful that that is ok.
> >
> 
> Another possible idea is that after copying table_data to the new
> table, we mark the old table as in_use_by_repack and release the
> ShareUpdateExclusiveLock on the old table. Then the function
> CheckTableNotInUse() should be updated to give an ERROR if the table
> is marked as in_use_by_repack. Now, acquiring AEL by repack
> (concurrently) should be safe because all concurrent DDLs should be
> errored out due to flag in_use_by_repack. Can this address the problem
> we are worried about the lock upgrade?

I don't think this is a viable path.  You need to prevent any further lock
acquisitions on the relation to be able to swap it, not just conflicting DDL.
And you need to wait for all pre-existing locks to have been released.  That
doesn't really get easier by what you propose.

I don't think CheckTableNotInUse() would work anyway - don't we already hold
locks by the point we call it? And even if that were not the case, there are
several paths to locking relations that don't ever go anywhere near
CheckTableNotInUse().

Greetings,

Andres Freund

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