pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
BUG #19755: Server-wide hang: DROP DATABASE waits forever for ProcSignalBarrier while VACUUM waits on pg_databas
2+ messages / 2 participants
[nested] [flat]

* BUG #19755: Server-wide hang: DROP DATABASE waits forever for ProcSignalBarrier while VACUUM waits on pg_databas
@ 2026-10-12 12:25  PG Bug reporting form <noreply@postgresql.org>
  0 siblings, 1 reply; 2+ messages in thread

From: PG Bug reporting form @ 2026-10-12 12:25 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org; +Cc: anncalla@163.com

The following bug has been logged on the website:

Bug reference:      19755
Logged by:          Ann Calla
Email address:      anncalla@163.com
PostgreSQL version: 18.6
Operating system:   Ubuntu 24.04 x86_64
Description:        

Description
-----------

With several sessions concurrently running DROP DATABASE / CREATE DATABASE
and database-wide VACUUM FULL in other databases, the server can enter a
state that it never leaves:

  * DROP DATABASE logs, every 5 seconds and indefinitely,
      LOG:  still waiting for backend with PID 1770 to accept
ProcSignalBarrier
      STATEMENT:  DROP DATABASE IF EXISTS sN
  * the backend it names is a VACUUM that is waiting for a heavyweight lock
    ("VACUUM waiting" in the process title);
  * every other VACUUM / ANALYZE / CREATE DATABASE, the autovacuum launcher
    and all new connections ("startup waiting") queue up behind it, so the
    server accepts no new sessions;
  * no "deadlock detected" error is raised;
  * the named VACUUM backend does not react to SIGTERM
    (pg_terminate_backend equivalent) or to a manual SIGUSR1.

Only a server restart recovers. We first hit this with an automated test
workload (8 client sessions, one database per session); the server stayed
in this state for more than 24 hours until it was restarted.

How to reproduce
----------------

On a freshly initialised cluster, run the attached repro.sh inside the
server container (it only needs psql and a trusted local superuser):

    docker run -d --name pg -e POSTGRES_HOST_AUTH_METHOD=trust
postgres:latest
    docker cp repro.sh pg:/repro.sh
    docker exec -u postgres pg bash /repro.sh 300
    docker logs pg 2>&1 | grep ProcSignalBarrier

The script starts 12 sessions. Each one loops over:

    DROP DATABASE IF EXISTS sN;  CREATE DATABASE sN;     -- connected to
"test"
    -- then, connected to sN: a few CREATE TABLE / INSERT statements,
    CREATE TEMP VIEW ...; ANALYZE; TRUNCATE t3; DISCARD TEMP;
    VACUUM FULL;
    VACUUM;

On a fresh cluster the hang appeared within two minutes in 2 of 2 runs.
It is timing dependent: on a cluster that had already been through a
restart, two runs of 4 and 9 minutes did not hang. The same workload with
"VACUUM FULL t0; VACUUM FULL t1; ..." (table-level) instead of the
database-wide "VACUUM FULL" ran for 6 minutes without hanging.

Stack traces
------------

Taken with gdb from the hung server (no debug symbols; full output of all
backends is attached as stacks.txt).

The DROP DATABASE backend:

    epoll_wait <- WaitEventSetWait <- WaitLatch <-
ConditionVariableTimedSleep
    <- WaitForProcSignalBarrier <- dropdb <- standard_ProcessUtility

The backend it is waiting for (PID 1770), and five other VACUUM backends:

    epoll_wait <- WaitEventSetWait <- WaitLatch <- ProcSleep
    <- LockAcquireExtended <- LockRelationOid <- relation_open <- table_open
    <- vac_update_datfrozenxid <- vacuum <- ExecVacuum

Four further VACUUM backends:

    ... ProcSleep <- LockAcquireExtended <- LockRelationOid
    <- try_relation_open <- vacuum_open_relation <- vacuum <- ExecVacuum

CREATE DATABASE:

    ... ProcSleep <- LockAcquireExtended <- LockRelationOid <- relation_open
    <- table_open <- createdb

Analysis (from reading the source; not confirmed with a debug build)
---------------------------------------------------------------------

vac_update_datfrozenxid() calls vac_truncate_clog(), which is static and
appears to be inlined in this build. vac_truncate_clog() does

    LWLockAcquire(WrapLimitsVacuumLock, LW_EXCLUSIVE);
    ...
    relation = table_open(DatabaseRelationId, AccessShareLock);

