Re: Non blocking type change for primary column

Hi,

Le jeu. 14 janv. 2021 à 17:27, Torge Kummerow <tk(at)panaccess(dot)com> a écrit :

> Hi,
>
> I wonder if there is a feature planned (or already available in 11+)
> that allows one to change the column type of a table without making it
> block until finished.
>
> As seemingly many others, we used serial instead of bigserial in our
> tables and some will at a not too distant future hit their limit.
>
> I am currently evaluating our options. One, that seems possible would be
>
> 1. adding a new nullable column of type bigint.
> 2. copying the primary key into this field in small batches
> 3. Once done. rename the primary column and new column, to swap them
> 4. attach the sequence to the new column and deattach it from the old
> column
> 5. Make new column NON NULL (Not sure if this will block)
> 6. Remove primary key from old column, make new column to be the primary
> key (I fear this will block to create the index, but I guess not nearly
> as long as ALTER COLUMN type)
> 7. Delete old column.
>
>
> This could be prevented, if this would be done in a non blocking way by
> the database system itself, so I hope this is already implemented or at
> least on the roadmap.
>
> Any thoughts?
>
>
Nope, the table needs to be re-written to change the 4-byte column into an
8-byte column. And I don't expect to see this anytime soon.

--
Guillaume.