0

What potential non-obvious data loss can be caused by DROP VIEW xy CASCADE?

I am using database migrations and as a feature I can have "repeatable migrations" - files containing SQL that get re-run every time after at least one new migration was applied. In my case they usually contain this:

DROP VIEW IF EXISTS x_y;
CREATE VIEW x_y AS ...

(I use this approach to define common views because otherwise I would usually have to find the latest version of the view among older migrations and then repeat it almost verbatim, just with one or two extra columns, and this quickly becomes very verbose and error prone).

Lately I have been getting more and more dependency issues where I have to manually drop views that will get created in later repeatable migrations (otherwise I get the Cannot drop view XY because other objects depend on it error). I don't want to use ALTER VIEW since it doesn't allow me to remove or reorder columns so it's become tempting to simply use DROP VIEW xy IF EXISTS CASCADE but I am worried about hidden data loss. Thinking about what could get dropped:

  • Other views: If other views are dropped it wouldn't be so bad because application integration tests & code gen would catch this (my database objects get generated into type-safe code so a missing view wouldn't get past the compiler as soon as it is used anywhere in the application code).

  • Triggers: this would be potentially bad because a trigger not generated in repeatable migrations could get deleted and not re-created thus cause information loss, although if triggers are covered by integration tests this is manageable

Anything else? Could table data be lost (althought again, application code and unit tests would probably catch this before it gets anywhere near production)? Is there a way data could get wiped during migrations and not noticed in unit & integration tests (as these usually test for functionality, but not pre-existing data)?

Gregor Petrin
  • 2,891
  • 1
  • 20
  • 24
  • I don't think that table data could get lost, because they don't depend on views. You could lose materialized views that depend on other views or functions that use the view data type as return type. – Laurenz Albe Jun 11 '21 at 03:15
  • Do not do this way, use [`CREATE OR REPLACE`](https://www.postgresql.org/docs/12/sql-createview.html) instead – astentx Jun 11 '21 at 05:53
  • What about the problems you've described: do not rely on the order of columns, because it doesn't matter, and always use enumeration of used columns. If the order matters for presentation layer, order the output on the app side, because it is purely display task, not storage or computation – astentx Jun 11 '21 at 05:57
  • I don't rely on the order of columns, except to make the view definition nicer, but this I would be willing to sacrifice; it's the inability to drop columns that's the real showstopper (for using `CREATE OR REPLACE`). – Gregor Petrin Jun 11 '21 at 07:23

1 Answers1

0

Anything else?

Nothing else in my mind but if st else you'll see it with the error with the DROP cde.

Could table data ?

That is the main question, and the fist answer should be No.

That said you can have poor data seting , for instance a computed field updated by a trigger, and you can forget to recreaete it after you change your view(s) ... you may not see this immediately but normaly should be able to correct afterward.

Dri372
  • 1,275
  • 3
  • 13
  • Yes, computed fields & materialized views can be recreated (although it could take a long time, so it's definitely not something to just ignore), triggers can actually lose data (for example audit histories..). The problem is that if I start using `CASCADE` I won't actually see an error, and it could happen at any time, not just when editing those specific migrations (if I inadverently add a dependency to one of these views, for example) – Gregor Petrin Jun 11 '21 at 07:29
  • it could take a long time, ... Yes, and a very lon time st, so it's best to well desgin your db (constrainst, true computed fields if needed, ...) and not use trigger for such cases. – Dri372 Jun 11 '21 at 09:46