Describe the bug
(Related to the testing I mentioned #2171 but this appears to be a straightforward bug.)
When connecting with a target_session_attrs preference that cannot be satisfied and the final host times out, the connection to the non-preferred host is not attempted and falsely times out.
To Reproduce
Create a connection string with two hosts and a preference for the second; e.g. a primary and standby with target_session_attrs=prefer-standby
Block network traffic from the client to the standby.
Configure a connection timeout for e.g. 1 second.
Execute a query.
Expected behavior
After 1 second the connection to the standby times out, the primary is chosen instead, and the query executes.
Actual behavior
After 1 second the query returns a timeout.
Version
go version go1.23.2 linux/amd64
PostgreSQL: PostgreSQL 14.13 (Ubuntu 14.13-1.pgdg22.04+1) on aarch64-unknown-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit
pgx: v5.7.1
Additional context
From my understanding of the code, connectPreferred resets the timeout for each new host as it iterates the possible targets. However, once it's decided to fall back to fallbackConnectOneConfig, it reuses whatever the last ctx it had from the initial pass was. If this had timed out, the new connection will start in a timed-out state. It should instead probably iterate the entire list again with fresh contexts based on octx. Or, as an optimization, the first non-preferred connection could be returned rather than the config and returned directly if no other preferred one is found / closed if a preferred one is found.
Describe the bug (Related to the testing I mentioned #2171 but this appears to be a straightforward bug.)
When connecting with a
target_session_attrspreference that cannot be satisfied and the final host times out, the connection to the non-preferred host is not attempted and falsely times out.To Reproduce
target_session_attrs=prefer-standbyExpected behavior After 1 second the connection to the standby times out, the primary is chosen instead, and the query executes.
Actual behavior After 1 second the query returns a timeout.
Version
Additional context From my understanding of the code,
connectPreferredresets the timeout for each new host as it iterates the possible targets. However, once it's decided to fall back tofallbackConnectOneConfig, it reuses whatever the lastctxit had from the initial pass was. If this had timed out, the new connection will start in a timed-out state. It should instead probably iterate the entire list again with fresh contexts based onoctx. Or, as an optimization, the first non-preferred connection could be returned rather than the config and returned directly if no other preferred one is found / closed if a preferred one is found.