PostgreSQL: Flyway uses "public" schema instead of current_schema when search_path starts with $user

[ghost]

What version of Flyway are you using?

5.0.7

Which client are you using? (Command-line, Java API, Maven plugin, Gradle plugin, SBT plugin, ANT tasks)

Java API

What database are you using (type & version)?

PostgreSQL 9.6

What operating system are you using?

Ubuntu 16.04

What did you do?

Migrate or baseline on a connection where search_path starts with $user.

This affects all migration actions due to the way ´PostgreSQLConnection.getFirstSchemaFromSearchPath´ works.

What did you expect to see?

Metadata table and application's schema objects are created in the current_schema which is also the name of the login role, say "my_application"

I also expect "public" to remain on the search path for each executed migration.

What did you see instead?

Metadata table and application's schema objects are in "public" schema.

Ideas (mutually exclusive):

• do select current_schema instead of parsing search_path
• if you're going to modify search_path, backup and restore the entire search_path expression I stand corrected
• document that migrations don't get to make a change to search_path that's visible to subsequent migrations. There are at least a dozen other bad ideas like this where flyway currently doesn't try to compensate :)
• document that migration authors are responsible for establishing any preconditions their migrations might have besides being connected to the correct database

[axelfontaine]

Thanks for the report. Related to #1407

Note that we do actually back up and restore the full search_path.

[axelfontaine]

@daniel-huss Could you elaborate a little on the following:

document that migrations don't get to make a change to search_path that's visible to subsequent migrations. There are at least a dozen other bad ideas like this where flyway currently doesn't try to compensate :)

We of course do this so that a migration run with V1 and V2 would result in the same database as two separate migration runs where the first would only do V1 and the second only V2. What other bad ideas do you feel we should compensate for and currently aren't?

document that migration authors are responsible for establishing any preconditions their migrations might have besides being connected to the correct database

Which preconditions exactly do you have in mind?

[ghost]

Thank you for giving me a chance to explain this more clearly, I'm genuinely bad at communicating with people.

What I was really trying to say is: I strongly believe Flyway should never ever touch the session state, nor should migrations, nor should other application code. Session state should be considered all set up and read-only once the application obtains some JDBC Connection instance from a DataSource.

I actually don't want Flyway to compensate for any problems caused by altered session state, current_schema just being a subset of that state.

If I'm not mistaken, Flyway setting current_schema was introduced for #1108. I disagree with its author about the part where Flyway should be responsible for reversing a session state change it didn't make.

If some migration really needs to temporarily alter session state because there's no equivalent to Postgres' set local, it should do so in a try...finally block.

Those documentation "ideas" otoh are probably just me trying to make the other option (=Flyway taking responsibility for session state) look bad. Sorry about that.

[axelfontaine]

I ended up going for the select current_schema route, completely eliminating the search path parsing logic