dropAll fails in postgres when attempting to drop a dependent view
Description
Environment
postgresql
Activity
Falko Modler August 1, 2013 at 2:18 PM
Piotr,
In the case of H2 (and we are having a problem on h2) – there seems to be no 'CASCADE'.
h2 does support CASCADE:
Piotr Findeisen June 5, 2013 at 2:32 PM
In the case of postgresql – when dropping all objects it won't hurt to use 'CASCADE'.
In the case of H2 (and we are having a problem on h2) – there seems to be no 'CASCADE'. There may be no explicit metadata, but you could use the following simple rule: drop view A before dropping B if A's definition (the SELECT) contains "B" (regex (?i)\bB\b).
Of course, this is only heuristic (one could define view like "CREATE VIEW A AS SELECT "B" AS cheat FROM ...", but should work in most (if not all) reasonable cases.
Nathan Voxland May 22, 2013 at 6:37 AM
I'm not sure we'll be able to determine the order from available metadata but will look when i can
Falko Modler January 22, 2012 at 1:59 PM
Workaround: I ended up dropping the views other views depend on with CASCADE before calling dropAll.
Falko Modler September 26, 2011 at 3:34 PM
I ran into the very same problem when I tried to update h2 database from 1.2 to 1.3. In 1.3, h2 checks for dependency violations which seem to be caused by liquibase.
Liquibase version: 2.0 or 2.0.1 (could not find 2.0.2 via Maven)
I ran into this problem when I defined two views, one of which is defined in terms of the other. For example, assume viewB depends on viewA. If liquibase attempts to drop viewA first, then the dropAll operation will fail. I worked around this problem by rewriting the view so as to remove the dependency. Note that I don't encounter this problem on oracle, derby, or hsqldb.