pg.ddx.io  reorg/pg_repack GitHub issues and pull requests  
help / color / mirror / Atom feed
[reorg/pg_repack] issue #486: SIGINT does not stop `--jobs` index builds: workers are not cancelled and remaining indexes keep being assigned
2+ messages / 2 participants
[nested] [flat]

* [reorg/pg_repack] issue #486: SIGINT does not stop `--jobs` index builds: workers are not cancelled and remaining indexes keep being assigned
@ 2026-10-13 17:18 "chobostar (@chobostar)" <noreply+chobostar@github.com>
  0 siblings, 0 replies; 2+ messages in thread

From: chobostar (@chobostar) @ 2026-10-13 17:18 UTC (permalink / raw)
  To: reorg/pg_repack <noreply+reorg-pg_repack@github.com>

## Summary

With `--jobs > 1`, interrupting pg_repack (SIGINT / Ctrl-C) during the index rebuild phase does not stop it:

1. index builds already running in worker connections are **not cancelled**;
2. the main loop in `rebuild_indexes()` keeps **assigning the remaining indexes** to the freed workers;
3. pg_repack exits (`FATAL: interrupted`) and cleans up only after **all** indexes of the table are built.

With `--jobs=1` the same interrupt cancels the running `CREATE INDEX` and cleans up within seconds.

This is the situation described in the existing `XXX` comment in `rebuild_indexes()` (still present on `master`):

```c
/* XXX: the errno != EINTR check means we won't bail
 * out on SIGINT. We should probably just remove this
 * check, though it seems we also need to fix up
 * the on_interrupt handling for workers' index
 * builds (those PGconns don't seem to have c->cancel
 * set, so we don't cancel the in-progress builds).
 */
```

I could not find an issue tracking it, so opening one with a reproduction and the operational impact.

## Why it matters

pg_repack is often run unattended — from cron, CI, or a Kubernetes Job/CronJob. There, a stop request is a
signal followed by a hard deadline: Kubernetes sends SIGTERM, waits `terminationGracePeriodSeconds`, then
SIGKILL. Our wrapper forwards SIGTERM to pg_repack as SIGINT.

With `--jobs > 1`, a stop request during the index phase of a large table can take as long as building all of
its remaining indexes (minutes to hours). If that exceeds the grace period, pg_repack is killed and leaves behind:

- `repack_trigger` on the original table, which keeps logging every change into `repack.log_<oid>`;
- `repack.table_<oid>`, `repack.log_<oid>` and the partially built indexes;
- **orphan `CREATE INDEX` backends that keep running on the server** after the client is gone (until they
  finish), consuming CPU/IO and holding locks on `repack.table_<oid>`.

The data is never at risk, and the next run cleans up the leftovers, but the stop behaviour differs sharply
between `--jobs=1` and `--jobs>1`, which is surprising for operators.

## Environment

- pg_repack 1.5.3 (client and extension), PostgreSQL 15
- run as the table owner with `--no-superuser-check`, `--no-kill-backend`, `--wait-timeout=60`

## Reproduction

A table with two deliberately slow expression indexes, so the index phase lasts about a minute:

```sql
CREATE EXTENSION IF NOT EXISTS pg_repack;
CREATE OR REPLACE FUNCTION slow_key(i bigint) RETURNS bigint
  LANGUAGE plpgsql IMMUTABLE AS $$ BEGIN PERFORM pg_sleep(0.001); RETURN i; END $$;
CREATE TABLE stop_t (id bigint PRIMARY KEY, a int, payload text);
INSERT INTO stop_t SELECT g, g % 1000, repeat(md5(g::text), 30) FROM generate_series(1, 2000000) g;
CREATE INDEX stop_t_slow1 ON stop_t (slow_key(id)) WHERE id <= 60000;
CREATE INDEX stop_t_slow2 ON stop_t (slow_key(id + 1)) WHERE id <= 60000;
DELETE FROM stop_t WHERE id % 2 = 0;
```

```bash
pg_repack --jobs=2 --table=stop_t mydb &
# wait for "LOG: Initial worker ... to build index", then:
kill -INT %1
```

### `--jobs=2`, SIGINT during the index phase

