[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-sql
Subject:    Re: Non blocking type change for primary column
From:       Mohamed Wael Khobalatte <mkhobalatte () grubhub ! com>
Date:       2021-01-16 5:18:39
Message-ID: CABZeWdxsitbHGhj2PahAc64HnB-BD7i3njok8gwh7FV_4Bft6A () mail ! gmail ! com
[Download RAW message or body]

> 
> 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?


Hye Torge,

I was recently involved with such a change. See this thread[1]  for
discussion. In particular, it touches on point 5, where you ask what's
blocking. Hope it helps.

[1]
https://www.postgresql.org/message-id/flat/CABZeWdybDVTgCQFZf0jDtj0p4yLxNrYd8aw61iFxY-Xt%2BfqPkw%40mail.gmail.com



[Attachment #3 (text/html)]

<div dir="ltr"><blockquote class="gmail_quote" style="margin:0px 0px 0px \
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">1. adding a new \
nullable column of type bigint.<br> 2. copying the primary key into this field in \
small batches<br> 3. Once done. rename the primary column and new column, to swap \
them<br> 4. attach the sequence to the new column and deattach it from the old \
column<br> 5. Make new column NON NULL (Not sure if this will block)<br>
6. Remove primary key from old column, make new column to be the primary<br>
key (I fear this will block to create the index, but I guess not nearly<br>
as long as ALTER COLUMN type)<br>
7. Delete old column.<br><br>
This could be prevented, if this would be done in a non blocking way by<br>
the database system itself, so I hope this is already implemented or at<br>
least on the roadmap.<br>
Any thoughts?</blockquote><div><br>Hye Torge,<br><br>I was recently involved with \
such a change. See this thread[1]   for discussion. In particular, it touches on \
point 5, where you ask what&#39;s blocking. Hope it helps.  <br><br>[1]  <a \
href="https://www.postgresql.org/message-id/flat/CABZeWdybDVTgCQFZf0jDtj0p4yLxNrYd8aw6 \
1iFxY-Xt%2BfqPkw%40mail.gmail.com">https://www.postgresql.org/message-id/flat/CABZeWdybDVTgCQFZf0jDtj0p4yLxNrYd8aw61iFxY-Xt%2BfqPkw%40mail.gmail.com</a></div>



</div>



[prev in list] [next in list] [prev in thread] [next in thread]