Re: Add Option To Check All Addresses For Matching target_session_attr

HI!
I just submitted a patch [1] addressing the same problem from a different angle. I wasn't aware of this earlier work until Andrey pointed me to it.
My patch takes a simpler approach: instead of adding a new check_all_addrs parameter, it just changes try_next_host to try_next_addr in the target_session_attrs mismatch handling (~12 lines changed). The rationale being that Artem's point about documentation seems valid - the docs already say "all the hosts and addresses will be tried in order, until one succeeds." The current skip-to-next-host behavior appears to contradict this.
I'm happy to coordinate - the core question seems to be:

1. Behavioral fix (my approach) - aligns with existing documentation, simpler
2. Opt-in parameter (your approach) - preserves backward compatibility explicitly

What do you think? If the community prefers backward compatibility via an explicit option, I could withdraw my patch in favor of yours. If the consensus is that this is actually a bug fix per the docs, perhaps the simpler change is better.
[1] https://www.postgresql.org/message-id/AM9PR09MB49008B02CDF003054D5D4E00977DA@AM9PR09MB4900.eurprd09.prod.outlook.com
Thanks,
Evgeny

________________________________
From: Andrew Jackson <andrewjackson947(at)gmail(dot)com>
Sent: Wednesday, November 20, 2024 3:51 PM
To: pgsql-hackers(at)postgresql(dot)org <pgsql-hackers(at)postgresql(dot)org>
Subject: Add Option To Check All Addresses For Matching target_session_attr

Hi,

I was attempting to set up a high availability system using DNS and target_session_attrs. I was using a DNS setup similar to below and was trying to use the connection strings `psql postgresql://user(at)pg(dot)database(dot)com/db_name?target_session=read-write`<http://user(at)pg(dot)database(dot)com/db_name?target_session=read-write`> to have clients dynamically connect to the primary or `psql postgresql://user(at)pg(dot)database(dot)com/db_name?target_session=read-only`<http://user(at)pg(dot)database(dot)com/db_name?target_session=read-only`> to have clients connect to a read replica.

The problem that I found with this setup is that if libpq is unable to get a matching target_session_attr on the first connection attempt it does not consider any further addresses for the given host. This patch is designed to provide an option that allows libpq to look at additional addresses for a given host if the target_session_attr check fails for previous addresses.

Would appreciate any feedback on the applicability/relevancy of the goal here or the implementation.

Example DNS setup
________________________________
Name | Type | Record
______________|______|___________
pg.database.com<http://pg.database.com> | A | ip_address_1
pg.database.com<http://pg.database.com> | A | ip_address_2
pg.database.com<http://pg.database.com> | A | ip_address_3
pg.database.com<http://pg.database.com> | A | ip_address_4