Clean fails when Postgres DOMAIN depends on SEQUENCE

[skissane]

What version of Flyway are you using?

Flyway 4.2.0

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

Reproduced issue with command line client.
(But I am using Java API in my code)

What database are you using (type & version)?

PostgreSQL 9.6.5

What operating system are you using?

macOS 10.12.6

What did you do?

(Please include the content causing the issue, any relevant configuration settings, and the command you ran)

1. Create following migration V1__init.sql

CREATE SEQUENCE foo_seq;
CREATE DOMAIN foo_seq_dom AS BIGINT NOT NULL DEFAULT nextval('foo_seq');

2. Run "flyway migrate"
3. Run "flyway clean"

What did you expect to see?

"flyway clean" successfully cleans schema

What did you see instead?

Database: jdbc:postgresql://localhost/flybug (PostgreSQL 9.6)
ERROR:
Unable to clean schema "public"

SQL State : 2BP01
Error Code : 0
Message : ERROR: cannot drop sequence foo_seq because other objects depend on it
Detail: type foo_seq_dom depends on sequence foo_seq
Hint: Use DROP ... CASCADE to drop the dependent objects too.

[skissane]

The problem is here:

flyway/flyway-core/src/main/java/org/flywaydb/core/internal/dbsupport/postgresql/PostgreSQLSchema.java

Line 87 in ac266f4

for (String statement : generateDropStatementsForSequences()) {

PostgreSQLSchema.doClean() is cleaning sequences before domains.

It should clean sequences after domains since domains can depend on sequences via the DEFAULT clause.

[skissane]

Workaround: in beforeClean.sql do this:

DROP SEQUENCE IF EXISTS foo_seq CASCADE;

This will drop the sequence and everything that depends on it (including the domain depending on it, and any types/tables/routines depending on that domain).

[skissane]

This is a more generic workaround, it works no matter what the sequences are named.

Add the following to beforeClean.sql:

DO LANGUAGE plpgsql $$ DECLARE
  v_sql TEXT;
BEGIN
  FOR v_sql IN (SELECT FORMAT('DROP SEQUENCE %I CASCADE', SEQUENCE_NAME)
                FROM INFORMATION_SCHEMA.SEQUENCES
                WHERE SEQUENCE_SCHEMA = CURRENT_SCHEMA) LOOP
    EXECUTE v_sql;
  END LOOP;
END;
$$;

[axelfontaine]

Good catch and thank you very much for the investigation! Fixed.