Flyway fails to drop aggregate functions from postgres on clean

[mjburghoffer]

Which version and edition of Flyway are you using?

flyway community , 5.1.4
group: 'org.flywaydb', name: 'flyway-core', version: '5.1.4'

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

Gradle plugin

Which database are you using (type & version)?

Postgres 9.6

Which operating system are you using?

Mac OSX (though using dockerized postgres postgres:9.6-alpine).

What did you do?

I created an aggregate function, then attempted to do a flywayClean task in gradle. Code is supplied below:

CREATE OR REPLACE FUNCTION public.first_agg(anyelement, anyelement)
    RETURNS anyelement LANGUAGE SQL
IMMUTABLE
STRICT AS $$
SELECT $1;
$$;

CREATE AGGREGATE public.first (
        sfunc = PUBLIC.first_agg,
        basetype = ANYELEMENT,
        stype = ANYELEMENT
);

CREATE OR REPLACE FUNCTION public.last_agg(anyelement, anyelement)
    RETURNS anyelement LANGUAGE SQL
IMMUTABLE
STRICT AS $$
SELECT $2;
$$;

CREATE AGGREGATE public.last (
        sfunc = PUBLIC.last_agg,
        basetype = ANYELEMENT,
        stype = ANYELEMENT
);

What did you expect to see?

I expected the aggregate functions to be dropped, or at very least be given the option to ignore these errors and get warnings instead.

What did you see instead?

> Error occurred while executing flywayClean
  
  Unable to clean schema "public"
  -------------------------------
  SQL State  : 42809
  Error Code : 0
  Message    : ERROR: "public.last" is an aggregate function
    Hint: Use DROP AGGREGATE to drop aggregate functions.
  
  ERROR: "public.last" is an aggregate function
    Hint: Use DROP AGGREGATE to drop aggregate functions.

[axelfontaine]

Thanks for the clear report and the concise test case. This was a great help to reproduce this issue. I couldn't reproduce for 9.6, but could do so on our 9.5 and 9.4 instances in our infrastructure. It turns out that the PG JDBC driver can be inconsistent (don't ask me why) in its conversion of boolean values to strings. This in turn caused our reading of the boolean flag whether a function is in fact an aggregate to return incorrect results when evaluating whether it was true. We have now switched to a more robust algorithm, which we couldn't get to fail against any PG version. Fixed.