What is the best strategy for changing a primary key from int to bigint in postgres when the column has dependencies?

Question

We have a few primary key id columns in our database with type integer that will soon overflow. We need to change the types of these columns to bigint with zero downtime. In order to do this, we've been following this guide, which has worked pretty well on a few tables. To summarize the guide, these are the steps:

• add a new column with type bigint (e.g. new_id)
• sync new_id with id on updates and creates with a triggered procedure
• backfill all of the old rows using some series of scheduled jobs
• create a new unique index with a NOT NULL constraint on new_id
• Perform the following in a transaction to transfer the new_id column to be the primary key, drop the old id column, and rename new_id to id:

BEGIN TRANSACTION;

-- explicitly lock the table against other changes (safety)
LOCK TABLE [table] IN EXCLUSIVE MODE;

-- drop and create the PK using existing index
ALTER TABLE [table] DROP CONSTRAINT [table]_pkey, ADD CONSTRAINT [table]_pkey PRIMARY KEY USING INDEX new_id_unique;

-- transfer the sequence
ALTER SEQUENCE [table]_id_seq OWNED BY [table].new_id;
ALTER TABLE [table] ALTER COLUMN new_id SET DEFAULT nextval('[table]_id_seq');

-- drop and rename the columns
ALTER TABLE [table] DROP COLUMN id;
ALTER TABLE [table] RENAME COLUMN new_id TO id;

-- drop the temporary trigger and procedure
DROP TRIGGER IF EXISTS set_new_id_trigger ON [table];
DROP FUNCTION IF EXISTS set_new_id();

COMMIT;

We are now onto a table where we have a few materialized views that are built using the primary key. This means that when we get to the line ALTER TABLE [table] DROP COLUMN id;, this fails with an error like

ERROR:  cannot drop column id of table some_table because other objects depend on it
DETAIL:  materialized view some_mat_view depends on column id of table some_table
...
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

What is the best strategy for us to move forward from here without nuking these dependent materialized views? The views are rebuilt daily.

Answer 1

I believe the answer here is that it's not possible to perform this kind of update while these dependent views exist.

As stated in my comment, one strategy for zero downtime would be to transfer the materialized views' contents to tables to read from while performing the update, but the technical answer to my original question is that it's not possible.