Issue 406 in flyway: Flyway.execute() fails if no public schema exists (with Postgres)

[fly...@googlecode.com]

Status: New
Owner: ----
Labels: Type-Defect Priority-Medium

New issue 406 by fifteenk...@gmail.com: Flyway.execute() fails if no public
schema exists (with Postgres)
http://code.google.com/p/flyway/issues/detail?id=406

Since my application does not need a public schema, I followed the advice
in the Postgres docs and deleted the public schema.

Flyway V 2.0.3
Database: Postgresql 8.1.4

Some query results from class PostgreSQLDbSupport
SELECT current_schema() --> NULL
SHOW search_path --> '"$user",public'
SET search_path = "trm","$user",public -->
org.postgresql.util.PSQLException: FEHLER: Schema »public« existiert nicht

Stack trace:
com.googlecode.flyway.core.api.FlywayException: Error setting current
schema to trm
at com.googlecode.flyway.core.Flyway.execute(Flyway.java:1250)
at com.googlecode.flyway.core.Flyway.info(Flyway.java:1012)
at com.razorcat.trm.server.TRMServer.checkDBMigrations(TRMServer.java:433)

[fly...@googlecode.com]

Comment #1 on issue 406 by axel.fontai...@gmail.com:

Flyway.execute() fails if no public schema exists (with Postgres)
http://code.google.com/p/flyway/issues/detail?id=406

Thanks for your report. It looks like the Postgresql version you are using
is buggy.

