Making sure that psycopg2 database connection alive

Question

I have a python application that opens a database connection that can hang online for an 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

Thanks.

score 81 · Answer 1 · edited Jan 07 '23 at 17:16

81

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

edited Jan 07 '23 at 17:16

Torxed

22,866
14
82
131

answered Sep 09 '13 at 23:29

Jaymon

5,363
3
34
34

6

Have you tried killing database connections TCP handle (on Windows). `connection.closed` unfortunately won't change value. – Vyktor Nov 19 '14 at 10:01
1

@Vyktor 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. – Jaymon Dec 11 '14 at 23:17
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` – raphael Aug 19 '16 at 13:57
connection.closed 2 means? – Smart Manoj Oct 09 '20 at 12:37
4

@SmartManoj Looking at the [code](https://github.com/psycopg/psycopg2/blob/e14e3385b4809ec4223894f8c7a009b1560eb41d/psycopg/connection.h#L95) it looks like 2 means something horrible happened – Jaymon Oct 14 '20 at 19:47

score 29 · Accepted Answer · answered Aug 15 '09 at 14:36

29

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.

answered Aug 15 '09 at 14:36

Martin v. Löwis

124,830
17
198
235

3

I came to the same conclusion while reading psycopg2 sources. Thanks. – HardQuestions Aug 15 '09 at 14:43
2

Will file the request to psycopg author to add such functionality. – HardQuestions Aug 15 '09 at 14:46
1

See Jaymon's answer below. – sage88 Feb 03 '17 at 08:24

score 19 · Answer 3 · answered Nov 20 '13 at 08:39

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")

Tested with psycopg2 2.5.2 and psql 8.4 - isolation level is always zero no matter what. — Zaar Hai, Feb 03 '14 at 12:05

score 5 · Answer 4 · answered Apr 13 '20 at 09:39

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(..)

score 0 · Answer 5 · answered Aug 09 '23 at 16:52

0

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.

answered Aug 09 '23 at 16:52

Ramón J Romero y Vigil

17,373
7
77
125

Making sure that psycopg2 database connection alive

5 Answers5

Linked