pg.ddx.io  pgbouncer/pgbouncer GitHub issues and pull requests  
help / color / mirror / Atom feed
[pgbouncer/pgbouncer] PR #1703: Send cancellations to the query's backend
5+ messages / 2 participants
[nested] [flat]

* [pgbouncer/pgbouncer] PR #1703: Send cancellations to the query's backend
@ 2026-11-13 05:22 "cosgroveb (@cosgroveb)" <noreply+cosgroveb@github.com>
  0 siblings, 0 replies; 5+ messages in thread

From: cosgroveb (@cosgroveb) @ 2026-11-13 05:22 UTC (permalink / raw)
  To: pgbouncer/pgbouncer <noreply+pgbouncer-pgbouncer@github.com>

Cancel connections selected a new host from the configured list and could send the key to a different backend. Reuse the query server's address for the cancellation connection.

Add a two-host regression test that cancels a running query. Fixes #1702.

inline patch 1: src/objects.c, test/test.ini, test/test_cancel.py (+73/-0 lines) download inline-1.patchdiff --git a/src/objects.c b/src/objects.c index 666c72d6a013..a39253342bea 100644 --- a/src/objects.c +++ b/src/objects.c @@ -1780,6 +1780,26 @@ static void dns_connect(struct PgSocket *server) free(host_copy); } +static void connect_cancel_server(PgSocket *server, const PgSocket *canceled_server) +{ + const PgAddr *addr = &canceled_server->remote_addr; + int salen; + + if (pga_family(addr) == AF_INET) { + salen = sizeof(addr->sin); + } else if (pga_family(addr) == AF_INET6) { + salen = sizeof(addr->sin6); + } else { + dns_connect(server); + return; + } + + if (canceled_server->host) + server->host = xstrdup(canceled_server->host); + + connect_server(server, &addr->sa, salen); +} + PgSocket *compare_connections_by_time(PgSocket *lhs, PgSocket *rhs) { if (!lhs) @@ -2005,6 +2025,16 @@ void launch_new_connection(PgPool *pool, bool evict_if_needed) if (pool->user_credentials) pool->user_credentials->global_user->connection_count++; + if (!pool->db->peer_id && !statlist_empty(&pool->waiting_cancel_req_list)) { + PgSocket *req = first_socket(&pool->waiting_cancel_req_list); + + if (req->canceled_server) { + /* A cancel key is valid only on the server that supplied it. */ + connect_cancel_server(server, req->canceled_server); + return; + } + } + dns_connect(server); } diff --git a/test/test.ini b/test/test.ini index 5899174d5ade..982a2d32ef66 100644 --- a/test/test.ini +++ b/test/test.ini @@ -40,6 +40,7 @@ authdb = port=6666 host=127.0.0.1 dbname=p1 auth_user=pswcheck hostlist1 = port=6666 host=127.0.0.1,::1 dbname=p0 user=bouncer hostlist2 = port=6666 host=127.0.0.1,127.0.0.1 dbname=p0 user=bouncer +cancel_hostlist = port=6666 host=127.0.0.1,127.0.0.2 dbname=p0 user=bouncer pool_mode=session hostlist_good_first = port=6666 host=127.0.0.1,127.0.0.3 dbname=p0 user=bouncer load_balance_hosts=disable hostlist_bad_first = port=6666 host=unresolvable-hostname,127.0.0.1 dbname=p0 user=bouncer load_balance_hosts=disable load_balance_hosts_update = port=6666 host=127.0.0.1,127.0.0.3 dbname=p0 user=bouncer load_balance_hosts=disable diff --git a/test/test_cancel.py b/test/test_cancel.py index 16014007b4b4..090f8b4d7f4e 100644 --- a/test/test_cancel.py +++ b/test/test_cancel.py @@ -4,6 +4,27 @@ import psycopg import pytest +from .utils import USE_UNIX_SOCKETS, Postgres, wait_until + + +@pytest.fixture +def second_postgres(pg, tmp_path): + other = Postgres(tmp_path / "second_pgdata") + other.initdb() + other.host = "127.0.0.2" + other.port = pg.port + with other.conf_path.open("a") as pgconf: + pgconf.write("listen_addresses = '127.0.0.2'\n") + if USE_UNIX_SOCKETS: + pgconf.write("unix_socket_directories = ''\n") + other.nossl_access("all", "trust") + other.commit_hba() + other.start() + other.sql("CREATE DATABASE p0") + other.sql("CREATE USER bouncer") + yield other + other.cleanup() + def test_cancel(bouncer): with bouncer.cur(dbname="p3") as cur, ThreadPoolExecutor(max_workers=2) as pool: @@ -17,6 +38,27 @@ def test_cancel(bouncer): query.result() +def test_cancel_uses_query_backend_host(bouncer, pg, second_postgres): + with ( + bouncer.cur(dbname="cancel_hostlist") as cur, + ThreadPoolExecutor(max_workers=2) as pool, + ): + query = pool.submit(cur.execute, "SELECT pg_sleep(2)") + + for _ in wait_until("query did not reach the first host"): + sleeping = pg.sql_value(""" + SELECT count(*) FROM pg_stat_activity + WHERE usename = 'bouncer' AND datname = 'p0' + AND state = 'active' AND wait_event = 'PgSleep' + """) + if sleeping == 1: + break + + pool.submit(cur.connection.cancel).result() + with pytest.raises(psycopg.errors.QueryCanceled, match="due to user request"): + query.result() + + # Test for waiting connections handling for cancel requests. # # The bug fixed by GH PR #542 was: When the connection pool is full,
^ permalink raw [nested|flat] 5+ messages in thread

