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
Clean tries to drop a function from an extension and fails #100
Comments
From axel.fontaine.business@gmail.com on May 22, 2012 00:08:11 thanks for your report. Sorry for the late reply. I've thought about this a bit. Do you believe the following solution would work?
Looking forward to your feedback P.S.: There are 2 things that still would need to be researched: how to do this with 9.0 and how to enumerate the installed extensions. |
From Martin.A...@gmail.com on May 22, 2012 20:15:00 Still the cleanup tries to delete individual functions from the extension. So either during cleanup functions from extensions are left alone or only a drop extension command is executed. |
From Martin.A...@gmail.com on May 29, 2012 20:15:21 |
From axel.fontaine.business@gmail.com on June 04, 2012 19:12:18 I've added the answer to your question to the FAQ: http://code.google.com/p/flyway/wiki/FAQ?ts=1338836686&updated=FAQ#Why_does_clean_drop_individual_data_objects_instead_of_the_schem I have been pondering the question of leaving functions from extensions alone vs drop extension. I am still undecided. I currently lean towards the latter as the former would invalidate the promise to truly clean the schema of all its objects. The disadvantage of this is of course the required introduction of separate cleaning methods for 9.0 and 9.1. Input and/or contributions welcome. Cheers |
From kevin...@gmail.com on February 11, 2013 16:44:49 A local modification that I am using is to only drop objects when running as superuser or when the object is owned by the user (since the drop would fail otherwise and the object is not likely to be part of the migration). The solution is not perfect. It may surprise users if some objects are left installed. This could, perhaps, be more transparent if a warning was issued or if it required a configuration option (dropObjects/dropOwnObjects/dropAllObjects or similar). |
From kevin...@gmail.com on February 11, 2013 16:45:40 |
From laser.ta...@googlemail.com on March 08, 2013 21:59:35 I have the same problem with the extension postgis, which creates a table. |
From axel.fontaine.business@gmail.com on March 12, 2013 09:57:28 Somehow I feel that the best solution/workaround involves putting your objects in your own schema, so extensions don't interfere and Flyway doesn't have to deal with them. Comments/contributions/pull requests welcome. |
+1 for cleaning the schema of all its objects |
I have submitted pull request #629, which would allow us to extend any of the DbSupport classes. This makes it trivial to override doClean(), as demonstrated by the 'CustomCleanTest' in the core module. |
Just ran into this when adding Postgres support. Surprised that there is still no solution. |
@axelfontaine I think @bjhartin might have dropped out and I agree with @sfitts that this issue is very annoying while doing Postgres development. Any chance you could take another look at it? |
+1 |
I used callbacks to work around this issue (thank you!) Just a head's up... Even once you fix this problem (dropping extensions), you'll also need to ensure you drop views before attempting to drop "basic types". Otherwise you'll get another error. For those of you wanting to implement your own workaround, please see #629 (comment) |
👍 |
I am having this problem. I have a migration that adds an extension for pg_trgm. Then I'm dead in the water for unit testing when I call flyway.clean(). What is the current workaround for this? |
Put your own objects in a different schema than public
|
Sorry Axel, I don't know what that means. Do you mean take "CREATE EXTENSION pg_trgm" out of my flyway migration file and do it manually? |
@rdifalco See #100 (comment) for a workaround. You register a |
Is there a way to implement a callback with an SQL file rather than in Java? |
@marcomorain This is only possible in Flyway 3.1. See #728 |
I'll give that a try when I get a change. Thanks. On Mon, Dec 1, 2014 at 1:11 PM, cowwoc notifications@github.com wrote:
|
Let's revisit this for 3.2 This could be a starting point: http://stackoverflow.com/questions/25387168/how-to-find-all-user-defined-not-extension-related-functions-in-postgresql Pull request or sponsoring welcome. |
Just incase someone comes looking here and doesn't fancy jumping between the issues to figure out a work around: Create a For example, if like me, when doing DROP EXTENSION IF EXISTS "uuid-ossp" CASCADE; |
+1 |
1 similar comment
+1 |
+1 Just attempted to use Flyway with a PostGIS-enabled database in an integration test. Cleaning fails with: org.flywaydb.core.internal.dbsupport.FlywaySqlException:
|
+1, same as above - clean doesn't work with Postgres databases with PostGIS extension installed. |
The reason we're encountering this is because we put our tables inside "public". There's a workaround to this which I've "uncovered" on Stackoverflow and the like. I'll try to re-iterate the solution:
Put in 'beforeEachMigrate.sql' on your migration path: DO $$ IF default_search_path IS NOT NULL THEN Took me a while to figure this out but this solution seems to have no side effects. :) |
I had the same issue with hstore extension (required superuser). Just create it in PG_CATALOG schema while setup test database as superuser. CREATE EXTENSION hstore SCHEMA pg_catalog In that case no need to set up search_paht. |
This is now working as expected. As long as you create your extensions with |
@axelfontaine unfortunately this is still not working for me. My setup: flyway version: tried both 5.0.7 (recent) and 3.2.1 (embedded in spring boot) V1__init.sql:
Then running against clean db:
And then:
You said
Could you provide a working example please? |
@maraswrona Check the milestone set for this issue. The fix is in the latest sources. |
ah.... so I guess the fix will be in the next release? Thanks for the clarification. |
Does the fix work with users without superuser privilege? Sorry if I could just build from source myself and find out, but I'm not familiar with java, have no idea how to build 5.1.0 from github source. |
This is not working for me either. I am using <dependency>
<groupId>org.flywaydb</groupId>
<artifactId>flyway-core</artifactId>
</dependency>
<dependency>
<groupId>org.flywaydb.flyway-test-extensions</groupId>
<artifactId>flyway-spring-test</artifactId>
<version>5.0.0</version>
<scope>test</scope>
</dependency> or
in a Spring Boot (2.0.1.RELEASE) application. However, running my unit tests will throw:
|
@axelfontaine Oh I only just saw your comment: Is it not supposed to work with |
Okay, I'll repeat what @foal said because it's easily overlooked. If you can, just install your extensions with CREATE EXTENSION IF NOT EXISTS cube WITH SCHEMA pg_catalog;
CREATE EXTENSION IF NOT EXISTS earthdistance WITH SCHEMA pg_catalog; in my Now my tests are running without problems. |
I had this problem for test environment, see this answer https://stackoverflow.com/questions/46656531#51038385 |
@axelfontaine perhaps I'm doing something wrong here but with flyway-core-5.2.1 and "create extension if not exists" on public (not pg_catalog) and I'm still getting the "ERROR: cannot drop table spatial_ref_sys because extension postgis ..." error on clean. |
@svpace Please open a new issue with exact steps how to reproduce. |
Original author: Martin.A...@gmail.com (May 11, 2012 10:11:31)
We are using PostgreSQL 9.1 and are using the uuid-ossp extension. Clean tries to drop one of its functions. The extension has been installed manually as it required adding binaries to the DBMS.
Any way to work around this?
FlywayException: Error executing statement at line 64: DROP FUNCTION IF EXISTS "public"."uuid_nil"() CASCADE
Occured in com.googlecode.flyway.core.migration.sql.SqlStatement in method execute, line number 78
Caused by org.postgresql.util.PSQLException: ERROR: cannot drop function uuid_nil() because extension uuid-ossp requires it
Hint: You can drop extension uuid-ossp instead.
Occured in org.postgresql.core.v3.QueryExecutorImpl in method receiveErrorResponse, line number 2102
Original issue: http://code.google.com/p/flyway/issues/detail?id=257
The text was updated successfully, but these errors were encountered: