11
11
usage. Of course, it is not intended as a replacement for server-specific
12
12
documentation or reference manuals.
17
.. _persistent-database-connections:
19
Persistent connections
20
----------------------
24
Persistent connections avoid the overhead of re-establishing a connection to
25
the database in each request. They're controlled by the
26
:setting:`CONN_MAX_AGE` parameter which defines the maximum lifetime of a
27
connection. It can be set independently for each database.
29
The default value is ``0``, preserving the historical behavior of closing the
30
database connection at the end of each request. To enable persistent
31
connections, set :setting:`CONN_MAX_AGE` to a positive number of seconds. For
32
unlimited persistent connections, set it to ``None``.
37
Django opens a connection to the database when it first makes a database
38
query. It keeps this connection open and reuses it in subsequent requests.
39
Django closes the connection once it exceeds the maximum age defined by
40
:setting:`CONN_MAX_AGE` or when it isn't usable any longer.
42
In detail, Django automatically opens a connection to the database whenever it
43
needs one and doesn't have one already — either because this is the first
44
connection, or because the previous connection was closed.
46
At the beginning of each request, Django closes the connection if it has
47
reached its maximum age. If your database terminates idle connections after
48
some time, you should set :setting:`CONN_MAX_AGE` to a lower value, so that
49
Django doesn't attempt to use a connection that has been terminated by the
50
database server. (This problem may only affect very low traffic sites.)
52
At the end of each request, Django closes the connection if it has reached its
53
maximum age or if it is in an unrecoverable error state. If any database
54
errors have occurred while processing the requests, Django checks whether the
55
connection still works, and closes it if it doesn't. Thus, database errors
56
affect at most one request; if the connection becomes unusable, the next
57
request gets a fresh connection.
62
Since each thread maintains its own connection, your database must support at
63
least as many simultaneous connections as you have worker threads.
65
Sometimes a database won't be accessed by the majority of your views, for
66
example because it's the database of an external system, or thanks to caching.
67
In such cases, you should set :setting:`CONN_MAX_AGE` to a low value or even
68
``0``, because it doesn't make sense to maintain a connection that's unlikely
69
to be reused. This will help keep the number of simultaneous connections to
72
The development server creates a new thread for each request it handles,
73
negating the effect of persistent connections. Don't enable them during
76
When Django establishes a connection to the database, it sets up appropriate
77
parameters, depending on the backend being used. If you enable persistent
78
connections, this setup is no longer repeated every request. If you modify
79
parameters such as the connection's isolation level or time zone, you should
80
either restore Django's defaults at the end of each request, force an
81
appropriate value at the beginning of each request, or disable persistent
14
84
.. _postgresql-notes:
19
.. versionchanged:: 1.4
21
Django supports PostgreSQL 8.2 and higher.
23
PostgreSQL 8.2 to 8.2.4
24
-----------------------
26
The implementation of the population statistics aggregates ``STDDEV_POP`` and
27
``VAR_POP`` that shipped with PostgreSQL 8.2 to 8.2.4 are `known to be
28
faulty`_. Users of these releases of PostgreSQL are advised to upgrade to
29
`Release 8.2.5`_ or later. Django will raise a ``NotImplementedError`` if you
30
attempt to use the ``StdDev(sample=False)`` or ``Variance(sample=False)``
31
aggregate with a database backend that falls within the affected release range.
33
.. _known to be faulty: http://archives.postgresql.org/pgsql-bugs/2007-07/msg00046.php
34
.. _Release 8.2.5: http://www.postgresql.org/docs/devel/static/release-8-2-5.html
89
Django supports PostgreSQL 8.4 and higher.
36
91
PostgreSQL connection settings
37
92
-------------------------------
59
115
.. _ALTER ROLE: http://www.postgresql.org/docs/current/interactive/sql-alterrole.html
64
:doc:`By default </topics/db/transactions>`, Django runs with an open
65
transaction which it commits automatically when any built-in, data-altering
66
model function is called. The PostgreSQL backends normally operate the same as
67
any other Django backend in this respect.
69
117
.. _postgresql-autocommit-mode:
74
If your application is particularly read-heavy and doesn't make many
75
database writes, the overhead of a constantly open transaction can
76
sometimes be noticeable. For those situations, you can configure Django
77
to use *"autocommit"* behavior for the connection, meaning that each database
78
operation will normally be in its own transaction, rather than having
79
the transaction extend over multiple operations. In this case, you can
80
still manually start a transaction if you're doing something that
81
requires consistency across multiple database operations. The
82
autocommit behavior is enabled by setting the ``autocommit`` key in
83
the :setting:`OPTIONS` part of your database configuration in
84
:setting:`DATABASES`::
90
In this configuration, Django still ensures that :ref:`delete()
91
<topics-db-queries-delete>` and :ref:`update() <topics-db-queries-update>`
92
queries run inside a single transaction, so that either all the affected
93
objects are changed or none of them are.
95
.. admonition:: This is database-level autocommit
97
This functionality is not the same as the :ref:`autocommit
98
<topics-db-transactions-autocommit>` decorator. That decorator is
99
a Django-level implementation that commits automatically after
100
data changing operations. The feature enabled using the
101
:setting:`OPTIONS` option provides autocommit behavior at the
102
database adapter level. It commits after *every* operation.
104
If you are using this feature and performing an operation akin to delete or
105
updating that requires multiple operations, you are strongly recommended to
106
wrap you operations in manual transaction handling to ensure data consistency.
107
You should also audit your existing code for any instances of this behavior
108
before enabling this feature. It's faster, but it provides less automatic
109
protection for multi-call operations.
122
.. versionchanged:: 1.6
124
In previous versions of Django, database-level autocommit could be enabled by
125
setting the ``autocommit`` key in the :setting:`OPTIONS` part of your database
126
configuration in :setting:`DATABASES`::
135
Since Django 1.6, autocommit is turned on by default. This configuration is
136
ignored and can be safely removed.
138
.. _database-isolation-level:
143
.. versionadded:: 1.6
145
Like PostgreSQL itself, Django defaults to the ``READ COMMITTED`` `isolation
146
level`_. If you need a higher isolation level such as ``REPEATABLE READ`` or
147
``SERIALIZABLE``, set it in the :setting:`OPTIONS` part of your database
148
configuration in :setting:`DATABASES`::
150
import psycopg2.extensions
155
'isolation_level': psycopg2.extensions.ISOLATION_LEVEL_SERIALIZABLE,
161
Under higher isolation levels, your application should be prepared to
162
handle exceptions raised on serialization failures. This option is
163
designed for advanced uses.
165
.. _isolation level: http://www.postgresql.org/docs/current/static/transaction-iso.html
111
167
Indexes for ``varchar`` and ``text`` columns
112
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
168
--------------------------------------------
114
170
When specifying ``db_index=True`` on your model fields, Django typically
115
171
outputs a single ``CREATE INDEX`` statement. However, if the database type
176
233
1005, "Can't create table '\\db_name\\.#sql-4a8_ab' (errno: 150)"
179
.. versionchanged:: 1.4
181
In previous versions of Django, fixtures with forward references (i.e.
182
relations to rows that have not yet been inserted into the database) would fail
183
to load when using the InnoDB storage engine. This was due to the fact that InnoDB
184
deviates from the SQL standard by checking foreign key constraints immediately
185
instead of deferring the check until the transaction is committed. This
186
problem has been resolved in Django 1.4. Fixture data is now loaded with foreign key
187
checks turned off; foreign key checks are then re-enabled when the data has
188
finished loading, at which point the entire table is checked for invalid foreign
189
key references and an `IntegrityError` is raised if any are found.
191
236
.. _storage engines: http://dev.mysql.com/doc/refman/5.5/en/storage-engines.html
192
237
.. _MyISAM: http://dev.mysql.com/doc/refman/5.5/en/myisam-storage-engine.html
193
238
.. _InnoDB: http://dev.mysql.com/doc/refman/5.5/en/innodb-storage-engine.html
699
762
Oracle imposes a name length limit of 30 characters. To accommodate this, the
700
763
backend truncates database identifiers to fit, replacing the final four
701
764
characters of the truncated name with a repeatable MD5 hash value.
765
Additionally, the backend turns database identifiers to all-uppercase.
767
To prevent these transformations (this is usually required only when dealing
768
with legacy databases or accessing tables which belong to other users), use
769
a quoted name as the value for ``db_table``::
771
class LegacyModel(models.Model):
773
db_table = '"name_left_in_lowercase"'
775
class ForeignModel(models.Model):
777
db_table = '"OTHER_USER"."NAME_ONLY_SEEMS_OVER_30"'
779
Quoted names can also be used with Django's other supported database
780
backends; except for Oracle, however, the quotes have no effect.
703
782
When running syncdb, an ``ORA-06552`` error may be encountered if
704
783
certain Oracle keywords are used as the name of a model field or the