CODEWITHSUNDEEP
postgresqlelixirphoenix-frameworkecto
Fletcher Moore
Fletcher Moore

Reputation: 13804

Ecto insert_or_update creating multiple inserts?

My code is resulting in a rare double or triple insert into the database and I am at a loss as to why. It is very difficult to reproduce but I can look at the timestamps to see the created at time is basically the same when it happens. I believe it only occurs when the CardMeta is not already found.

I figure I need to add a unique key or wrap it in a transaction.

  def get_or_create_meta(user, card) do
    case Repo.all(from c in CardMeta, where: c.user_id == ^user.id,
      where: c.card_id == ^card.id) do
        [] ->
          %CardMeta{}
        metas ->
          hd metas
    end   
  end

  def bury(user, card) do
    get_or_create_meta(user, card)
    |> Repo.preload([:card, :user])
    |> CardMeta.changeset(%{last_seen: DateTime.utc_now(), user_id: user.id, card_id: card.id,
      learning: false, known: false, prev_interval: 0})
    |> Repo.insert_or_update
  end

Edit: adding changeset source

  def changeset(struct, params \\ %{}) do
    struct
    |> cast(params, [:last_seen, :difficulty, :prev_interval, :due, :known, :learning,
                      :user_id, :card_id])
    |> assoc_constraint(:user)
    |> assoc_constraint(:card)
  end

Calling bury from the controller

def update(conn, %{"currentCardId" => card_id, "command" => command}) do
    # perform some update on card
    card = Repo.get!(Card,card_id)
    user = Guardian.Plug.current_resource(conn)

    case command do
      "fail" ->
        SpacedRepetition.fail(user, card)
      "learn" ->
        SpacedRepetition.learn(user, card)
      _ ->
        SpacedRepetition.bury(user, card)
    end
    sendNextCard(conn, user)
  end

Edit:

I noticed the last_seen field is microseconds different between duplicated rows, whereas the create_at field does not have that resolution. Thus I suspect the insert_or_update call is fine, but the controller is firing twice before the DB updates. This could be something on the client side, which I don't want to think about. So I am just going to add a unique key.

Upvotes: 1

Views: 780

Answers (2)

Mike Buhot
Mike Buhot

Reputation: 4885

As an alternative to @aliCna's answer, if you don't want to change the primary key on CardMeta, you can put a unique index constraint in the database with a migration:

defmodule YourApp.Repo.Migrations.AddCardMetaUniqueIndex do
  use Ecto.Migration

  def change do
    create unique_index(
      :card_meta, 
      [:card_id, :user_id], 
      name: :card_meta_unique_index)
  end
end

Which you can then handle in your changeset to produce nice errors if conflicts occur:

def changeset(struct, params \\ %{}) do
  struct
  |> cast(params, [:last_seen, :difficulty, :prev_interval, :due, :known, :learning,
                    :user_id, :card_id])
  |> assoc_constraint(:user)
  |> assoc_constraint(:card)
  |> unique_constraint(:user_id, name: :card_meta_unique_index)
end

Upvotes: 2

aliCna
aliCna

Reputation: 45

I believe you can solve this by adding a composite primary key on user_id and card_id

defmodule Anything.CardMeta do
  use Anything.Web, :model

  @primary_key false
  schema "card_meta" do
    field :user_id, :integer, primary_key: true
    field :card_id, :integer, primary_key: true
    . . .

    timestamps()
  end
end

If this does't solve your problem please add your data model here!

Upvotes: 0

Related Questions