Upsert, `many_to_many` and `cast_assoc`: insert in join table generated even when the upsert turns out to be an update

[tanguilp]

Elixir version

1.19.5

Database and Version

Postgres 16.1

Ecto Versions

3.13.5

Database Adapter and Versions (postgrex, myxql, etc)

postgrex

Current behavior

Let's assume I have 3 tables country, city and country_cities (the join table).

I insert and update the countries and there associated cities using cast_assoc on a many_to_many association from the Country schema using an upsert.

For example this is the params of the changeset:

%{
  "cities" => %{
    "0" => %{
      "_persistent_id" => "0",
      "id" => "4215",
      "name" => "Lyon modified"
    },
    "1" => %{
      "_persistent_id" => "1",
      "id" => "2456",
      "name" => "Grenoble"
    }
  },
  "cities_drop" => [""],
  "cities_sort" => ["0", "1"],
  "name" => "France",
}

We then apply these params to the existing Country and get a changeset passed to Repo.insert/....

It doesn't work and the transaction is rolled back because a insert in the join table is generated, even though the Country exists and the association (in this case cities) is loaded:

[debug] QUERY OK source="country" db=0.3ms
INSERT INTO "country" ("id","name","updated_at","inserted_at") VALUES ($1,$2,$3,$4) ON CONFLICT ("id") DO UPDATE SET "updated_at" = EXCLUDED."updated_at","inserted_at" = EXCLUDED."inserted_at","traffic" = EXCLUDED."traffic","name" = EXCLUDED."name" RETURNING "updated_at","inserted_at","name","id" [1267, "France",  ~U[2026-05-12 17:54:37.577948Z], ~U[2026-04-28 18:07:49.000000Z]]

[debug] QUERY OK source="city" db=0.3ms
UPDATE "city" SET "name" = $1, "updated_at" = $2 WHERE "id" = $3 ["Lyon modified", ~U[2026-05-12 17:54:37.579648Z], 4215]

[debug] QUERY ERROR source="country_cities" db=0.3ms
INSERT INTO "country_cities" ("country_id","city_id","inserted_at","updated_at") VALUES ($1,$2,$3,$4) [1267, 4215, ~N[2026-05-12 17:54:37], ~N[2026-05-12 17:54:37]]

As you can see the last statement is an insert in the join table.

It is generated because:

1. in

   ecto/lib/ecto/repo/schema.ex

   Line 453 in 270aceb

   changeset = put_repo_and_action(changeset, :insert, repo, tuplet)

   the action is set to :insert
2. then in

   ecto/lib/ecto/association.ex

   Line 1595 in 270aceb

   defp insert_join?(%{action: :insert}, _, _field, _related_key), do: true

   Ecto determines it's a new, fresh insert and therefore a association record has to be added in the join table

Expected behavior

First it remains to be determined if it is a bug.

In my opinion it is, because the changeset contains all the required information (preloaded assoc, ids in the changeset generated by cast_assoc) to determine whether we should insert in the join table or not. Moreover, when using upserts, we except it to be an insert or an update.

I think this bug is a reminiscence of Ecto before upserts were implemented. In this case it made sense to assume that a Repo.insert/... could only be an insert, but this no longer the case since. Maybe just removing this line would make sense, because as far as I know there is no generic way to know if the result of an upsert is a new record or an existing one.

PS: the example is trivial and doesn't represent what I'm really doing, and I don't really have the option to edit cities separately. I'm building a collaborative subsystem based on Ecto changesets. In a nutshell: changes are proposed by the user, then params are stored in the DB and later applied to the current stored object.

[greg-rychlewski]

It's a bit different for the main struct vs the associations. For associations we can determine the correct operation because the current state is loaded and can be compared against the changes. For the main struct you can't really tell if it should be an insert or not. For example if you didn't have any associations loaded they would all be considered an insert and would fail too.

I think we'd either have to default join table inserts to use on_conflict: :nothing or let the user configure the conflict behaviour. For the :nothing option then we need to be careful if this will break on some adapters..it might have to be opt in or something.

[greg-rychlewski]

FWIW when I had to do this at a previous job I created a transaction and basically did the changeset magic by hand as separate steps. So that is always an option if we can't think of a good way to enhance the changeset functionality

[tanguilp]

Hi, thanks for the swift answer!

FWIW when I had to do this at a previous job I created a transaction and basically did the changeset magic by hand as separate steps. So that is always an option if we can't think of a good way to enhance the changeset functionality

Yup but in my case I'm building a generic collaboration/diff system on top of changesets and I'm literally doing, when accepting a user's changes:

change_request
|> get_current_object() # from DB if there's an id, blank otherwise
|> MyApp.Protocol.DataObject.to_changeset(change_request.changes) # changes from changeset.params
|> MyApp.Data.upsert()
|> case do
  {:ok, _} ->
     MyApp.Collab.accept_change_request(change_request, user)

  {:error, _} ->
    ...
end

(in a transaction of course)

and that's so elegant, works for everything except for update (via upserts) of many_to_many assocs.

For the main struct you can't really tell if it should be an insert or not.

How about simply not relying on the action but comparing every time as proposed in

Maybe just removing this line would make sense, because as far as I know there is no generic way to know if the result of an upsert is a new record or an existing one.

? I deleted this line, just works because we fall back to comparing with the loaded association data, but not sure what are the side effects.

[greg-rychlewski]

It feels a bit weird because you are essentially trying to say you don't know whether you should be inserting or updating the main struct but also you are sure that you know these things are associated. The reason I went for on_conflict is because I can see this making sense in a race condition sense where people might be trying to do inserts simultaneously and you want to handle conflicts gracefully.

Maybe understanding your use case a bit better would help. Why would you want to upsert if you are sure you have an entity that exists and is associated to these things?

[tanguilp]

Maybe understanding your use case a bit better would help. Why would you want to upsert if you are sure you have an entity that exists and is associated to these things?

1. for the sake of simplicity - we take a changeset as a parameter and don't care about the rest
2. between the time the change request is created for a new schema and the time it is accepted and recorded to the DB, a similar object (unique constraint) might have been created

[greg-rychlewski]

That doesn’t really answer my question though. How can you know the records are already associated if you don’t know whether the main record exists? Because that’s what your proposal means.

[tanguilp]

That doesn’t really answer my question though. How can you know the records are already associated if you don’t know whether the main record exists?

Not sure to understand, but before create the changeset for an existing record, we preload the associations that can be edited. This is what the get_current_object() function does in the pseudo-code above. Then the internals of Ecto does the rest of the job.

[greg-rychlewski]

At that point though you know the record exists right? Otherwise how could the join table entry exist.

[greg-rychlewski]

What I mean is for inserts everything is a new change. And on the chance someone got to it first you have on_conflict for conflict resolution.

If you know it already exists and has associations it’s an update. Which is what your reply sounds like to me. Because you’re saying before you insert you already know the association exists. Which should only happen if both records exist

[josevalim]

The records exist but we don’t know they are associated because the information is on the many to many table. So indeed we don’t if we should insert or update. This is exclusive to many_to_many. Indeed we could support something like on_conflict though, but we probably should keep the default as raise.