Executing SET LOCAL commands with Ecto?

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:

  1. Passing the “current database connection” to Ecto.Adapters.SQL.query!/1
  2. Using a Server.Repo command (which is scoped to the correct DB connection) and passing it raw SQL.

Any idea if either of these is possible?

:wave:

I’d use Repo.query!/1 from within a multi.

Ecto.Multi.new()
|> Ecto.Multi.run(:set_join_collapse_limit, fn repo, _changes ->
  repo.query!("set local join_collapse_limit=1")
  {:ok, nil}
end)
|> # now your query
|> Repo.transaction()

I’m using a roughly similar approach for set local role and set local {key} to {value}.

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)
1 Like

Repo.query!/1 is definitely what I want to exist, but I don’t see it in the Ecto.Repo documentation. What am I missing?

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.

1 Like