Debian Codegraph @

django/db/backends/postgresql/base.py

raw
1 """
2 PostgreSQL database backend for Django.
3
4 Requires psycopg2 >= 2.8.4 or psycopg >= 3.1.8
5 """
6
7 import asyncio
8 import threading
9 import warnings
10 from contextlib import contextmanager
11
12 from django.conf import settings
13 from django.core.exceptions import ImproperlyConfigured
14 from django.db import DatabaseError as WrappedDatabaseError
15 from django.db import connections
16 from django.db.backends.base.base import NO_DB_ALIAS, BaseDatabaseWrapper
17 from django.db.backends.utils import CursorDebugWrapper as BaseCursorDebugWrapper
18 from django.utils.asyncio import async_unsafe
19 from django.utils.functional import cached_property
20 from django.utils.safestring import SafeString
21 from django.utils.version import get_version_tuple
22
23 try:
24 try:
25 import psycopg as Database
26 except ImportError:
27 import psycopg2 as Database
28 except ImportError:
29 raise ImproperlyConfigured("Error loading psycopg2 or psycopg module")
30
31
32 def psycopg_version():
33 version = Database.__version__.split(" ", 1)[0]
34 return get_version_tuple(version)
35
36
37 if psycopg_version() < (2, 8, 4):
38 raise ImproperlyConfigured(
39 f"psycopg2 version 2.8.4 or newer is required; you have {Database.__version__}"
40 )
41 if (3,) <= psycopg_version() < (3, 1, 8):
42 raise ImproperlyConfigured(
43 f"psycopg version 3.1.8 or newer is required; you have {Database.__version__}"
44 )
45
46
47 from .psycopg_any import IsolationLevel, is_psycopg3 # NOQA isort:skip
48
49 if is_psycopg3:
50 from psycopg import adapters, sql
51 from psycopg.pq import Format
52
53 from .psycopg_any import get_adapters_template, register_tzloader
54
55 TIMESTAMPTZ_OID = adapters.types["timestamptz"].oid
56
57 else:
58 import psycopg2.extensions
59 import psycopg2.extras
60
61 psycopg2.extensions.register_adapter(SafeString, psycopg2.extensions.QuotedString)
62 psycopg2.extras.register_uuid()
63
64 # Register support for inet[] manually so we don't have to handle the Inet()
65 # object on load all the time.
66 INETARRAY_OID = 1041
67 INETARRAY = psycopg2.extensions.new_array_type(
68 (INETARRAY_OID,),
69 "INETARRAY",
70 psycopg2.extensions.UNICODE,
71 )
72 psycopg2.extensions.register_type(INETARRAY)
73
74 # Some of these import psycopg, so import them after checking if it's installed.
75 from .client import DatabaseClient # NOQA isort:skip
76 from .creation import DatabaseCreation # NOQA isort:skip
77 from .features import DatabaseFeatures # NOQA isort:skip
78 from .introspection import DatabaseIntrospection # NOQA isort:skip
79 from .operations import DatabaseOperations # NOQA isort:skip
80 from .schema import DatabaseSchemaEditor # NOQA isort:skip
81
82
83 def _get_varchar_column(data):
84 if data["max_length"] is None:
85 return "varchar"
86 return "varchar(%(max_length)s)" % data
87
88
89 class DatabaseWrapper(BaseDatabaseWrapper):
90 vendor = "postgresql"
91 display_name = "PostgreSQL"
92 # This dictionary maps Field objects to their associated PostgreSQL column
93 # types, as strings. Column-type strings can contain format strings; they'll
94 # be interpolated against the values of Field.__dict__ before being output.
95 # If a column type is set to None, it won't be included in the output.
96 data_types = {
97 "AutoField": "integer",
98 "BigAutoField": "bigint",
99 "BinaryField": "bytea",
100 "BooleanField": "boolean",
101 "CharField": _get_varchar_column,
102 "DateField": "date",
103 "DateTimeField": "timestamp with time zone",
104 "DecimalField": "numeric(%(max_digits)s, %(decimal_places)s)",
105 "DurationField": "interval",
106 "FileField": "varchar(%(max_length)s)",
107 "FilePathField": "varchar(%(max_length)s)",
108 "FloatField": "double precision",
109 "IntegerField": "integer",
110 "BigIntegerField": "bigint",
111 "IPAddressField": "inet",
112 "GenericIPAddressField": "inet",
113 "JSONField": "jsonb",
114 "OneToOneField": "integer",
115 "PositiveBigIntegerField": "bigint",
116 "PositiveIntegerField": "integer",
117 "PositiveSmallIntegerField": "smallint",
118 "SlugField": "varchar(%(max_length)s)",
119 "SmallAutoField": "smallint",
120 "SmallIntegerField": "smallint",
121 "TextField": "text",
122 "TimeField": "time",
123 "UUIDField": "uuid",
124 }
125 data_type_check_constraints = {
126 "PositiveBigIntegerField": '"%(column)s" >= 0',
127 "PositiveIntegerField": '"%(column)s" >= 0',
128 "PositiveSmallIntegerField": '"%(column)s" >= 0',
129 }
130 data_types_suffix = {
131 "AutoField": "GENERATED BY DEFAULT AS IDENTITY",
132 "BigAutoField": "GENERATED BY DEFAULT AS IDENTITY",
133 "SmallAutoField": "GENERATED BY DEFAULT AS IDENTITY",
134 }
135 operators = {
136 "exact": "= %s",
137 "iexact": "= UPPER(%s)",
138 "contains": "LIKE %s",
139 "icontains": "LIKE UPPER(%s)",
140 "regex": "~ %s",
141 "iregex": "~* %s",
142 "gt": "> %s",
143 "gte": ">= %s",
144 "lt": "< %s",
145 "lte": "<= %s",
146 "startswith": "LIKE %s",
147 "endswith": "LIKE %s",
148 "istartswith": "LIKE UPPER(%s)",
149 "iendswith": "LIKE UPPER(%s)",
150 }
151
152 # The patterns below are used to generate SQL pattern lookup clauses when
153 # the right-hand side of the lookup isn't a raw string (it might be an expression
154 # or the result of a bilateral transformation).
155 # In those cases, special characters for LIKE operators (e.g. \, *, _) should be
156 # escaped on database side.
157 #
158 # Note: we use str.format() here for readability as '%' is used as a wildcard for
159 # the LIKE operator.
160 pattern_esc = (
161 r"REPLACE(REPLACE(REPLACE({}, E'\\', E'\\\\'), E'%%', E'\\%%'), E'_', E'\\_')"
162 )
163 pattern_ops = {
164 "contains": "LIKE '%%' || {} || '%%'",
165 "icontains": "LIKE '%%' || UPPER({}) || '%%'",
166 "startswith": "LIKE {} || '%%'",
167 "istartswith": "LIKE UPPER({}) || '%%'",
168 "endswith": "LIKE '%%' || {}",
169 "iendswith": "LIKE '%%' || UPPER({})",
170 }
171
172 Database = Database
173 SchemaEditorClass = DatabaseSchemaEditor
174 # Classes instantiated in __init__().
175 client_class = DatabaseClient
176 creation_class = DatabaseCreation
177 features_class = DatabaseFeatures
178 introspection_class = DatabaseIntrospection
179 ops_class = DatabaseOperations
180 # PostgreSQL backend-specific attributes.
181 _named_cursor_idx = 0
182 _connection_pools = {}
183
184 @property
185 def pool(self):
186 pool_options = self.settings_dict["OPTIONS"].get("pool")
187 if self.alias == NO_DB_ALIAS or not pool_options:
188 return None
189
190 if self.alias not in self._connection_pools:
191 if self.settings_dict.get("CONN_MAX_AGE", 0) != 0:
192 raise ImproperlyConfigured(
193 "Pooling doesn't support persistent connections."
194 )
195 # Set the default options.
196 if pool_options is True:
197 pool_options = {}
198
199 try:
200 from psycopg_pool import ConnectionPool
201 except ImportError as err:
202 raise ImproperlyConfigured(
203 "Error loading psycopg_pool module.\nDid you install psycopg[pool]?"
204 ) from err
205
206 connect_kwargs = self.get_connection_params()
207 # Ensure we run in autocommit, Django properly sets it later on.
208 connect_kwargs["autocommit"] = True
209 enable_checks = self.settings_dict["CONN_HEALTH_CHECKS"]
210 pool = ConnectionPool(
211 kwargs=connect_kwargs,
212 open=False, # Do not open the pool during startup.
213 configure=self._configure_connection,
214 check=ConnectionPool.check_connection if enable_checks else None,
215 **pool_options,
216 )
217 # setdefault() ensures that multiple threads don't set this in
218 # parallel. Since we do not open the pool during it's init above,
219 # this means that at worst during startup multiple threads generate
220 # pool objects and the first to set it wins.
221 self._connection_pools.setdefault(self.alias, pool)
222
223 return self._connection_pools[self.alias]
224
225 def close_pool(self):
226 if self.pool:
227 self.pool.close()
228 del self._connection_pools[self.alias]
229
230 def get_database_version(self):
231 """
232 Return a tuple of the database's version.
233 E.g. for pg_version 120004, return (12, 4).
234 """
235 return divmod(self.pg_version, 10000)
236
237 def get_connection_params(self):
238 settings_dict = self.settings_dict
239 # None may be used to connect to the default 'postgres' db
240 if settings_dict["NAME"] == "" and not settings_dict["OPTIONS"].get("service"):
241 raise ImproperlyConfigured(
242 "settings.DATABASES is improperly configured. "
243 "Please supply the NAME or OPTIONS['service'] value."
244 )
245 if len(settings_dict["NAME"] or "") > self.ops.max_name_length():
246 raise ImproperlyConfigured(
247 "The database name '%s' (%d characters) is longer than "
248 "PostgreSQL's limit of %d characters. Supply a shorter NAME "
249 "in settings.DATABASES."
250 % (
251 settings_dict["NAME"],
252 len(settings_dict["NAME"]),
253 self.ops.max_name_length(),
254 )
255 )
256 if settings_dict["NAME"]:
257 conn_params = {
258 "dbname": settings_dict["NAME"],
259 **settings_dict["OPTIONS"],
260 }
261 elif settings_dict["NAME"] is None:
262 # Connect to the default 'postgres' db.
263 settings_dict["OPTIONS"].pop("service", None)
264 conn_params = {"dbname": "postgres", **settings_dict["OPTIONS"]}
265 else:
266 conn_params = {**settings_dict["OPTIONS"]}
267 conn_params["client_encoding"] = "UTF8"
268
269 conn_params.pop("assume_role", None)
270 conn_params.pop("isolation_level", None)
271
272 pool_options = conn_params.pop("pool", None)
273 if pool_options and not is_psycopg3:
274 raise ImproperlyConfigured("Database pooling requires psycopg >= 3")
275
276 server_side_binding = conn_params.pop("server_side_binding", None)
277 conn_params.setdefault(
278 "cursor_factory",
279 (
280 ServerBindingCursor
281 if is_psycopg3 and server_side_binding is True
282 else Cursor
283 ),
284 )
285 if settings_dict["USER"]:
286 conn_params["user"] = settings_dict["USER"]
287 if settings_dict["PASSWORD"]:
288 conn_params["password"] = settings_dict["PASSWORD"]
289 if settings_dict["HOST"]:
290 conn_params["host"] = settings_dict["HOST"]
291 if settings_dict["PORT"]:
292 conn_params["port"] = settings_dict["PORT"]
293 if is_psycopg3:
294 conn_params["context"] = get_adapters_template(
295 settings.USE_TZ, self.timezone
296 )
297 # Disable prepared statements by default to keep connection poolers
298 # working. Can be reenabled via OPTIONS in the settings dict.
299 conn_params["prepare_threshold"] = conn_params.pop(
300 "prepare_threshold", None
301 )
302 return conn_params
303
304 @async_unsafe
305 def get_new_connection(self, conn_params):
306 # self.isolation_level must be set:
307 # - after connecting to the database in order to obtain the database's
308 # default when no value is explicitly specified in options.
309 # - before calling _set_autocommit() because if autocommit is on, that
310 # will set connection.isolation_level to ISOLATION_LEVEL_AUTOCOMMIT.
311 options = self.settings_dict["OPTIONS"]
312 set_isolation_level = False
313 try:
314 isolation_level_value = options["isolation_level"]
315 except KeyError:
316 self.isolation_level = IsolationLevel.READ_COMMITTED
317 else:
318 # Set the isolation level to the value from OPTIONS.
319 try:
320 self.isolation_level = IsolationLevel(isolation_level_value)
321 set_isolation_level = True
322 except ValueError:
323 raise ImproperlyConfigured(
324 f"Invalid transaction isolation level {isolation_level_value} "
325 f"specified. Use one of the psycopg.IsolationLevel values."
326 )
327 if self.pool:
328 # If nothing else has opened the pool, open it now.
329 self.pool.open()
330 connection = self.pool.getconn()
331 else:
332 connection = self.Database.connect(**conn_params)
333 if set_isolation_level:
334 connection.isolation_level = self.isolation_level
335 if not is_psycopg3:
336 # Register dummy loads() to avoid a round trip from psycopg2's
337 # decode to json.dumps() to json.loads(), when using a custom
338 # decoder in JSONField.
339 psycopg2.extras.register_default_jsonb(
340 conn_or_curs=connection, loads=lambda x: x
341 )
342 return connection
343
344 def ensure_timezone(self):
345 # Close the pool so new connections pick up the correct timezone.
346 self.close_pool()
347 if self.connection is None:
348 return False
349 return self._configure_timezone(self.connection)
350
351 def _configure_timezone(self, connection):
352 conn_timezone_name = connection.info.parameter_status("TimeZone")
353 timezone_name = self.timezone_name
354 if timezone_name and conn_timezone_name != timezone_name:
355 with connection.cursor() as cursor:
356 cursor.execute(self.ops.set_time_zone_sql(), [timezone_name])
357 return True
358 return False
359
360 def _configure_role(self, connection):
361 if new_role := self.settings_dict["OPTIONS"].get("assume_role"):
362 with connection.cursor() as cursor:
363 sql = self.ops.compose_sql("SET ROLE %s", [new_role])
364 cursor.execute(sql)
365 return True
366 return False
367
368 def _configure_connection(self, connection):
369 # This function is called from init_connection_state and from the
370 # psycopg pool itself after a connection is opened.
371
372 # Commit after setting the time zone.
373 commit_tz = self._configure_timezone(connection)
374 # Set the role on the connection. This is useful if the credential used
375 # to login is not the same as the role that owns database resources. As
376 # can be the case when using temporary or ephemeral credentials.
377 commit_role = self._configure_role(connection)
378
379 return commit_role or commit_tz
380
381 def _close(self):
382 if self.connection is not None:
383 # `wrap_database_errors` only works for `putconn` as long as there
384 # is no `reset` function set in the pool because it is deferred
385 # into a thread and not directly executed.
386 with self.wrap_database_errors:
387 if self.pool:
388 # Ensure the correct pool is returned. This is a workaround
389 # for tests so a pool can be changed on setting changes
390 # (e.g. USE_TZ, TIME_ZONE).
391 self.connection._pool.putconn(self.connection)
392 # Connection can no longer be used.
393 self.connection = None
394 else:
395 return self.connection.close()
396
397 def init_connection_state(self):
398 super().init_connection_state()
399
400 if self.connection is not None and not self.pool:
401 commit = self._configure_connection(self.connection)
402
403 if commit and not self.get_autocommit():
404 self.connection.commit()
405
406 @async_unsafe
407 def create_cursor(self, name=None):
408 if name:
409 if is_psycopg3 and (
410 self.settings_dict["OPTIONS"].get("server_side_binding") is not True
411 ):
412 # psycopg >= 3 forces the usage of server-side bindings for
413 # named cursors so a specialized class that implements
414 # server-side cursors while performing client-side bindings
415 # must be used if `server_side_binding` is disabled (default).
416 cursor = ServerSideCursor(
417 self.connection,
418 name=name,
419 scrollable=False,
420 withhold=self.connection.autocommit,
421 )
422 else:
423 # In autocommit mode, the cursor will be used outside of a
424 # transaction, hence use a holdable cursor.
425 cursor = self.connection.cursor(
426 name, scrollable=False, withhold=self.connection.autocommit
427 )
428 else:
429 cursor = self.connection.cursor()
430
431 if is_psycopg3:
432 # Register the cursor timezone only if the connection disagrees, to
433 # avoid copying the adapter map.
434 tzloader = self.connection.adapters.get_loader(TIMESTAMPTZ_OID, Format.TEXT)
435 if self.timezone != tzloader.timezone:
436 register_tzloader(self.timezone, cursor)
437 else:
438 cursor.tzinfo_factory = self.tzinfo_factory if settings.USE_TZ else None
439 return cursor
440
441 def tzinfo_factory(self, offset):
442 return self.timezone
443
444 @async_unsafe
445 def chunked_cursor(self):
446 self._named_cursor_idx += 1
447 # Get the current async task
448 # Note that right now this is behind @async_unsafe, so this is
449 # unreachable, but in future we'll start loosening this restriction.
450 # For now, it's here so that every use of "threading" is
451 # also async-compatible.
452 try:
453 current_task = asyncio.current_task()
454 except RuntimeError:
455 current_task = None
456 # Current task can be none even if the current_task call didn't error
457 if current_task:
458 task_ident = str(id(current_task))
459 else:
460 task_ident = "sync"
461 # Use that and the thread ident to get a unique name
462 return self._cursor(
463 name="_django_curs_%d_%s_%d"
464 % (
465 # Avoid reusing name in other threads / tasks
466 threading.current_thread().ident,
467 task_ident,
468 self._named_cursor_idx,
469 )
470 )
471
472 def _set_autocommit(self, autocommit):
473 with self.wrap_database_errors:
474 self.connection.autocommit = autocommit
475
476 def check_constraints(self, table_names=None):
477 """
478 Check constraints by setting them to immediate. Return them to deferred
479 afterward.
480 """
481 with self.cursor() as cursor:
482 cursor.execute("SET CONSTRAINTS ALL IMMEDIATE")
483 cursor.execute("SET CONSTRAINTS ALL DEFERRED")
484
485 def is_usable(self):
486 if self.connection is None:
487 return False
488 try:
489 # Use a psycopg cursor directly, bypassing Django's utilities.
490 with self.connection.cursor() as cursor:
491 cursor.execute("SELECT 1")
492 except Database.Error:
493 return False
494 else:
495 return True
496
497 def close_if_health_check_failed(self):
498 if self.pool:
499 # The pool only returns healthy connections.
500 return
501 return super().close_if_health_check_failed()
502
503 @contextmanager
504 def _nodb_cursor(self):
505 cursor = None
506 try:
507 with super()._nodb_cursor() as cursor:
508 yield cursor
509 except (Database.DatabaseError, WrappedDatabaseError):
510 if cursor is not None:
511 raise
512 warnings.warn(
513 "Normally Django will use a connection to the 'postgres' database "
514 "to avoid running initialization queries against the production "
515 "database when it's not needed (for example, when running tests). "
516 "Django was unable to create a connection to the 'postgres' database "
517 "and will use the first PostgreSQL database instead.",
518 RuntimeWarning,
519 )
520 for connection in connections.all():
521 if (
522 connection.vendor == "postgresql"
523 and connection.settings_dict["NAME"] != "postgres"
524 ):
525 conn = self.__class__(
526 {
527 **self.settings_dict,
528 "NAME": connection.settings_dict["NAME"],
529 },
530 alias=self.alias,
531 )
532 try:
533 with conn.cursor() as cursor:
534 yield cursor
535 finally:
536 conn.close()
537 break
538 else:
539 raise
540
541 @cached_property
542 def pg_version(self):
543 with self.temporary_connection():
544 return self.connection.info.server_version
545
546 def make_debug_cursor(self, cursor):
547 return CursorDebugWrapper(cursor, self)
548
549
550 if is_psycopg3:
551
552 class CursorMixin:
553 """
554 A subclass of psycopg cursor implementing callproc.
555 """
556
557 def callproc(self, name, args=None):
558 if not isinstance(name, sql.Identifier):
559 name = sql.Identifier(name)
560
561 qparts = [sql.SQL("SELECT * FROM "), name, sql.SQL("(")]
562 if args:
563 for item in args:
564 qparts.append(sql.Literal(item))
565 qparts.append(sql.SQL(","))
566 del qparts[-1]
567
568 qparts.append(sql.SQL(")"))
569 stmt = sql.Composed(qparts)
570 self.execute(stmt)
571 return args
572
573 class ServerBindingCursor(CursorMixin, Database.Cursor):
574 pass
575
576 class Cursor(CursorMixin, Database.ClientCursor):
577 pass
578
579 class ServerSideCursor(
580 CursorMixin, Database.client_cursor.ClientCursorMixin, Database.ServerCursor
581 ):
582 """
583 psycopg >= 3 forces the usage of server-side bindings when using named
584 cursors but the ORM doesn't yet support the systematic generation of
585 prepareable SQL (#20516).
586
587 ClientCursorMixin forces the usage of client-side bindings while
588 ServerCursor implements the logic required to declare and scroll
589 through named cursors.
590
591 Mixing ClientCursorMixin in wouldn't be necessary if Cursor allowed to
592 specify how parameters should be bound instead, which ServerCursor
593 would inherit, but that's not the case.
594 """
595
596 class CursorDebugWrapper(BaseCursorDebugWrapper):
597 def copy(self, statement):
598 with self.debug_sql(statement):
599 return self.cursor.copy(statement)
600
601 else:
602 Cursor = psycopg2.extensions.cursor
603
604 class CursorDebugWrapper(BaseCursorDebugWrapper):
605 def copy_expert(self, sql, file, *args):
606 with self.debug_sql(sql):
607 return self.cursor.copy_expert(sql, file, *args)
608
609 def copy_to(self, file, table, *args, **kwargs):
610 with self.debug_sql(sql="COPY %s TO STDOUT" % table):
611 return self.cursor.copy_to(file, table, *args, **kwargs)