Error: drop "<view_name>" view: pq: cannot drop view <view_name> because other objects depend on it

First I am very new to this project and even this approach to managing database migrations. (I started using atlas yesterday.) So far I'm very happy! I did a quick search for related issues. I found https://github.com/ariga/atlas/issues/1523, but I'm not sure if it's actually related or not. I apologize if this is a duplicate. By the way I am not a Go developer. I'm using atlas in a Node/Next project.

Background

I defined a schema.hcl file with a couple views in it (latest_versions and versioned_items). When I ran the migration, it errored because the views were re-ordered from the source I defined them in. The versioned_items depends on latest_versions. No worries as I found the depends_on option for defining versioned_items. Here's the relevant portions from my schema.hcl:

view "latest_versions" {
  schema = schema.public
  as     = <<-SQL
    SELECT
      *,
      ROW_NUMBER() OVER (
        PARTITION BY edition
        ORDER BY released_on DESC
      ) = 1 AS is_latest,
      ROW_NUMBER() OVER (
        PARTITION BY edition, cycle
        ORDER BY released_on DESC
      ) = 1 AS is_latest_in_cycle
    FROM versions
  SQL
}

view "versioned_items" {
  schema     = schema.public
  depends_on = [view.latest_versions]
  as         = <<-SQL
    SELECT
      v.id AS version_id,
      v.edition,
      v.version,
      v.cycle,
      v.is_latest,
      i.*
    FROM items AS i
      INNER JOIN item_versions AS iv ON i.id = iv.item_id
      INNER JOIN latest_versions AS v ON iv.version_id = v.id
  SQL
}

Later I found I needed to a new field to versioned_items. I added the new field and re-ran atlas. Here's the diff:

diff --git a/src/services/datastore-service/schema.hcl b/src/services/datastore-service/schema.hcl
index e4f708d..d9e480c 100644
--- a/src/services/datastore-service/schema.hcl
+++ b/src/services/datastore-service/schema.hcl
@@ -426,6 +426,7 @@ view "versioned_items" {
       v.version,
       v.cycle,
       v.is_latest,
+      v.is_latest_in_cycle,
       i.*
     FROM items AS i
       INNER JOIN item_versions AS iv ON i.id = iv.item_id

Issue

Here's the error I got:

Error: drop "latest_versions" view: pq: cannot drop view latest_versions because other objects depend on it

Examining the planned changes made it obvious: latest_versions was dropped and recreated before versioned_items was dropped and recreated. Here's the relevant portion of the planned changes:

-- Drop "latest_versions" view
DROP VIEW "public"."latest_versions";
-- Create "latest_versions" view
CREATE VIEW "public"."latest_versions" AS SELECT
  *,
  ROW_NUMBER() OVER (
    PARTITION BY edition
    ORDER BY released_on DESC
  ) = 1 AS is_latest,
  ROW_NUMBER() OVER (
    PARTITION BY edition, cycle
    ORDER BY released_on DESC
  ) = 1 AS is_latest_in_cycle
FROM versions;
-- Drop "versioned_items" view
DROP VIEW "public"."versioned_items";
-- Create "versioned_items" view
CREATE VIEW "public"."versioned_items" AS SELECT
  v.id AS version_id,
  v.edition,
  v.version,
  v.cycle,
  v.is_latest,
  v.is_latest_in_cycle,
  i.*
FROM items AS i
  INNER JOIN item_versions AS iv ON i.id = iv.item_id
  INNER JOIN latest_versions AS v ON iv.version_id = v.id;

Solution?

I seems to me that edits to views should drop dependent views (recursively) before dropping the view being edited. This might result in some views being dropped/recreated multiple times, so maybe it would be best to drop all target views first, then recreate them.

In my case the planned changes should look like the following. Notice how views are dropped in reverse dependency order, then created in dependency order:

-- Drop "versioned_items" view
DROP VIEW "public"."versioned_items";
-- Drop "latest_versions" view
DROP VIEW "public"."latest_versions";
-- Create "latest_versions" view
CREATE VIEW "public"."latest_versions" AS SELECT
  *,
  ROW_NUMBER() OVER (
    PARTITION BY edition
    ORDER BY released_on DESC
  ) = 1 AS is_latest,
  ROW_NUMBER() OVER (
    PARTITION BY edition, cycle
    ORDER BY released_on DESC
  ) = 1 AS is_latest_in_cycle
FROM versions;
-- Create "versioned_items" view
CREATE VIEW "public"."versioned_items" AS SELECT
  v.id AS version_id,
  v.edition,
  v.version,
  v.cycle,
  v.is_latest,
  v.is_latest_in_cycle,
  i.*
FROM items AS i
  INNER JOIN item_versions AS iv ON i.id = iv.item_id
  INNER JOIN latest_versions AS v ON iv.version_id = v.id;

Alternatively the commands to DROP/CREATE views should be wrapped in constraint defer commands so the database doesn't error out.

Work-around

For now I can work around this issue by dropping my database and recreating it, as I haven't pushed any of these changes to production. However that wouldn't work if I had deployed to production between initially creating the views and later editing versioned_items.

Edit: typo

ariga / atlas