PostgreSQL: clean fails when trying to drop aggregates added by extensions

[CatalinaMoisuc]

Which version and edition of Flyway are you using?

    <dependency>
      <groupId>org.flywaydb</groupId>
      <artifactId>flyway-core</artifactId>
      <version>5.1.3</version>
    </dependency>

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

Java API

Which database are you using (type & version)?

PostgreSQL 9.6

What did you do?

I added a new migration containing:
CREATE EXTENSION IF NOT EXISTS CITEXT WITH SCHEMA public;

and then another one altering some columns to use CITEXT instead of VARCHAR.

What did you expect to see?

I expected org.flywaydb.core.Flyway.clean() to know how to clean up the extention.

What did you see instead?

I got this error:

Unable to clean schema "public"
-------------------------------
SQL State  : 2BP01
Error Code : 0
Message    : ERROR: cannot drop function min(citext) because extension citext requires it
  Hint: You can drop extension citext instead.

	at org.flywaydb.core.internal.database.Schema.clean(Schema.java:151)
	at org.flywaydb.core.internal.command.DbClean$3.call(DbClean.java:172)
	at org.flywaydb.core.internal.command.DbClean$3.call(DbClean.java:169)
	at org.flywaydb.core.internal.util.jdbc.TransactionTemplate.execute(TransactionTemplate.java:74)
	at org.flywaydb.core.internal.command.DbClean.cleanSchema(DbClean.java:169)
	at org.flywaydb.core.internal.command.DbClean.clean(DbClean.java:113)
	at org.flywaydb.core.Flyway.doClean(Flyway.java:1043)
	at org.flywaydb.core.Flyway.access$300(Flyway.java:78)
	at org.flywaydb.core.Flyway$3.execute(Flyway.java:1061)
	at org.flywaydb.core.Flyway$3.execute(Flyway.java:1054)
	at org.flywaydb.core.Flyway.execute(Flyway.java:1238)
	at org.flywaydb.core.Flyway.clean(Flyway.java:1054)
	at ….Migrations.clean(Migrations.java:57)
	at ….Migrations.execute(Migrations.java:64)
	at ….Main.main(Main.java:48)
	at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
	at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.codehaus.mojo.exec.ExecJavaMojo$1.run(ExecJavaMojo.java:282)
	at java.lang.Thread.run(Thread.java:748)
Caused by: org.postgresql.util.PSQLException: ERROR: cannot drop function min(citext) because extension citext requires it
  Hint: You can drop extension citext instead.
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2433)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2178)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:306)
	at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:441)
	at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:365)
	at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:155)
	at org.postgresql.jdbc.PgPreparedStatement.execute(PgPreparedStatement.java:144)
	at org.flywaydb.core.internal.util.jdbc.JdbcTemplate.execute(JdbcTemplate.java:240)
	at org.flywaydb.core.internal.database.postgresql.PostgreSQLSchema.doClean(PostgreSQLSchema.java:105)
	at org.flywaydb.core.internal.database.Schema.clean(Schema.java:149)
	... 20 common frames omitted

Note:

I was waiting since a few months ago for this fix to be released:
flyway#100
It was released finally in version: Flyway 5.1.0, but unfortunately it doesn't work for me.

[axelfontaine]

Thanks for reporting. It turns out citext creates an aggregate function instead of a regular one and we weren't dealing properly with that case. We have now fixed this by unifying to routine cleanup code.

[CatalinaMoisuc]

thank you @axelfontaine. Any idea when 5.1.4 will be released?

[axelfontaine]

@CatalinaMoisuc See https://github.com/flyway/flyway/milestones