Repo.explain doesn't work in transactions

[philippe-lammerts-remote]

Elixir version

1.18.1

Database and Version

14.15

Ecto Versions

3.12.6

Database Adapter and Versions (postgrex, myxql, etc)

postgrex 0.20.0

Current behavior

Using Repo.explain within a transaction results in a rollback, whereas calling explain manually within a transaction using Repo.query works.

Repo.explain inside transaction

iex> Hello.Repo.transaction(fn ->
        Hello.Repo.explain(:all, Hello.Accounts.Account)
end)

[debug] QUERY OK db=1.3ms idle=400.3ms
begin []
↳ :elixir.eval_external_handler/3, at: src/elixir.erl:386
[debug] QUERY OK db=1.0ms
EXPLAIN SELECT a0."id", a0."name", a0."inserted_at", a0."updated_at" FROM "accounts" AS a0 []
[debug] QUERY OK db=0.7ms
rollback []
↳ :elixir.eval_external_handler/3, at: src/elixir.erl:386
{:error, :rollback}

Repo.explain outside transaction

iex> Hello.Repo.explain(:all, Hello.Accounts.Account)

[debug] QUERY OK db=1.6ms idle=1187.0ms
begin []
↳ Ecto.Adapters.SQL.explain/4, at: lib/ecto/adapters/sql.ex:523
[debug] QUERY OK db=1.7ms
EXPLAIN SELECT a0."id", a0."name", a0."inserted_at", a0."updated_at" FROM "accounts" AS a0 []
[debug] QUERY OK db=0.9ms
rollback []
↳ Ecto.Adapters.SQL.explain/4, at: lib/ecto/adapters/sql.ex:523
"Seq Scan on accounts a0  (cost=0.00..11.40 rows=140 width=540)"

Repo.query! with explain query inside transaction

iex> Hello.Repo.transaction(fn ->
         Hello.Repo.query!("EXPLAIN SELECT a0.\"id\", a0.\"name\", a0.\"inserted_at\", a0.\"updated_at\" FROM \"accounts\" AS a0")
       end)

[debug] QUERY OK db=1.4ms idle=438.4ms
begin []
↳ :elixir.eval_external_handler/3, at: src/elixir.erl:386
[debug] QUERY OK db=1.3ms
EXPLAIN SELECT a0."id", a0."name", a0."inserted_at", a0."updated_at" FROM "accounts" AS a0 []
[debug] QUERY OK db=1.2ms
commit []
↳ :elixir.eval_external_handler/3, at: src/elixir.erl:386
{:ok,
 %Postgrex.Result{
   command: :explain,
   columns: ["QUERY PLAN"],
   rows: [["Seq Scan on accounts a0  (cost=0.00..11.40 rows=140 width=540)"]],
   num_rows: 0,
   connection_id: 547,
   me

Expected behavior

I would expect that Repo.explain can be used inside transactions.

[greg-rychlewski]

Thanks for the report @philippe-lammerts-remote .

The reason you're seeing this behaviour is because the explain command is actually creating a transaction that is rolled back. So when you nest it inside another transaction, the outer one is rolled back as well. This is because when running explain with analyze, you may generate side effects.

Do you have a use case where you would like to run it inside of a transaction or was this more to point out an unexpected result? If the former maybe we need to add an option to turn off the forced rollback. If the latter then we can update the docs to be more clear.

[philippe-lammerts-remote]

Thanks for the report @philippe-lammerts-remote .

The reason you're seeing this behaviour is because the explain command is actually creating a transaction that is rolled back. So when you nest it inside another transaction, the outer one is rolled back as well. This is because when running explain with analyze, you may generate side effects.

Do you have a use case where you would like to run it inside of a transaction or was this more to point out an unexpected result? If the former maybe we need to add an option to turn off the forced rollback. If the latter then we can update the docs to be more clear.

Thank you @greg-rychlewski. We are currently utilizing the explain command to retrieve estimated row counts for a very large database table within a downstream service. This service operates within a transaction initiated by an upstream service.

Your point regarding the side effects is valid, given that analyze: true. Therefore, there are two potential solutions: either disable the forced rollback when analyze: false since there are no side effects here, or go for your suggestion by implementing an option to explicitly disable the forced rollback.

[philippe-lammerts-remote]

Thanks for fixing this @greg-rychlewski !! 🚀🙏🏻