Migration table is not found

[fouadkada]

Description

When running dbmate up the migration table is not found thus migrations in the migration directory are re-ran every single time which result in an error.

We currently run dbmate part of a shell script that loops over some values and applies the migrations for each value in the loop.
It looks something like the following:

values=$(cat cars)
for value in $values; do
  echo "Migrating up '$value' schema"
  dbmate \
    --url "$DATABASE_URL" \
    --migrations-dir "migrations/$value" \
    --migrations-table "$value.schema_migrations" \
    --no-dump-schema \
    --wait \
    up
done

The DATABASE_URL has the following value: postgresql://atlas:postgres@localhost:5432/atlas?sslmode=disable

When using dbmate version 1.6.0, everything works as expected. When we updated to version 2.X.X, we started seeing the previously described behavior.

By running dbmate in my IDE and setting breakpoints, I was able to trace where the issue is. For Postgres, in the quotedMigrationsTableNameParts method, the quote_ident function that's being used to add quotations around the schema name are resulting in the table schema_migrations not to be found even though it exists. If the query used to find the schema migration tables is adjusted from select quote_ident(unnest($1::text[])) to select unnest($1::text[]) then the schema migrations is correctly found.
This behavior seems to not exist in version 1.6.0

• Version: 2
• Database: Postgres 12 running inside a docker container
• Operating System: macOS

Steps To Reproduce

dbmate
--url "postgresql://atlas:postgres@localhost:5432/atlas?sslmode=disable"
--migrations-dir "migrations/bmw"
--migrations-table "bmw.schema_migrations"
--no-dump-schema
--wait
up

the migrations in the migration directory are always ran as if it's the first time dbmate is running

Expected Behavior

the migrations that already ran and are in the schema_migrations table are not ran and only new migrations run

[sgielen]

For future visitors to this issue.

I thought I had the issue mentioned here, since I was getting the same error on an initial migration. However, in my case, the underlying issue wasn't quoting -- the issue was that dbmate sometimes used the schema_migrations table without a schema (i.e. public.schema_migrations), and sometimes with an explicit schema. Notably, it tried to create the table without a schema, but then insert a row into the table with a schema. Obviously, this insert failed with the error relation "<schema>.schema_migrations" does not exist.

I fixed this by setting DBMATE_MIGRATIONS_TABLE to include the schema <schema>.schema_migrations explicitly. After that, the table was created in the correct schema, and inserts occurred to the same table as well.

[dossy]

Thanks for raising this issue, @fouadkada and @sgielen.

Considering our current tests pass, I'd like to try and figure out what is different about how Postgres is configured in your environments vs. the one our tests run against.

Our tests are currently run against PostgreSQL 10.21, with all default settings based on the official Docker image for postgres:10:

root@c2a4e68d4451:/src# PGPASSWORD=postgres psql -h postgres -U postgres -tc "select version();" 
 PostgreSQL 10.21 (Debian 10.21-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit

I wonder if it's a matter of the postgres user's default search_path vs. the search_path that's set for the user or database that you're connecting to?

dbmate_test=# show search_path;
   search_path   
-----------------
 "$user", public
(1 row)

dbmate_test=# \dn
  List of schemas
  Name  |  Owner   
--------+----------
 public | postgres
(1 row)

Per the documentation on search path: "The first schema in the search path that exists is the default location for creating new objects." In our case in the dbmate test database, the only schema that exists is public so that's the default schema where objects (tables, indexes, etc.) will be created and found.

I'd like help coming up with a reproducible test that fails with the current dbmate, that we can then use as a guiderail to test all possible solutions and also ensure we don't regress this in the future as it's not easily understood, given the number of ways this particular code has been implemented in dbmate over time.

What is different about your environments that triggers this error, that the current dbmate test suite's environment and test cases do not trip over?

[dossy]

I also suspect this issue is related to #429 as well.