[Bug] PG connections staying open

[DrSocket]

I am running a private instance of invidious that is being deployed with Kubernetes following the production docker-compose build.
To regularly restart there's a cronjob that restarts the service at 30 minutes intervals and I noticed a lot of postgres connections are staying open and idle, I think they are not closed properly when the service restarts.

I don't know crystal lang but I looked a bit at the code and ended up in base.cr which seems to handle all db actions. There is never a close connection function, in python I manage that using 'with' blocks to so that it manages context and deals with opening and closing the connections. I looked through the issues and didn't see anything about this so either I'm the only one experiencing this or no one is checking their database connection logs/metrics. Is this something you are aware of or have heard before, is there a solution? thanks

[unixfox]

That's not possible, when the service restart all the connections are forcibly closed because there is nothing keeping those TCP connections alive.

[DrSocket]

So why do db connections from invidious build up more and more over a few hours. I can forcibly close all connections to the invidious db and 6 hrs later it's back to almost 100. It progressively goes up over time. The db is not hosted by the image. It is hosted on a db server.

[unixfox]

You should try again with the database shipped in invidious to be sure.

How did you check which connections are still opened? Through a SQL query?

[DrSocket]

Yes I'll try that, well in that case you're right it will never grow long because it's tied to the container and will be destroyed with the restart. but if you want to reproduce it you can probably just leave it open doing a lot of searches for a day and you'll have a bunch of open connections. You're thinking maybe that's the bug requiring the regular restart? I checked with a monitoring dashboard first and then via psql connected to the db in question: SELECT * FROM pg_stat_activity WHERE datname = current_database(); you can just get the count with this: SELECT count(*) FROM pg_stat_activity WHERE datname = current_database();

[absidue]

I'm guessing that even though Invidious forcefully closes the tcp connection, the database doesn't get informed that the connection was closed and keeps it open on its end. Properly closing the connection probably involves some kind of handshake with both parties agreeing the the connection has closed.

[unixfox]

I don't have much time lately to test but there are multiple solutions presented:

• either set a parameter called idle_in_transaction_session_timeout which should kill the idle connections automatically: https://stackoverflow.com/a/52704477
• or use pgbouncer, a proxy that avoid filling up the real connections count on the real database: https://wiki.postgresql.org/wiki/PgBouncer
  there is also this parameter server_idle_timeout: https://stackoverflow.com/questions/12391174/how-to-close-idle-connections-in-postgresql-automatically

[github-actions[bot]]

This issue has been automatically marked as stale and will be closed in 30 days because it has not had recent activity and is much likely outdated. If you think this issue is still relevant and applicable, you just have to post a comment and it will be unmarked.