pg.ddx.io  pgbouncer/pgbouncer GitHub issues and pull requests  
help / color / mirror / Atom feed
From: jcoleman (@jcoleman) <noreply+jcoleman@github.com>
To: pgbouncer/pgbouncer <noreply+pgbouncer-pgbouncer@github.com>
Subject: [pgbouncer/pgbouncer] issue #715: Concurrent use of RESUME with PAUSE makes PAUSE hang permanently
Date: Fri, 06 May 2022 19:31:21 +0000
Message-ID: <gh-pgbouncer-pgbouncer-715@github.com> (raw)

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.

view thread (6+ messages)  latest in thread

Message-ID: <gh-pgbouncer-pgbouncer-715@github.com>
Permalink:  ../gh-pgbouncer-pgbouncer-715@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