migrate dev and PostGis Views

[zigzactly]

Bug description

Hi there, i am having difficulties running prisma migrate dev on a postgres db with postgis installed.

Looking at past issues with PostGis (#4745), spatial_ref_sys looks like is properly ignored now. But i also have in my database "geometry_columns" and "geography_columns" views which prisma migrate cannot seem to do anything about.

Error: P3016

The fallback method for database resets failed, meaning Migrate could not clean up the database entirely. Original error:
Database error
Error querying the database: db error: ERROR: cannot drop view geography_columns because extension postgis requires it
HINT: You can drop extension postgis instead.
   0: sql_migration_connector::best_effort_reset
           with connection=<connection details>
             at migration-engine/connectors/sql-migration-connector/src/lib.rs:100
   1: migration_core::api::Reset
             at migration-engine/core/src/api.rs:159

Unfortunately i cannot remove the PostGis extension. (ps. i am new to postgis and it is also possible that i have configured PostGis wrongly, as there doesnt seem to be anyone with the same issue)

Is there any place i can define a list of ignored tables / views for migrate?

How to reproduce

1. With PostGis and related views enabled in database
2. Run prisma migrate dev

Expected behavior

No response

Prisma information

Running prisma migrate dev --name init

Environment & setup

• OS: Mac OS
• Database: PostgreSQL
• Node.js version: 14.4.0

Prisma Version

"@prisma/client": "^2.25.0"
"prisma": "^2.25.0"

[pantharshit00]

We can add more exceptions in migrate. Thanks for raising this.

[tomhoule]

Ok I researched this and geometry_columns and spatial_ref_sys used to be tables, but they are views in newer versions of postgis. We have code to ignore these, it just needs to be extended to views. Working on this.

[capaj]

@tomhoule was this released? I am getting this error on prisma 4.0.0

Error: P3016

The fallback method for database resets failed, meaning Migrate could not clean up the database entirely. Original error: 
db error: ERROR: cannot drop view geography_columns because extension postgis requires it
HINT: You can drop extension postgis instead.
   0: sql_migration_connector::best_effort_reset
             at migration-engine/connectors/sql-migration-connector/src/lib.rs:288
   1: migration_core::state::Reset
             at migration-engine/core/src/state.rs:341

[capaj]

Should I open a new issue or can you reopen this one @tomhoule ?

[tomhoule]

@capaj please open a new issue, it's a different view from those that were in the postgis docs we based the fix on (https://postgis.net/docs/manual-1.4/ch04.html#id41859)

[capaj]

created as #14182