Postgrex disconnected inside transaction

Hello,

I made a form to import CSV into my database through a form. Because the csv can contain errors and the model does not have unique constraints I want to do it in inside a transaction to let the user correct the csv then retry to import. It’s working for small CSV but for big one I have some issues.

I’m using

elixir 1.10.4
erlang 23.1.1

Postgres is running inside a docker.

My first approach with a Multi.

opts = [prefix: Teams.team_prefix(team), timeout: :infinity, debug: true]
# rows = [%{customer_info: ..}, ...]
rows
# Starting with 2 to be able to match the line in case of error in the csv
|> Enum.with_index(2)
|> Enum.reduce(Multi.new(), fn {row, index}, multi ->
  Multi.insert(
    multi,
    "#{index}_customer_line",
    Customers.changeset(row),
    opts
  )
end)
|> Repo.transaction()

When I try on a 6k lines CSV after some times I got

[info] Postgrex.Protocol (#PID<0.948.0>) disconnected: ** (DBConnection.ConnectionError) client #PID<0.1100.0> exited

I try to set different options in the insert :timeout, :pool_timeout to :infinity

I also try using insert_all inside a transaction

now = NaiveDateTime.utc_now() |> NaiveDateTime.truncate(:second)
opts = [prefix: Teams.team_prefix(team), timeout: :infinity, pool_timeout: :infinity, debug: true]

rows =
  rows
  |> Enum.map(fn row ->
    row
    |> Map.put(:inserted_at, now)
    |> Map.put(:updated_at, now)
  end)
  |> Enum.chunk_every(100)

Repo.transaction(
  fn ->
    Enum.map(rows, &Repo.insert_all(Customer, &1, opts))
  end,
  opts
)

I can see some insert happening until the error.

[info] Postgrex.Protocol (#PID<0.950.0>) disconnected: ** (DBConnection.ConnectionError) client #PID<0.1101.0> exited

Thanks for your time.

Try also setting timeout to :infinity, not only pool_timeout. You are doing things in a single transaction so the pool timeout isn’t the issue here.

Speaking of which: do you really need to insert all records in a single transaction?

I already try the :timeout option to :infinity still result in the same error.
Doing all in a single transaction was a good way to avoid making duplicate if it crash in the middle of import and the csv need to be fixed before importing again.

You need to set the timeout for both the transaction itself and the connection.

If you check the second exemple I pass the timeout to both Repo.insert_all and Repo.transaction with opts. I also try to set the timeout in the repo config directly but still

[info] Postgrex.Protocol (#PID<0.1736.0>) disconnected: ** (DBConnection.ConnectionError) client #PID<0.1894.0> exited