oban-bg/oban

(Postgrex.Error) ERROR 42601 (syntax_error) on deploy

Closed this issue · 1 comments

martInatpeek commented

Environment

  • Oban Version: 2.17.6
  • Oban Pro: 1.3.5
  • Oban Web: 2.10.2
  • PostgreSQL Version: PostgreSQL 11.22
  • Ecto: 3.11.1
  • Elixir & Erlang/OTP:
elixir v1.16.1-otp-26
erlang 26.2

Current Behavior

We are using Oban Pro, we recently upgraded elixir from 1.15 to 1.16
oban pro 1.2.1 to oban pro 1.3.5
oban 2.17.1 to 2.17.6
and are now experiencing the following error every time we deploy our app

** (Postgrex.Error) ERROR 42601 (syntax_error) syntax error at or near "MATERIALIZED"

    query: WITH "subset" AS MATERIALIZED(SELECT so0."id" AS "id" FROM "public"."oban_jobs" AS so0 WHERE ((so0."state" = 'available') AND (so0."queue" = $1)) ORDER BY so0."priority", so0."scheduled_at", so0."id" LIMIT $2 FOR UPDATE SKIP LOCKED) UPDATE "public"."oban_jobs" AS o0 SET "state" = $3, "attempted_at" = $4, "attempted_by" = $5, "attempt" = o0."attempt" + $6 FROM "subset" AS f1 WHERE (((o0."id" = f1."id") AND (o0."state" = 'available')) AND (o0."attempt" < o0."max_attempts")) RETURNING o0."id", o0."state", o0."queue", o0."worker", o0."args", o0."meta", o0."tags", o0."errors", o0."attempt", o0."attempted_by", o0."max_attempts", o0."priority", o0."attempted_at", o0."cancelled_at", o0."completed_at", o0."discarded_at", o0."inserted_at", o0."scheduled_at"

this error occurs 2 or 3 times and then only appears the next time we deploy.

Before we had a different error on deploy (which appears to be gone)

RuntimeError: ** (RuntimeError) No Oban instance named `Oban` is running and config isn't available.

lib/oban/registry.ex:37 in Oban.Registry.config/1
lib/oban/peer.ex:126 in Oban.Peer.leader?/2
lib/oban/met/reporter.ex:89 in Oban.Met.Reporter.handle_info/2

Expected Behavior

No error occurs during deployments.

Please let us know if you need any more info from us to help you debug this potential problem. Thank you so much!!

sorentwo commented

That error is from the MATERIALIZED keyword, which is only supported in Postgres 12+. Oban has only officially supported Postgres 12 for the past year or so.

Your best options are to pin Oban to a slightly older version (before the materialized keyword was added), or upgrade Postgres to a more recent version. I highly recommend upgrading Postgres if possible, there are a ton of improvements!

  • ❤️1