I hope I’m not just missing some docs somewhere, but I can’t get Ecto.Adapters.SQL.query/4 to work the way I would expect it to work.
I’m trying to insert a column into a very big table and keep getting errors that the connection times out.
referencing this github issue I changed my migrations to:
Ecto.Adapters.SQL.query(Repo, “ALTER TABLE requests_table ADD fx_type_id integer;”, , timeout: :infinity)
Still, the migrations timeout at around 15 seconds, (the default timeout)
however I noticed, if I set the timeout in my config.exs to :infinity, the migrations actually work.
it seems to me that the timeout option passed into the query/4 should override the config settings for that query. in any case, is there a way to allow for long queries, without compromising my config settings?
Iirc the timeout option is passed to the Repo call when you hand it the query not the the query itself.
See the docs: https://hexdocs.pm/ecto/Ecto.Repo.html#module-shared-options
I’m sorry, I don’t understand. passed to the Repo call when you HAND it the query? what does that mean? I’m calling it exactly in the way mentioned in the github issue.
Woops sorry. I was too used to everything called query needing to be handed to
What you wrote should definitely work for all I know I don’t have a big elixir app handy right now to check it out further though.
To debug what might be wrong I’d jump into about this line of ecto code.
Sorry that I can’t be of more help/use
thanks for the place to start looking, I’ll let you know what I figure out.
A timeout is per transaction and not per query. A single query outside an explicit transaction is treated implicitly as a transaction so can have a timeout. Therefore any timeout passed to a query inside a transaction is ignored. A migration will use a transaction for postgresql by default, so the migration’s timeout is the timeout in affect and the query’s timeout is ignored. Changing the (global) configuration succeeds because that is being passed to the migration transaction. I think it makes sense to be able to pass the timeout manually however the migration is being run. If this is not available from the mix task we could definitely support it. Please open an issue and/or PR .
Wow thanks totally missed this!
Just curious since I’ve been experiencing a bit of timeout in production. Will the below suffice.
config :debitor, Debitor.Repo,
pool_size: String.to_integer(System.get_env(“POOL_SIZE”) || “10”)
Repo.all(query, [timeout: 120_000])