From the docs
(http://www.postgresql.org/docs/8.1/static/functions-info.html):

current_schema returns the name of the schema that is at the front of the
search path (or a null value if the search path is empty)

As your search_path is not empty, current_schema can not be null.

Please upgrade to a more recent version (Flyway officially supports
Postgresql 9.0 and above) or fix the search path and current_schema of the
connection.

Cheers
Axel

[fly...@googlecode.com]

Comment #2 on issue 406 by fifteenk...@gmail.com: Flyway.execute() fails if

no public schema exists (with Postgres)
http://code.google.com/p/flyway/issues/detail?id=406

Hi Axel,

correction: Postgresql version is 8.4.4.

Unfortunately I cannot upgrade to Postgresql 9.0 or above since there are
no RPMs available for the Redhat distribution used at our customer side.

The docs (http://www.postgresql.org/docs/8.1/static/functions-info.html)
seem to be incomplete or wrong:
If I manually re-add the public schema, current_schema() returns 'public'.
current_schema() seems to return a schema from the search path only if that
schema actually exists, which makes some sense to me.

Setting the search path globally in postgresql.conf is not an option, since
that will affect all databases on the server.
And setting the search path of the connection seems impossible, since the
connection is opened by Flyway.

Anyway, I will check whether this is a driver issue.

Thanks,
Martin

[fly...@googlecode.com]

Comment #3 on issue 406 by fifteenk...@gmail.com: Flyway.execute() fails if

no public schema exists (with Postgres)
http://code.google.com/p/flyway/issues/detail?id=406

Hi Axel,

I just installed "PostgreSQL 9.1.6 on i586-suse-linux-gnu" on SUSE 12.2 and
created an empty database.
Same behaviour here: current_schema() returns a schema from the search path
only if that schema actually exists.

SELECT current_schema(), current_schemas(true)
--> "public";"{pg_catalog,public}"
DROP SCHEMA public;
SELECT current_schema(), current_schemas(true) --> "";"{pg_catalog}"

With Postgresql 8.4.4, current_schema() returns NULL, whereas 9.1.6 returns
an empty string. Is that a bug?

Shouldn't be too hard, to make PostgreSQLDbSupport.java to test for null to
fix that.

Shall I send a patch?

Martin

[fly...@googlecode.com]

Updates:
Labels: Database-PostgreSQL Component-Core

Comment #4 on issue 406 by axel.fontai...@gmail.com:

Flyway.execute() fails if no public schema exists (with Postgres)
http://code.google.com/p/flyway/issues/detail?id=406

Hi Martin,

sure. I'll have a look at the pull request once you've sent it. I'm
currently not sure how this will fix your original problem though.

Cheers
Axel

[fly...@googlecode.com]

Comment #5 on issue 406 by fifteenk...@gmail.com: Flyway.execute() fails if

no public schema exists (with Postgres)
http://code.google.com/p/flyway/issues/detail?id=406

Hi Axel,

yes, You're right, it won't fix the problem. So no patch attached:-(

But the problem is reproducable with PostgreSQL 9.1.6 (using pgadmin3, no
JDBC, no Flyway).

With public schema:
SET search_path = "trm","$user",public ==> OK
Without public schema:
SET search_path = "trm","$user",public ==> ERROR: invalid value for
parameter "search_path": "trm, "$user", public"
DETAIL: schema "public" does not exist

This does not look like Postgresql version 8.4.4 is buggy.

Martin

[fly...@googlecode.com]

Comment #6 on issue 406 by kamil.sz...@gmail.com: Flyway.execute() fails if

no public schema exists (with Postgres)
http://code.google.com/p/flyway/issues/detail?id=406

@Martin
ALTER ROLE <NAME> SET search_path TO trm, "$user"; should fix your problem.

@Axel
I've added validation for cases when no schemas are set and current schema
for current user is not set.
Now a meaningful exception is thrown instead of NPE.
Other problems related to this issue seem to be resolved in latest snapshot
builds.
Pull request submitted.

Best regards
Kamil Szymański

[fly...@googlecode.com]

Comment #7 on issue 406 by kamil.sz...@gmail.com: Flyway.execute() fails if

no public schema exists (with Postgres)
http://code.google.com/p/flyway/issues/detail?id=406

Unfortunatly "Other problems related to this issue seem to be resolved in
latest snapshot builds" is not true :<.
I was testing it with the wrong DB user what resulted in not all cases
being tested properly :<.
But a good thing is that error messages are descriptive enough:
[ERROR] com.googlecode.flyway.core.api.FlywayException: Error setting
current schema to "flyway_test"
[ERROR] Caused by org.postgresql.util.PSQLException: ERROR: invalid value
for parameter "search_path": "flyway_test, "$user", public"
Detail: schema "public" does not exist

One could argue with it but I would go with not fixing this issue further.
The cause of this error is DB role not being set up properly and should be
handled by DB administrator.

[fly...@googlecode.com]

Comment #8 on issue 406 by kamil.sz...@gmail.com: Flyway.execute() fails if

no public schema exists (with Postgres)
http://code.google.com/p/flyway/issues/detail?id=406

Unfortunately "Other problems related to this issue seem to be resolved in

[fly...@googlecode.com]

Comment #9 on issue 406 by fifteenk...@gmail.com: Flyway.execute() fails if

no public schema exists (with Postgres)
http://code.google.com/p/flyway/issues/detail?id=406

Of course,
'ALTER ROLE <NAME> SET search_path TO trm, "$user";' should fix my
problem...
..Unfortunately, I cannot use Flyway to fix the problem on databases that
already exist.

BTW: Why does Flyway need to set the search path? If no flyway.schemas is
given, Postgres will simply use the default schema -- or fail if that does
not exist.

Martin

[fly...@googlecode.com]

Comment #10 on issue 406 by kamil.sz...@gmail.com: Flyway.execute() fails

if no public schema exists (with Postgres)
http://code.google.com/p/flyway/issues/detail?id=406

Speaking of: ALTER ROLE <NAME> SET search_path TO trm, "$user";
You don't create user (with it's password, permissions, search_path etc.)
used by the Flyway in a migration script, do you?

You cannot use Flyway to fix your problem because you made a structure
change outside of Flyway (look at the FAQ).

You could have just revoke all permissions from public (role) instead of
dropping public (schema).

Ignoring search_path at this point may lead to some backward
incompatibilities in existing migration scripts (they may depend on some
functions, operators, etc. defined in the schemas on the search_path that
are not listed in Flyway configuration [bad practice?]).

[fly...@googlecode.com]

Updates:
Status: WontFix

Comment #11 on issue 406 by axel.fontai...@gmail.com:

Flyway.execute() fails if no public schema exists (with Postgres)
http://code.google.com/p/flyway/issues/detail?id=406

@all: I agree with Kamil.

I have now checked in a change that will throw a meaningful exception if no
current schema is set.

Won't fix the other issue.

Cheers
Axel

[fly...@googlecode.com]

Comment #12 on issue 406 by fifteenk...@gmail.com: Flyway.execute() fails

if no public schema exists (with Postgres)
http://code.google.com/p/flyway/issues/detail?id=406

@Kamil

> Speaking of: ALTER ROLE <NAME> SET search_path TO trm, "$user";
> You don't create user (with it's password, permissions, search_path etc.)
> used by the Flyway in a migration script, do you?

The user has been created without search path (CREATE ROLE <name>). At that
time, Flyway 1.6/7 did not try to set the search path, I had no issues when
I was testing that with the public schema missing.


The real problem is: In its current state, Flyway 2.0.3 (the agile DB
migration tool) is not able to migrate from DBs that have been initialized
with Flyway 1.x. To make Flyway 2 work on these existing databases, I will
have to deliver migration scripts to the DBAs that must be manually
applied. I added Flyway to my application exactly to get rid of those
manual migrations; now I have to introduce these again.

Martin

[fly...@googlecode.com]

Comment #13 on issue 406 by axel.fontai...@gmail.com:

Flyway.execute() fails if no public schema exists (with Postgres)
http://code.google.com/p/flyway/issues/detail?id=406

Hi Martin,

I am really sorry to hear this is causing you so much trouble.

Hopefully this should be the last manual change you need to get the DBAs to
apply.

Cheers
Axel