Expose Connection acquire

[cranst0n]

Would it be possible to add a function that would return a raw connection as opposed to the withConnection function? This would obviously come with ultimate responsibility on the caller to properly release the acquired connection. The notional acquire function could return the connection as well as a function that could be called to release the connection.

I have my own resource safety mechanism that won't fit with the existing API.

Thanks for your time and effort.

[isoos]

@cranst0n: curious: what resource safety mechanism would you use, and what would be needed to fit it here in the library?

[cranst0n]

Hi @isoos I'm trying to integrate pg.Pool with Resource, which is a data type that handles resource acquisition and cleanup. It integrates with a library I'm building that handles resource safety in a streaming context which is why I'm pushing in this direction.

That being said, after sleeping on this I think I may have a path forward with the existing pg.Pool API so I may have spoken too soon on this request.

I appreciate your consideration though. After I have a chance to investigate further I'll report back. I imagine you'd probably prefer to not expose connections in the way I'm asking if at all possible.

[isoos]

Why not just use Connection.openFromUrl and .close?

Yes, the current API prefers this restricted approach to limit misuse, and the pool this way can have better guarantees of how connections are handled.

[cranst0n]

I'm trying to piggy back off of pg.Pool since it already manages connections but using Connection.openFromUrl would eliminate all the nice features that come with it, unless I'm misunderstanding.

I think I've found a way around this though by blocking the withConnection closure until I manually finish it, so I think this issue can be closed, feel free to do so.

Thanks for your time and effort!

[isoos]

I think you shouldn't do that workaround, as it feels like a call for trouble. I'm not sure how your resource concept maps to physical network connections, it would be a great if you could define that, and also what are the expectations of the connection over time. We could check if the pool is helpful for that or not, as the current pool is an opinionated resource handling, which may or may not be what you are looking for.

[cranst0n]

Resource can be thought of as kind of a try/finally block encoded as data. It attempts to:

• acquire something
• allow the user to use whatever was acquired in an asynchronous context
• ensure that the acquired thing is released

Note that the acquire/use/release thing can be anything (file handle, socket, semaphore, etc.) but in this case they are:

• Acquire a connection from the pool (withConnection)
• Use the connection for a query or a streaming response (using the Completer to hold the connection until finished
• Release the acquired connection back to the pool (fulfill the Completer)

The crux of my problem was bridging the gap between Future and my own async type IO which can be troublesome at times, but ultimately I believe the solution I posted above accomplishes this, although I can appreciate your concern since it's using some unorthodox methods. I've created a number of unit tests exercising the acquire/release of pooled connections and things seem to be working as intended.

If you have any potential ideas of things I may be missing though, I would defintely be interested to make sure I'm not overlooking anything.

[isoos]

Question: why don't you just give/provide the pool as the resource, though it may be as the Connection interface? It seems that you are not really better off by calling acquire/release on the pool, and it would just simplify your life. Otherwise you would just do the acquire/release without much benefit anyway, and the actual resource allocation wouldn't be that much different.

[isoos]

And by Connection interface, I meant the SessionExecutor: https://pub.dev/documentation/postgres/latest/postgres/SessionExecutor-class.html

[cranst0n]

I provide the pool as Resource here where Trasactor is a wrapper around pg.Pool with other features. But I also want to model the acquired connection as a Resource here where SqlConnection is a wrapper around pg.Session with some other features.

I would consider both things a resource since they must be acquired and eventually closed or returned as the case may be. I would prefer to work with pg.Pool since you've already created a battle tested implementation to reuse connections, versus me having to reinvent it.

[isoos]

I obviously have limited understanding on why you are doing these wrappers, but I could be convinced to create an interface that is scary by the name (e.g. UnsafeRawPool) that has a public acquire() method, and at the same time maybe it does not implement Session or SessionExecutor. It would keep the internals of connection tracking and lifecycle though. Would you be interested of creating it?

[cranst0n]

Yes if you're willing to implement it I would likely it over the approach I'm using now. I leave it to you to decide if it's worth your time and effort. Happy to offer any help that I can. Thanks!