Postgresql stops responding after several minutes of inactivity with a directly connected application, requires a first timeout error request and responds again during the next few minutes

[emirtb]

Bug report

• [ X] I confirm this is a bug with Supabase, not with my own application.
• [ X] I confirm I have searched the Docs, GitHub Discussions, and Discord.

Describe the bug

I am a Supabase PRO user.

I'm in the initial phase of modeling, using various applications to connect to the postgresql database directly. But after a few minutes of inactivity the database apparently goes into an unavailable state.

After several minutes it only responds with a timeout error, the next immediate request responds correctly.

In a forum I found that it was resolved by restarting the service, this seemed to resolve it in the following hours, but it has occurred again, it is exasperating, I cannot trust a service like that.

I have tested on Mac OS and Microsoft Windows using DBSchema, AzureData Studio, in both cases using different connection drivers to Postgresql, I have used port 5432 and port 6543, the error occurs in any case.

These are some of the error messages:

(Ussing Azure Data Studio)
[Error - 8:49:04 PM] Request connection/listdatabases failed.
Message: sending query failed: Operation timed out
SSL SYSCALL error: Operation timed out

(Using DBSchema)
org.postgresql.util.PSQLException: An I/O error occurred while sending to the backend.
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:383)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:490)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:408)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:329)
at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:315)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:291)
at org.postgresql.jdbc.PgStatement.executeUpdate(PgStatement.java:265)
at com.wisecoders.dbs.dbms.connect.model.envoy.UpdateStatement.a(UpdateStatement.java:42)
at com.wisecoders.dbs.dbms.connect.model.envoy.Envoy.g(Envoy.java:346)
at com.wisecoders.dbs.dbms.sync.engine.nodes.SyncPair.a(SyncPair.java:482)
at com.wisecoders.dbs.dbms.sync.engine.nodes.EditorSyncRoot.a(EditorSyncRoot.java:34)
at com.wisecoders.dbs.dialogs.layout.d.a(FxDbDialog.java:149)
at com.wisecoders.dbs.dialogs.layout.d.call(FxDbDialog.java:124)
javax.net.ssl.SSLException: Operation timed out
at java.base/sun.security.ssl.Alert.createSSLException(Alert.java:127)
at java.base/sun.security.ssl.TransportContext.fatal(TransportContext.java:369)
at java.base/sun.security.ssl.TransportContext.fatal(TransportContext.java:312)
at java.base/sun.security.ssl.TransportContext.fatal(TransportContext.java:307)
at java.base/sun.security.ssl.SSLSocketImpl.handleException(SSLSocketImpl.java:1680)
at java.base/sun.security.ssl.SSLSocketImpl$AppInputStream.read(SSLSocketImpl.java:1054)

What will happen when my application is in production?

To Reproduce

Steps to reproduce the behavior, please provide code snippets or a repository:

Using any desktop application (and I suppose the web app too) and connect directly to the database without using the supabase library.

Expected behavior

After several minutes or hours (it is inconsistent) without inactivity, the database will respond with a connectivity error (timeout) in the first request, in the following immediate requests it will respond correctly.

Screenshots

System information

• OS: macOS, Windows
• Browser N/A
• Version of supabase-js: N/A
• Version of Node.js: N/A

Additional context

Add any other context about the problem here.

[emirtb]

additional info, I can see this in the log

{
"file": null,
"host": "db-ixxxxxxxxxxxxxxxxxx",
"metadata": [],
"parsed": [
{
"application_name": null,
"backend_type": "client backend",
"command_tag": "SELECT",
"connection_from": "127.0.0.1:52476",
"context": "PL/pgSQL function pgbouncer.get_auth(text) line 3 at RAISE",
"database_name": "postgres",
"detail": null,
"error_severity": "WARNING",
"hint": null,
"internal_query": null,
"internal_query_pos": null,
"leader_pid": null,
"location": null,
"process_id": 31122,
"query": null,
"query_id": -2227272355416136200,
"query_pos": null,
"session_id": "651b6dbb.7992",
"session_line_num": 3,
"session_start_time": "2023-10-03 01:26:19 UTC",
"sql_state_code": "01000",
"timestamp": "2023-10-03 01:26:19.240 UTC",
"transaction_id": 0,
"user_name": "pgbouncer",
"virtual_transaction_id": "19/451"
}
],
"parsed_from": null,
"project": null,
"source_type": null
}

[MincePie]

I am having similar issues with supabase. I can successfully submit to the db for about 15 submits, then I get an error which is either not logged in supabase (postgres logs) or is logged as an auth error. This is despite being able to successfully submit to the db in the same session (from another browser tab, to another table in the same db, or to edit existing records in the table that fails with the auth error... and after a 10ish minute wait, in the same session, the submit button in the form that was producing the error, submits successfully). This is happening daily.

[encima]

Are you setting any keepalive parameters to the DB in that time?

Is this happening only with idle connections or any type?

[AndyWangMSFT]

I have experience same issue. Any resolution? Thanks!

[encima]

Hi @AndyWangMSFT (et al),

How are you all connecting? If you have SSL enforced, ensure you have specified this in the connection string (and have the client certificate). Currently, I am not able to reproduce using pgadmin, psql or TablePlus.

When you are using a client to connect, is this occurring with the direct DB URI? or with the Pooler connection string? If you are using the Pooler, please make sure you are using it in session mode.

[LuanPianeli07]

I have the same problem.

[vestedpr-dev]

Hello: I had a similar problem these past two days. I need to run some long running queries to generate some materialized views. I need to trigger the queries obviously via some sort of client (Ruby's pg gem and psql are two I tried), and the client has an open connection during the execution of the query. If the client loses the connection the query is not necessarily aborted I have found, but as a have a whole string of them to run, it messes up my script.

The symptom was to get generic TCP errors sometimes on my client, and always in the Postgres Supabase log. Similar to: "could not receive data from client: Connection reset by peer", "could not receive data from client: Connection timed out", "sql_state_code": "08006" ... and you can corroborate other details like the client's IP address (in the logs) etc.

I tried many things, what ultimately seemed to solve the problems was to append the TCP "keepalive" params to the database connection string: <snip>.supabase.co:5432/postgres?keepalives=1&keepalives_idle=120&keepalives_interval=30&keepalives_count=15 Once this was set, I found I could influence the frequency and number of the error messages in the server logs.

I also steered myself towards using the direct DB URI as referenced earlier in this thread, trying to simplify things.

A mysterious and frustrating error to be sure. Nice to finally (seemingly) crack it. I will comment again if this solution turned out not to ultimately work.

[Hallidayo]

Hi everyone, due to inactivity on this issue I've moved the issue over to discussions for future reference.