Using AsyncPostgresSaver with Supabase's session pooler crashes with psycopg.OperationalError

[afnan-davia]

Hello all! I hope you are doing well. I am having issues with the psycopg.OperationalError and AsyncConnectionPool. I am using FastAPI in a container that is deployed on AWS AppRunner. Given that AppRunner doesn't have outgoing IPv6, I cannot directly use Supabase's postgres URL postgresql://postgres:[YOUR-PASSWORD]@db.[PROJECT-ID].supabase.co:5432/postgres.

Therefore, I have to go through the Session pooler: postgresql://postgres.[PROJECT-ID]:[YOUR-PASSWORD]@aws-0-eu-west-3.pooler.supabase.com:5432/postgres.

Here is how I instanciate my pool:

@asynccontextmanager
async def lifespan(app: FastAPI):
    global db_pool
    db_pool = AsyncConnectionPool(
        conninfo=os.getenv("DB_URL"),
        max_size=10,
        kwargs={
            "autocommit": True,
            "prepare_threshold": 0,
        },
    )
    # Startup: create pool and tables
    await db_pool.open()
    yield
    # Shutdown: close all connections
    await db_pool.close()


app = FastAPI(lifespan=lifespan)

Unfortunately, I am randomly having the error psycopg.OperationalError: consuming input failed: server closed the connection unexpectedly when the code tries to execute

saved = await self.checkpointer.aget_tuple(self.checkpoint_config)

I tried using:

kwargs={
    "autocommit": True,
    "prepare_threshold": 0,
    "keepalives": 1,
    "keepalives_idle": 30,
    "keepalives_interval": 10,
    "keepalives_count": 5
}

but it still doesn't work. I looked on Supabase what connections are there and I can see 4 idle connections yet it crashed with the error mentioned above:

SELECT
  pg_stat_activity.pid as connection_id,
  ssl,
  datname as database,
  usename as connected_role,
  application_name,
  client_addr as IP,
  query,
  query_start,
  state,
  backend_start
FROM pg_stat_ssl
JOIN pg_stat_activity
ON pg_stat_ssl.pid = pg_stat_activity.pid
WHERE application_name = 'Supavisor';

I would be really grateful for any help on this subject. This is the last step missing to put our chatbot with LangGraph's superpowers into production. Thank you very much for your time!

[saocorley]

Hey,
I'm having a similar issue. Did you solve it?

[ehtashamtoor]

I am having this issue also. after some time the connection gets lost. what I tried for now is this. whenever a connection is lost, i reconnect it automatically in my workflow. some how it is working, but not an optimal solution. So looking for that.

[saocorley]

I managed to solve it by using the following parameters

kwargs={
    "autocommit": False,
    "prepare_threshold": None,
}

I manually close the connections when needed. In my case it seemed like the "autocommit" function was creating many redundant connections and this fixed it. Not sure if this is helpful to you though