Always use a schema for expressing the schema version table

[alex]

With the currently unprefixed schema_version table the following sequence of events is possible.

• First tern run creates schema_version in public
• You run a migration that creates a schema with the same name as the user
• Next run of tern now creates schema_version in $user
• tern now attempts to re-apply migrations from 0, potentially causing havoc

Simply specifying versionTable: "public.schema_version" solves this. Using a pgx.Identifier for versionTable in type Migrator will encourage people to do this.

[jackc]

That would be an unfortunate occurrence. Changing the type of versionTable would be a breaking interface change -- so I would prefer to avoid that. However, I will change the default from "schema_version" to "public.schema_version".

[jackc]

I've just pushed v1.9.0. New uses of the CLI should avoid this issue. I also added a comment to the NewMigrator[Ex] functions recommending fully qualifying the version table name.

[jackc]

Another option to more fully guard against that situation would be to inspect the database and see if there is already a version table in a different schema.

....

Actually, wait a second. In your original scenario, in step 3 the original schema_version table is still visible in the search path. Why is it not just using that?

....

That's the problem... create table if not exists is looking at the top of the search path instead of seeing if a table by that name is visible at all.

So I guess it might be fair to add a check to ensureSchemaVersionTableExists to detect the version table instead of relying on create table if not exists.

...

Okay. Just released v1.9.1 with ensureSchemaVersionTableExists checking for existence of the version table anywhere in the search path instead of only the top. This should resolve the problem regardless of fully qualifying the version table name.