```
12:43:21 NOTICE: Setting up workers.conns
12:43:21 INFO: repacking table "public.stop_t"
12:43:26 LOG: Initial worker 0 to build index: CREATE UNIQUE INDEX index_17350 ON repack.table_17345 USING btree (id)
12:43:26 LOG: Initial worker 1 to build index: CREATE INDEX index_17352 ON repack.table_17345 USING btree (slow_key(id)) WHERE (id <= 60000)
12:43:29 <SIGINT sent>
12:43:30 LOG: Command finished in worker 0: CREATE UNIQUE INDEX index_17350 ...
12:43:30 LOG: Assigning worker 0 to build index #2: CREATE INDEX index_17353 ON repack.table_17345 USING btree (slow_key((id + 1))) WHERE (id <= 60000)
12:44:30 LOG: Command finished in worker 1: CREATE INDEX index_17352 ...
12:44:33 LOG: Command finished in worker 0: CREATE INDEX index_17353 ...
12:44:33 FATAL: interrupted
```

No `Cancel request sent`; a new index is assigned **after** the signal; pg_repack exits 64 s after SIGINT,
when every index is built. Cleanup is complete in this case.

### `--jobs=1`, same table, SIGINT during the index phase

```
12:42:07 INFO: repacking table "public.stop_t"
12:42:17 <SIGINT sent>
12:42:17 WARNING: Cancel request sent
12:42:17 ERROR: query failed: ERROR:  canceling statement due to user request
DETAIL: query was: CREATE INDEX index_17352 ON repack.table_17345 USING btree (slow_key(id)) WHERE (id <= 60000)
```

Exits and cleans up in under a second.

### `--jobs=2` with a hard deadline (SIGINT, then SIGKILL after 20 s)

After the kill, the leftovers listed above remain: `repack_trigger` on `stop_t`, `repack.log_<oid>`,
`repack.table_<oid>`, its indexes, and the worker `CREATE INDEX` backends still active in `pg_stat_activity`.
The next pg_repack run on the table drops the leftovers and repacks it successfully.

### Summary of our runs

| `--jobs` | Phase at SIGINT | Time to exit | Leftovers |
|---|---|---|---|
| 1 | copy | < 1 s | none |
| 1 | index build | < 1 s | none |
| 2 | copy | < 1 s | none |
| 2 | index build | 64 s (= remaining index builds) | none |
| 2 | index build, SIGKILL after 20 s | 20 s | trigger, log table, copy, indexes, orphan `CREATE INDEX` backends |

## Analysis

As the `XXX` comment says, two things are missing for the worker connections:

1. `poll()`/`select()` returning `EINTR` does not break the loop, and the loop does not check `interrupted`,
   so after SIGINT the freed workers get the next indexes;
2. the worker `PGconn`s are not registered for cancellation, so `on_interrupt()` cancels only the main
   connection (which is idle while waiting on workers) and the running worker builds continue.

A related symptom of worker connections being set up differently from the main one: #478 (workers ignore
`--elevel`).

## Expected behaviour

SIGINT with `--jobs > 1` behaves like with `--jobs=1`: cancel the index builds in progress in all worker
connections, stop assigning new ones, run the cleanup and exit — within seconds, regardless of the number and
size of the remaining indexes.

## Possible fix

- in `rebuild_indexes()`, leave the polling loop when `interrupted` is set (and treat `EINTR` accordingly);
- make worker connections cancellable on interrupt (e.g. keep a `PGcancel` per worker connection, or send
  `PQcancel` to every busy worker from the interrupt path), then drain their results before the cleanup.

Happy to test a patch.


^ permalink  raw  [nested|flat] 2+ messages in thread

* Re: [reorg/pg_repack] issue #486: SIGINT does not stop `--jobs` index builds: workers are not cancelled and remaining indexes keep being assigned
@ 2026-11-12 12:38 "za-arthur (@za-arthur)" <noreply+za-arthur@github.com>
  0 siblings, 0 replies; 2+ messages in thread

From: za-arthur (@za-arthur) @ 2026-11-12 12:38 UTC (permalink / raw)
  To: reorg/pg_repack <noreply+reorg-pg_repack@github.com>

@za-arthur closed this.

^ permalink  raw  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2026-11-12 12:38 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-10-13 17:18 [reorg/pg_repack] issue #486: SIGINT does not stop `--jobs` index builds: workers are not cancelled and remaining indexes keep being assigned "chobostar (@chobostar)" <noreply+chobostar@github.com>
2026-11-12 12:38 Re: [reorg/pg_repack] issue #486: SIGINT does not stop `--jobs` index builds: workers are not cancelled and remaining indexes keep being assigned "za-arthur (@za-arthur)" <noreply+za-arthur@github.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox