4

I have a large, partitioned table with a field in that needs to upgraded from INT to BIGINT.

My intention is to detach each partition, make the change and then re-attach, using something like this:

ALTER TABLE testTable DETACH PARTITION testTable_201908; -- Should be very quick
ALTER TABLE testTable_201908 ALTER COLUMN recordId TYPE bigint;  -- likely to take ages!
ALTER TABLE testTable_201908 ADD CONSTRAINT con_201908 CHECK ( loadedTime >= DATE '2019-08-01' AND loadedTime < DATE '2019-08-01' ); -- Likely to take ages.
ALTER TABLE testTable ATTACH PARTITION testTable_201908 FOR VALUES FROM ('2019-08-01') TO ('2008-09-01' ); -- Should be quick with the constraint in place.
ALTER TABLE testTable_201908 DROP CONSTRAINT con_201908; -- Should be quick

The table is really big (about 100 millions records per partition), dozens of partitions. I've done some testing, DETACH PARTITION is really quick, ALTER COLUMN recordId TYPE bigint takes ages (but I don't think there's anything I can do about this)

Then I have a choice, if I just re-attach the partition to the parent table (without a constraint) then that takes ages whilst the database checks integrity (at also seems to lock-up the parent table for the duration). OR, I can add a constraint (takes hours) and then re-attach to the parent (takes seconds only)

Is there a way I can force the re-attachment of the partition without checking integrity and without requiring a constraint? After-all, I know the partition was valid before I detached it, and I know I haven't altered any of the relevant data?

1 Answer 1

4

You have to change all partitions at once, because the column data type of the partitioned table has to be the same as the column data type in the partitions.

One option is to add a new bigint column, fill it with values from the old column and then drop the old column.

This will take a while, but requires only short ACCESS EXCLUSIVE locks on the table.

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.