PostgreSQL reconnect: if the reconnect itself fails, the adapter never recovers (follow-up to #236) #242

Summary

#236 (merged to v3, not yet in a release) makes the PostgreSQL adapter reconnect after the server
drops the connection. One path is still open: the reconnect attempt itself fails.

That is likely during a failover. The first statement after the drop often runs before the new
primary can be reached. After a failed reconnect, the adapter never tries again, and every
statement fails until the process restarts. That is the same symptom #236 fixes.

I found this by reading tina4_python/database/postgres.py on v3 (cec6076). I have not
reproduced it against a live PostgreSQL yet. The regression test below should confirm it, or show me
wrong.

Why it happens

1. _exec_with_handling() sees the dead connection and calls _reconnect().
2. _reconnect() calls self._conn.close(), then self.connect(...).
3. connect() only assigns self._conn when psycopg2.connect() succeeds. If the server is still
   unreachable, connect() raises and self._conn stays the old, now closed, handle.
4. On the next statement, execute(), fetch() and fetch_one() call self._conn.cursor(...)
   before _exec_with_handling(). psycopg2 raises InterfaceError: connection already closed
   right there. That's outside the reconnect logic, so _connection_is_dead() is never checked.
5. Every later statement fails the same way. Only a process restart recovers.

Suggested fix

Replace a dead link before creating the cursor, and use this at the three call sites:

def _open_cursor(self, cursor_factory=None):
    """Return a cursor on a live connection.

    A link the server dropped (and that is not inside an explicit transaction) is
    replaced first. If that reconnect fails, the dead handle stays in place, so the
    next statement tries again instead of failing forever.
    """
    if self._connection_is_dead() and not self._in_transaction:
        self._reconnect()
    if cursor_factory is None:
        return self._conn.cursor()
    return self._conn.cursor(cursor_factory=cursor_factory)

Regression test (real PostgreSQL, no mocks)

For tests/test_postgres_reconnect_after_drop.py. The failure is real: a refused connection to a
local port nothing listens on.

def test_a_failed_reconnect_is_retried_on_the_next_statement(observer, tested):
    adapter = tested.adapter
    working_arguments = adapter._connect_args
    connection_string, username, password, kwargs = working_arguments
    parsed = urlparse(connection_string)
    credentials = parsed.netloc.rpartition("@")[0]
    unreachable = parsed._replace(
        netloc=f"{credentials}@127.0.0.1:1" if credentials else "127.0.0.1:1"
    ).geturl()

    _drop(observer, _pid(tested))
    adapter._connect_args = (unreachable, username, password, kwargs)   # server still down
    try:
        with pytest.raises(Exception):
            tested.fetch_one("SELECT 1 AS ok")                          # the reconnect fails
    finally:
        adapter._connect_args = working_arguments                       # server is back

    # Expected to fail on v3 today with "InterfaceError: connection already closed".
    assert tested.fetch_one("SELECT 1 AS ok")["ok"] == 1

Also worth checking

• Release: Reconnect PostgreSQL after the server drops the connection #236 is not in a release yet (3.13.151 does not contain it). Apps on 3.13.x stay
  exposed until it ships. A release with Reconnect PostgreSQL after the server drops the connection #236 plus this fix would let them remove app-side
  workarounds.
• Firebird: Reconnect PostgreSQL after the server drops the connection #236 says it follows the Firebird adapter's dead-connection handling. The same
  failed-reconnect path may exist there.

🤖 Generated with Claude Code