Deepak Mahto runs into a common issue:
Instead of doing a straight:
ALTER TABLE ... ADD COLUMN ... DEFAULT ... NOT NULL;we chose the commonly recommended performance approach:
- Create a new table (optionally UNLOGGED),
- Copy the data,
- Rename/swap the tables.
This approach is widely used to avoid long-running locks and table rewrites but it comes with hidden gotchas. This post is about one such gotcha: object dependencies, especially views, and how PostgreSQL tracks them internally using OIDs.
This also happens in SQL Server, though we do have a helpful sp_refreshview procedure that means not needing to drop + recreate views.