Re: change bigint column to int

You are talking about running alter table x alter column type int; Indeed I
agree with you that it will have an AccessExclusiveLock on the entire
lock. But what if instead I'm doing all the next steps :
1.add a new column
2.update the values in the new column
3.drop constraints on the old column
4.drop old column
5.rename new column to be the old one
6.add new constraints on the new col

I think that in this case the only step that will require AccessExclusiveLock
will be at step 5/6 while in the other steps it will require
RowExclusiveLock.

What do you think ?

‫בתאריך יום ה׳, 1 בנוב׳ 2018 ב-13:08 מאת ‪Shreeyansh Dba‬‏ <‪
shreeyansh2014(at)gmail(dot)com‬‏>:‬

> Hi Mariel,
>
> After changing the column bigint to int size get reduced.
>
> But it has two huge and interrelated problems: locking and time. This
> approach locks the table for as long as the command takes to run. And by
> lock, taking a heavy “access exclusive” lock which shuts everything else
> out of the table. If your table is small, this is not an issue.
>
> <http://www.shreeyansh.com>
>
>
> On Thu, Nov 1, 2018 at 3:51 PM Mariel Cherkassky <
> mariel(dot)cherkassky(at)gmail(dot)com> wrote:
>
>> Hi,
>>
>> I want to change the column type from bigint to int to save space. By
>> doing so, does it include some risks ? As I see it, I should only run
>> vacuum full afterwards if I want to return space to the os right ?
>>
>>