Found two places where Postgres connections may be leaking. One of them looks like a likely contributor to the "remaining connection slots are reserved for non-replication superuser connections" error in generate-datasets.
The first is run_sql_script() in sql2csv.py. The connection is closed when the query succeeds, but not when it fails. If pd.read_sql_query raises an exception, execution moves to the except block, logs the error, and continues without closing the connection.
Because this function runs once for every .sql file in the queries/ directory, each failed query can leave a connection open. Given the number of files processed in a single run, this seems like the more likely cause of tonight’s issue.
The second is in run_pg.py, which is used by a separate Prefect flow. In that case, the connection is never explicitly closed, whether the query succeeds or fails. The impact is likely smaller, but it is the same underlying problem in another part of the codebase.
Both cases should be fixed by ensuring the connection is always closed, either with a try/finally block or a context manager.
(Happy to help once connection pool recovers)
Found two places where Postgres connections may be leaking. One of them looks like a likely contributor to the "remaining connection slots are reserved for non-replication superuser connections" error in generate-datasets.
The first is run_sql_script() in sql2csv.py. The connection is closed when the query succeeds, but not when it fails. If pd.read_sql_query raises an exception, execution moves to the except block, logs the error, and continues without closing the connection.
Because this function runs once for every .sql file in the queries/ directory, each failed query can leave a connection open. Given the number of files processed in a single run, this seems like the more likely cause of tonight’s issue.
The second is in run_pg.py, which is used by a separate Prefect flow. In that case, the connection is never explicitly closed, whether the query succeeds or fails. The impact is likely smaller, but it is the same underlying problem in another part of the codebase.
Both cases should be fixed by ensuring the connection is always closed, either with a try/finally block or a context manager.
(Happy to help once connection pool recovers)