Fixed #35028 -- Disabled server-side bindings for named cursors on psycopg >= 3.

While we provide a `cursor_factory` based on the value of the
`server_side_bindings` option to `psycopg.Connection` it is ignored by
the `cursor` method when `name` is specified for `QuerySet.iterator()`
usage and it causes the usage of `psycopg.ServerCursor` which performs
server-side bindings.

Since the ORM doesn't generates SQL that is suitable for server-side
bindings when dealing with parametrized expressions a specialized cursor
must be used to allow server-side cursors to be used with client-side
bindings.

Thanks Richard Ebeling for the report.

Thanks Florian Apolloner and Daniele Varrazzo for reviews.

1 files changed, 48 insertions, 2 deletions

diff --git a/tests/backends/postgresql/test_server_side_cursors.py b/tests/backends/postgresql/test_server_side_cursors.py
index 694421b5cb..9a6457cce6 100644
--- a/tests/backends/postgresql/test_server_side_cursors.py
+++ b/tests/backends/postgresql/test_server_side_cursors.py
@@ -4,12 +4,18 @@ from collections import namedtuple
 from contextlib import contextmanager
 
 from django.db import connection, models
+from django.db.utils import ProgrammingError
 from django.test import TestCase
 from django.test.utils import garbage_collect
 from django.utils.version import PYPY
 
 from ..models import Person
 
+try:
+    from django.db.backends.postgresql.psycopg_any import is_psycopg3
+except ImportError:
+    is_psycopg3 = False
+
 
 @unittest.skipUnless(connection.vendor == "postgresql", "PostgreSQL tests")
 class ServerSideCursorsPostgres(TestCase):
@@ -20,8 +26,8 @@ class ServerSideCursorsPostgres(TestCase):
 
     @classmethod
     def setUpTestData(cls):
-        Person.objects.create(first_name="a", last_name="a")
-        Person.objects.create(first_name="b", last_name="b")
+        cls.p0 = Person.objects.create(first_name="a", last_name="a")
+        cls.p1 = Person.objects.create(first_name="b", last_name="b")
 
     def inspect_cursors(self):
         with connection.cursor() as cursor:
@@ -108,3 +114,43 @@ class ServerSideCursorsPostgres(TestCase):
         # collection breaks the transaction wrapping the test.
         with self.override_db_setting(DISABLE_SERVER_SIDE_CURSORS=True):
             self.assertNotUsesCursor(Person.objects.iterator())
+
+    @unittest.skipUnless(
+        is_psycopg3, "The server_side_binding option is only effective on psycopg >= 3."
+    )
+    def test_server_side_binding(self):
+        """
+        The ORM still generates SQL that is not suitable for usage as prepared
+        statements but psycopg >= 3 defaults to using server-side bindings for
+        server-side cursors which requires some specialized logic when the
+        `server_side_binding` setting is disabled (default).
+        """
+
+        def perform_query():
+            # Generates SQL that is known to be problematic from a server-side
+            # binding perspective as the parametrized ORDER BY clause doesn't
+            # use the same binding parameter as the SELECT clause.
+            qs = (
+                Person.objects.order_by(
+                    models.functions.Coalesce("first_name", models.Value(""))
+                )
+                .distinct()
+                .iterator()
+            )
+            self.assertSequenceEqual(list(qs), [self.p0, self.p1])
+
+        with self.override_db_setting(OPTIONS={}):
+            perform_query()
+
+        with self.override_db_setting(OPTIONS={"server_side_binding": False}):
+            perform_query()
+
+        with self.override_db_setting(OPTIONS={"server_side_binding": True}):
+            # This assertion could start failing the moment the ORM generates
+            # SQL suitable for usage as prepared statements (#20516) or if
+            # psycopg >= 3 adapts psycopg.Connection(cursor_factory) machinery
+            # to allow client-side bindings for named cursors. In the first
+            # case this whole test could be removed, in the second one it would
+            # most likely need to be adapted.
+            with self.assertRaises(ProgrammingError):
+                perform_query()