* Re: [pgbouncer/pgbouncer] PR #1703: Fix cancellation with host lists
@ 2026-11-13 14:27 ` "cosgroveb (@cosgroveb)" <noreply+cosgroveb@github.com>
  3 siblings, 0 replies; 5+ messages in thread

From: cosgroveb (@cosgroveb) @ 2026-11-13 14:27 UTC (permalink / raw)
  To: pgbouncer/pgbouncer <noreply+pgbouncer-pgbouncer@github.com>

An extra pg server is a heavy handed test infra but I almost always strongly prefer a true integration test wherever reasonable.

I am happy to cover with a mock instead if that's preferred ❤️

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

* Re: [pgbouncer/pgbouncer] PR #1703: Send cancellations to the query's backend
@ 2026-11-15 02:38 ` "cosgroveb (@cosgroveb)" <noreply+cosgroveb@github.com>
  3 siblings, 0 replies; 5+ messages in thread

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

@cosgroveb renamed this.

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

* Re: [pgbouncer/pgbouncer] PR #1703: Send cancellations to the query's backend
@ 2026-11-18 19:09 ` "ritojoao (@ritojoao)" <noreply+ritojoao@github.com>
  3 siblings, 0 replies; 5+ messages in thread

From: ritojoao (@ritojoao) @ 2026-11-18 19:09 UTC (permalink / raw)
  To: pgbouncer/pgbouncer <noreply+pgbouncer-pgbouncer@github.com>

Hi, and thank you for working on this. I tested the head of this PR (dc3eeff, which includes cef2802) as a user, and I wanted to share what I saw in case it helps.

Setup: two PostgreSQL 17 servers on 127.0.0.1 and 127.0.0.2, session pool, a query with `pg_sleep(3)`, and a cancel after 1 second. I ran 6 iterations per case, with my own small script.

Cancelled queries out of 6:
- `host=A,B` with round-robin: base 3/6, this PR 6/6
- `host=A,B` with `load_balance_hosts=disable`: 6/6 on all
- single host: 6/6 on all

So this PR fixes the problem from #1702 in my test. The separate change I proposed in #1710 gives the same numbers in these three cases, so I am happy if you prefer your approach, which also covers the case where the cancel must reach the exact backend address.

I also tried a rough script for the case where the first cancel in the queue leaves before the login ends (two clients on different servers, the first cancel closes its socket right away). I got 6/6 on this PR and 6/6 on my change, so I could not show a difference. I am not sure my script really hits that timing on localhost, so please treat it as not proven either way.

Test suite on this PR: `make -C test check` passed. pytest gave 287 passed and 8 skipped. Two failures in test_misc.py (`test_server_check_query_default_negative` and `test_server_check_query_negative`) also fail on the base commit in my environment. `test_prepared_failed_prepare_pipeline` failed once in the full run and passed 3 of 3 when run alone, so it may be flaky, but I only ran the full suite once on this PR.

What I could not check: the two new tests `test_cancel_uses_query_backend_host` and `test_cancel_not_forwarded_to_other_host` did not run in my container, because the second PostgreSQL on 127.0.0.2 could not bind its port ("Address already in use"). This is probably a problem with my setup and not with the PR, but I did not confirm it. I also did not test peering, TLS, IPv6, a host removed by reload, or a down host.

Thanks again, and please tell me if I should run anything else.


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

* Re: [pgbouncer/pgbouncer] PR #1703: Send cancellations to the query's backend
@ 2026-11-20 02:46 ` "cosgroveb (@cosgroveb)" <noreply+cosgroveb@github.com>
  3 siblings, 0 replies; 5+ messages in thread

From: cosgroveb (@cosgroveb) @ 2026-11-20 02:46 UTC (permalink / raw)
  To: pgbouncer/pgbouncer <noreply+pgbouncer-pgbouncer@github.com>

OK, this PR now also changes the following:

- New connections to other hosts skip the cancel
- It adaps Unix socket fix from @ritojoao's #1710
- macOS tests sudo-alias 127.0.0.2 on lo0


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


end of thread, other threads:[~2026-11-20 02:46 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-11-13 05:22 [pgbouncer/pgbouncer] PR #1703: Send cancellations to the query's backend "cosgroveb (@cosgroveb)" <noreply+cosgroveb@github.com>
2026-11-13 14:27 ` Re: [pgbouncer/pgbouncer] PR #1703: Fix cancellation with host lists "cosgroveb (@cosgroveb)" <noreply+cosgroveb@github.com>
2026-11-15 02:38 ` "cosgroveb (@cosgroveb)" <noreply+cosgroveb@github.com>
2026-11-18 19:09 ` "ritojoao (@ritojoao)" <noreply+ritojoao@github.com>
2026-11-20 02:46 ` "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