connection closed when using postgres

[edublancas]

when connected to postgres, I run a few queries, and if I leave the terminal (I'm assuming this also happens in jupyter) open for a few minutes and come back to run more queries, I get this:

In [5]: %sql select * from table
Running query in 'postgresql://****:***@****:5432/****'
(psycopg2.OperationalError) server closed the connection unexpectedly
	This probably means the server terminated abnormally
	before or while processing the request.

[SQL: select * from users]
(Background on this error at: https://sqlalche.me/e/20/e3q8)

Unsure if this is a postgres (or psycopg2) exclusive problem or it happens with other drivers.

We should first investigate what's happening with Postgres and try replicating it with others. I'm creating the connection with:

from sqlalchemy import create_engine

engine = create_engine(DB_URI)

%sql engine

I've had similar issues before (when developing REST APIs) and I fixed the problem by configuring the engine. Example:

kwargs =  dict(
        pool_pre_ping=True,
        pool_recycle=3600,
        connect_args={
            "keepalives": 1,
            "keepalives_idle": 30,
            "keepalives_interval": 10,
            "keepalives_count": 5,
        },
    )

engine = create_engine(DB_URI, **kwargs)

but even with such configuration, the problem persists

[edublancas]

Update: this doesn't happen when I ran the postgres server locally (perhaps this is a server setting? can we changing via some settings when creating the sqlalchemy engine?)

to test it, I ran this in a terminal:

from sql import _testing
from time import sleep

print(_testing.DatabaseConfigHelper.get_database_url("postgreSQL"))

with _testing.postgres():
	sleep(6000)

Then, in a second one:

%load_ext sql
%sql postgresql://ploomber_app:ploomber_app_password@localhost:5432/db

and I kept running this:

%sql SELECT * FROM pg_catalog.pg_tables

it didn't fail, even after the terminal was idling for >10 minutes

[rinarakaki]

I’m using psycopg and AWS SSM Session Manager running on the local host to connect/port forward to a remote RDS instance. The problem of connection that frequently closes causes a local session manager process exit, which is why I need to rerun %sql postgresql+psycopg://… every time as well. I’d really appreciate if the issue can be solved, as this would provides us with more stable local environment.

[edublancas]

@rnarkk are you seeing the same error message we reported here?

(psycopg2.OperationalError) server closed the connection unexpectedly
	This probably means the server terminated abnormally
	before or while processing the request.

[SQL: select * from users]
(Background on this error at: https://sqlalche.me/e/20/e3q8)

[rinarakaki]

@edublancas Yes, I'm getting the same kind of error but with psycopg version 3:

RuntimeError: (psycopg.OperationalError) consuming input failed: EOF detected
[SQL: <SQL>]
[parameters: <parameters>]
(Background on this error at: https://sqlalche.me/e/20/e3q8)
If you need help solving this issue, send us a message: https://ploomber.io/community

[edublancas]

consuming input failed: EOF detected

This looks like a different error. This issue is about

server closed the connection unexpectedly

I have an open PR that fixes that and will be part of the next release (we'll release it during the week). If that doesn't fix the problem, feel free to open an issue!