Liquibase, Postgres and Views.

[Rupert Smith]

There is an issue with Liquibase being unable to drop database views (on postgresql, others too possible):

https://liquibase.jira.com/browse/CORE-2268

It does not figure out what order to drop the views in, and when one view is dependant on another, it can fail to drop a view because others depend on it. 3.5.0 is cited as the next version of liquibase that will fix this, but DW 0.9.1 is on the current latest version 3.4.1, where this is broken.

Has anyone got a workaround for this issue?

I currently put some code like this in a script and run it before building the database from scratch:

psql -c "DROP DATABASE IF EXISTS somedb"

psql -c "DROP USER IF EXISTS somedb"

psql -c "CREATE USER somedb WITH PASSWORD 'somedb'"

psql -c "CREATE DATABASE somedb"

psql -c "GRANT ALL PRIVILEGES ON DATABASE somedb TO somedb"

However, this needs my local user to have superuser rights on postgres, ok for a script on my machine, but I need something more robust for running on automated build and integration environments.

I am thinking maybe one of the solutions here:

http://stackoverflow.com/questions/12075582/dropping-all-views-in-postgresql

Just execute this SQL using JDBC:

SELECT 'DROP VIEW ' || table_name || ';'
  FROM information_schema.views
 WHERE table_schema NOT IN ('pg_catalog', 'information_schema')
   AND table_name !~ '^pg_';

I could set up a 'command' to run the above, and run it each time before invoking a drop-all/migrate cycle. The drop-views/drop-all/migrate cycle would also likely be run prior to each integration test class.

Any thoughts or other workarounds?

Rupert

[Rupert Smith]

On Tuesday, November 17, 2015 at 10:49:52 AM UTC, Rupert Smith wrote:

There is an issue with Liquibase being unable to drop database views (on postgresql, others too possible):

https://liquibase.jira.com/browse/CORE-2268

Anyone know if flyway has this problem too? 

[Nasir Rasul]

It doesn't sound like a bug. Views aren't getting dropped because it would violate a relation.

Does the drop views cascade syntax work (as mentioned in the bug)?

Additionally, you can run SQL in liquifies changeset. Do either of the two work for you, or is it unacceptable for you?

Cheers

- Nasir

--
You received this message because you are subscribed to the Google Groups "dropwizard-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to dropwizard-us...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Rupert Smith]

On Tuesday, November 17, 2015 at 2:42:01 PM UTC, Nasir Rasul wrote:

It doesn't sound like a bug. Views aren't getting dropped because it would violate a relation.

Its a bug because the "db drop-all --confirm-delete-everything" does not work.

 

Does the drop views cascade syntax work (as mentioned in the bug)?

Yes, drop view with a cascade works but there is no option in DW or liquibase to use this. You have to do it yourself. 

 

Additionally, you can run SQL in liquifies changeset. Do either of the two work for you, or is it unacceptable for you?

You cannot put the drop cascade in the migration SQL changeset yourself, as it does not work. The reason being the liquibase fails before it gets as far as processing the changeset.

I wrote my own "drop-views" command. Here is the SQL I am running with it:

public static final String DROP_VIEWS_SQL =

        "DO $$DECLARE r record;\n" +

        "DECLARE s TEXT;\n" +

        "BEGIN\n" +

        "    FOR r IN select table_schema,table_name\n" +

        "        from information_schema.views\n" +

        "        where table_schema = 'public'\n" +

        "    LOOP\n" +

        "        s := 'DROP VIEW IF EXISTS ' ||  quote_ident(r.table_schema) || '.'\n" +

        "             || quote_ident(r.table_name) || ' CASCADE ;';\n" +

        "        EXECUTE s;\n" +

        "        RAISE NOTICE 's = % ',s;\n" +

        "    END LOOP;\n" +

        "END$$;";

A combination of IF EXISTS and CASCADE means you don't have to care about the delete order.

Rupert