[Django] #35685: Connection Pooling Not Working with psycopg3 in Django

[Django]

#35685: Connection Pooling Not Working with psycopg3 in Django
------------------------+-----------------------------------------
Reporter: tsw025 | Type: Uncategorized
Status: new | Component: Uncategorized
Version: 5.1 | Severity: Normal
Keywords: | Triage Stage: Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
------------------------+-----------------------------------------
We are encountering issues with database connection pooling in Django when
using psycopg3. The connections are being closed after each request,
preventing effective pooling. Attempting to set CONN_MAX_AGE to a value
greater than 0 results in an ImproperlyConfigured error, indicating that
"Pooling doesn't support persistent connections."

Steps to Reproduce:

Configure Django to use psycopg3 with PostgreSQL.
Set CONN_MAX_AGE to a value greater than 0 in the DATABASES settings.
Observe that connections are closed after each request and pooling is not
functioning as expected.
Expected Behavior:
Database connections should persist and be reused across requests
according to the pooling configuration.

Actual Behavior:
Connections are closed after each request, and an error is raised when
setting CONN_MAX_AGE to a non-zero value.

Configuration:

Python 3.10
Django 5.1
psycopg3 3.2.1 (with pooling enabled)
PostgreSQL


Config:

{{{
RDS_DB_ENGINE = "django.db.backends.postgresql"

if USE_SSL and not TESTING:
OPTIONS = {
"sslmode": "verify-full",
"sslrootcert": SECURE_RDS_CERTIFICATE_FILE,
"pool": True
}
else:
OPTIONS = {
"sslmode": "disable",
}

DATABASES = {
"default": {
"ENGINE": IAM_DB_ENGINE if USE_RDS_IAM_AUTH else RDS_DB_ENGINE,
"NAME": RDS_DB_NAME,
"USER": RDS_DB_USER,
"PASSWORD": RDS_DB_PASSWORD,
"HOST": RDS_HOST,
"PORT": RDS_PORT,
"OPTIONS": OPTIONS,
},
"read-db": {
"ENGINE": IAM_DB_ENGINE if USE_RDS_IAM_AUTH else RDS_DB_ENGINE,
"NAME": RDS_DB_NAME,
"USER": RDS_DB_USER,
"PASSWORD": RDS_DB_PASSWORD,
"HOST": RDS_READ_HOST,
"PORT": RDS_READ_PORT,
"OPTIONS": OPTIONS,
},
}
}}}


Error and Pooling Logic: Django.db.backends.postgres.base


{{{
@property
def pool(self):
pool_options = self.settings_dict["OPTIONS"].get("pool")
if self.alias == NO_DB_ALIAS or not pool_options:
return None

if self.alias not in self._connection_pools:
if self.settings_dict.get("CONN_MAX_AGE", 0) != 0:
raise ImproperlyConfigured(
"Pooling doesn't support persistent connections."
)
# Set the default options.
if pool_options is True:
pool_options = {}
}}}

Connection Close Logic:Django.db.backends.base.base



{{{
max_age = self.settings_dict["CONN_MAX_AGE"]
self.close_at = None if max_age is None else time.monotonic() + max_age

def close_if_unusable_or_obsolete(self):
if self.connection is not None:
self.health_check_done = False
if self.get_autocommit() != self.settings_dict["AUTOCOMMIT"]:
self.close()
return

if self.errors_occurred:
if self.is_usable():
self.errors_occurred = False
self.health_check_done = True
else:
self.close()
return

if self.close_at is not None and time.monotonic() >=
self.close_at:
self.close()
return

}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/35685>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.

[Django]

#35685: Connection Pooling Not Working with psycopg3 in Django

-------------------------------+--------------------------------------
Reporter: tsw025 | Owner: (none)

Type: Uncategorized | Status: new
Component: Uncategorized | Version: 5.1

Severity: Normal | Resolution:

Keywords: | Triage Stage: Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0

-------------------------------+--------------------------------------
Changes (by tsw025):

* Attachment "error_log_after_minor_adjustments" added.

[Django]

#35685: Connection Pooling Not Working with psycopg3 in Django

-------------------------------------+-------------------------------------
Reporter: Tharinda Seth | Owner: (none)
Wijesekera |

Type: Uncategorized | Status: new
Component: Uncategorized | Version: 5.1
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0

-------------------------------------+-------------------------------------
Changes (by Tharinda Seth Wijesekera):

* Attachment "image-20240817-023203.png" added.

[Django]

#35685: Connection Pooling Not Working with psycopg3 in Django
-------------------------------------+-------------------------------------
Reporter: Tharinda Seth | Owner: (none)
Wijesekera |
Type: Uncategorized | Status: new
Component: Uncategorized | Version: 5.1
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Tharinda Seth Wijesekera):

* Attachment "error_log_after_minor_adjustments.2" added.

was able to get the code running after few adjustment to the codebase.

[Django]

#35685: Connection Pooling Not Working with psycopg3 in Django

-------------------------------+--------------------------------------
Reporter: tsw025 | Owner: (none)

Type: Uncategorized | Status: new
Component: Uncategorized | Version: 5.1
Severity: Normal | Resolution:
Keywords: | Triage Stage: Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0

-------------------------------+--------------------------------------
Changes (by tsw025):

* Attachment "image-20240817-023352.png" added.

[Django]

#35685: Connection Pooling Not Working with psycopg3 in Django
-------------------------------+--------------------------------------
Reporter: tsw025 | Owner: (none)
Type: Uncategorized | Status: new
Component: Uncategorized | Version: 5.1
Severity: Normal | Resolution:
Keywords: | Triage Stage: Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------+--------------------------------------
Changes (by tsw025):

* Attachment "error_log_after_minor_adjustments.2" removed.

[Django]

#35685: Connection Pooling Not Working with psycopg3 in Django
-------------------------------+--------------------------------------
Reporter: tsw025 | Owner: (none)
Type: Uncategorized | Status: new
Component: Uncategorized | Version: 5.1
Severity: Normal | Resolution:
Keywords: | Triage Stage: Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------+--------------------------------------
Changes (by tsw025):

* Attachment "image-20240817-023352.png" removed.

[Django]

#35685: Connection Pooling Not Working with psycopg3 in Django

-------------------------------------+-------------------------------------
Reporter: Tharinda Seth | Owner: (none)
Wijesekera |

Type: Uncategorized | Status: new
Component: Uncategorized | Version: 5.1
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0

-------------------------------------+-------------------------------------
Changes (by Tharinda Seth Wijesekera):

* Attachment "image-20240817-023420.png" added.

[Django]

#35685: Connection Pooling Not Working with psycopg3 in Django
-------------------------------------+-------------------------------------
Reporter: Tharinda Seth | Owner: (none)
Wijesekera |
Type: Uncategorized | Status: new
Component: Uncategorized | Version: 5.1
Severity: Normal | Resolution:
Keywords: | Triage Stage:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------------+-------------------------------------
Changes (by Tharinda Seth Wijesekera):

* Attachment "image-20240817-023601.png" added.