Merge "repair psycopg2 (and psycopg) multiple hosts format" into main

3 files changed, 55 insertions, 17 deletions

diff --git a/lib/sqlalchemy/dialects/postgresql/_psycopg_common.py b/lib/sqlalchemy/dialects/postgresql/_psycopg_common.py
index 8dcd36c6d..efd1dbe41 100644
--- a/lib/sqlalchemy/dialects/postgresql/_psycopg_common.py
+++ b/lib/sqlalchemy/dialects/postgresql/_psycopg_common.py
@@ -131,20 +131,27 @@ class _PGDialect_common_psycopg(PGDialect):
         if "host" in url.query:
             is_multihost = isinstance(url.query["host"], (list, tuple))
 
-        if opts:
+        if opts or url.query:
+            if not opts:
+                opts = {}
             if "port" in opts:
                 opts["port"] = int(opts["port"])
             opts.update(url.query)
             if is_multihost:
-                opts["host"] = ",".join(url.query["host"])
-            # send individual dbname, user, password, host, port
-            # parameters to psycopg2.connect()
-            return ([], opts)
-        elif url.query:
-            # any other connection arguments, pass directly
-            opts.update(url.query)
-            if is_multihost:
-                opts["host"] = ",".join(url.query["host"])
+                hosts, ports = zip(
+                    *[
+                        token.split(":") if ":" in token else (token, "")
+                        for token in url.query["host"]
+                    ]
+                )
+                opts["host"] = ",".join(hosts)
+                if "port" in opts:
+                    raise exc.ArgumentError(
+                        "Can't mix 'multihost' formats together; use "
+                        '"host=h1,h2,h3&port=p1,p2,p3" or '
+                        '"host=h1:p1&host=h2:p2&host=h3:p3" separately'
+                    )
+                opts["port"] = ",".join(ports)
             return ([], opts)
         else:
             # no connection arguments whatsoever; psycopg2.connect()
diff --git a/lib/sqlalchemy/dialects/postgresql/psycopg.py b/lib/sqlalchemy/dialects/postgresql/psycopg.py
index 90bae61e1..414976a62 100644
--- a/lib/sqlalchemy/dialects/postgresql/psycopg.py
+++ b/lib/sqlalchemy/dialects/postgresql/psycopg.py
@@ -184,6 +184,7 @@ class PGDialect_psycopg(_PGDialect_common_psycopg):
     psycopg_version = (0, 0)
 
     _has_native_hstore = True
+    _psycopg_adapters_map = None
 
     colspecs = util.update_copy(
         _PGDialect_common_psycopg.colspecs,
@@ -241,7 +242,8 @@ class PGDialect_psycopg(_PGDialect_common_psycopg):
         # see https://github.com/psycopg/psycopg/issues/83
         cargs, cparams = super().create_connect_args(url)
 
-        cparams["context"] = self._psycopg_adapters_map
+        if self._psycopg_adapters_map:
+            cparams["context"] = self._psycopg_adapters_map
         if self.client_encoding is not None:
             cparams["client_encoding"] = self.client_encoding
         return cargs, cparams
diff --git a/lib/sqlalchemy/dialects/postgresql/psycopg2.py b/lib/sqlalchemy/dialects/postgresql/psycopg2.py
index 2fe1ee15b..6f78dafdd 100644
--- a/lib/sqlalchemy/dialects/postgresql/psycopg2.py
+++ b/lib/sqlalchemy/dialects/postgresql/psycopg2.py
@@ -117,22 +117,51 @@ Specifying multiple fallback hosts
 psycopg2 supports multiple connection points in the connection string.
 When the ``host`` parameter is used multiple times in the query section of
 the URL, SQLAlchemy will create a single string of the host and port
-information provided to make the connections::
+information provided to make the connections.  Tokens may consist of
+``host::port`` or just ``host``; in the latter case, the default port
+is selected by libpq.  In the example below, three host connections
+are specified, for ``HostA::PortA``, ``HostB`` connecting to the default port,
+and ``HostC::PortC``::
 
     create_engine(
-        "postgresql+psycopg2://user:password@/dbname?host=HostA:port1&host=HostB&host=HostC"
+        "postgresql+psycopg2://user:password@/dbname?host=HostA:PortA&host=HostB&host=HostC:PortC"
     )
 
-A connection to each host is then attempted until either a connection is successful
-or all connections are unsuccessful in which case an error is raised.
+As an alternative, libpq query string format also may be used; this specifies
+``host`` and ``port`` as single query string arguments with comma-separated
+lists - the default port can be chosen by indicating an empty value
+in the comma separated list::
+
+    create_engine(
+        "postgresql+psycopg2://user:password@/dbname?host=HostA,HostB,HostC&port=PortA,,PortC"
+    )
+
+With either URL style, connections to each host is attempted based on a
+configurable strategy, which may be configured using the libpq
+``target_session_attrs`` parameter.  Per libpq this defaults to ``any``
+which indicates a connection to each host is then attempted until a connection is successful.
+Other strategies include ``primary``, ``prefer-standby``, etc.  The complete
+list is documented by PostgreSQL at
+`libpq connection strings <https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING>`_.
+
+For example, to indicate two hosts using the ``primary`` strategy::
+
+    create_engine(
+        "postgresql+psycopg2://user:password@/dbname?host=HostA:PortA&host=HostB&host=HostC:PortC&target_session_attrs=primary"
+    )
+
+.. versionchanged:: 1.4.40 Port specification in psycopg2 multiple host format
+   is repaired, previously ports were not correctly interpreted in this context.
+   libpq comma-separated format is also now supported.
 
 .. versionadded:: 1.3.20 Support for multiple hosts in PostgreSQL connection
    string.
 
 .. seealso::
 
-    `PQConnString \
-    <https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING>`_
+    `libpq connection strings <https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING>`_ - please refer
+    to this section in the libpq documentation for complete background on multiple host support.
+
 
 Empty DSN Connections / Environment Variable Connections
 ---------------------------------------------------------