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.