An issue was reported in https://github.com/peterldowns/pgmigrate/issues/4 that when a migration executed SET search_path TO ..., pgmigrate would subsequently break by failing to query against the table it uses to track which migrations have been applied:
"ERROR: relation \"pgmigrate_migrations\" does not exist (SQLSTATE 42P01)"
This PR fixes this problem by making the following changes to solve the problem:
The pgmigrate.Migrator logic has been updated to properly quote/escape the migrator.TableName value, allowing customers to use a fully-qualified schema.table_name value that will resolve to the same table in the same schema regardless of the current search_path setting on the open connection.
The pgmigrate.DefaultTableName is now fully-qualified with a leading schema prefix, and becomes public.pgmigrate_migrations by default.
This is a breaking change for anyone using pgmigrate and connecting to their database with a ?search_path=something connection string parameter, where something != public.
Because https://github.com/peterldowns/pgmigrate/pull/6 has been merged, these affected customers can prevent any problems by setting the migrations table to what it was previously being resolved to (something.pgmigrate_migrations) using the --table-name cli argument or table_name: yaml config key.
To confirm the fix, I added unit tests for:
The schema-and-table name escaping logic.
The exact scenario reported in #4 showing that pgmigrate now operates correctly in the presence of migrations that include SET search_path TO ....
A potentially unexpected consequence of using SET search_path TO ... in one migration, which is that subsequent migrations may execute with a different search_path.
My overall recommendation is that if you're modifying connection-specific settings inside of a migration, you:
ALWAYS re-set the relevant settings at the beginning of every migration, to ensure that your migrations run with the settings you expect.
Consider using SET LOCAL instead of SET to scope the changes to just the transaction that is executing your migration, to help prevent accidental "contamination" of the connection state that may change the behavior of subsequent migrations.
Generally try to avoid modifying connection settings inside migrations, it just makes everything more confusing.
With this PR, pgmigrate's correctness will no longer be affected by the use of SET search_path TO ... in a migration.
An issue was reported in https://github.com/peterldowns/pgmigrate/issues/4 that when a migration executed
SET search_path TO ..., pgmigrate would subsequently break by failing to query against the table it uses to track which migrations have been applied:This PR fixes this problem by making the following changes to solve the problem:
pgmigrate.Migratorlogic has been updated to properly quote/escape themigrator.TableNamevalue, allowing customers to use a fully-qualifiedschema.table_namevalue that will resolve to the same table in the same schema regardless of the currentsearch_pathsetting on the open connection.pgmigrate.DefaultTableNameis now fully-qualified with a leading schema prefix, and becomespublic.pgmigrate_migrationsby default.?search_path=somethingconnection string parameter, wheresomething != public.something.pgmigrate_migrations) using the--table-namecli argument ortable_name:yaml config key.To confirm the fix, I added unit tests for:
SET search_path TO ....SET search_path TO ...in one migration, which is that subsequent migrations may execute with a differentsearch_path.My overall recommendation is that if you're modifying connection-specific settings inside of a migration, you:
SET LOCALinstead ofSETto scope the changes to just the transaction that is executing your migration, to help prevent accidental "contamination" of the connection state that may change the behavior of subsequent migrations.With this PR,
pgmigrate's correctness will no longer be affected by the use ofSET search_path TO ...in a migration.