first installation - missing oban_jobs table

[clementnuss]

Describe the bug Upon a fresh installation of asciinema-server on an empty PGSQL DB version 14.10, I found myself stuck with the following error message:

16:24:50.472 [info] == Running 20211212114046 Asciinema.Repo.Migrations.InsertUpgradeJobs.up/0 forward
** (Postgrex.Error) ERROR 42P01 (undefined_table) relation "oban_jobs" does not exist

    query: INSERT INTO "oban_jobs" ("args","attempt","errors","max_attempts","meta","queue","state","tags","worker") VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9) RETURNING "id"
    (ecto_sql 3.11.1) lib/ecto/adapters/sql.ex:1054: Ecto.Adapters.SQL.raise_sql_call_error/1
    (ecto 3.11.1) lib/ecto/repo/schema.ex:775: Ecto.Repo.Schema.apply/4
    (ecto 3.11.1) lib/ecto/repo/schema.ex:377: anonymous fn/15 in Ecto.Repo.Schema.do_insert/4
    (ecto 3.11.1) lib/ecto/multi.ex:883: Ecto.Multi.apply_operation/5
    (elixir 1.14.3) lib/enum.ex:2468: Enum."-reduce/3-lists^foldl/2-0-"/3
    (ecto 3.11.1) lib/ecto/multi.ex:856: anonymous fn/5 in Ecto.Multi.apply_operations/5
    (ecto_sql 3.11.1) lib/ecto/adapters/sql.ex:1358: anonymous fn/3 in Ecto.Adapters.SQL.checkout_or_transaction/4
    nofile:1: (file)

I've found this other issue, where it was recommend to run a setup phase first, which I also did after cleaning up all schemas in my DB, and re-creating them.

I might be doing something wrong, but at this stage I'm also suspecting that initial setup could be broken in the latest versions.

To Reproduce Steps to reproduce the behavior:

1. create a fresh PostgreSQL DB (in my case asciinema).
2. try to start the :latest asciinema-server
3. See error

Expected behavior On initial startup, all tables should be created without trouble.

Versions:

• asciinema server: latest

Additional context Add any other context about the problem here.

when running \d in psql, I get the following output (also including more output, might help)

asciinema=> \d
                  List of relations
  Schema   |       Name        |   Type   |   Owner
-----------+-------------------+----------+-----------
 asciinema | api_tokens        | table    | asciinema
 asciinema | api_tokens_id_seq | sequence | asciinema
 asciinema | asciicasts        | table    | asciinema
 asciinema | asciicasts_id_seq | sequence | asciinema
 asciinema | schema_migrations | table    | asciinema
 asciinema | users             | table    | asciinema
 asciinema | users_id_seq      | sequence | asciinema
 public    | oban_jobs         | table    | asciinema
 public    | oban_jobs_id_seq  | sequence | asciinema
 public    | oban_peers        | table    | asciinema

asciinema=> SHOW search_path;
    search_path
-------------------
 asciinema, public
(1 row)

asciinema=> \dn+
                       List of schemas
   Name    |   Owner   |   Access privileges    | Description
-----------+-----------+------------------------+-------------
 asciinema | asciinema |                        |
 public    | asciinema | asciinema=UC/asciinema+|
           |           | =UC/asciinema          |
(2 rows)

[clementnuss]

I found my issue! it was actually a search_path problem. In my (limited) understanding of postgresql, the search_path that was reported when connected with psql might not have been the same as what was used when running the setup step.

in any case, here's how to solve your issue and re-create the proper public schema with the right permissions, and the right search_paths for your role (in my case asciinema):

DROP SCHEMA asciinema cascade;
DROP SCHEMA public cascade;
CREATE SCHEMA IF NOT EXISTS public;
grant usage on schema public to public;
grant create on schema public to public;
\dn+ # check that everything is configured correctly, there should only be the public schema with non-empty access privileges
alter role asciinema IN DATABASE asciinema set search_path = "$user", public;

finally you can run a ./bin/server setup from within your container (I typically started my container with entrypoint /bin/sleep infinity to simplify debugging).

hope this helps 🙃