How to Set Timeouts in psycopg2 using Python

Setting timeouts in psycopg2 is crucial for ensuring your application doesn't hang indefinitely while waiting for a response from the PostgreSQL database. Timeouts help maintain application performance and reliability by ensuring that long-running queries or connection issues do not negatively impact the overall user experience. In this article, we will discuss what timeouts in psycopg2 mean, and provide three good code examples on how to set them.

What is Meant by Timeouts in psycopg2?

In psycopg2, timeouts refer to the maximum amount of time allowed for a database operation to complete. If the operation does not complete within this time frame, an exception is raised, allowing the application to handle the timeout gracefully. Timeouts can be set for different aspects such as connection, statement execution, and read/write operations.

Examples of How to Set Timeouts in psycopg2

1. Setting Connection Timeout

The connection timeout specifies how long the client will wait for a connection to be established with the PostgreSQL server before giving up.

In this example, the connect_timeout parameter is set to 10 seconds. If the connection is not established within this time, an OperationalError is raised.

import psycopg2

conn = psycopg2.connect(
    dbname="postgres1",
    user="postgres",
    password="1234",
    host="localhost",
    port="5432",
    connect_timeout=10  # timeout in seconds
)

print('connected')

Output

connected

2. Setting Statement Timeout

The statement timeout specifies the maximum amount of time a query is allowed to run before being aborted by the server.

In this example, the SET statement_timeout command is used to set the statement timeout to 5 seconds. If the query execution exceeds this time, it is aborted.

import psycopg2
from psycopg2 import sql, OperationalError

try:
    conn = psycopg2.connect(
        dbname="postgres1",
        user="postgres",
        password="1234",
        host="localhost",
        port="5432"
    )
    cursor = conn.cursor()
    cursor.execute("SET statement_timeout = %s", ('5000',))  # timeout in milliseconds

    query = sql.SQL("SELECT pg_sleep(10)")  # This query will run for 10 seconds
    cursor.execute(query)
    print("Query executed successfully")
except OperationalError as e:
    print(f"Query failed: {e}")
finally:
    if 'cursor' in locals() and cursor is not None:
        cursor.close()
    if 'conn' in locals() and conn is not None:
        conn.close()

Output

3. Setting Read/Write Timeouts

The read and write timeouts specify how long the client will wait for data to be read from or written to the server.

In this example, a custom function set_socket_timeout is used to set the read/write timeout for the connection. The timeout is set to 5 seconds, and if the query execution exceeds this time, it is aborted.

import psycopg2
from psycopg2 import OperationalError

# Custom function to set timeout
def set_socket_timeout(conn, timeout):
    with conn.cursor() as cursor:
        cursor.execute(f"SET statement_timeout = {timeout}")

try:
    conn = psycopg2.connect(
        dbname="postgres1",
        user="postgres",
        password="1234",
        host="localhost",
        port="5432"
    )
    set_socket_timeout(conn, '5000')  # timeout in milliseconds
    cursor = conn.cursor()
    cursor.execute("SELECT pg_sleep(10)")  # This query will run for 10 seconds
    print("Query executed successfully")
except OperationalError as e:
    print(f"Read/Write operation failed: {e}")
finally:
    if 'cursor' in locals():
        cursor.close()
    if 'conn' in locals():
        conn.close()

Output

Use Cases

1. Preventing Long-Running Queries: Ensures queries that exceed a set duration are automatically terminated.
2. Handling Network Issues: Limits wait time for establishing a connection in case of network instability.
3. Avoiding Database Deadlocks: Helps avoid situations where queries wait indefinitely due to database locks.
4. Managing Resource Usage: Prevents excessive resource consumption by terminating stalled queries.
5. Improving Application Responsiveness: Ensures that the application remains responsive by avoiding hanging operations.

Conclusion

Setting timeouts in psycopg2 is essential for maintaining the performance and reliability of your applications. By configuring connection, statement, and read/write timeouts, you can ensure that your application does not hang indefinitely and can handle database operation delays gracefully. The examples provided demonstrate how to set these timeouts effectively, allowing you to enhance your application's robustness when interacting with PostgreSQL databases.