2

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.

3
  • One idea I have: - before doing the switchover, change the materialized views to use the replicated new_id column 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 id before the next build cycle. Will this work? Will I be able to rename the column while the materialized views depend on it?
    – Doug Mill
    Jan 7, 2023 at 1:02
  • Useful read making.lyst.com/2020/05/26/…
    – Sotis
    Jan 23, 2023 at 16:07
  • We wound up deciding just to incur downtime in order to perform this update. It seems it is impossible to perform this kind of update while the dependent materialized views exist - there is no way to remove the constraint. I suppose if downtime was entirely out of the question, one could transfer the contents of the view to a table, switch all consumers to read from the table rather than the view, perform the update, rebuild and refresh the view, and then switch consumers back to the view. This was way too much work for us so we just ate a few hours of downtime.
    – Doug Mill
    Jan 24, 2023 at 16:41

1 Answer 1

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.