fix and delete purge activities duplicates

1 files changed, 29 insertions, 0 deletions

diff --git a/priv/repo/migrations/20210128092834_remove_duplicates_from_activity_expiration_queue.exs b/priv/repo/migrations/20210128092834_remove_duplicates_from_activity_expiration_queue.exs
new file mode 100644
index 000000000..309009205
--- /dev/null
+++ b/priv/repo/migrations/20210128092834_remove_duplicates_from_activity_expiration_queue.exs
@@ -0,0 +1,29 @@
+defmodule Pleroma.Repo.Migrations.RemoveDuplicatesFromActivityExpirationQueue do
+  use Ecto.Migration
+
+  import Ecto.Query, only: [from: 2]
+
+  def up do
+    duplicate_ids =
+      from(j in Oban.Job,
+        where: j.queue == "activity_expiration",
+        where: j.worker == "Pleroma.Workers.PurgeExpiredActivity",
+        where: j.state == "scheduled",
+        select:
+          {fragment("(?)->>'activity_id'", j.args), fragment("array_agg(?)", j.id), count(j.id)},
+        group_by: fragment("(?)->>'activity_id'", j.args),
+        having: count(j.id) > 1
+      )
+      |> Pleroma.Repo.all()
+      |> Enum.map(fn {_, ids, _} ->
+        max_id = Enum.max(ids)
+        List.delete(ids, max_id)
+      end)
+      |> List.flatten()
+
+    from(j in Oban.Job, where: j.id in ^duplicate_ids)
+    |> Pleroma.Repo.delete_all()
+  end
+
+  def down, do: :noop
+end