I have SQLAlchemy CORE 1.0.9 with Pyramid framework 1.7. And I am using the following configuration for connecting to a postgres 9.4 database:
# file __ini__.py
from .factories import root_factory
from pyramid.config import Configurator
from sqlalchemy import engine_from_config
def main(global_config, **settings):
""" This function returns a Pyramid WSGI application."""
config = Configurator(settings=settings, root_factory=root_factory)
engine = engine_from_config(settings, prefix='sqlalchemy.')
# Retrieves database connection
def get_db(request):
connection = engine.connect()
def disconnect(request):
connection.close()
request.add_finished_callback(disconnect)
return connection
config.add_request_method(get_db, 'db', reify=True)
config.scan()
return config.make_wsgi_app()
After a few hours using the app I start getting the following error:
sqlalchemy.exc.OperationalError: (psycopg2.OperationalError) FATAL: remaining connection slots are reserved for non-replication superuser connections
Apparently I have reached the maximum number of connections. It seems like connections.close() doesn't really close the connection, just returns the connection to the pool. I know I could use the NullPool to disable pooling but probably that will have a huge impact in the performance.
Does somebody know the right way to configure SQLAlchemy Core to get a good performance and close the connections properly?
Please abstain from sending links to pyramid tutorials. I am NOT interested in SQLAlchemy ORM setups. Only SQLAlchemy Core please.
Actually everything was fine in the previous setup. The problem was caused by Celery workers not closing connections.