so the heavyweight lock on pg_database is requested while an LWLock is
held, i.e. with interrupts held off. If that request has to wait, the
backend can neither absorb a ProcSignalBarrier nor be cancelled or
terminated. That would give this cycle, which the deadlock detector cannot
see because one edge is not a heavyweight lock:

    A: DROP DATABASE   holds RowExclusiveLock on pg_database,
                       waits in WaitForProcSignalBarrier() for B
    B: VACUUM          holds WrapLimitsVacuumLock (interrupts held off),
                       waits for AccessShareLock on pg_database behind C
    C: VACUUM FULL     (database-wide, now processing pg_database)
                       waits for AccessExclusiveLock on pg_database,
                       blocked by A

This matches the observations that the named backend ignores SIGTERM and
that the hang needs a database-wide VACUUM FULL, the only statement in the
workload that requests AccessExclusiveLock on pg_database.

Expected behaviour
------------------

DROP DATABASE should complete, or one of the participants should fail with
an error. A lock wait on pg_database should not be uninterruptible.








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

* Re: BUG #19755: Server-wide hang: DROP DATABASE waits forever for ProcSignalBarrier while VACUUM waits on pg_databas
@ 2026-10-15 04:31  shihao zhong <zhong950419@gmail.com>
  parent: PG Bug reporting form <noreply@postgresql.org>
  0 siblings, 0 replies; 2+ messages in thread

From: shihao zhong @ 2026-10-15 04:31 UTC (permalink / raw)
  To: anncalla@163.com; pgsql-bugs@lists.postgresql.org

Hi Ann,

Thanks for filing the bug. Surprisingly we still have that kind of 3 way
deadloads.
I guess this is because not a lots of people running these command together.

You don't need a restart to get out, though. Canceling the VACUUM FULL
or the DROP DATABASE from a session that is already connected breaks
the cycle, and so does kill -INT on one of them from the shell.

The attached patch locks pg_database before taking
WrapLimitsVacuumLock. The scan of pg_database still runs under the
LWLock, so the race fixed by 566372b3d64 stays fixed.

Thanks,
Shihao

Attachments:

v1-0001-Lock-pg_database-before-WrapLimitsVacuumLock-in-v.patch (application/octet-stream, 1.9K) download From 176e8fbfcb460941e74e0aed3bfe06bea831c336 Mon Sep 17 00:00:00 2001 From: shihao zhong <zhong950419@gmail.com> Date: Thu, 15 Oct 2026 00:11:16 -0400 Subject: [PATCH v1] Lock pg_database before WrapLimitsVacuumLock in vac_truncate_clog vac_truncate_clog() locked pg_database while holding WrapLimitsVacuumLock. If that lock had to wait, the backend could not be canceled and could not absorb a ProcSignalBarrier. Together with DROP DATABASE and VACUUM FULL of pg_database this formed a cycle the deadlock detector cannot see, and new connections hung. Lock pg_database first. The scan stays under the LWLock. Reported-by: Ann Calla <anncalla@163.com> Discussion: https://postgr.es/m/19755-6ffb2ecb4e3fd782@postgresql.org Backpatch-through: 14 --- src/backend/commands/vacuum.c | 8 ++++++-- 1 file changed, 6 insertions(+), 2 deletions(-) diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c index a257dd8d21e..ce7c5f70288 100644 --- a/src/backend/commands/vacuum.c +++ b/src/backend/commands/vacuum.c @@ -1866,6 +1866,12 @@ vac_truncate_clog(TransactionId frozenXID, bool bogus = false; bool frozenAlreadyWrapped = false; + /* + * Lock pg_database before the LWLock. A lock wait while holding an + * LWLock could neither be canceled nor absorb a ProcSignalBarrier. + */ + relation = table_open(DatabaseRelationId, AccessShareLock); + /* Restrict task to one backend per cluster; see SimpleLruTruncate(). */ LWLockAcquire(WrapLimitsVacuumLock, LW_EXCLUSIVE); @@ -1891,8 +1897,6 @@ vac_truncate_clog(TransactionId frozenXID, * worst possible outcome is that pg_xact is not truncated as aggressively * as it could be. */ - relation = table_open(DatabaseRelationId, AccessShareLock); - scan = table_beginscan_catalog(relation, 0, NULL); while ((tuple = heap_getnext(scan, ForwardScanDirection)) != NULL) -- 2.37.1 (Apple Git-137.1)
^ permalink raw reply [nested|flat] 2+ messages in thread


end of thread, other threads:[~2026-10-15 04:31 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-10-12 12:25 BUG #19755: Server-wide hang: DROP DATABASE waits forever for ProcSignalBarrier while VACUUM waits on pg_databas PG Bug reporting form <noreply@postgresql.org>
2026-10-15 04:31 ` shihao zhong <zhong950419@gmail.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