I have an api endpoint that receives JSON posts from a 3-rd party.
In the controller I check for the presence of a key_field in the JSON. If the fields is not nil, I do a Repo.get_by(DataStructure, key_field: key_field). If I get a nil I do an insert, if I get a record, I do an update.
The trouble is that sometimes, the third party sends two posts with the same data in quick succession and this causes the same data to be inserted twice. This then leads to Ecto.MultipleResultsError on subsequent calls for the same key.
Trouble is that the column is not unique. There are multiple rows where it can be null.
Is there a constrain that I can put on that column that would make it allow null but only unique values for non-null? (in that case I imagine that on_conflict would work.
I use Repo.get_by(DataStructure, key_field: key_field) . If it returns nil this means that the key_field value is not in the database and I do an insert, otherwise update.
The main problem is cause by another call that happens in parallel and happens before the Repo.insert has the chance to finish.
The way I’m currently able to reproduce this is using a helper like this:
defp post_to_endpoint() do
body = %{
"first_name" => "John",
"last_name" => "Doe",
"key_field" => "123456"
}
url = "#{Application.get_env(:my_app, :endpoint_url)}/api/endpoint"
HTTPoison.post(url, Jason.encode!(body), ["Content-Type": "application/json"])
Logger.debug("posted #{url}")
end
def test_endpoint() do
Task.start(fn -> post_to_endpoint() end)
Task.start(fn -> post_to_endpoint() end)
end
This approximates the concurrent calls I receive from the third party server.