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.
idon 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
- Perform the following in a transaction to transfer the
new_idcolumn to be the primary key, drop the old
idcolumn, and rename
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.
new_idcolumn where this data is needed. - After all of the views are rebuilt, perform the steps in the guide. - Change the view query definitions back to using
idbefore the next build cycle. Will this work? Will I be able to rename the column while the materialized views depend on it?