Persistent connections not reused on request.

[cryptogun]

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

1. 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,
       }
   }
   

2. 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

3. Open a new incognito browser Ctrl + Shift + n.
4. Goto landing page.
5. Check Django log output.

open connection +++++++++++++++++++
No close log printed.

6. Goto 3. and try as many times as you want.
7. Switch to PostgreSQL backend, refresh 100 times, and you will get the above OperationalError.

8. Set CONN_MAX_AGE to 0 in settings.py.
9. Retry 3~5

Now the close was executed.

open connection +++++++++++++++++++
[04/May/2019 16:09:53] "GET / HTTP/1.1" 200 2
close connection ......................

[cryptogun]

Demo project.

[Mariusz Felisiak]

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.

[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?

[Mahdi Zareie]

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.

[Mahdi Zareie]

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.