Issue 257 in flyway: Clean tries to drop a function from an extension and fails on PostgreSQL

[fly...@googlecode.com]

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

New issue 257 by Martin.A...@gmail.com: Clean tries to drop a function from
an extension and fails on PostgreSQL
http://code.google.com/p/flyway/issues/detail?id=257

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

[fly...@googlecode.com]

Comment #1 on issue 257 by axel.fontai...@gmail.com: Clean tries to

drop a function from an extension and fails on PostgreSQL
http://code.google.com/p/flyway/issues/detail?id=257

Hi Martin,

thanks for your report. Sorry for the late reply. I've thought about this a
bit.

Do you believe the following solution would work?

- Add support to Flyway to clean extension (using DROP EXTENSION)
- Add a CREATE EXTENSION line to your initial migration

Looking forward to your feedback
Axel

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.

[fly...@googlecode.com]

Comment #2 on issue 257 by Martin.A...@gmail.com: Clean tries to drop a

function from an extension and fails on PostgreSQL
http://code.google.com/p/flyway/issues/detail?id=257

Looks like a feasible solution. Let me explain what actions we took when
establishing our migration using the extension.
1.) The uuid-ossp extension requires a binary/os-specific addon. On the
windows platform that was already installed. For linux we had to get the
package first and install it
2.) Since the extension required some additional software to be installed
we decided not to have the CREATE EXTENSION statement as part of the
migration script

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.

[fly...@googlecode.com]

Comment #3 on issue 257 by Martin.A...@gmail.com: Clean tries to drop a

function from an extension and fails on PostgreSQL
http://code.google.com/p/flyway/issues/detail?id=257

As we are currently looking for a workaround for the failing clean process,
we have a question regarding the reason why clean is actually performing
a 'object by object' delete process?
How much would it differ from a simple DROP SCHEMA, CREATE SCHEMA sequence?

[fly...@googlecode.com]

Updates:
Status: Accepted

Comment #4 on issue 257 by axel.fontai...@gmail.com: Clean tries to

drop a function from an extension and fails on PostgreSQL
http://code.google.com/p/flyway/issues/detail?id=257

Hi Martin,

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
Axel

[fly...@googlecode.com]

Comment #5 on issue 257 by kevin...@gmail.com: Clean tries to drop a

function from an extension and fails on PostgreSQL
http://code.google.com/p/flyway/issues/detail?id=257

Doing DROP/CREATE SCHEMA may be useful for some situations, but there are
many extensions which require superuser privileges to install/remove (e.g.
isn), and others which do not require superuser may still be managed by a
database administrator team which would prevent application developers (or
the application user) from modifying them.

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).

[fly...@googlecode.com]

Comment #6 on issue 257 by kevin...@gmail.com: Clean tries to drop a

function from an extension and fails on PostgreSQL
http://code.google.com/p/flyway/issues/detail?id=257

Sorry, in my last comment I meant DROP/CREATE EXTENSION rather than
DROP/CREATE SCHEMA.

[fly...@googlecode.com]

Comment #7 on issue 257 by laser.ta...@googlemail.com: Clean tries to drop

a function from an extension and fails on PostgreSQL
http://code.google.com/p/flyway/issues/detail?id=257

Hi there, is there any solution yet?

I have the same problem with the extension postgis, which creates a table.
So when running clean the table cannot be dropped because postgis extension
is still enabled...

--
You received this message because this project is configured to send all
issue notifications to this address.
You may adjust your notification preferences at:
https://code.google.com/hosting/settings

[fly...@googlecode.com]

Comment #8 on issue 257 by axel.fontai...@gmail.com: Clean tries to

drop a function from an extension and fails on PostgreSQL
http://code.google.com/p/flyway/issues/detail?id=257

No solution yet.

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.

[fly...@googlecode.com]

Updates:
Status: Migrated

Comment #9 on issue 257 by axel.fontai...@gmail.com: Clean tries to

drop a function from an extension and fails on PostgreSQL
http://code.google.com/p/flyway/issues/detail?id=257

Migrated to GitHub: https://github.com/flyway/flyway/issues/100

To everyone who starred this issue: please leave a +1 comment on the linked
GitHub issue