I have a table with many dependancies such as: functions, views and materialised views. I'd like to update a column from an int to a bigint.

The issue I have is that doing so requires dropping a large number of dependancies in order and then re-building them in order.

Is it possible to turn the dependancy ladder off while I alter the column, update the required views/functions then re-enable it all.

This is coming from an Oracle background where it's perfectly fine to have temporarily invalid dependancies which makes migrations over various DB's environments a breeze.

In Postgresql I'm finding the process is very orchestrated and time consuming to engineer, whereas in Oracle I can just update all the columns & dependancies irrespective of order.

  • You don't need to recreate functions, indexes or foreign keys. Just views (with permissions) should be recreated. But because of data type change, the functions related with that field won't work anymore. – Sahap Asci Apr 17 '19 at 23:39
  • @SahapAsci when the functions depend on the data type it's not possible to alter the table without first dropping the functions, therefore they must be recreated. – Alexander De Sousa Apr 18 '19 at 7:34
  • well, its possible, maybe you are missing something or I misunderstand the case. check here. it works / tested – Sahap Asci Apr 18 '19 at 14:51
  • It is a somewhat unpleasant restriction, I agree. We manage that by simply dropping all functions and views before applying any "real" DDL migrations. If everything is finished we re-create all of them. As we do all this through Liquibase, we only need to setup the changelog script once properly, and then never think about it. The only drawback are of course MVIEWs as they might take a while to populate, but we don't usually use them, so it' not a big deal. – a_horse_with_no_name Apr 23 '19 at 7:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.