Manually checking out and closing database (PostgreSQL) connection with Ecto

Questions / Help
,
1

I have a fairly peculiar use case. Whenever a HTTP request is being handled in Phoenix app, I want to, from a plug mounted in router, set some session variables, create some temporary views, in a way that further down the road, my Phoenix/Ecto app does use those variables and views when making SQL queries.

There are in fact multiple reasons why I want to do the above, sufficient to say is that it involves auditing on database level, time-travel (with temporal_tables extension) and limiting the visibility of records on per-user basis that has to be done in database.

The obvious problem here is that Ecto maintains a pool of connections/sessions and whenever a SQL query is executed it checks out another connection. So, if I do 5 SQL queries during my request handling, I could very likely use 5 different DB connections/sessions.

As far as I can tell, these are also not cleaned up, i.e. the views and variables I set during one request would likely leak to another ones. I don’t want that.

I can, most likely, force the behavior I want by wrapping up all of my code in transaction, provide it function and within that function my database connection ID would be constant and my temporary views /variables would survive between database calls.

Is there a similar mechanism that would involve not using a transaction? I don’t want to wrap everything in a transaction, that can be possibly dangerous to the performance and I actually have multiple steps that I want to partially succeed.

2

It’s a planned feature for Ecto 3.0 to checkout the connection without wrapping in a transaction (and also to have transaction support with explicit checkout/checking calls instead of just a closure which severely limits the control flow possibilities).

Right now, I think the only way to do that could be abusing some of the ecto internals and the fact that ecto stores the connection pid during transaction in pdict. But I didn’t say that, if anybody asks :wink:

5 Likes
3

I did precisely that but don’t tell @josevalim. I’m getting the connection, setting it in process’ own map with the same key as transaction would do. The only issue I see is that in_transaction? returns true but I do not think it causes any problems in my case…

4

I’d be interested in seeing how you did that. I’m using advisory locks and I’m having to manage connections outside the Repo’s pool as there’s no way to ask the Repo for the connection that holds the lock in order to unlock it.

5

Now that Ecto 3 has landed what’s the recommended approach here? :slight_smile:

6

Ecto 3.0 ships with Repo.checkout/2 function for this use case.

1 Like
7

Thank you! :slight_smile: