Not working to load postgres schema dump changing catalog search_path

[rbauduin]

Describe the Bug
I had a pg_dump generated file (with option --schema-only) which had this statement causing trouble:

SELECT pg_catalog.set_config('search_path', '', false);

the error was

error: pq: relation "schema_migrations" does not exist in line 0: TRUNCATE "schema_migrations"

Steps to Reproduce
Steps to reproduce the behavior:

1. create a migration which include said statement
2. run migrate up
3. See error

Expected Behavior
No error and succesful migration expected.

Migrate Version
4.2.2

Loaded Source Drivers
e.g. s3, github, go-bindata, gcs, file
Obtained by running: migrate -help

Loaded Database Drivers
e.g. spanner, stub, clickhouse, cockroachdb, crdb-postgres, postgres, postgresql, redshift, cassandra, cockroach, mysql
Obtained by running: migrate -help

[dhui]

Are you running migrate in parallel from multiple instances?
If so, try using the master branch which contains this fix.

Another thing to check would be the db user's permissions.

[rbauduin]

I could work around the problem, but I confirm that the problem comes from the statement SELECT pg_catalog.set_config('search_path', '', false);.
This seems to empty the schema list searched to find a table: https://www.postgresql.org/docs/11/ddl-schemas.html#DDL-SCHEMAS-PATH
It still works for pg_dump as all tables have now their schema explicit.

An up migration with only this works:

create table test (id int);

Setting the search path empty before the create statement, as in

SELECT pg_catalog.set_config('search_path', '', false);
create table test (id int);

results in the error

error: migration failed: no schema has been selected to create in (column 14) in line 2: SELECT pg_catalog.set_config('search_path', '', false);
create table test (id int);
 (details: pq: no schema has been selected to create in)

This can be made to work by specifying the schema explicitly for the table to create:

SELECT pg_catalog.set_config('search_path', '', false);
create table public.test (id int);

but then migrate still has the problem:

error: pq: relation "schema_migrations" does not exist in line 0: TRUNCATE "schema_migrations"

A quick grep show that for postgres, the truncate statement is:

query := `TRUNCATE "` + p.config.MigrationsTable + `"`

It would seem that making the schema explicitely in p.config.MigrationsTable would fix it. I don't know what further implications this has though.
As I said, I could work around the problem by commenting the problematic statement. But as it was generated with pg_dump, other users might encounter the same problem.

[dhui]

If the create table test (id int); statement failed with the error: no schema has been selected to create in, then I wouldn't expect the CREATE TABLE "` + p.config.MigrationsTable + `" (version bigint not null primary key, dirty boolean not null) statement to succeed either.

This seems like an issue with the options you're specifying to pg_dump or with your DB user's access to a schema.