One notable gotcha with this approach in Ecto vs ActiveRecord: ActiveRecord’s find_by takes the “first” matching record and silently ignores anything else, while Ecto’s get_by includes enforcement that there is exactly one matching record and raises if not.
That means that if two racing calls to MyApp.Repo.get_or_insert(MyItem, %{var1: "some value"}) ever manage to both do an insert, every subsequent call to MyApp.Repo.get_or_insert(MyItem, %{var1: "some value"}) will crash.
Which is why his function’s body should be wrapped in Repo.transaction, and maybe with the lock type specified (depending on scenario).
Strong agree. Most use cases for this kind of function should come with a unique constraint so the on_conflict option works perfectly well (I’ve never run into any gotchas around the behavior of returning). If it’s really not possible to use a constraint I would think it’s probably worth it to use a transaction and a lock, since it doesn’t add that much more complexity.
Good shout. I’ve noticed this and planned to catch the error, but realised in my use cases it was essentially not possible to have two copies of the same record for the tables I’m using this, so went with the original suggestion from SO that doesn’t catch.
Can you elaborate on what “essentially not possible” means in your use case if it isn’t enforced via a unique constraint? In my experience if the database allows it, it will eventually happen.
On default postgres even a transaction and an explicit lock is not enough to prevent a race condition where multiple rows get inserted. You can’t take an explicit lock on a row that doesn’t exist - for that you need a predicate lock (in practice a range lock), which you will get if you run under SERIALIZABLE isolation.
Even under snapshot isolation (I believe this is “repeatable read” in postgres) you will still have this anomaly. If you have two concurrent transactions they will both see that the row is missing in their snapshots and so they will both insert a new row.
So your options here are either to run under serializable isolation (which has a performance hit as I understand it) or to just use a unique constraint. Frankly I am struggling to think of a scenario where you want to get_or_insert without a unique constraint - the get_or_insert itself essentially is a unique constraint, albeit a buggy one.
Also, even if you run under serializable isolation I’m pretty sure you still won’t get an atomic get_or_insert, because postgres’s “serializable snapshot isolation” (SSI) is optimistic and I belive it will just abort one of the transactions. I think the only way to get a truly atomic operation is to do with Jose suggested above and use an insert/on_conflict/returning with a unique constraint.
Finally, there is actually another option - you can “materialize the conflict”. In this case that would mean locking another related row which you know exists. For example, imagine you had a table users and another table user_profiles, and you wanted to enforce that there is only one row in the latter for each user, and you didn’t want to use unique constraints or serializable for some reason. You could instead lock the user’s row in users before doing your get_or_insert, which would effectively serialize the operations and protect you from the race condition. This works because the user row already exists, so there’s always a row to lock on.
I’ve actually used this last trick for a product I’m working on, which involves some rather nightmarish logic for implementing nested drag/drop reordering with undo/redo on top of postgres rows. Clearly I need to write a blog post about this…
P.S. for those interested in this sort of thing there is actually a really cool paper about postgres’s SSI implementation:
It’s a great read. Serializable snapshot isolation is a neat trick and is used in some distributed databases too (e.g. CockroachDB and FoundationDB). FoundationDB’s distributed SSI in particular is a really simple and beautiful algorithm. Cockroach’s on the other hand is kinda cursed but I digress.
In my case I only insert records into the table through the get_or_insert for the given table, so unless race condition happens there (possible of course), there are no other places to insert a duplicate record.
I’m more after a simpler Rails find_or_create behaviour that doesn’t require a DB constraints and quietly returns the first matching record. To match that behaviour closer, I’ve updated my code:
def get_or_insert(schema, params) do
case find_first_by(schema, params) do
nil -> insert(schema.changeset(schema.__struct__, params))
struct -> {:ok, struct}
end
end
import Ecto.Query, only: [where: 2, first: 1], warn: false
def find_first_by(schema, params) do
params = Map.to_list(params)
where(schema, ^params) |> first |> one
end
def get_or_insert!(schema, params) do
{:ok, record} = get_or_insert(schema, params)
record
end
In my use case duplicates aren’t the end of the world, but if it becomes an issue, I’ll look into adding constraints. I have bulky jsonb columns on one of the tables where I’m using get_or_insert, and I’m worried creating unique indexes (Postgres) might take too much storage. Would be nice to test to see if that’s indeed the case, but my time is limited, and this solutions does the trick for now.
I don’t know what Rails does, but the reason Ecto lacks this feature is probably due to what I mentioned above: it’s unfortunately just not possible to do this safely without serializable isolation or a constraint.
Obviously you gotta do what you gotta do, but the race will bite you eventually, and probably when it’s least convenient (or however the saying goes…)
To be clear, the json columns would only inflate your index if they’re actually in the constraint, i.e. they are the things that you are checking for uniqueness. That’s rather unusual, but if that’s really the case then you could hash the json (like just dump it into sha256) and then store the hash along with the data and put the constraint on that instead. I’ve actually done this exact thing (except on multiple columns) to diff RSS entries for the very same app I mentioned above.
Also, if the JSON is what you are checking for uniqueness, that would mean you’re doing your lookup against a column with large JSON blobs without an index. Once that gets slow (it will), you’re going to need an index. At which point, you’re back to square one…
I’ve never had much cause to dig too deep into postgres’ full feature set around this sort of thing, precisely because a constraint is almost always called for and they are what I rely on 95% of the time.
For the other times however, I have used explicit locking I am wondering how to square what you’re describing with the point @jeremyjh makes above, which specifically distinguishes isolation level from locking. My understanding is two connections requesting an exclusive lock on a table in order to perform this kind of “find or insert” will protect against race conditions.