Making sure that psycopg2 database connection alive

Question

I have a python application that opens a database connection that can hang online for hours, but sometimes the database server reboots and while python still have the connection it won't work with OperationalError exception.

So I'm looking for any reliable method to "ping" the database and know that connection is alive. I've checked a psycopg2 documentation but can't find anything like that. Sure I can issue some simple SQL statement like SELECT 1 and catch the exception, but I hope there is a native method, something like PHP pg_connection_status.

Outgrowth

Answer 1

This question is really old, but still pops up on Google searches so I think it's valuable to know that the psycopg2.connection instance now has a closed attribute that will be 0 when the connection is open, and greater than zero when the connection is closed. The following example should demonstrate:

import psycopg2
import subprocess

connection = psycopg2.connect(
    dbname=database,
    user=username,
    password=password,
    host=host,
    port=port
)

print connection.closed # 0

# restart the db externally
subprocess.check_call("sudo /etc/init.d/postgresql restart", shell=True)

# this query will fail because the db is no longer connected
try:
    cur = connection.cursor()
    cur.execute('SELECT 1')
except psycopg2.OperationalError:
    pass

print connection.closed # 2

Lentissimo

Have you tried killing database connections TCP handle (on Windows). connection.closed unfortunately won't change value. – Hamo

@Hamo You're right! The problem is that Python's connection doesn't know it has been severed until it tries to communicate with the db. I've updated the example. The good news is you can wrap the query executing code to check the connection on error and reconnect as appropriate. – Lentissimo

My connection closed during a query because the database restarted, cur.execute('SELECT 1') threw an InterfaceError in my case with message cursor already closed – Cretaceous

connection.closed 2 means? – Antinucleon

@SmartManoj Looking at the code it looks like 2 means something horrible happened – Lentissimo

Answer 2

pg_connection_status is implemented using PQstatus. psycopg doesn't expose that API, so the check is not available. The only two places psycopg calls PQstatus itself is when a new connection is made, and at the beginning of execute. So yes, you will need to issue a simple SQL statement to find out whether the connection is still there.

Butcherbird

I came to the same conclusion while reading psycopg2 sources. Thanks. – Outgrowth

Will file the request to psycopg author to add such functionality. – Outgrowth

See Jaymon's answer below. – Fourscore

Answer 3

connection.closed does not reflect a connection closed/severed by the server. It only indicates a connection closed by the client using connection.close()

In order to make sure a connection is still valid, read the property connection.isolation_level. This will raise an OperationalError with pgcode == "57P01" in case the connection is dead.

This adds a bit of latency for a roundtrip to the database but should be preferable to a SELECT 1 or similar.

import psycopg2
dsn = "dbname=postgres"
conn = psycopg2.connect(dsn)

# ... some time elapses, e.g. connection within a connection pool

try:
    connection.isolation_level
except OperationalError as oe:
    conn = psycopg2.connect(dsn)

c = conn.cursor()
c.execute("SELECT 1")

Gatling

Tested with psycopg2 2.5.2 and psql 8.4 - isolation level is always zero no matter what. – Mortise

Answer 4

howto check if connection closed:

• conn.closed is 1 if closed else 0

• if closed it raises except psycopg2.InterfaceError as exc: not only on query but in context manager: with conn: is sufficient for raise.

• you then need to reestablish the connection. eg read out the pw and put into .connect(..)

Norrie

Answer 5

The ultimate solution, that addresses the problem of connection maintenance, is best implemented using a connection pool which then becomes responsible for maintaining live connections.

Empathic

Answer 6

For those who are using a ConnectionPool in new psycopg 3, I note that since version 3.2 the check parameter is being introduced which calls a callback function before returning the connection. In this way the application receives always a working connection.
Also is provided a builtin ConnectionPool.check_connection method as a simple check. A configuration example is (from official documentation):

with ConnectionPool(
    ..., check=ConnectionPool.check_connection, ...
) as pool:
    ...

Bisectrix