Support for Cockroach DB

[Th3-M4jor]

In #806 it was mentioned that this could be supported if the issue was primarily syntax related.

I've only done some simple testing so far with the Basic engine and it seems that the only issue may well be Oban's Migrations.

The lack of support for triggers and notify in theory makes it not all that different from connecting to Postgres behind something like PgBouncer.

Migration File used for testing

defmodule TestProject.Repo.Migrations.AddOban do
  use Ecto.Migration

  def change do
    add_job_state()
    add_peers_table()
    add_jobs_table()
    add_indexes()
  end

  defp add_job_state do
    execute(
      """
      CREATE TYPE IF NOT EXISTS oban_job_state AS ENUM (
        'available',
        'scheduled',
        'executing',
        'retryable',
        'completed',
        'discarded',
        'cancelled'
      )
      """,
      "DROP TYPE IF EXISTS oban_job_state"
    )
  end

  defp add_peers_table do
    create_if_not_exists table(:oban_peers, primary_key: false) do
      add :name, :text, primary_key: true
      add :node, :text, null: false
      add :started_at, :timestamp, null: false
      add :expires_at, :timestamp, null: false
    end
  end

  defp add_jobs_table do
    create_if_not_exists table(:oban_jobs) do
      add :state, :oban_job_state, null: false, default: "available"
      add :queue, :text, null: false, default: "default"
      add :worker, :text, null: false
      add :args, :jsonb, null: false, default: "{}"
      add :errors, :jsonb, null: false, default: "[]"
      add :attempt, :integer, null: false, default: 0
      add :max_attempts, :integer, null: false, default: 20

      add :inserted_at, :utc_datetime_usec,
        null: false,
        default: fragment("timezone('UTC', now())")

      add :scheduled_at, :utc_datetime_usec,
        null: false,
        default: fragment("timezone('UTC', now())")

      add :attempted_at, :utc_datetime_usec
      add :completed_at, :utc_datetime_usec

      add :attempted_by, {:array, :text}
      add :discarded_at, :utc_datetime_usec
      add :priority, :integer, null: false, default: 0
      add :tags, {:array, :string}, null: false, default: []
      add :meta, :jsonb, null: false, default: "{}"
      add :cancelled_at, :utc_datetime_usec
    end

    create constraint(:oban_jobs, "attempt_range", check: "((attempt >= 0) AND (attempt <= max_attempts))")
    create constraint(:oban_jobs, "positive_max_attempts", check: "(max_attempts > 0)")
    create constraint(:oban_jobs, "priority_range", check: "((priority >= 0) AND (priority <= 3))")
    create constraint(:oban_jobs, "queue_length", check: "((char_length(queue) > 0) AND (char_length(queue) < 128))")
    create constraint(:oban_jobs, "worker_length", check: "((char_length(worker) > 0) AND (char_length(worker) < 128))")
  end

  defp add_indexes do
    create_if_not_exists index(:oban_jobs, [:state, :queue, :priority, :scheduled_at, :id])

    create_if_not_exists index(:oban_jobs, [:args], using: :gin)

    create_if_not_exists index(:oban_jobs, [:meta], using: :gin)
  end
end

[sorentwo]

@Th3-M4jor Thanks for confirming that Oban appears to work with Cockroach DB and extracting the migration. An option to disable creating triggers and notifications from the migrations would make it possible to run them without so much manual effort (or duplicating the migration in docs).

[Th3-M4jor]

It seems to also not be liking the use of anonymous functions such as the one here so disabling the creation of triggers/notifications might not be enough.

In the next few days I'll look into opening a PR to document using Oban with CockroachDB or if there is interest possibly adding a second set of migrations that are selectively used in a similar manner to how it is done for SQLite.

[sorentwo]

It seems to also not be liking the use of anonymous functions such as the one here so disabling the creation of triggers/notifications might not be enough.

That's a bummer. The DO block is required to change an enum in older versions of PG. Eventually, we do plan on compacting the migrations into a single step for Oban 3.0.

In the next few days I'll look into opening a PR to document using Oban with CockroachDB

That sounds perfect 👍

[Th3-M4jor]

I'm sadly going to have to close this one as being not currently possible at this time, CockroachDB's lack of support for advisory locks means that a separate engine or special casing around it with an alternative method to guarantee uniqueness would be necessary