Debian Codegraph @

tests/backends/postgresql/tests.py

raw
1 import copy
2 import unittest
3 from io import StringIO
4 from unittest import mock
5
6 from django.core.exceptions import ImproperlyConfigured
7 from django.db import (
8 DEFAULT_DB_ALIAS,
9 DatabaseError,
10 NotSupportedError,
11 ProgrammingError,
12 connection,
13 connections,
14 )
15 from django.db.backends.base.base import BaseDatabaseWrapper
16 from django.test import TestCase, override_settings
17
18 try:
19 from django.db.backends.postgresql.psycopg_any import errors, is_psycopg3
20 except ImportError:
21 is_psycopg3 = False
22
23
24 def no_pool_connection(alias=None):
25 new_connection = connection.copy(alias)
26 new_connection.settings_dict = copy.deepcopy(connection.settings_dict)
27 # Ensure that the second connection circumvents the pool, this is kind
28 # of a hack, but we cannot easily change the pool connections.
29 new_connection.settings_dict["OPTIONS"]["pool"] = False
30 return new_connection
31
32
33 @unittest.skipUnless(connection.vendor == "postgresql", "PostgreSQL tests")
34 class Tests(TestCase):
35 databases = {"default", "other"}
36
37 def test_nodb_cursor(self):
38 """
39 The _nodb_cursor() fallbacks to the default connection database when
40 access to the 'postgres' database is not granted.
41 """
42 orig_connect = BaseDatabaseWrapper.connect
43
44 def mocked_connect(self):
45 if self.settings_dict["NAME"] is None:
46 raise DatabaseError()
47 return orig_connect(self)
48
49 with connection._nodb_cursor() as cursor:
50 self.assertIs(cursor.closed, False)
51 self.assertIsNotNone(cursor.db.connection)
52 self.assertIsNone(cursor.db.settings_dict["NAME"])
53 self.assertIs(cursor.closed, True)
54 self.assertIsNone(cursor.db.connection)
55
56 # Now assume the 'postgres' db isn't available
57 msg = (
58 "Normally Django will use a connection to the 'postgres' database "
59 "to avoid running initialization queries against the production "
60 "database when it's not needed (for example, when running tests). "
61 "Django was unable to create a connection to the 'postgres' "
62 "database and will use the first PostgreSQL database instead."
63 )
64 with self.assertWarnsMessage(RuntimeWarning, msg):
65 with mock.patch(
66 "django.db.backends.base.base.BaseDatabaseWrapper.connect",
67 side_effect=mocked_connect,
68 autospec=True,
69 ):
70 with mock.patch.object(
71 connection,
72 "settings_dict",
73 {**connection.settings_dict, "NAME": "postgres"},
74 ):
75 with connection._nodb_cursor() as cursor:
76 self.assertIs(cursor.closed, False)
77 self.assertIsNotNone(cursor.db.connection)
78 self.assertIs(cursor.closed, True)
79 self.assertIsNone(cursor.db.connection)
80 self.assertIsNotNone(cursor.db.settings_dict["NAME"])
81 self.assertEqual(
82 cursor.db.settings_dict["NAME"], connections["other"].settings_dict["NAME"]
83 )
84 # Cursor is yielded only for the first PostgreSQL database.
85 with self.assertWarnsMessage(RuntimeWarning, msg):
86 with mock.patch(
87 "django.db.backends.base.base.BaseDatabaseWrapper.connect",
88 side_effect=mocked_connect,
89 autospec=True,
90 ):
91 with connection._nodb_cursor() as cursor:
92 self.assertIs(cursor.closed, False)
93 self.assertIsNotNone(cursor.db.connection)
94
95 def test_nodb_cursor_raises_postgres_authentication_failure(self):
96 """
97 _nodb_cursor() re-raises authentication failure to the 'postgres' db
98 when other connection to the PostgreSQL database isn't available.
99 """
100
101 def mocked_connect(self):
102 raise DatabaseError()
103
104 def mocked_all(self):
105 test_connection = copy.copy(connections[DEFAULT_DB_ALIAS])
106 test_connection.settings_dict = copy.deepcopy(connection.settings_dict)
107 test_connection.settings_dict["NAME"] = "postgres"
108 return [test_connection]
109
110 msg = (
111 "Normally Django will use a connection to the 'postgres' database "
112 "to avoid running initialization queries against the production "
113 "database when it's not needed (for example, when running tests). "
114 "Django was unable to create a connection to the 'postgres' "
115 "database and will use the first PostgreSQL database instead."
116 )
117 with self.assertWarnsMessage(RuntimeWarning, msg):
118 mocker_connections_all = mock.patch(
119 "django.utils.connection.BaseConnectionHandler.all",
120 side_effect=mocked_all,
121 autospec=True,
122 )
123 mocker_connect = mock.patch(
124 "django.db.backends.base.base.BaseDatabaseWrapper.connect",
125 side_effect=mocked_connect,
126 autospec=True,
127 )
128 with mocker_connections_all, mocker_connect:
129 with self.assertRaises(DatabaseError):
130 with connection._nodb_cursor():
131 pass
132
133 def test_nodb_cursor_reraise_exceptions(self):
134 with self.assertRaisesMessage(DatabaseError, "exception"):
135 with connection._nodb_cursor():
136 raise DatabaseError("exception")
137
138 def test_database_name_too_long(self):
139 from django.db.backends.postgresql.base import DatabaseWrapper
140
141 settings = connection.settings_dict.copy()
142 max_name_length = connection.ops.max_name_length()
143 settings["NAME"] = "a" + (max_name_length * "a")
144 msg = (
145 "The database name '%s' (%d characters) is longer than "
146 "PostgreSQL's limit of %s characters. Supply a shorter NAME in "
147 "settings.DATABASES."
148 ) % (settings["NAME"], max_name_length + 1, max_name_length)
149 with self.assertRaisesMessage(ImproperlyConfigured, msg):
150 DatabaseWrapper(settings).get_connection_params()
151
152 def test_database_name_empty(self):
153 from django.db.backends.postgresql.base import DatabaseWrapper
154
155 settings = connection.settings_dict.copy()
156 settings["NAME"] = ""
157 msg = (
158 "settings.DATABASES is improperly configured. Please supply the "
159 "NAME or OPTIONS['service'] value."
160 )
161 with self.assertRaisesMessage(ImproperlyConfigured, msg):
162 DatabaseWrapper(settings).get_connection_params()
163
164 def test_service_name(self):
165 from django.db.backends.postgresql.base import DatabaseWrapper
166
167 settings = connection.settings_dict.copy()
168 settings["OPTIONS"] = {"service": "my_service"}
169 settings["NAME"] = ""
170 params = DatabaseWrapper(settings).get_connection_params()
171 self.assertEqual(params["service"], "my_service")
172 self.assertNotIn("database", params)
173
174 def test_service_name_default_db(self):
175 # None is used to connect to the default 'postgres' db.
176 from django.db.backends.postgresql.base import DatabaseWrapper
177
178 settings = connection.settings_dict.copy()
179 settings["NAME"] = None
180 settings["OPTIONS"] = {"service": "django_test"}
181 params = DatabaseWrapper(settings).get_connection_params()
182 self.assertEqual(params["dbname"], "postgres")
183 self.assertNotIn("service", params)
184
185 def test_connect_and_rollback(self):
186 """
187 PostgreSQL shouldn't roll back SET TIME ZONE, even if the first
188 transaction is rolled back (#17062).
189 """
190 new_connection = no_pool_connection()
191 try:
192 # Ensure the database default time zone is different than
193 # the time zone in new_connection.settings_dict. We can
194 # get the default time zone by reset & show.
195 with new_connection.cursor() as cursor:
196 cursor.execute("RESET TIMEZONE")
197 cursor.execute("SHOW TIMEZONE")
198 db_default_tz = cursor.fetchone()[0]
199 new_tz = "Europe/Paris" if db_default_tz == "UTC" else "UTC"
200 new_connection.close()
201
202 # Invalidate timezone name cache, because the setting_changed
203 # handler cannot know about new_connection.
204 del new_connection.timezone_name
205
206 # Fetch a new connection with the new_tz as default
207 # time zone, run a query and rollback.
208 with self.settings(TIME_ZONE=new_tz):
209 new_connection.set_autocommit(False)
210 new_connection.rollback()
211
212 # Now let's see if the rollback rolled back the SET TIME ZONE.
213 with new_connection.cursor() as cursor:
214 cursor.execute("SHOW TIMEZONE")
215 tz = cursor.fetchone()[0]
216 self.assertEqual(new_tz, tz)
217
218 finally:
219 new_connection.close()
220
221 def test_connect_non_autocommit(self):
222 """
223 The connection wrapper shouldn't believe that autocommit is enabled
224 after setting the time zone when AUTOCOMMIT is False (#21452).
225 """
226 new_connection = no_pool_connection()
227 new_connection.settings_dict["AUTOCOMMIT"] = False
228
229 try:
230 # Open a database connection.
231 with new_connection.cursor():
232 self.assertFalse(new_connection.get_autocommit())
233 finally:
234 new_connection.close()
235
236 @unittest.skipUnless(is_psycopg3, "psycopg3 specific test")
237 def test_connect_pool(self):
238 from psycopg_pool import PoolTimeout
239
240 new_connection = no_pool_connection(alias="default_pool")
241 new_connection.settings_dict["OPTIONS"]["pool"] = {
242 "min_size": 0,
243 "max_size": 2,
244 "timeout": 5,
245 }
246 self.assertIsNotNone(new_connection.pool)
247
248 connections = []
249
250 def get_connection():
251 # copy() reuses the existing alias and as such the same pool.
252 conn = new_connection.copy()
253 conn.connect()
254 connections.append(conn)
255 return conn
256
257 try:
258 connection_1 = get_connection() # First connection.
259 connection_1_backend_pid = connection_1.connection.info.backend_pid
260 get_connection() # Get the second connection.
261 with self.assertRaises(PoolTimeout):
262 # The pool has a maximum of 2 connections.
263 get_connection()
264
265 connection_1.close() # Release back to the pool.
266 connection_3 = get_connection()
267 # Reuses the first connection as it is available.
268 self.assertEqual(
269 connection_3.connection.info.backend_pid, connection_1_backend_pid
270 )
271 finally:
272 # Release all connections back to the pool.
273 for conn in connections:
274 conn.close()
275 new_connection.close_pool()
276
277 @unittest.skipUnless(is_psycopg3, "psycopg3 specific test")
278 def test_connect_pool_set_to_true(self):
279 new_connection = no_pool_connection(alias="default_pool")
280 new_connection.settings_dict["OPTIONS"]["pool"] = True
281 try:
282 self.assertIsNotNone(new_connection.pool)
283 finally:
284 new_connection.close_pool()
285
286 @unittest.skipUnless(is_psycopg3, "psycopg3 specific test")
287 def test_connect_pool_with_timezone(self):
288 new_time_zone = "Africa/Nairobi"
289 new_connection = no_pool_connection(alias="default_pool")
290
291 try:
292 with new_connection.cursor() as cursor:
293 cursor.execute("SHOW TIMEZONE")
294 tz = cursor.fetchone()[0]
295 self.assertNotEqual(new_time_zone, tz)
296 finally:
297 new_connection.close()
298
299 del new_connection.timezone_name
300 new_connection.settings_dict["OPTIONS"]["pool"] = True
301 try:
302 with self.settings(TIME_ZONE=new_time_zone):
303 with new_connection.cursor() as cursor:
304 cursor.execute("SHOW TIMEZONE")
305 tz = cursor.fetchone()[0]
306 self.assertEqual(new_time_zone, tz)
307 finally:
308 new_connection.close()
309 new_connection.close_pool()
310
311 @unittest.skipUnless(is_psycopg3, "psycopg3 specific test")
312 def test_pooling_health_checks(self):
313 new_connection = no_pool_connection(alias="default_pool")
314 new_connection.settings_dict["OPTIONS"]["pool"] = True
315 new_connection.settings_dict["CONN_HEALTH_CHECKS"] = False
316
317 try:
318 self.assertIsNone(new_connection.pool._check)
319 finally:
320 new_connection.close_pool()
321
322 new_connection.settings_dict["CONN_HEALTH_CHECKS"] = True
323 try:
324 self.assertIsNotNone(new_connection.pool._check)
325 finally:
326 new_connection.close_pool()
327
328 @unittest.skipUnless(is_psycopg3, "psycopg3 specific test")
329 def test_cannot_open_new_connection_in_atomic_block(self):
330 new_connection = no_pool_connection(alias="default_pool")
331 new_connection.settings_dict["OPTIONS"]["pool"] = True
332
333 msg = "Cannot open a new connection in an atomic block."
334 new_connection.in_atomic_block = True
335 new_connection.closed_in_transaction = True
336 with self.assertRaisesMessage(ProgrammingError, msg):
337 new_connection.ensure_connection()
338
339 @unittest.skipUnless(is_psycopg3, "psycopg3 specific test")
340 def test_pooling_not_support_persistent_connections(self):
341 new_connection = no_pool_connection(alias="default_pool")
342 new_connection.settings_dict["OPTIONS"]["pool"] = True
343 new_connection.settings_dict["CONN_MAX_AGE"] = 10
344 msg = "Pooling doesn't support persistent connections."
345 with self.assertRaisesMessage(ImproperlyConfigured, msg):
346 new_connection.pool
347
348 @unittest.skipIf(is_psycopg3, "psycopg2 specific test")
349 def test_connect_pool_setting_ignored_for_psycopg2(self):
350 new_connection = no_pool_connection()
351 new_connection.settings_dict["OPTIONS"]["pool"] = True
352 msg = "Database pooling requires psycopg >= 3"
353 with self.assertRaisesMessage(ImproperlyConfigured, msg):
354 new_connection.connect()
355
356 def test_connect_isolation_level(self):
357 """
358 The transaction level can be configured with
359 DATABASES ['OPTIONS']['isolation_level'].
360 """
361 from django.db.backends.postgresql.psycopg_any import IsolationLevel
362
363 # Since this is a django.test.TestCase, a transaction is in progress
364 # and the isolation level isn't reported as 0. This test assumes that
365 # PostgreSQL is configured with the default isolation level.
366 # Check the level on the psycopg connection, not the Django wrapper.
367 self.assertIsNone(connection.connection.isolation_level)
368
369 new_connection = no_pool_connection()
370 new_connection.settings_dict["OPTIONS"][
371 "isolation_level"
372 ] = IsolationLevel.SERIALIZABLE
373 try:
374 # Start a transaction so the isolation level isn't reported as 0.
375 new_connection.set_autocommit(False)
376 # Check the level on the psycopg connection, not the Django wrapper.
377 self.assertEqual(
378 new_connection.connection.isolation_level,
379 IsolationLevel.SERIALIZABLE,
380 )
381 finally:
382 new_connection.close()
383
384 def test_connect_invalid_isolation_level(self):
385 self.assertIsNone(connection.connection.isolation_level)
386 new_connection = no_pool_connection()
387 new_connection.settings_dict["OPTIONS"]["isolation_level"] = -1
388 msg = (
389 "Invalid transaction isolation level -1 specified. Use one of the "
390 "psycopg.IsolationLevel values."
391 )
392 with self.assertRaisesMessage(ImproperlyConfigured, msg):
393 new_connection.ensure_connection()
394
395 def test_connect_role(self):
396 """
397 The session role can be configured with DATABASES
398 ["OPTIONS"]["assume_role"].
399 """
400 try:
401 custom_role = "django_nonexistent_role"
402 new_connection = no_pool_connection()
403 new_connection.settings_dict["OPTIONS"]["assume_role"] = custom_role
404 msg = f'role "{custom_role}" does not exist'
405 with self.assertRaisesMessage(errors.InvalidParameterValue, msg):
406 new_connection.connect()
407 finally:
408 new_connection.close()
409
410 @unittest.skipUnless(is_psycopg3, "psycopg3 specific test")
411 def test_connect_server_side_binding(self):
412 """
413 The server-side parameters binding role can be enabled with DATABASES
414 ["OPTIONS"]["server_side_binding"].
415 """
416 from django.db.backends.postgresql.base import ServerBindingCursor
417
418 new_connection = no_pool_connection()
419 new_connection.settings_dict["OPTIONS"]["server_side_binding"] = True
420 try:
421 new_connection.connect()
422 self.assertEqual(
423 new_connection.connection.cursor_factory,
424 ServerBindingCursor,
425 )
426 finally:
427 new_connection.close()
428
429 def test_connect_custom_cursor_factory(self):
430 """
431 A custom cursor factory can be configured with DATABASES["options"]
432 ["cursor_factory"].
433 """
434 from django.db.backends.postgresql.base import Cursor
435
436 class MyCursor(Cursor):
437 pass
438
439 new_connection = no_pool_connection()
440 new_connection.settings_dict["OPTIONS"]["cursor_factory"] = MyCursor
441 try:
442 new_connection.connect()
443 self.assertEqual(new_connection.connection.cursor_factory, MyCursor)
444 finally:
445 new_connection.close()
446
447 def test_connect_no_is_usable_checks(self):
448 new_connection = no_pool_connection()
449 try:
450 with mock.patch.object(new_connection, "is_usable") as is_usable:
451 new_connection.connect()
452 is_usable.assert_not_called()
453 finally:
454 new_connection.close()
455
456 def test_client_encoding_utf8_enforce(self):
457 new_connection = no_pool_connection()
458 new_connection.settings_dict["OPTIONS"]["client_encoding"] = "iso-8859-2"
459 try:
460 new_connection.connect()
461 if is_psycopg3:
462 self.assertEqual(new_connection.connection.info.encoding, "utf-8")
463 else:
464 self.assertEqual(new_connection.connection.encoding, "UTF8")
465 finally:
466 new_connection.close()
467
468 def _select(self, val):
469 with connection.cursor() as cursor:
470 cursor.execute("SELECT %s::text[]", (val,))
471 return cursor.fetchone()[0]
472
473 def test_select_ascii_array(self):
474 a = ["awef"]
475 b = self._select(a)
476 self.assertEqual(a[0], b[0])
477
478 def test_select_unicode_array(self):
479 a = ["ᄲawef"]
480 b = self._select(a)
481 self.assertEqual(a[0], b[0])
482
483 def test_lookup_cast(self):
484 from django.db.backends.postgresql.operations import DatabaseOperations
485
486 do = DatabaseOperations(connection=None)
487 lookups = (
488 "iexact",
489 "contains",
490 "icontains",
491 "startswith",
492 "istartswith",
493 "endswith",
494 "iendswith",
495 "regex",
496 "iregex",
497 )
498 for lookup in lookups:
499 with self.subTest(lookup=lookup):
500 self.assertIn("::text", do.lookup_cast(lookup))
501
502 def test_lookup_cast_isnull_noop(self):
503 from django.db.backends.postgresql.operations import DatabaseOperations
504
505 do = DatabaseOperations(connection=None)
506 # Using __isnull lookup doesn't require casting.
507 tests = [
508 "CharField",
509 "EmailField",
510 "TextField",
511 ]
512 for field_type in tests:
513 with self.subTest(field_type=field_type):
514 self.assertEqual(do.lookup_cast("isnull", field_type), "%s")
515
516 def test_correct_extraction_psycopg_version(self):
517 from django.db.backends.postgresql.base import Database, psycopg_version
518
519 with mock.patch.object(Database, "__version__", "4.2.1 (dt dec pq3 ext lo64)"):
520 self.assertEqual(psycopg_version(), (4, 2, 1))
521 with mock.patch.object(
522 Database, "__version__", "4.2b0.dev1 (dt dec pq3 ext lo64)"
523 ):
524 self.assertEqual(psycopg_version(), (4, 2))
525
526 @override_settings(DEBUG=True)
527 @unittest.skipIf(is_psycopg3, "psycopg2 specific test")
528 def test_copy_to_expert_cursors(self):
529 out = StringIO()
530 copy_expert_sql = "COPY django_session TO STDOUT (FORMAT CSV, HEADER)"
531 with connection.cursor() as cursor:
532 cursor.copy_expert(copy_expert_sql, out)
533 cursor.copy_to(out, "django_session")
534 self.assertEqual(
535 [q["sql"] for q in connection.queries],
536 [copy_expert_sql, "COPY django_session TO STDOUT"],
537 )
538
539 @override_settings(DEBUG=True)
540 @unittest.skipUnless(is_psycopg3, "psycopg3 specific test")
541 def test_copy_cursors(self):
542 copy_sql = "COPY django_session TO STDOUT (FORMAT CSV, HEADER)"
543 with connection.cursor() as cursor:
544 with cursor.copy(copy_sql) as copy:
545 for row in copy:
546 pass
547 self.assertEqual([q["sql"] for q in connection.queries], [copy_sql])
548
549 def test_get_database_version(self):
550 new_connection = no_pool_connection()
551 new_connection.pg_version = 140009
552 self.assertEqual(new_connection.get_database_version(), (14, 9))
553
554 @mock.patch.object(connection, "get_database_version", return_value=(13,))
555 def test_check_database_version_supported(self, mocked_get_database_version):
556 msg = "PostgreSQL 14 or later is required (found 13)."
557 with self.assertRaisesMessage(NotSupportedError, msg):
558 connection.check_database_version_supported()
559 self.assertTrue(mocked_get_database_version.called)
560
561 def test_compose_sql_when_no_connection(self):
562 new_connection = no_pool_connection()
563 try:
564 self.assertEqual(
565 new_connection.ops.compose_sql("SELECT %s", ["test"]),
566 "SELECT 'test'",
567 )
568 finally:
569 new_connection.close()
570
571 def test_bypass_timezone_configuration(self):
572 from django.db.backends.postgresql.base import DatabaseWrapper
573
574 class CustomDatabaseWrapper(DatabaseWrapper):
575 def _configure_timezone(self, connection):
576 return False
577
578 for Wrapper, commit in [
579 (DatabaseWrapper, True),
580 (CustomDatabaseWrapper, False),
581 ]:
582 with self.subTest(wrapper=Wrapper, commit=commit):
583 new_connection = no_pool_connection()
584 self.addCleanup(new_connection.close)
585
586 # Set the database default time zone to be different from
587 # the time zone in new_connection.settings_dict.
588 with new_connection.cursor() as cursor:
589 cursor.execute("RESET TIMEZONE")
590 cursor.execute("SHOW TIMEZONE")
591 db_default_tz = cursor.fetchone()[0]
592 new_tz = "Europe/Paris" if db_default_tz == "UTC" else "UTC"
593 new_connection.timezone_name = new_tz
594
595 settings = new_connection.settings_dict.copy()
596 conn = new_connection.connection
597 self.assertIs(Wrapper(settings)._configure_connection(conn), commit)
598
599 def test_bypass_role_configuration(self):
600 from django.db.backends.postgresql.base import DatabaseWrapper
601
602 class CustomDatabaseWrapper(DatabaseWrapper):
603 def _configure_role(self, connection):
604 return False
605
606 new_connection = no_pool_connection()
607 self.addCleanup(new_connection.close)
608 new_connection.connect()
609
610 settings = new_connection.settings_dict.copy()
611 settings["OPTIONS"]["assume_role"] = "django_nonexistent_role"
612 conn = new_connection.connection
613 self.assertIs(
614 CustomDatabaseWrapper(settings)._configure_connection(conn), False
615 )