Indexes are ignored

Questions / Help
,
1

So I have a bit of an issue on my application and I don’t know how it is caused. I’m not sure whether my code is fail-safe. I created a table with a context which has an an index on 2 elements but for some reason sometimes the elements are added so I have 2+ elements with the same index so I have an issue.

Here’s my migration file:

defmodule App.Repo.Migrations.CreateCounts do
  use Ecto.Migration

  def change do
    create table(:counts) do
      add :date, :string
      add :count, :integer
      add :owner_id, references(:users, on_delete: :nothing)

      timestamps()
    end

    create index(:counts, [:owner_id, :date])
  end
end

And then my count module:

defmodule App.Count do
  use Ecto.Schema
  import Ecto.Changeset
  import Ecto.Query
  alias App.Repo

  schema "counts" do
    field :count, :integer
    field :date, :string
    field :owner_id, :id

    timestamps()
  end

  def changeset(email_count, attrs) do
    email_count
    |> cast(attrs, [:date, :count, :owner_id])
    |> validate_required([:date, :count, :owner_id])
  end

  def increment(struct) do
    %__MODULE__{struct | count: struct.count + 1}
  end

  def create_new() do
    today = Timex.now
    %__MODULE__{date: Timex.format!(today, "{0D}/{0M}/{YYYY}"), count: 0}
  end

  def set_owner_id(struct, owner) do
    %__MODULE__{struct | owner_id: owner.id}
  end

  def update_count(owner) do
    id = owner.id
    date_now = Timex.format!(Timex.now, "{0D}/{0M}/{YYYY}")

    buffer =
      from(e in __MODULE__, where: e.owner_id == ^id, where: e.date == ^date_now)
      |> Repo.one()

    if buffer != nil do
      data = buffer |> increment() |> Map.from_struct()
      changeset(buffer, data)
      |> Repo.update()
    else
      data = create_new() |> set_owner_id(owner) |> increment() |> Map.from_struct()
      changeset(%__MODULE__{}, data)
      |> Repo.insert()
    end
  end
end

But then sometimes (I am unable to create it at will) I get an error:

%Ecto.MultipleResultsError{
   message: "expected at most one result but got 3 in query:\n\nfrom e0 in App.Count,\n  where: e0.owner_id == ^1,\n  where: e0.date == ^\"10/02/2020\"\n"
 }
``

It looks like the indexes are being ignored because it inserts values with the same `owner_id` and `date`?:

[
%App.Count{
meta: #Ecto.Schema.Metadata<:loaded, “counts”>,
count: 1,
date: “10/02/2020”,
id: 14,
inserted_at: ~N[2020-02-10 13:00:11],
owner_id: 1,
updated_at: ~N[2020-02-10 13:00:11]
},
%App.Count{
meta: #Ecto.Schema.Metadata<:loaded, “counts”>,
count: 1,
date: “10/02/2020”,
id: 15,
inserted_at: ~N[2020-02-10 13:00:11],
owner_id: 1,
updated_at: ~N[2020-02-10 13:00:11]
},
%App.Count{
meta: #Ecto.Schema.Metadata<:loaded, “counts”>,
count: 1,
date: “10/02/2020”,
id: 16,
inserted_at: ~N[2020-02-10 13:00:11],
owner_id: 1,
updated_at: ~N[2020-02-10 13:00:11]
}
]```

2

Unless you put also a unique constraint over the index, it is allowed to have multiple entries.

An index by itself is just a catalog of some small data fragments to a list of table rows that contains that fragment.

As an analogy take a look at a technical book. At the back you usually find a list of words, each pointing to one or many pages in the book explaining the word. This is an index.

3 Likes
3

Ah, I thought Ecto indexes put constraints automatically. Thanks. Marking as resolved.

4

I just used unique_index/2 instead of index/2 in my migrations and I don’t have the issue anymore!

1 Like
5

think what you want is unique_index: https://hexdocs.pm/ecto_sql/Ecto.Migration.html#unique_index/3

then you must also handle the conflict case…

additionally I would default the count to 0, and then I would do the increment on the DB side eg. something like this (ecto-fied)

UPDATE counts 
  SET count = count + 1
WHERE owner_id = X and date = Y;

you might just want to bring it all together in an upsert…

1 Like