Plotly make too many connections

📊 Plotly Python
1

Hi all.
I have a server using plotly and multipage reports.

My typical report looks something like this:

import things

conn = psycopg2.connect(database="postgres", user="log", password='pass', host='127.0.0.1', port='5432')
register_page(__name__, path="/index")
layout = html.Div(children=[
    html.Div(id='graph'),
])
  
@callback(
    Output('graph', 'figure'),
    Input('url', 'pathname'),
)
def table(url):
    cursor = conn.cursor()
    cursor.execute("""
SELECT *
FROM table
ORDER BY date ASC 
    """)

    df = pd.DataFrame(cursor.fetchall(), columns=['date', 'sum'])
    cursor.close()
    fig = px.line(df, x="date", y="sum")
    
    return fig

It works. But I ran into a problem. After starting the server, there will be approximately 40 sessions with the database.
2023-07-28 11_06_02-pgAdmin 4

There is also a test server that uses the same database. And it turned out that we have a limit on the number of sessions. Now the test server starts not every time, because the two of them exceed the limit of such connections.

Why are sessions created? Did I do something wrong?
How to make sure that connections are created only at the time of calling callback?

2

Damn it.
It’s because I have conn = psycopg2.connect outside of the callback, right? Now I need to view all the files and move this line inside the callback?

3

I think the issue you’re hitting is that the cursors created in the callback aren’t getting closed when the callback is done with them and those are contributing the the number of idle sessions. Psycopg2 recommends closing cursors as soon as you’re done with them in most cases so I think you can help mitigate this issue by adding cursor.close() at the bottom of your callback.

There is some merit to creating the connection in the callback though since if the db gets restarted, that global connection object would go stale and your app would break until you redeploy it. You would take a performance hit doing that since it takes some time to create a connection though so a better solution would be to use a connection pool like sqlalchemy’s engine. That would create a few connections that your app’s callbacks can share and if any go stale, it can automatically remove it and create a new one.

2 Likes
4

No no no. I have cursor.close() of course. At the end of each of the callbacks and in each of the except if something went wrong. Accidentally deleted from example.