~olly REPORTED INVALID 4 years ago

Make sure you supply the schema in the connection string (eg postgresql://myserver/mydb?schema=myserver) and yoyo should create its internal tables in each schema separately, so there won't be a conflict.

~yyscamper INVALID REPORTED 4 years ago*

Thanks.

I tried to supply a schema, it workers, but another issue happens: the search_path is broken.

I have many common functions/procedures/types in public schema, and some extensions, such as PostGIS, also installs functions/types into public schema. So by default I don't need to quote those function with schema (ST_Intersects vs public.ST_Intersects).

But using yoyo migration with schema is supplied in connection string, yoyo forces the search_path to the schema only (the public is excluded), then I have to modify all those functions and types to schema quoted ones.

I would hope yoyo inserts the selected schema into the very beginning of current search_path, take the code for example:

Replace:

cursor.execute("SET search_path TO {}".format(self.schema))

with:

currpath = self.execute("SHOW search_path").fetchone()[0]

targetpath = ','.join([self.schema, currpath]

cursor.execute("SET search_path TO {}".format(targetpath))

Do you agree this is a more reasonable design?

~olly 4 years ago

I might be wrong, but I think that solution would create problems if you had previously applied migrations to the public schema. With public on the search path yoyo would see the public._yoyo_migrations table and so not create schema-specific tables.

I haven't tested this, but would it work to specify ?schema=myschema,public in the connection string?

~yyscamper REPORTED FIXED 4 years ago*

if yoyo references those _yoyo_### tables always using the schema quoted name (myschema._yoyo_migration vs _yoyo_migration), this seems will not be a problem. But I haven't go through the whole yoyo sources code, and I have no idea to what extent yoyo depends on the search_path.

~yyscamper FIXED REPORTED 4 years ago

~yyscamper 4 years ago

I have tried my solution (insert schema at beginning of search_path), mostly works, except following problem:

The _yoyo_lock table will still be created firstly at public schema instead of myschema, but run the migration the second time, another _yoyo_lock table will be created at myschema.

I dig out it is because myschema is created within the same transaction of migration. unlike other tables, _yoyo_lock is created within a new transaction, so it is not aware of the newly created myschema, then follows the search_path, _yoyo_lock will fallback to create in public schema. But at the second time, it is aware of the newly created myschema, and found the table _yoyo_lock is not present within myschema, so it creates a new one.

To solve this problem. I have to make sure myschema is present before running the yoyo migration.

From my experience, the search_path often leads to some strange behavior. it does job implicitly rather than explicitly. I would suggest getting rid of the dependency of search_path if it doesn't lead to much pain and refactor works.