Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

beforeEachMigrate callback can't change search_path anymore? #1280

Closed
oohira opened this issue Apr 14, 2016 · 1 comment
Closed

beforeEachMigrate callback can't change search_path anymore? #1280

oohira opened this issue Apr 14, 2016 · 1 comment
Labels
Milestone

Comments

@oohira
Copy link

@oohira oohira commented Apr 14, 2016

What version of Flyway are you using?

Flyway 4.0

What database are you using (type & version)?

PostgreSQL 9.4

What operating system are you using?

MacOS X 10.10.5, CentOS 6.5

What did you do?

We use beforeEachMigrate callback to set up search_path because our DB uses table inheritance feature.

sql/tenant/beforeEachMigrate.sql:

SET search_path TO ${search_path};
  • ${search_path} is replaced dynamically depending on a target schema. For example,
    • SET search_path TO aaa,sss,public
    • SET search_path TO bbb,sss,public
    • SET search_path TO ccc,ttt,public

sql/tenant/V8.0.0_20160330_140700__30928_tenant_budget.sql:

CREATE    TABLE    budget_h    (
    primary key    (    term    )
) inherits (co_budget_h)
...
  • co_budget_h only exists in a parent table (i.e. sss and ttt in previous example)

Executed command:

$ flyway -configFile=./database.conf -locations=filesystem:./sql/tenant -table=schema_version_tenant -schemas=aaa -placeholders.search_path=aaa,sss,public -outOfOrder=true migrate
What did you expect to see?

Migration successfully applied.

What did you see instead?

Migration failed because SQL can not resolve a parent table.

Command Output:

$ flyway -configFile=./database.conf -locations=filesystem:./sql/tenant -table=schema_version_tenant -schemas=aaa -placeholders.search_path=aaa,sss,public -outOfOrder=true migrate
Flyway 4.0 by Boxfuse

Database: jdbc:postgresql://localhost:5433/myapp (PostgreSQL 9.4)
Successfully validated 4 migrations (execution time 00:00.105s)
Current version of schema "aaa": 8.0.0.20160324.152800
WARNING: outOfOrder mode is active. Migration of schema "aaa" may not be reproducible.
Migrating schema "aaa" to version 8.0.0.20160330.140700 - 30928 tenant budget
Executing SQL callback: beforeEachMigrate
ERROR: Migration of schema "aaa" to version 8.0.0.20160330.140700 - 30928 tenant budget failed! Changes successfully rolled back.
ERROR:
Migration V8.0.0_20160330_140700__30928_tenant_budget.sql failed
----------------------------------------------------------------
SQL State  : 42P01
Error Code : 0
Message    : ERROR: relation "co_budget_h" does not exist
Location   : ./sql/tenant/V8.0.0_20160330_140700__30928_tenant_budget.sql (/home/username/flyway/sql/tenant/V8.0.0_20160330_140700__30928_tenant_budget.sql)
Line       : 5
Statement  : CREATE    TABLE    budget_h    (
    primary key    (    term    )
    ) inherits (co_budget_h)

Flyway 3.2.1 did not reset search_path after beforeEachMigrate callback, but now Flyway 4.0 does (for #1108).

I agree the basic rule that each migration should be executed independently. However, in #1025, you moved beforeEachMigrate callback into same transaction of each migration. It means that you think beforeEachMigrate is a part of the migration. If so, I don't think Flyway should reset search_path or add any additional side effect after beforeEachMigrate.

    private void doMigrate(MigrationInfoImpl migration, MigrationExecutor migrationExecutor, String migrationText) throws SQLException {
        for (final FlywayCallback callback : callbacks) {
            dbSupportUserObjects.changeCurrentSchemaTo(schema);
            callback.beforeEachMigrate(connectionUserObjects, migration);
        }

        dbSupportUserObjects.changeCurrentSchemaTo(schema);
        migrationExecutor.execute(connectionUserObjects);
        LOG.debug("Successfully completed migration of " + migrationText);

        for (final FlywayCallback callback : callbacks) {
            dbSupportUserObjects.changeCurrentSchemaTo(schema);
            callback.afterEachMigrate(connectionUserObjects, migration);
        }
    }

I know this is a really rare use case, but our software depends on the behavior of Flyway 3. What do you think?

Thanks for your awesome software.

@axelfontaine
Copy link
Contributor

@axelfontaine axelfontaine commented Apr 14, 2016

Your analysis makes sense and that would indeed be more consistent.

@axelfontaine axelfontaine added this to the Flyway 4.0.1 milestone Apr 14, 2016
axelfontaine added a commit to flyway/flywaydb.org that referenced this issue May 6, 2016
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
2 participants