search

jackc / pgx

PostgreSQL driver and toolkit for Go
MIT License
10.84k stars 846 forks source link

How can we tell if pgx.Rows don't get closed? #2058

Open alifemove opened 5 months ago

alifemove commented 5 months ago

Is your feature request related to a problem? Please describe. When using db.Query and returning pgx.Rows to other functions it can sometimes get lost and not be closed. The connections eventually pile up and the app stops responding to new requests.

Describe the solution you'd like I don't know if this could be a linting fix potentially, where it could tell that the rows don't get closed, or if there could be some kind of message that there are no connections left available (though that would catch the issue after its already an issue I guess..). Not sure, but would be nice to be able to tell if we are potentially leaking connections.

jackc commented 5 months ago

It might be possible to add some additional diagnostics such as capturing a stack trace whenever a connection is acquired or a query is started. Then something could inspect the pool to find what all the connections are doing. Probably would need to be off by default to avoid the performance penalty.

Not sure exactly what the interface would be or how it would be implemented. Maybe something with the existing tracing system would work.

Or maybe instead a conn could store what it is currently doing in a concurrency safe manner such that diagnostic tools could inspect conns that are checked out or busy. Not sure exactly what that would look like either.

But to be honest, this isn't a problem I personally run into. pgx.CollectRows is your friend. The only reason not to immediately call pgx.CollectRows is if you need to stream the results. But that is very rare and you need to be very careful because you might partially process a result set and then encounter a PostgreSQL or network error. In almost all cases it is better to buffer the results before processing.

alifemove commented 5 months ago

But that is very rare and you need to be very careful because you might partially process a result set and then encounter a PostgreSQL or network error. Yeah, streaming isn't great for the user, but it is great for resource usage. What I'm working on allows streaming in order to get a larger result set, as well as non streaming to get better error messages. Kind of a trade off.

I also think that this might be the same issue from here