I’ve found myself in a situation where I need to run a Postgres-specific SQL command to set connection state, and I can’t figure out a way to do that with Ecto.
Specifically, I’m trying to temporarily override the PostgreSQL query optimizer with the command SET LOCAL join_collapse_limit=1. This changes per-connection state in the database server until the end of the current transaction. I can run this using Ecto.Adapters.SQL.query!/2, but this uses an arbitrary connection from the connection pool. I want to use the connection currently checked out for a transaction, since I’m in a Eco.Repo.transaction/1 block. It seems like my options are:
Passing the “current database connection” to Ecto.Adapters.SQL.query!/1
Using a Server.Repo command (which is scoped to the correct DB connection) and passing it raw SQL.
Any arbitrary sequence of queries executed within an Ecto.Repo.transaction/2 that is written with a function argument will intrinsically be using the same connection from the connection pool and should work the way you desire without requiring custom arguments, dipping into implementation-detail modules, or writing wrapper functions, though the last one is still an attractive option if it’s a frequent need.
MyApp.Repo.transaction(fn ->
# Use your SET LOCAL
MyApp.Repo.query!(...)
# Perform any other queries you need
MyApp.Repo.all(...)
end)
Aha! The issue was that due a misunderstanding of some logic further up the call stack, I was not actually in a transaction when running the query is question. Repo.query!/1 does work once I have the transaction in place, and thanks to @shanesveller for explaining the guarantee that transaction/2 makes.