Opened 6 years ago
Closed 5 years ago
#30441 closed Bug (invalid)
Persistent connections not reused on request.
Reported by: | cryptogun | Owned by: | nobody |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | dev |
Severity: | Normal | Keywords: | |
Cc: | Triage Stage: | Accepted | |
Has patch: | no | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description (last modified by )
PostgreSQL default max connection is 100.
if CONN_MAX_AGE == None
in setting.py:
Every time I authenticate() a user, a new connection is opened but not closed.
user = authenticate(request=request, username='admin', password='123456')
if CONN_MAX_AGE == 0
:
The connection will be closed properly.
Traceback (most recent call last): File "C:\Users\me\AppData\Local\Programs\Python\Python36\lib\site-packages\django\utils\autoreload.py", line 225, in wrapper fn(*args, **kwargs) File "C:\Users\me\AppData\Local\Programs\Python\Python36\lib\site-packages\django\core\management\commands\runserver.py", line 120, in inner_run self.check_migrations() File "C:\Users\me\AppData\Local\Programs\Python\Python36\lib\site-packages\django\core\management\base.py", line 442, in check_migrations executor = MigrationExecutor(connections[DEFAULT_DB_ALIAS]) File "C:\Users\me\AppData\Local\Programs\Python\Python36\lib\site-packages\django\db\migrations\executor.py", line 18, in __init__ self.loader = MigrationLoader(self.connection) File "C:\Users\me\AppData\Local\Programs\Python\Python36\lib\site-packages\django\db\migrations\loader.py", line 49, in __init__ self.build_graph() File "C:\Users\me\AppData\Local\Programs\Python\Python36\lib\site-packages\django\db\migrations\loader.py", line 212, in build_graph self.applied_migrations = recorder.applied_migrations() File "C:\Users\me\AppData\Local\Programs\Python\Python36\lib\site-packages\django\db\migrations\recorder.py", line 61, in applied_migrations if self.has_table(): File "C:\Users\me\AppData\Local\Programs\Python\Python36\lib\site-packages\django\db\migrations\recorder.py", line 44, in has_table return self.Migration._meta.db_table in self.connection.introspection.table_names(self.connection.cursor()) File "C:\Users\me\AppData\Local\Programs\Python\Python36\lib\site-packages\django\db\backends\base\base.py", line 256, in cursor return self._cursor() File "C:\Users\me\AppData\Local\Programs\Python\Python36\lib\site-packages\django\db\backends\base\base.py", line 233, in _cursor self.ensure_connection() File "C:\Users\me\AppData\Local\Programs\Python\Python36\lib\site-packages\django\db\backends\base\base.py", line 217, in ensure_connection self.connect() File "C:\Users\me\AppData\Local\Programs\Python\Python36\lib\site-packages\django\db\utils.py", line 89, in __exit__ raise dj_exc_value.with_traceback(traceback) from exc_value File "C:\Users\me\AppData\Local\Programs\Python\Python36\lib\site-packages\django\db\backends\base\base.py", line 217, in ensure_connection self.connect() File "C:\Users\me\AppData\Local\Programs\Python\Python36\lib\site-packages\django\db\backends\base\base.py", line 194, in connect self.connection = self.get_new_connection(conn_params) File "C:\Users\me\AppData\Local\Programs\Python\Python36\lib\site-packages\django\db\backends\postgresql\base.py", line 178, in get_new_connection connection = Database.connect(**conn_params) File "C:\Users\me\AppData\Local\Programs\Python\Python36\lib\site-packages\psycopg2\__init__.py", line 126, in connect conn = _connect(dsn, connection_factory=connection_factory, **kwasync) django.db.utils.OperationalError: FATAL: remaining connection slots are reserved for non-replication superuser connections
Bug reproduce (with demo project attached):
- set CONN_MAX_AGE to None:
# settings.py DATABASES = { 'default': { 'ENGINE': 'django.db.backends.sqlite3', 'NAME': os.path.join(BASE_DIR, 'db.sqlite3'), 'CONN_MAX_AGE': None, } }
- Print and trace db connection:
Modify site-packages\django\db\backends\base\base.py
# site-packages\django\db\backends\base\base.py # Add a print line: def ensure_connection(self): """Guarantee that a connection to the database is established.""" if self.connection is None: with self.wrap_database_errors: print('open connection +++++++++++++++++++') self.connect()
# Add a print line: def close(self): """Close the connection to the database.""" self.validate_thread_sharing() self.run_on_commit = [] # Don't call validate_no_atomic_block() to avoid making it difficult # to get rid of a connection in an invalid state. The next connect() # will reset the transaction state anyway. if self.closed_in_transaction or self.connection is None: return try: print('close connection ......................') self._close() finally: if self.in_atomic_block: self.closed_in_transaction = True self.needs_rollback = True else: self.connection = None
- Open a new incognito browser
Ctrl + Shift + n
. - Goto landing page.
- Check Django log output.
open connection +++++++++++++++++++
No close log printed.
- Goto 3. and try as many times as you want.
- Switch to PostgreSQL backend, refresh 100 times, and you will get the above
OperationalError
.
- Set CONN_MAX_AGE to
0
in settings.py. - Retry 3~5
Now the close was executed.
open connection +++++++++++++++++++ [04/May/2019 16:09:53] "GET / HTTP/1.1" 200 2 close connection ......................
Attachments (1)
Change History (6)
comment:1 by , 6 years ago
Description: | modified (diff) |
---|
by , 6 years ago
Attachment: | connection_leak.zip added |
---|
comment:2 by , 6 years ago
Component: | Uncategorized → Database layer (models, ORM) |
---|---|
Summary: | Connection leak if CONN_MAX_AGE == None → Persistent connections not reused on request. |
Triage Stage: | Unreviewed → Accepted |
Version: | 2.2 → master |
I can reproduce this issue only when I reload page before previous request is handled, e.g.
open connection +++++++++++++++++++ [06/May/2019 08:02:37] "GET / HTTP/1.1" 200 2 open connection +++++++++++++++++++ open connection +++++++++++++++++++ open connection +++++++++++++++++++ [06/May/2019 08:02:37] "GET / HTTP/1.1" 200 2 [06/May/2019 08:02:37] "GET / HTTP/1.1" 200 2 [06/May/2019 08:02:37] "GET / HTTP/1.1" 200 2
Accepted for future investigation.
follow-up: 4 comment:3 by , 5 years ago
correct me if I'm wrong, CONN_MAX_AGE
is a numeric parameter, and None is not a valid value for a numeric parameter. isn't it reasonable to raise ImproperlyConfigured exception when someone set this parameter to None?
comment:4 by , 5 years ago
Replying to Mahdi Zareie:
correct me if I'm wrong,
CONN_MAX_AGE
is a numeric parameter, and None is not a valid value for a numeric parameter. isn't it reasonable to raise ImproperlyConfigured exception when someone set this parameter to None?
I just understood what's going on, according to the documentations:
The default value is 0, preserving the historical behavior of closing the database connection at the end of each request. To enable persistent connections, set CONN_MAX_AGE to a positive number of seconds. For unlimited persistent connections, set it to None.
comment:5 by , 5 years ago
Resolution: | → invalid |
---|---|
Status: | new → closed |
I reproduce the problem using the example source code you have provided when I used development server, it said django.db.utils.OperationalError: FATAL: sorry, too many clients already
.
But I failed in reproducing the same error when I tried the same scenario using gunicorn, according to documentation:
The development server creates a new thread for each request it handles, negating the effect of persistent connections. Don’t enable them during development.
so I think it's not a bug, it's the expected behavior if you are using development server.
Demo project.