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.