target_session_attrs=prefer-standby not handled correctly

[AndrewJackson2020]

It seems that target_session_attrs=prefer-standby is not handled by psycopg. I believe a patch similar to the below (or something better) would be needed in order to make it work.

References:

• postgres docs
• libpq implementation

diff --git a/psycopg/psycopg/connection.py b/psycopg/psycopg/connection.py
index d7035e27..c5f8a3c1 100644
--- a/psycopg/psycopg/connection.py
+++ b/psycopg/psycopg/connection.py
@@ -94,7 +94,19 @@ class Connection(BaseConnection[Row]):
         params = cls._get_connection_params(conninfo, **kwargs)
         timeout = timeout_from_conninfo(params)
         rv = None
-        attempts = conninfo_attempts(params)
+
+        if "target_session_attrs" in params and params["target_session_attrs"] == "prefer-standby":
+          params_standby = dict(params)
+          params_primary = dict(params)
+
+          params_standby["target_session_attrs"] = "standby"
+          params_primary["target_session_attrs"] = "primary"
+
+          attempts = conninfo_attempts(params_standby)
+          attempts.extend(conninfo_attempts(params_primary))
+        else:
+          attempts = conninfo_attempts(params)
+
         for attempt in attempts:
             try:
                 conninfo = make_conninfo("", **attempt)
diff --git a/psycopg/psycopg/connection_async.py b/psycopg/psycopg/connection_async.py
index 94a8bf3c..4fa8ba6e 100644
--- a/psycopg/psycopg/connection_async.py
+++ b/psycopg/psycopg/connection_async.py
@@ -110,7 +110,19 @@ class AsyncConnection(BaseConnection[Row]):
         params = await cls._get_connection_params(conninfo, **kwargs)
         timeout = timeout_from_conninfo(params)
         rv = None
-        attempts = await conninfo_attempts_async(params)
+
+        if "target_session_attrs" in params and params["target_session_attrs"] == "prefer-standby":
+          params_standby = dict(params)
+          params_primary = dict(params)
+
+          params_standby["target_session_attrs"] = "standby"
+          params_primary["target_session_attrs"] = "primary"
+
+          attempts = await conninfo_attempts_async(params_standby)
+          attempts.extend(await conninfo_attempts_async(params_primary))
+        else:
+          attempts = await conninfo_attempts_async(params)
+
         for attempt in attempts:
             try:
                 conninfo = make_conninfo("", **attempt)

[dvarrazzo]

Hello, thank you very much for the report. I assume this is another of those features that are not supported in async mode and we need to reimplement...

Did you try if postgres behaves well in the other cases - if it fails an attempt when target_session_attrs doesn't mach the characteristic requested ( i.e. in the read-write, read-only, primary, standby modes)?

The algorithm you suggest seem correct to me - of course I'll play with it and do our own tests before releasing a fix. Did you try it on your side and works for you, or it's just for illustrative purpose?

Cheers!

[AndrewJackson2020]

I ran code similar to this and it worked for me (modified a pip install), copy pasted into blocks into the git source for the diff. Was not able to test it in your unit test framework. Can confirm that target_session_attrs work on standby and primary though I don't see unit test coverage for that in psycopg. Did not test any non existing target_session_attr values though it would be nice if the user got a reasonable error message in that cas le. Can't speak to how it works in async mode. Tested everything synchronously.

Would definitely appreciate a fix.

Thank you!

[dvarrazzo]

Thank you @AndrewJackson2020. There aren't tests for specific modes because my expectation is that the libpq deals with them itself, however I understand that prefer-standby is a special case. Probably I will just unit-test the behaviour at connection attempts level, like for other cases, because setting up a complete replication system in github seems overkilling.

The libpq error message seems reasonable:

$ psql "target_session_attrs=read-only"
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: session is not read-only

[hack hack...]

Can you check if MR #1022 does what expected?