Migration table is not found about dbmate

[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.