Inserting into DB , one by one concurrently

I am trying to insert many records into database, one by one as:

 rec= SRepo.get_by(Trackware.Schools.Parent, mobile_number: father_head.mobile_number)
 father_id= case rec do
  nil -> 
     # its not inserted in DB before
     {:ok, %Trackware.Schools.Parent{ id: father_id }} = SRepo.insert(father_head)
     father_id
  father -> 
     #its inserted before, return the record id
     father.id
 end

this work fine, however, if I try to use Stream.chunk and have the code above run in concurrent processes, I get error like:

** (Ecto.ConstraintError) constraint error when attempting to insert struct:

    * unique: parents_mobile_number_uindex

If you would like to convert this constraint into an error, please
call unique_constraint/3 in your changeset and define the proper
constraint name. The changeset has not defined any constraint.

Although I check before insert a new record, but it seems such error is a result of concurrent process…
How can I solve this?

I tried to use insert_or_update but I got the same error as it seems processes are raising on the same line?

can’t quite make out your code… but look at insert_all which does an “upsert” https://hexdocs.pm/ecto/Ecto.Repo.html#c:insert/2-upserts then you decide how to handle the conflict.

1 Like

I did not use insert_all, because the student record belongs to one parent, and I need to save that parent, and get its id, then to set the parent_id in the student before saving it…

When you have such combined things you really need to be using Ecto.Multi.

1 Like

Can I use Ecto.Multi to add records and associate them too? as I have belongs to relation

Yep. Ecto.Multi is just ‘transactions’ in Ecto, so definitely yes. :slight_smile:

sorry, was to fast and there was a bug in the documentation, I guess I meant use upsert options for insert (doc bug fixed here https://github.com/elixir-ecto/ecto/commit/acf8016b102b268df1de3216ad3e97945faa553b)

{:ok, %Trackware.Schools.Parent{ id: father_id }} = SRepo.insert(father_head , on_conflict: :replace_all, conflict_target: :mobile_number)

should be able to replace the entire code snippet you posted.

I would guess this would still be needed in a multi, as you might have student siblings that gives you these conflicts.

1 Like

Is there an example for such case in Ecto.multi? Inserting two records in different tables and set the foreign key of one to point to the other?

Well you can insert the other first then insert the original that points to the first, that is the usual way (in a transaction of course).

Or you can insert the first with a null foreign key, then the other, then update the first with with the key, but that is too many calls.

Basically for the first method just Multi.insert the first, then Multi.run to grab the id and insert the second. :slight_smile:

2 Likes