| From: | Colin McDonnell <colinmcd94(at)gmail(dot)com> |
|---|---|
| To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
| Subject: | Anti-wraparound autovacuum stops for good after "no slot available for new autovacuum worker process" |
| Date: | 2026-11-19 16:18:35 |
| Message-ID: | CALdYYtQiRr3euNjK5j3j0D2Mmkdyu7RnE_=ucxAkoBpESK5Kfw@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-bugs |
With autovacuum = off and autovacuum_worker_slots = 1, the autovacuum that
the postmaster starts to prevent XID wraparound can stop for good after
vacuuming one database. The postmaster logs this once:
LOG: no slot available for new autovacuum worker process
After that no autovacuum worker starts again, however far the other
databases go past autovacuum_freeze_max_age, until the server is restarted
or autovacuum is turned on. The documentation for autovacuum says that even
when it is disabled, the system will launch autovacuum processes if
necessary to prevent transaction ID wraparound.
Versions and platform:
- 18.7, Ubuntu package 18.7-1.pgdg24.04+2
- 19.0, built from the REL_19_0 tag
- master at 4280abf884
- Ubuntu 24.04 in Docker, Linux 6.8.0 aarch64, 8 CPUs, 24 GB RAM, glibc 2.39
17.12 (with autovacuum_max_workers = 1 in place of autovacuum_worker_slots)
does not have it.
Reproduction, with initdb, pg_ctl, pgbench and psql on PATH:
#!/bin/sh
set -e
D=/tmp/avdata
initdb -D $D >/dev/null
cat >> $D/postgresql.conf <<'EOF'
autovacuum = off
autovacuum_worker_slots = 1
autovacuum_freeze_max_age = 100000
EOF
pg_ctl -D $D -l $D/log -w start >/dev/null
# Consume XIDs past 131072, the first multiple of 65536 above xidVacLimit,
# where GetNewTransactionId() asks the postmaster for an emergency launcher.
echo 'SELECT txid_current();' > /tmp/xid.sql
pgbench -n -f /tmp/xid.sql -c 4 -j 4 -t 34000 postgres >/dev/null
sleep 60
psql -X -c "SELECT datname, age(datfrozenxid) FROM pg_database ORDER BY 1"
postgres
grep -E 'no slot|could not fork' $D/log || true
Expected: workers vacuum postgres, template1 and template0 in turn, and
every age in the query is below 100000. On 17.12 the three workers finish
within 50 ms of the first launcher starting.
Actual, on the 18.7 package:
datname | age
-----------+--------
postgres | 5522
template0 | 136008
template1 | 136008
(3 rows)
2026-11-19 07:47:41.583 UTC [2926] LOG: no slot available for new
autovacuum worker process
With log_min_messages = debug1 and the backend type in log_line_prefix,
another run ended like this:
07:18:33.384 [3793] autovacuum launcher DEBUG: autovacuum launcher started
07:18:33.387 [3794] autovacuum worker DEBUG: autovacuum: processing
database "postgres"
07:18:33.404 [3794] autovacuum worker DEBUG: transaction ID wrap limit is
2147484391, limited by database with OID 1
07:18:33.404 [3795] autovacuum launcher DEBUG: autovacuum launcher started
07:18:33.405 [3767] postmaster LOG: no slot available for new autovacuum
worker process
Consuming another 70,000 XIDs crosses the next multiple of 65536, so
GetNewTransactionId() starts another emergency launcher. It exits without
starting a worker, and the ages keep growing. Setting autovacuum = on and
reloading recovers, and the new launcher logs:
WARNING: autovacuum worker took too long to start; canceled
It depends on timing. The script above left databases past the limit, with
that LOG line, in 7 of 8 runs on the 18.7 package. A longer version, which
also consumes the 70,000 extra XIDs before turning autovacuum on, gave
these counts (the extra databases were made with CREATE DATABASE before
consuming XIDs):
build databases fsync stalled for good
18.7 package 3 off 6 of 6
18.7 package 303 off 2 of 2
18.7 package 303 on 2 of 2
18.7 package 1003 on 1 of 1
18.7 built from source 3 off 0 of 3
19.0 3 off 0 of 5
19.0 303 off 1 of 2
master 3 off 1 of 6
master 303 off 1 of 2
Adding pg_usleep(100000L) at the end of FreeWorkerInfo(), which only widens
the window described below, makes master and a source build of 18.7 stall
in every run.
Cause:
1. In emergency mode (autovacuum or track_counts off),
AutoVacLauncherMain() calls do_start_worker() and exits. do_start_worker()
takes a WorkerInfo off av_freeWorkers, points av_startingWorker at it, and
sends PMSIGNAL_START_AUTOVAC_WORKER. Only the launcher's main loop retries
a failed start (AutoVacForkFailed) or gives back the WorkerInfo of a worker
that never started ("took too long to start; canceled"), and an emergency
launcher never gets there.
2. When the postmaster can't start the worker, StartAutovacuumWorker()
calls AutoVacWorkerFailed() only while a launcher is running. The emergency
launcher has already exited, so the WorkerInfo stays in av_startingWorker
and nothing gives it back. Every later emergency launcher finds
av_worker_available() false and exits.
3. No fork() has to fail. The worker that just finished calls
SetTransactionIdLimit() from vac_update_datfrozenxid(), which signals for
the next launcher while the worker is still running. The worker then
returns its WorkerInfo in FreeWorkerInfo() and exits. The new launcher can
take that WorkerInfo and signal the postmaster before the postmaster has
reaped the old worker. Since a78af04270 (in 18), autovacuum workers have
their own pool of exactly autovacuum_worker_slots postmaster child slots,
so AssignPostmasterChildSlot() returns NULL and StartChildProcess() logs
"no slot available". In 17 the workers took slots from a shared pool of 2 *
(MaxConnections + autovacuum_max_workers + 1 + max_wal_senders +
max_worker_processes), so an unreaped worker didn't matter.
A real fork() failure in emergency mode leaks the WorkerInfo the same way
in older releases, one slot per failure.
This is close to the race reported in 2024 for the xid_wraparound tests
[1], where the new emergency launcher finds no free WorkerInfo because the
previous worker hasn't exited yet. That launcher exits without leaking
anything, so autovacuum resumes at the next multiple of 65536 XIDs, and
800cd3e923 [2] changed the tests to run with autovacuum on. I see that
variant too, on 17.12 and on 18.7 with the default 16 slots and
autovacuum_max_workers = 1, and it recovers both times.
Suggested fix: the emergency launcher waits for the worker it asked for to
start before exiting, retrying a failed start and timing out a stuck one as
the main loop does. The attached patch for master does that, and moves the
main loop's give-back code into a function both use. Results with the
pg_usleep() above and fsync = off:
build databases stalled for good failed starts, all retried
master 3 3 of 3 -
master + patch 3 0 of 3 2 per run
master + patch 53 0 of 1 52
18.7 built from source 3 2 of 2 -
18.7 built from source + patch 3 0 of 2 0
18.7 built from source + patch 53 0 of 1 1
On master each retry runs as soon as the postmaster reaps the old worker,
since the postmaster wakes the launcher then, and the 53 databases took
about 6 seconds. The patch applies to REL_18_STABLE too, but there the
emergency launcher sets av_launcherpid only after the emergency branch, so
the starting worker can't wake it and the wait relies on its 1 s timeout.
The 53 databases took 54 seconds. A backpatch could set av_launcherpid
before the branch and clear it before the emergency launcher exits.
[1]
https://www.postgresql.org/message-id/5811175c-1a31-4869-032f-7af5e3e4506a@gmail.com
[2] https://postgr.es/m/3e2cbd24-f45e-4b2b-ba83-8149214f0a4d@dunslane.net
| Attachment | Content-Type | Size |
|---|---|---|
| v1-0001-Retry-a-failed-worker-start-in-the-emergency-autova.patch | application/octet-stream | 5.9 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Robert Haas | 2026-11-19 16:55:17 | Re: BUG #19903: ALTER COLUMN TYPE: "unexpected object depending on column" internal error via partition key |
| Previous Message | Fujii Masao | 2026-11-19 15:59:30 | Re: BUG #19594: to_char/jsonpath format cache serves a format tree parsed in the wrong strict-mode |