CODEWITHSUNDEEP
pythonpython-3.xpostgresql
Joysn
Joysn

Reputation: 1029

DB connections stay in idle state using psycopg2 Postgres driver with Python

When running the following code to insert data to a database table, each time a DB connection appears in pg_stat_activity which remains there in state idle:

column_names = ", ".join(columns)

query = f"INSERT INTO {table_name} ({column_names}) VALUES %s"
values = [tuple(record.values()) for record in records]

with psycopg2.connect(dbname=dbname, user=user, password=password, host=host, port=port, application_name=app_name) as conn:
    with conn.cursor() as c:
        psycopg2.extras.execute_values(cur=c, sql=query, argslist=values, page_size=batch_size)

UPDATE: Code looks like this now, after input from Adrian:

conn = psycopg2.connect(dbname=dbname, user=user, password=password, host=host, port=port, application_name=app_name)
with conn:
    with conn.cursor() as c:
        psycopg2.extras.execute_values(cur=c, sql=query, argslist=values, page_size=self._batch_size)
        conn.commit()
conn.close()
del conn

When running multiple times, the following error is suddenly thrown, indicating that the connections are exhausted:

E       psycopg2.OperationalError: connection to server at "localhost" (::1), port 5446 failed: Connection refused (0x0000274D/10061)
E           Is the server running on that host and accepting TCP/IP connections?
E       connection to server at "localhost" (127.0.0.1), port 5446 failed: FATAL:  remaining connection slots are reserved for roles with the SUPERUSER attribute

Might it be that this happens when connecting to the DB through a ssh tunnel? Using port forwarding via VSCode to a DB in Azure cloud. I experience the same behaviour when using DBeaver DB client.

Upvotes: 0

Views: 37

Answers (2)

Joysn
Joysn

Reputation: 1029

The problem arose from the port forwarding via VSCode from my local machine to the remote Postgres database server. I switched to the ssh command line client and now the connections are closed properly.

Upvotes: 0

Adrian Klaver
Adrian Klaver

Reputation: 19724

From here psycopg2 connection:

Note that the connection is not closed by the context and it can be used for several contexts..

For psycopg2 you will need to explicitly close the connection.

In psycopg(3) that changes per:

https://www.psycopg.org/psycopg3/docs/basic/from_pg2.html

with connection

In psycopg2, using the syntax with connection, only the transaction is closed, not the connection. This behaviour is surprising for people used to several other Python classes wrapping resources, such as files.

In Psycopg 3, using with connection will close the connection at the end of the with block, making handling the connection resources more familiar.

In order to manage transactions as blocks you can use the Connection.transaction() method, which allows for finer control, for instance to use nested transactions.

Upvotes: 1

Related Questions