Forgot to close() connection to the postgres and opening the new connection was inside of the query loop... So I successfully overfilled all the possibie 100 connections to 5 dbs out of 6. I have no direct access to them so I cant delete them. I'm still on probation and I have to go to lead dev and tell him that I messed up...

Happens

Done that more times than I’d like to admit

Database connections have timeout, don't they?

Happens all-the-time.

Own your failures like you own your successes.

Nothing is more annoying than someone who refuses to accept responsibility.

If you're still on your probation, you shouldn't really get access to production database imo

@vicary They don't, apparently. On a related note, I'm probably never touching postgres unless forced to.

@vicary i could not hold myself :D

@iiii brilliant

Finite resources should either have a sophisticated ownership model that allows to fix mistakes (Users can take over or at least see and close their own connections) or a timeout.

@myss I definitely hope that it was not a production database :/

@homo-lorens what would you touch then? NoSQL?

Any database can have user defined limits.

The global / max limit is... a last resort.

It should be treated like that.

@shine This is not about structure, but I prefer structured data models because there is always structure and your code always depends on it so the only thing unstructured databases and untyped languages achieve is that you can't be certain that there isn't a record or call that will have unexpected types and break your code - in the worst case in a non-crashing way.

@IntrusionCM A limit is a setting and as such should have a sensible default. Pgsql connections aren't meant to idle ever, unless you subscribed to events.

@homo-lorens I think you misunderstood my comment

The connection limit is per role / user - any database supports this afaik.

It makes sense to use this. Especially in Postgres where auth is tad more complicated, but you can quite literal plug any auth into Postgres.

The problem of 'too many connections cannot do anything' boils down to 'we use a single user in a multi user database and now we're fucked'.

So PostgreSQL neither uses timeouts, nor a heartbeat protocol?!
Or is it just a case of shitty server configuration?

There are so many reasons, why once in a while a connection might not get properly closed by the client or it does and the server just doesn't hear about it.
So eventually you should end up without free connection slots even if not making rookie mistakes...

@iiii nice one, made my day!

If i would be a lead dev, i would probably be disappointed if you don't do that at least once while on probation lol

Joke aside, going to the person that is able to help as soon as possible is probably making the best out of this situation, as it proves that you know how to take responsibility AND ask for help as soon as the need arises (which is the most sugested improvement i heard given to juniors)