pg.ddx.io  pgbouncer/pgbouncer GitHub issues and pull requests  
help / color / mirror / Atom feed
[pgbouncer/pgbouncer] issue #715: Concurrent use of RESUME with PAUSE makes PAUSE hang permanently
6+ messages / 4 participants
[nested] [flat]

* [pgbouncer/pgbouncer] issue #715: Concurrent use of RESUME with PAUSE makes PAUSE hang permanently
@ 2022-05-06 19:31  "jcoleman (@jcoleman)" <noreply+jcoleman@github.com>
  0 siblings, 0 replies; 6+ messages in thread

From: jcoleman (@jcoleman) @ 2022-05-06 19:31 UTC (permalink / raw)
  To: pgbouncer/pgbouncer <noreply+pgbouncer-pgbouncer@github.com>

I've been improving our setup for using `PAUSE`/`RESUME` on transaction pooled databases to do hitless failovers and started digging into any possible concurrency concerns. To avoid causing issues with an operation like this it's important to be able to time out the `PAUSE` command and automatically `RESUME` if for some reason an unexpected long-running query occurs.

My first question was "which command wins?” Could the `PAUSE` eventually succeed leaving one pgbouncer instance permanently paused?

I opened up 3 different psql sessions:

1. Regular connection to the target database through pgbouncer - on this one I ran `SELECT pg_sleep(30)`
2. Admin connection to the `pgbouncer` database — on this one I ran `PAUSE test_db`
3. Admin connection to the `pgbouncer` database — on this one I ran `RESUME test_db`

Unsurprisingly (2) hung waiting on (1). But (3) actually returned successfully immediately. And then when (1) returned (2) remained blocked — but no databases were in pause mode, and I confirmed I can execute other queries through psql session (1). *Here’s the big issue:* (2) _never_ returned.

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

* Re: [pgbouncer/pgbouncer] issue #715: Concurrent use of RESUME with PAUSE makes PAUSE hang permanently
@ 2022-11-07 15:46  "petere (@petere)" <noreply+petere@github.com>
  4 siblings, 0 replies; 6+ messages in thread

From: petere (@petere) @ 2022-11-07 15:46 UTC (permalink / raw)
  To: pgbouncer/pgbouncer <noreply+pgbouncer-pgbouncer@github.com>

Looking at the implementations of `admin_cmd_pause()` and `admin_cmd_resume()`, it's easy to see how this can happen.

To fix, we might need some locking on the "pause state", or count the pause requests, or something along those lines. Ideas welcome.

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

* Re: [pgbouncer/pgbouncer] issue #715: Concurrent use of RESUME with PAUSE makes PAUSE hang permanently
@ 2022-11-07 15:46  "petere (@petere)" <noreply+petere@github.com>
  4 siblings, 0 replies; 6+ messages in thread

From: petere (@petere) @ 2022-11-07 15:46 UTC (permalink / raw)
  To: pgbouncer/pgbouncer <noreply+pgbouncer-pgbouncer@github.com>

@petere applied label "bug".

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

* Re: [pgbouncer/pgbouncer] issue #715: Concurrent use of RESUME with PAUSE makes PAUSE hang permanently
@ 2026-11-14 16:15  "ritojoao (@ritojoao)" <noreply+ritojoao@github.com>
  4 siblings, 0 replies; 6+ messages in thread

From: ritojoao (@ritojoao) @ 2026-11-14 16:15 UTC (permalink / raw)
  To: pgbouncer/pgbouncer <noreply+pgbouncer-pgbouncer@github.com>

Hi, and thanks for the detailed report.

I was able to reproduce this on 1.26.1 (current master, ac3bb6d) with three psql sessions: session 1 runs `SELECT pg_sleep(8)` on the database, session 2 runs `PAUSE test_db` on the console, and session 3 runs `RESUME test_db`. RESUME returns right away, and after session 1 finishes, session 2 never gets an answer.

