Debian Codegraph @

tests/backends/postgresql/test_server_side_cursors.py

raw
1 import operator
2 import unittest
3 from collections import namedtuple
4 from contextlib import contextmanager
5
6 from django.db import connection, models
7 from django.db.utils import ProgrammingError
8 from django.test import TestCase
9 from django.test.utils import garbage_collect
10 from django.utils.version import PYPY
11
12 from ..models import Person
13
14 try:
15 from django.db.backends.postgresql.psycopg_any import is_psycopg3
16 except ImportError:
17 is_psycopg3 = False
18
19
20 @unittest.skipUnless(connection.vendor == "postgresql", "PostgreSQL tests")
21 class ServerSideCursorsPostgres(TestCase):
22 cursor_fields = (
23 "name, statement, is_holdable, is_binary, is_scrollable, creation_time"
24 )
25 PostgresCursor = namedtuple("PostgresCursor", cursor_fields)
26
27 @classmethod
28 def setUpTestData(cls):
29 cls.p0 = Person.objects.create(first_name="a", last_name="a")
30 cls.p1 = Person.objects.create(first_name="b", last_name="b")
31
32 def inspect_cursors(self):
33 with connection.cursor() as cursor:
34 cursor.execute(
35 "SELECT {fields} FROM pg_cursors;".format(fields=self.cursor_fields)
36 )
37 cursors = cursor.fetchall()
38 return [self.PostgresCursor._make(cursor) for cursor in cursors]
39
40 @contextmanager
41 def override_db_setting(self, **kwargs):
42 for setting in kwargs:
43 original_value = connection.settings_dict.get(setting)
44 if setting in connection.settings_dict:
45 self.addCleanup(
46 operator.setitem, connection.settings_dict, setting, original_value
47 )
48 else:
49 self.addCleanup(operator.delitem, connection.settings_dict, setting)
50
51 connection.settings_dict[setting] = kwargs[setting]
52 yield
53
54 def assertUsesCursor(self, queryset, num_expected=1):
55 next(queryset) # Open a server-side cursor
56 cursors = self.inspect_cursors()
57 self.assertEqual(len(cursors), num_expected)
58 for cursor in cursors:
59 self.assertIn("_django_curs_", cursor.name)
60 self.assertFalse(cursor.is_scrollable)
61 self.assertFalse(cursor.is_holdable)
62 self.assertFalse(cursor.is_binary)
63
64 def assertNotUsesCursor(self, queryset):
65 self.assertUsesCursor(queryset, num_expected=0)
66
67 def test_server_side_cursor(self):
68 self.assertUsesCursor(Person.objects.iterator())
69
70 def test_values(self):
71 self.assertUsesCursor(Person.objects.values("first_name").iterator())
72
73 def test_values_list(self):
74 self.assertUsesCursor(Person.objects.values_list("first_name").iterator())
75
76 def test_values_list_flat(self):
77 self.assertUsesCursor(
78 Person.objects.values_list("first_name", flat=True).iterator()
79 )
80
81 def test_values_list_fields_not_equal_to_names(self):
82 expr = models.Count("id")
83 self.assertUsesCursor(
84 Person.objects.annotate(id__count=expr)
85 .values_list(expr, "id__count")
86 .iterator()
87 )
88
89 def test_server_side_cursor_many_cursors(self):
90 persons = Person.objects.iterator()
91 persons2 = Person.objects.iterator()
92 next(persons) # Open a server-side cursor
93 self.assertUsesCursor(persons2, num_expected=2)
94
95 def test_closed_server_side_cursor(self):
96 persons = Person.objects.iterator()
97 next(persons) # Open a server-side cursor
98 del persons
99 garbage_collect()
100 cursors = self.inspect_cursors()
101 self.assertEqual(len(cursors), 0)
102
103 @unittest.skipIf(
104 PYPY,
105 reason="Cursor not closed properly due to differences in garbage collection.",
106 )
107 def test_server_side_cursors_setting(self):
108 with self.override_db_setting(DISABLE_SERVER_SIDE_CURSORS=False):
109 persons = Person.objects.iterator()
110 self.assertUsesCursor(persons)
111 del persons # Close server-side cursor
112
113 # On PyPy, the cursor is left open here and attempting to force garbage
114 # collection breaks the transaction wrapping the test.
115 with self.override_db_setting(DISABLE_SERVER_SIDE_CURSORS=True):
116 self.assertNotUsesCursor(Person.objects.iterator())
117
118 @unittest.skipUnless(
119 is_psycopg3, "The server_side_binding option is only effective on psycopg >= 3."
120 )
121 def test_server_side_binding(self):
122 """
123 The ORM still generates SQL that is not suitable for usage as prepared
124 statements but psycopg >= 3 defaults to using server-side bindings for
125 server-side cursors which requires some specialized logic when the
126 `server_side_binding` setting is disabled (default).
127 """
128
129 def perform_query():
130 # Generates SQL that is known to be problematic from a server-side
131 # binding perspective as the parametrized ORDER BY clause doesn't
132 # use the same binding parameter as the SELECT clause.
133 qs = (
134 Person.objects.order_by(
135 models.functions.Coalesce("first_name", models.Value(""))
136 )
137 .distinct()
138 .iterator()
139 )
140 self.assertSequenceEqual(list(qs), [self.p0, self.p1])
141
142 with self.override_db_setting(OPTIONS={}):
143 perform_query()
144
145 with self.override_db_setting(OPTIONS={"server_side_binding": False}):
146 perform_query()
147
148 with self.override_db_setting(OPTIONS={"server_side_binding": True}):
149 # This assertion could start failing the moment the ORM generates
150 # SQL suitable for usage as prepared statements (#20516) or if
151 # psycopg >= 3 adapts psycopg.Connection(cursor_factory) machinery
152 # to allow client-side bindings for named cursors. In the first
153 # case this whole test could be removed, in the second one it would
154 # most likely need to be adapted.
155 with self.assertRaises(ProgrammingError):
156 perform_query()