Timeouts and cancellation#

Three different things can keep a caller waiting, and each has its own remedy:

Waiting for

Bounded by

a statement that takes too long

a deadline, which the server enforces

a statement you want to stop now

cancelling it

a network that has silently gone away

TCP keepalive, on by default

Deadlines#

A Deadline is a budget in seconds that starts when you create it:

>>> from agensgraph import Deadline
>>> budget = Deadline(5.0)
>>> budget.remaining() <= 5.0, budget.expired
(True, False)

Give one to a pool borrow, pool.connection(deadline=budget), and it bounds the wait for a connection and the statements run on it together; see Pooling. On a connection you hold yourself, conn.deadline() does the same for a block:

>>> with conn.deadline(5.0) as budget:
...     conn.execute("SHOW statement_timeout").fetchone()
('4499ms',)
>>> conn.execute("SHOW statement_timeout").fetchone()
('0',)

It takes a Deadline or a number of seconds, and returns the Deadline it is spending.

The server enforces it. The budget reaches the server as statement_timeout, so a statement that runs past it is stopped by the server and fails with QueryCanceled:

>>> with conn.deadline(1.0):
...     conn.execute("SELECT pg_sleep(3)")
Traceback (most recent call last):
  ...
psycopg.errors.QueryCanceled: canceling statement due to statement timeout
>>> conn.rollback()

That is deliberate. A caller that simply stopped waiting would leave the statement running on a connection nobody is reading. The limit is set half a second inside what is left (gap= changes it), so the server gives up before the caller does, and the setting is put back afterwards, by name, even when a statement in the block raised: a connection opened with its own options="-c statement_timeout=..." returns to that, not to none.

Three things to know about a deadline:

  • The limit applies to each statement. It is computed once, as the block starts, so every statement in the block gets the same limit; it does not shrink as they run. Check budget.remaining() between statements if the total matters.

  • A budget too small for the gap fails at once. With half a second reserved, Deadline(0.4) leaves nothing for a statement, and the block raises before running anything:

    >>> with conn.deadline(0.4):
...     pass
Traceback (most recent call last):
  ...
agensgraph.deadline.Expired: setting a limit ran out of its 0.400s budget after 0.000s

    Lower gap= for budgets that tight.

  • Setting the limit costs two statements, one to set it and one to put it back, so a block is the right size for it: over a local connection one bare statement measured three times slower in a block of its own, and ten statements in one block 1.26 times. That is why execute_query() has no timeout_ argument.

Expired is raised when the budget runs out before anything is sent: while waiting for a pooled connection, or before setting a limit. It is a subclass of the built-in TimeoutError, so code that already catches that keeps working. transaction_timeout and idle_in_transaction_session_timeout are never used as a deadline, because they end the whole session rather than the statement.

Deadline(seconds, commit_reserve=...) keeps part of the budget back from the statements, for the COMMIT at the end: available() is what the statements may spend, and can_commit whether the reserve is still there. Deadline.none() is a budget with no limit, which sets nothing and costs nothing.

Cancelling a statement#

To stop a statement that is running, ask the server to stop it. cancel_safe() is psycopg’s method, and it can be called from another thread or task:

import threading, time

def cancel_soon():
    time.sleep(0.5)
    conn.cancel_safe(timeout=5.0)

threading.Thread(target=cancel_soon).start()

>>> conn.execute("SELECT pg_sleep(10)")
Traceback (most recent call last):
  ...
psycopg.errors.QueryCanceled: canceling statement due to user request
>>> conn.rollback()
>>> conn.execute("SELECT 1").fetchone()
(1,)

The statement’s caller receives QueryCanceled, SQLSTATE 57014, which is the only evidence that the cancel arrived: a cancel request that reaches no statement returns as quietly as one that does. A connection whose statement was cancelled this way is in step and idle, and a pool keeps it.

Interrupting is not the same as cancelling. A statement whose caller is interrupted, by a cancelled asyncio task, an asyncio.timeout() around the call, or KeyboardInterrupt, leaves its connection in a state nobody can describe: the statement may still be running, and part of its result may be waiting on the socket. A pool closes such a connection when it comes back rather than lend it again. A connection you hold yourself is left to you; psycopg tries to bring it back into step, and if a later statement on it fails strangely, close it and open another.

Abandoning a task is neither. A statement whose task is never awaited and never cancelled keeps running on the server; nothing on the client can see it, because it is simply a statement that has not answered yet. Cancel the task, or call cancel_safe(); do not just stop waiting.

57014 has several causes, among them a cancel, statement_timeout, and a conflict with recovery on a standby, so the driver does not guess which from the message, which is localised. Retries classifies it as not worth retrying on its own; decide yourself whether a statement you cancelled or timed out should run again.

When the network goes quiet#

A connection waiting for a reply has nothing to send, so if the network between it and the server silently stops carrying packets, nothing on the client notices: the wait lasts until the operating system gives up on the connection, which on Linux takes over two hours.

TCP keepalive probes are what notice. The driver turns them on unless you have set them yourself: the first probe after 30 seconds of silence, then every 10 seconds, and the connection fails after 3 unanswered, about a minute in all. Each of the four settings is filled in only if you did not give it, so naming one keeps the others:

watcher = agensgraph.connect("host=localhost dbname=docs user=agens keepalives_idle=10")

keepalives=0 turns them off. A connection the probes have given up on reports conn.broken, and a pool replaces it.

tcp_user_timeout is often recommended for this and does not do it. It bounds how long sent data may go unacknowledged, and a connection waiting for an answer has sent nothing. Measured against a server whose traffic was dropped, a connection with only tcp_user_timeout was still waiting when the test gave up after twenty seconds, while one with keepalive probes, shortened for the test to one second apart, failed in under fifteen. It is useful alongside keepalive, and the driver leaves it unset because too small a value would also end a healthy connection that is sending a large statement.