ERROR: cannot drop view csw_breweryanalytics because other objects depend on it

[gregorwolf]

Hi @mikezaschka,

I've just discovered an issue in with cds-dbm with the DB schema of my pg-beershop example. There I've added entity BreweryAnalytics as projection on Brewery. This results in the view csw_breweryanalytics. This view is used in the service layer. The first deploy with cds-dbm works just fine. But when I do another deploy I get the error:

Error: Command failed: /home/gwolf/projects/pg-beershop/node_modules/cds-dbm/liquibase/liquibase --username=postgres --password=postgres --url=jdbc:postgresql://localhost:5432/beershop --classpath=/home/gwolf/projects/pg-beershop/node_modules/cds-dbm/dist/adapter/../../drivers/postgresql-42.2.8.jar --driver=org.postgresql.Driver --changeLogFile=tmp/_autodeploy.json update 

    at ChildProcess.exithandler (child_process.js:308:12)
    at ChildProcess.emit (events.js:314:20)
    at maybeClose (internal/child_process.js:1021:16)
    at Process.ChildProcess._handle.onexit (internal/child_process.js:286:5) {

When I execute the Liquibase command that is mentioned in the error I see:

gwolf@penguin:~/projects/pg-beershop$ /home/gwolf/projects/pg-beershop/node_modules/cds-dbm/liquibase/liquibase --username=postgres --password=postgres --url=jdbc:postgresql://localhost:5432/beershop --classpath=/home/gwolf/projects/pg-beershop/node_modules/cds-dbm/dist/adapter/../../drivers/postgresql-42.2.8.jar --driver=org.postgresql.Driver --changeLogFile=tmp/_autodeploy.json update 
Liquibase Community 4.1.0 by Datical
####################################################
##   _     _             _ _                      ##
##  | |   (_)           (_) |                     ##
##  | |    _  __ _ _   _ _| |__   __ _ ___  ___   ##
##  | |   | |/ _` | | | | | '_ \ / _` / __|/ _ \  ##
##  | |___| | (_| | |_| | | |_) | (_| \__ \  __/  ##
##  \_____/_|\__, |\__,_|_|_.__/ \__,_|___/\___|  ##
##              | |                               ##
##              |_|                               ##
##                                                ## 
##  Get documentation at docs.liquibase.com       ##
##  Get certified courses at learn.liquibase.com  ## 
##  Get support at liquibase.com/support          ##
##                                                ##
####################################################
Starting Liquibase at 10:42:29 (version 4.1.0 #3 built at 2020-09-28 21:02+0000)
Unexpected error running Liquibase: Migration failed for change set tmp/_autodeploy.json::1607247729779-10::gwolf (generated):
     Reason: liquibase.exception.DatabaseException: ERROR: cannot drop view csw_breweryanalytics because other objects depend on it
  Detail: view beershopservice_breweryanalytics depends on view csw_breweryanalytics
  Hint: Use DROP ... CASCADE to drop the dependent objects too. [Failed SQL: (0) DROP VIEW public.csw_breweryanalytics]
For more information, please use the --logLevel flag

Looking forward for a fix.

CU
Gregor

[vobu]

might not be an issue with cds-dbm. had a similar issue w/o it - root cause was the docker volume. manually deleted it, all good afterwards.

[mikezaschka]

It's an issue with cds-dbm:
Internally I am creating a liquibase changelog, that I have to manually reorder before deploying to the database. It seems, that dependent views are not being handled correctly when they are dropped. I am already checking for references when creating views and I am going to add this kind of check for drop statements as well.

Relevant code part is here:
https://github.com/mikezaschka/cds-dbm/blob/main/src/ChangeLog.ts#L35

[mikezaschka]

Reference commit: 41cd76a

@gregorwolf
The issue should be fixed in version 0.0.20. I am now safely handling cascading views on create and drop. Tested also with your beershop and worked for me.

[mikezaschka]

It should also work with the latest @sap/cds releases (4.4.4 and 4.4.6).

[gregorwolf]

Thank you. Works now like a charm.