From the code, `admin_cmd_pause()` sets `db_paused` and, if the database still has active servers, leaves the admin client with `wait_for_response = true`. The only place that answers it is `admin_pause_done()`, called from `per_loop_maint()` while a pause is in effect. Once `RESUME` clears `db_paused` (or `cf_pause_mode`), that condition is not true anymore, so the waiting admin is never answered.

I tried a small change: `admin_cmd_resume()` also answers any admin client still waiting for a PAUSE with an error, `PAUSE cancelled by RESUME`. It uses a separate flag so clients waiting for WAIT_CLOSE are not touched. With it, session 2 gets that error as soon as RESUME runs. It is about 23 added lines in `admin.c` and `bouncer.h`, and it avoids locking or counting pause requests. It is only a first idea: it wakes every waiting PAUSE on any RESUME, so with two databases paused it would also cancel a waiter whose database is still paused.

I also tried pressing ^C on a waiting PAUSE, on master only. With `PAUSE test_db`, the database stays paused and new clients block. With a global `PAUSE`, new clients can connect, but the psql that ran PAUSE never returned in my test. The change above does not cover ^C, and I have not added a regression test or run the pytest suite yet.

Would you be open to a PR along these lines? I am happy to change the approach if you had something different in mind.


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

* Re: [pgbouncer/pgbouncer] issue #715: Concurrent use of RESUME with PAUSE makes PAUSE hang permanently
@ 2026-11-15 01:08  "cosgroveb (@cosgroveb)" <noreply+cosgroveb@github.com>
  4 siblings, 0 replies; 6+ messages in thread

From: cosgroveb (@cosgroveb) @ 2026-11-15 01:08 UTC (permalink / raw)
  To: pgbouncer/pgbouncer <noreply+pgbouncer-pgbouncer@github.com>

> I also tried pressing ^C on a waiting PAUSE, on master only. With PAUSE test_db, the database stays paused and new clients block. With a global PAUSE, new clients can connect, but the psql that ran PAUSE never returned in my test. The change above does not cover ^C, and I have not added a regression test or run the pytest suite yet.

> Would you be open to a PR along these lines? I am happy to change the approach if you had something different in mind.

Apologies! Your reply reminded me of this issue (a longstanding old thorn in my side) so I PRed a fix without reading your whole comment.

I did not take a stab at fixing the adjacent ^C you're reporting, however.

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

* Re: [pgbouncer/pgbouncer] issue #715: Concurrent use of RESUME with PAUSE makes PAUSE hang permanently
@ 2026-11-15 01:10  "cosgroveb (@cosgroveb)" <noreply+cosgroveb@github.com>
  4 siblings, 0 replies; 6+ messages in thread

From: cosgroveb (@cosgroveb) @ 2026-11-15 01:10 UTC (permalink / raw)
  To: pgbouncer/pgbouncer <noreply+pgbouncer-pgbouncer@github.com>

https://github.com/pgbouncer/pgbouncer/pull/378 looks related to the ^C thing you mentioned, perhaps?

> “Complete the admin CancelRequest circuit. Here we notify the admin client that the command is canceled, so it doesn't hang.”

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


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

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-05-06 19:31 [pgbouncer/pgbouncer] issue #715: Concurrent use of RESUME with PAUSE makes PAUSE hang permanently "jcoleman (@jcoleman)" <noreply+jcoleman@github.com>
2022-11-07 15:46 ` "petere (@petere)" <noreply+petere@github.com>
2022-11-07 15:46 ` "petere (@petere)" <noreply+petere@github.com>
2026-11-14 16:15 ` "ritojoao (@ritojoao)" <noreply+ritojoao@github.com>
2026-11-15 01:08 ` "cosgroveb (@cosgroveb)" <noreply+cosgroveb@github.com>
2026-11-15 01:10 ` "cosgroveb (@cosgroveb)" <noreply+cosgroveb@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