Problem migrating from Oban 1.0 to 2.7

Hi, we’ve been using Oban for a while now and had am absolutely great time using it in production. Today I finally took the time to upgrade from 1.0 to the current 2.7 version. Locally everything went well, I could run the necessary migrations and the test suite passed. I could even confirm the upgrade with a local dev database. However when trying to deploy the changes to our staging environment, the migrations won’t pass and I can’t figure out what is going wrong.

My setup is
Oban version 1.0 & 2.7
Postgres 12
Elixir 1.11.4 (compiled with Erlang/OTP 23)


15:02:38.075 [info]  == Running 20210713122231 Skrot.Repo.Migrations.UpgradeObanJobsToV9.up/0 forward

15:02:38.080 [info]  execute "DO $$\nBEGIN\nIF NOT EXISTS (SELECT 1 FROM pg_type\n               WHERE typname = 'oban_job_state'\n                 AND typnamespace = 'public'::regnamespace::oid) THEN\n    CREATE TYPE public.oban_job_state AS ENUM (\n      'available',\n      'scheduled',\n      'executing',\n      'retryable',\n      'completed',\n      'discarded'\n    );\n  END IF;\nEND$$;\n"

15:02:38.085 [info]  create table if not exists public.oban_jobs

15:02:38.088 [info]  relation "oban_jobs" already exists, skipping

15:02:38.088 [info]  create index if not exists public.oban_jobs_queue_index

15:02:38.107 [info]  create index if not exists public.oban_jobs_state_index

15:02:38.120 [info]  create index if not exists public.oban_jobs_scheduled_at_index

15:02:38.132 [info]  execute "CREATE OR REPLACE FUNCTION public.oban_jobs_notify() RETURNS trigger AS $$\nDECLARE\n  channel text;\n  notice json;\nBEGIN\n  IF (TG_OP = 'INSERT') THEN\n    channel = 'public.oban_insert';\n    notice = json_build_object('queue', NEW.queue, 'state', NEW.state);\n\n    -- No point triggering for a job that isn't scheduled to run now\n    IF NEW.scheduled_at IS NOT NULL AND NEW.scheduled_at > now() AT TIME ZONE 'utc' THEN\n      RETURN null;\n    END IF;\n  ELSE\n    channel = 'public.oban_update';\n    notice = json_build_object('queue', NEW.queue, 'new_state', NEW.state, 'old_state', OLD.state);\n  END IF;\n\n  PERFORM pg_notify(channel, notice::text);\n\n  RETURN NULL;\nEND;\n$$ LANGUAGE plpgsql;\n"

15:02:38.139 [info]  execute "DROP TRIGGER IF EXISTS oban_notify ON public.oban_jobs"

15:02:38.142 [info]  execute "CREATE TRIGGER oban_notify\nAFTER INSERT OR UPDATE OF state ON public.oban_jobs\nFOR EACH ROW EXECUTE PROCEDURE public.oban_jobs_notify();\n"

15:02:38.146 [info]  drop index if exists public.oban_jobs_scheduled_at_index

15:02:38.149 [info]  create index public.oban_jobs_scheduled_at_index

15:02:38.158 [info]  create check constraint worker_length on table public.oban_jobs
** (Postgrex.Error) ERROR 42710 (duplicate_object) constraint "worker_length" for relation "oban_jobs" already exists
    (ecto_sql 3.6.2) lib/ecto/adapters/sql.ex:760: Ecto.Adapters.SQL.raise_sql_call_error/1
    (elixir 1.11.4) lib/enum.ex:1411: Enum."-map/2-lists^map/1-0-"/2
    (ecto_sql 3.6.2) lib/ecto/adapters/sql.ex:848: Ecto.Adapters.SQL.execute_ddl/4
    (ecto_sql 3.6.2) lib/ecto/migration/runner.ex:343: Ecto.Migration.Runner.log_and_execute_ddl/3
    (ecto_sql 3.6.2) lib/ecto/migration/runner.ex:117: anonymous fn/6 in Ecto.Migration.Runner.flush/0
    (elixir 1.11.4) lib/enum.ex:2193: Enum."-reduce/3-lists^foldl/2-0-"/3
    (ecto_sql 3.6.2) lib/ecto/migration/runner.ex:116: Ecto.Migration.Runner.flush/0
    (ecto_sql 3.6.2) lib/ecto/migration/runner.ex:280: Ecto.Migration.Runner.perform_operation/3```

This is the V9 migration file (copied from oban documentation)

```defmodule Skrot.Repo.Migrations.UpgradeObanJobsToV9 do
  use Ecto.Migration

  def up do
    Oban.Migrations.up(version: 9)
  end

  def down do
    Oban.Migrations.down(version: 8)
  end
end

In the Oban changelog it says Oban will manage upgrading to V9 regardless of the version your application is currently using, and it will roll back a single version. So I don’t think I’m missing any migrations.
I suppose it is possible that my dev database simply does not have the conflicting constraints, but then I would be interested to know if anyone can recommend how I go about fixing it. Should I maybe drop the constraints in the migration file before the line Oban.Migrations.up(version: 9)? But the line that adds the constraint is in the oban V2 migration, so I’m guessing I will have more of these problems in later migrations if I don’t figure out the root cause.

Edit: running Oban.Migrations.migrated_version(Skrot.Repo, "public") yields 0 in the staging environment, which is not what i expected. I guess thats why its trying to run those older migrations.

Any suggestions are welcome!

Thanks for your help.

Woah! That’s a long way to upgrade.

The big issue seems to be that the oban_jobs table lacks a version comment (no clue why) and that’s causing the migrator to run everything. If you’re sure that the table was previously migrated and running for Oban 1.X you can set the version to 8 manually with this command:

execute "COMMENT ON TABLE oban_jobs IS '8'"

Then, when you run the migration again the migrated_version check will report 8 instead of 0.

2 Likes

Did some digging and found a old script from when we switched database providers that seems to have stripped comments from tables! Set the comment to ‘8’ and now everything is running smoothly again. Thanks for the help @sorentwo!

3 Likes