~yyscamper


#70 how to stop migration at a specific version? 3 years ago

Ticket created by ~yyscamper on ~olly/yoyo

For example, I have verion1, version2, version3 migration files. Yoyo will always upgrade the system to version3, how could I do if only want to migrate to verion2 without manually removing the version3 file.

#69 logging step with friendly descriptive message 3 years ago

Ticket created by ~yyscamper on ~olly/yoyo

while yoyo doing migrations, it outputs the following log message:

INFO:yoyo.migrations: - applying step 0
INFO:yoyo.migrations: - applying step 1
INFO:yoyo.migrations: - applying step 2
INFO:yoyo.migrations: - applying step 3

It would be better to add some descriptive message, for example:

INFO:yoyo.migrations: - applying step 0: create table foo
INFO:yoyo.migrations: - applying step 1: migrate data from bar to foo
INFO:yoyo.migrations: - applying step 2: create triggers
INFO:yoyo.migrations: - applying step 3: drop table bar

This requires the step decorator supports a new argument, for example:

step('create table public.foo (id int, name varchar)', title='create table foo')

#68 how to handle multiple apps share same db? 3 years ago

Comment by ~yyscamper on ~olly/yoyo

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.

#68 how to handle multiple apps share same db? 3 years ago

on ~olly/yoyo

RESOLVED FIXED REPORTED

#68 how to handle multiple apps share same db? 3 years ago

Comment by ~yyscamper on ~olly/yoyo

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.

REPORTED RESOLVED FIXED

#68 how to handle multiple apps share same db? 3 years ago

Comment by ~yyscamper on ~olly/yoyo

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?

RESOLVED INVALID REPORTED

#68 how to handle multiple apps share same db? 3 years ago

Ticket created by ~yyscamper on ~olly/yoyo

We have 3 apps run independently, in production, each app has its own dedicated database (PostgreSQL) and each app hosts its own migration scripts. But during test, or some simple on-premises environment, we may choose to run them within the same database (using different schema to separate data). I know yoyo tracks the current db version via '_yoyo_version' table, so there will be conflict for different app if they share the same db. I wonder whether there is a solution for such case?

Thanks.