On Wed, Jul 22, 2020 at 9:27 AM Daniel Verite <daniel@xxxxxxxxxxxxxxxx> wrote:
Mohamed Wael Khobalatte wrote:
> > alter table change_seq alter COLUMN id set data
> > type bigint;
> This is significant downtime, since it locks exclusively, no? We want to
> avoid that.
Well, in the steps you mentioned upthread, the transaction starts by
doing LOCK TABLE some_table, so it will hold an exclusive lock on it
for the rest of the transaction.
If you can test how the ALTER TABLE... SET TYPE ... compares
to your procedure in terms of downtime, that would be interesting.
To me, it's not clear why the procedure in multiple steps would
be better overall than a single ALTER TABLE.
We lock the table as a precaution, with the understanding that we are undergoing a "small" downtime to finish replacing the int id by the new bigint. The only slow thing in my procedure is the sequential scan that the ADD CONSTRAINT does because the column is a primary key. A direct alter table would be far slower, not to mention space requirements?
