Horton
Menu

Re: Adding REPACK [concurrently]

Antonin Houska <ah@cybertec.at>

View thread
From: Antonin Houska <ah@cybertec.at>
To: Mihail Nikalayeu <mihailnikalayeu@gmail.com>
Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>, Pg Hackers <pgsql-hackers@lists.postgresql.org>, Robert Treat <rob@xzilla.net>
Date: 2026-01-15T16:36:59Z
Lists: pgsql-hackers 
Mihail Nikalayeu <mihailnikalayeu@gmail.com> wrote:

> Also, there are some crashes of stress tests for v30 (for both single snapshot and multiple snapshot versions).
> 
> ---------------------
> 
> Looks like something is leaking, but not sure.
> 
> https://cirrus-ci.com/task/5577209672368128?logs=test_world#L277 (multiple snapshots)
> https://cirrus-ci.com/task/6439044873191424 (without multiple snapshots)

As the test runs pgbench with --client=30 and the default value of
max_worker_processes is 8, I'm not sure this is a leak. I've increased this
parameter I couldn't see the error anymore.

> This one showed something goes wrong, the sum of the table is broken. It may be 0 because non-MVCC safe, but I checked the logs:
> 
> 2026-01-12 18:41:11.656 UTC client backend[76247] 007_repack_concurrently.pl LOG:  statement: SELECT (490588) / 0;

I agree that this is due to the missing MVCC safety feature. I commented that
check in the script for now.

Besides that, I saw some deadlocks. I think this was due to the fact that
multiple rows are updated per transaction, and that the keys are random, so it
can happen that two transactions try to update the same rows in different
order. I increased the number of rows in the test table to 10000 and don't see
the deadlocks anymore.

> backend[54349] 007_repack_concurrently.pl ERROR:  could not create unique index "tbl_pkey_repacknew"
> 2026-01-12 18:41:12.477 UTC client backend[54349] 007_repack_concurrently.pl DETAIL:  Key (i)=(942) is duplicated.
> 2026-01-12 18:41:12.477 UTC client backend[54349] 007_repack_concurrently.pl STATEMENT:  REPACK (CONCURRENTLY) tbl;

This is tricky. I could reproduce the problem on my FreeBSD box a few times,
never on Linux (no idea if the OS makes the difference since HW is also quite
different, but CI also seemed to fail more often on FreeBSD.)

Something seems to be wrong about UPDATE, but I'm failing to understand how it
could relate to REPACK. This is an example of a duplicate value i=6118

SELECT i, j, xmin, xmax, ctid FROM tbl WHERE i=6118;
  i   |   j    |  xmin  |  xmax  |  ctid   
------+--------+--------+--------+---------
 6118 | 445435 | 102317 | 103702 | (1,216)
 6118 | 391135 | 103702 |      0 | (56,62)

According to log, xid=102317 is the transaction used by REPACK and xid=103702
one of the test. pageinspect shows that the old version has not only
HEAP_XMIN_COMMITTED in t_infomask, but also HEAP_XMAX_INVALID.

So far I could not reproduce the duplicities with the REPACK (CONCURRENTLY)
command commented out in the test script, but that does not prove much (even
with REPACK, not every run fails). Also I noticed that REPACK incorrectly sets
cmin/cmax to 1 instead of 0 and it needs to be fixed, but I have no idea why
this bug should cause exactly this weird behavior.

I even added quite a few logging messages to reveal where in the code the
HEAP_XMAX_INVALID flag is set for particular ctid, but after a failure I could
not find the message for the problematic tuples. Ideas are appreciated.

-- 
Antonin Houska
Web: https://www.cybertec-postgresql.com

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