dropAll fails in postgres when attempting to drop a dependent view

Description

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.

Environment

postgresql

Activity

Show:

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:

http://www.h2database.com/html/grammar.html#drop_view

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)

Details

Reporter

Components

Affects versions

Priority

Created December 14, 2009 at 3:57 PM
Updated August 1, 2013 at 2:18 PM