`atlas schema diff` erroring with materialized view

[Adam-Mustafa]

I'm running a local postgres in docker and getting an error when I try to run to run a atlas schema diff.

Error: materialized view "table_name" was not found in schema "public"

CMD:

atlas schema diff \
  --from "postgres://USER:PASS@HOST:5432/DBNAME?sslmode=disable" \
  --to "postgres://root:root@localhost:5432/atlasdbtarget?sslmode=disable"

Version: atlas version v0.15.1-940c07f-canary System: Ubuntu 22.04.3 LTS Database: Postgres 14.5 (Debian 14.5-1.pgdg110+1)

As described in this issue, downgrading got rid of the error.

[a8m]

Thanks for reporting this, @Adam-Mustafa. Do you have any schema that can reproduce this issue?

[Adam-Mustafa]

@a8m

Trying to reduce this revealed that it has something to do with the interactions with views. Copy this schema in postgres and run the diff against and empty db with the empty db as the --from and the schema below as the --to.

CREATE TABLE "public"."transactions" ("id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY, "account_id" uuid NOT NULL, PRIMARY KEY ("id"));

create MATERIALIZED VIEW "test_mv"  AS
SELECT t.account_id                                                          AS id,
       t.account_id,
       NOW()                                                                 AS last_refresh
FROM transactions t
GROUP BY t.account_id;

CREATE VIEW test_v AS
SELECT
    tv.id AS id
FROM
    transactions AS tv

Despite the error occurring on the materialized view, it doesn't happen without the view being there.

Also, when the view is gone and the script does work the materialized view is not present in the output. Not sure if that is known or not:

atlas schema diff \
  --from "postgres://root:root@localhost:5432/target?sslmode=disable" \
  --to "postgres://root:root@localhost:5432/atlasmigratedb?sslmode=disable"

-- Create "transactions" table
CREATE TABLE "public"."transactions" ("id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY, "account_id" uuid NOT NULL, PRIMARY KEY ("id"));

When I downgrade to v0.14, the materialized view does appear (as does the view):

atlas schema diff \
  --from "postgres://root:root@localhost:5432/target?sslmode=disable" \
  --to "postgres://root:root@localhost:5432/atlasmigratedb?sslmode=disable"

-- Create "transactions" table
CREATE TABLE "public"."transactions" ("id" bigint NOT NULL GENERATED BY DEFAULT AS IDENTITY, "account_id" uuid NOT NULL, PRIMARY KEY ("id"));
-- Create "test_mv" view
CREATE MATERIALIZED VIEW "public"."test_mv" ("id", "account_id", "last_refresh") AS SELECT t.account_id AS id,
    t.account_id,
    now() AS last_refresh
   FROM transactions t
  GROUP BY t.account_id;

[fabianmodig]

I have the same problem when running the following:

atlas schema inspect -u 'postgres://USER:PASSWORD@localhost:5432/DATABASE_NAME?sslmode=disable'

Error: materialized view "MATERIALIZED_VIEW_NAME" was not found in schema "SOME_SCHEMA_IN_DB"

I can try version 14 aswell and see if that makes any difference.

[utsav-upendra]

You found any solution for this @fabianmodig ?

[a8m]

Hey all! Materialized views require login to Atlas as mentioned here: https://atlasgo.io/atlas-schema/hcl#materialized-view

Can you try login in to Atlas and checking this? I'll also send a patch to disable views in such cases.