| 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() |